How to find all user Roles and Privileges?

Here is the scripts to help you to get this information:

PROMPT 

PROMPT 

PROMPT ******************************************** ROLES AND PRIVILEGES 

PROMPT 

PROMPT ******************************************** USER ROLES 

SELECT grantee user, granted_role, admin_option, default_role 

FROM dba_role_privs 

WHERE grantee IN (SELECT username FROM dba_users)

AND       grantee NOT LIKE ‘%SYS%’

AND       grantee NOT IN (‘DBSNMP’,‘OUTLN’

ORDER BY grantee; 

PROMPT 

PROMPT ******************************************** USER PRIVILEGES 

SELECT grantee user, privilege, admin_option 

FROM dba_sys_privs 

WHERE    grantee IN (SELECT username FROM dba_users)

AND      grantee NOT LIKE ‘%SYS%’

AND      grantee NOT IN (‘DBSNMP’,‘OUTLN’

ORDER BY grantee; 

set pages 58 

column role         format a19 heading ‘User or Role’ 

column admin_option format a3  heading ‘Ad?’ 

column owner        format a7 heading ‘Owner’ 

column table_name   format a26 heading ‘Table name’ 

column privilege    format a21 heading ‘Priv, Grant or Role’ 

column r_ord noprint 

break on role start

titel132 ‘ORACLE ROLES REPORT’ 

select    2 r_ord, b.role role, b.owner owner, b.table_name,  

b.privilege privilege, b.grantable admin_option 

from sys.role_tab_privs b 

union 

select    1 r_ord, a.role role, ‘N/A’ owner, ‘N/A’ table_name,   

a.privilege privilege, a.admin_option admin_option  

from sys.role_sys_privs a 

union 

select    3 r_ord, c.role role, ‘N/A’ owner, ‘N/A’ table_name,   

c.granted_role privilege, c.admin_option admin_option 

from sys.role_role_privs c 

order by role,r_ord; 

set flush on term on pagesize 22  linesize 80 

clear columns 

clear breaks 

ttitle off 

pause Press enter to continue

Cheers,

Francisco Munoz Alvarez

Leave a Reply