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

Advertisements

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:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

%d bloggers like this: