isitdevops.com/databases

— usr_privs.sql
— mdw 11/10/01
— select what privs a user has. Will need to be done in stages,
— selecting what sys privs a user has, what roles they have and what
— privs the role has. Hmmmm, this might be easier with PL/SQL
—
set pause on pages 32
spool usr_privs.lst
col grantee form a20 word wrap
col type    form a4 
col privilege form a40 word wrap
col admin_option form a3  head adm
accept usr_name char prompt ‘enter user whos privs you wish to see> ‘
select grantee, ‘SYSP’   type, privilege, admin_option from dba_sys_privs where grantee like nvl(upper(‘&usr_name’),’WHOOPS’)||’%’
union
select grantee, ‘ROLE’   type, granted_role    privelege, admin_option from dba_role_privs where grantee like nvl(upper(‘&usr_name’),’WHOOPS’)||’%’
union
select grantee, ‘OBJP’   type, owner||’.’||table_name||’-‘||privilege    privelege, grantable admin_option from dba_tab_privs where grantee like nvl(upper(‘&usr_name’),’WHOOPS’)||’%’
order by 1,2 desc,3
/
—
— MDW BELOW WILL NOT WORK BECAUSE ORACLE WON’T TREE WALK A VIEW WITH A
–     DISTINCT OR GROUP BY IN IT. ARSE
–select
–grantee
–,level   lvl
–,lpad(‘ ‘,2*level)||granted_role    privelege
–,admin_option
–from dba_role_privs
–connect by prior granted_role = grantee
–start with grantee like nvl(upper(‘&usr_name’),’WHOOPS’)||’%’
–order by 1
–/
—
— now an attempt to get all of the system privs granted via the roles
—
clear colu
spool off
— eof
—