How to find all user Roles and Privileges?

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



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


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 ******************************************** 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


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 


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 


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


Francisco Munoz Alvarez


Oracle ACE Director and President of LAOUC, NZOUG and CLOUG. Organizer of LA and APAC OTN Tours,

Posted in Oracle FAQ, Questions, Security

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: