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
breakon 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 onterm on pagesize 22 linesize 80
clear columns
clear breaks
ttitle off
pause Press enter tocontinue
Cheers,
Francisco Munoz Alvarez
