wrapper to a main SQL script to pass each username from DBA_USER

I have a SQL script that lists for a specific user all the roles and other privileges granted

I need a wrapper so that, I can pass each user from the dba_users.username to the main script which looks like

-- Main script for single User
set lines 100 pages 1000 ver off
col grantedto for a15
col priv form a75 heading "PRIVILEGE" wrap

define usertolist = '&userid'                -- This is where I need help

select * from
(
.....
union
.....
union
......
)
order by1;


Thanks
Greens8301Asked:
Who is Participating?
 
slightwv (䄆 Netminder) Commented:
I'm confused.  If you want one report for ALL users, just comment out the 'grantee like' pieces?
set lines 110 pages 1000 ver off 

col grantee for a15
col pv for a75 hea 'PRIVILEGE OR ROLE' wrap

---bre on grantee on type skip 1

--define usercheck = '&userid'

select * from
(
select grantee
, 'ROL' type
, granted_role pv 
from dba_role_privs 
--where grantee like '&usercheck' 
union 
select grantee
, 'PRV' type
, privilege pv 
from dba_sys_privs 
--where grantee like  '&usercheck' union
union
select grantee
, 'OBJ' type, 
regexp_replace(max(decode(privilege,'WRITE','WRITE,'))||
max(decode(privilege,'READ','READ,'))||
max(decode(privilege,'EXECUTE','EXECUTE')),'WRITE,READ,EXECUTE','ALL')||
regexp_replace(max(decode(privilege,'SELECT','SELECT'))||
max(decode(privilege,'DELETE',',DELETE'))||
max(decode(privilege,'UPDATE',',UPDATE'))||
max(decode(privilege,'INSERT',',INSERT')),'SELECT,DELETE,UPDATE,INSERT','ALL')||
' ON '||object_type||' "'||a.owner||'"."'||table_name||'"' pv 
from dba_tab_privs a
, dba_objects b 
where a.owner=b.owner 
and a.table_name = b.object_name 
--and a.grantee like '&usercheck'
group by a.owner
, table_name
, object_type
, grantee 
union
select grantee
, 'COL' type, 
privilege||' ('||column_name||') ON "'||owner||'"."'||table_name||'"' pv 
from dba_col_privs
--where grantee like '&usercheck'
union
select username grantee
, '---' type
, 'empty user ---' pv 
from dba_users 
where not username in (select distinct grantee from dba_role_privs) 
and not username in (select distinct grantee from dba_sys_privs) 
and not username in (select distinct grantee from dba_tab_privs) 
--and username like '%&usercheck%'
group by username
order by grantee
, type
, pv) where grantee in (
select role as grantee from dba_roles where role in ('NDS_RO','NDS_RW','NRS_DM_RO','NRS_DM_RW')
union all
select username as grantee from dba_users where username not in  (select username from dba_users where trunc(created) = (select trunc(created) from dba_users where username = 'SYS'))
) order by 1;

Open in new window

0
 
sdstuberCommented:
sqlplus doesn't really have a procedural language.

but if you use pl/sql, then you can't invoke other sqlplus scripts (not without a lot of jumping through hoops)

you "could" create a shell script that runs a query from sqlplus and reads the output and invokes sqlplus again for each user.

Best solution would probably be to have your existing script recognize a keyword like "--ALL--"  and query dba_users for you and iterate

can you post more of your existing script and I can help you modify it
0
 
sdstuberCommented:
even easier,  put all your logic in a pl/sql package.  Then simply call the package from sqlplus, your script could collapse to being just one line
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
slightwv (䄆 Netminder) Commented:
Do you want a separate report for 'each'?

Just join the queries to dba_users and set up the sqlplus report to break on each individual username.
0
 
Greens8301Author Commented:
Below is the query for a single user

Thanks for your help

==================================

set lines 110 pages 1000 ver off

col grantee for a15
col pv for a75 hea 'PRIVILEGE OR ROLE' wrap

---bre on grantee on type skip 1

define usercheck = '&userid'

select * from
(
select grantee
, 'ROL' type
, granted_role pv
from dba_role_privs
where grantee like '&usercheck'
union
select grantee
, 'PRV' type
, privilege pv
from dba_sys_privs
where grantee like  '&usercheck' union
select grantee
, 'OBJ' type,
regexp_replace(max(decode(privilege,'WRITE','WRITE,'))||
max(decode(privilege,'READ','READ,'))||
max(decode(privilege,'EXECUTE','EXECUTE')),'WRITE,READ,EXECUTE','ALL')||
regexp_replace(max(decode(privilege,'SELECT','SELECT'))||
max(decode(privilege,'DELETE',',DELETE'))||
max(decode(privilege,'UPDATE',',UPDATE'))||
max(decode(privilege,'INSERT',',INSERT')),'SELECT,DELETE,UPDATE,INSERT','ALL')||
' ON '||object_type||' "'||a.owner||'"."'||table_name||'"' pv
from dba_tab_privs a
, dba_objects b
where a.owner=b.owner
and a.table_name = b.object_name
and a.grantee like '&usercheck'
group by a.owner
, table_name
, object_type
, grantee
union
select grantee
, 'COL' type,
privilege||' ('||column_name||') ON "'||owner||'"."'||table_name||'"' pv
from dba_col_privs
where grantee like '&usercheck'
union
select username grantee
, '---' type
, 'empty user ---' pv
from dba_users
where not username in (select distinct grantee from dba_role_privs)
and not username in (select distinct grantee from dba_sys_privs)
and not username in (select distinct grantee from dba_tab_privs)
and username like '%&usercheck%'
group by username
order by grantee
, type
, pv) where grantee in (
select role as grantee from dba_roles where role in ('NDS_RO','NDS_RW','NRS_DM_RO','NRS_DM_RW')
union all
select username as grantee from dba_users where username not in  (select username from dba_users where trunc(created) = (select trunc(created) from dba_users where username = 'SYS'))
) order by 1;
0
 
Greens8301Author Commented:
I want one report for all users in a database and I am planning on exporting the output to a excel file

Thanks
0
 
sdstuberCommented:
or wildcard it with a keyword like I mentioned above


and (a.grantee like '&usercheck' or '&usercheck' ='--ALL--')
0
 
sdstuberCommented:

or let the absence of a username be the key


and (a.grantee like '&usercheck' or '&usercheck' is NULL)
0
 
slightwv (䄆 Netminder) Commented:
>>or wildcard it with a keyword like I mentioned above


When prompted could you not just enter a % instead of a delimiter or null?
0
 
sdstuberCommented:
yeah, since it's "like" then % will be the keyword
0
 
Greens8301Author Commented:
Great , Quck Respnse

Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.