Method 1
SQL> select distinct grantee as "USER",PRIVILEGE from dba_tab_privs where grantee not in ( select role from dba_roles)
union all
select grantee,PRIVILEGE from dba_sys_privs where grantee not in ( select role from dba_roles)
union all
select grantee,granted_role from dba_role_privs where grantee not in ( select role from dba_roles) order by 1 ;
WSDB_USER SELECT
WSI_USER SELECT
WSI_USER CREATE SESSION
WSI_USER EXECUTE
WSOMBW RIMS_LOGON_CORP
WSOMBW FLOODREPORTUSER
WSOMBW IEDITION
WWTS IEDITION
WWTS FLOODREPORTUSER
WWTS RIMS_LOGON_CORP
WW_USER SELECT
WW_USER CREATE SESSION
XDB EXECUTE
XDB JAVAUSERPRIV
XDB QUERY REWRITE
XDB CREATE INDEXTYPE
XDB CREATE PUBLIC SYNONYM
XDB UNLIMITED TABLESPACE
XDB CREATE LIBRARY
XDB CREATE OPERATOR
XDB DROP PUBLIC SYNONYM
XDB CREATE VIEW
XDB CREATE SESSION
Method 2
set heading off
set pages 0
set long 9999999
select dbms_metadata.get_granted_ddl('ROLE_GRANT', user)
from dual;
select dbms_metadata.get_granted_ddl('SYSTEM_GRANT', user)
from dual;
select dbms_metadata.get_granted_ddl('OBJECT_GRANT', user)
from dual;
GRANT ALTER TABLESPACE TO "SYS"
GRANT CREATE TABLESPACE TO "SYS"
GRANT RESTRICTED SESSION TO "SYS"
GRANT ALTER SESSION TO "SYS"
GRANT CREATE SESSION TO "SYS"
GRANT AUDIT SYSTEM TO "SYS"
GRANT ALTER SYSTEM TO "SYS"
SQL> 2
GRANT SELECT ON "OUTLN"."OL$" TO "SYS" WITH GRANT OPTION
GRANT SELECT ON "OUTLN"."OL$HINTS" TO "SYS" WITH GRANT OPTION
GRANT SELECT ON "OUTLN"."OL$NODES" TO "SYS" WITH GRANT OPTION
GRANT SELECT ON "SYSTEM"."DEF$_ERROR" TO "SYS" WITH GRANT OPTION
GRANT SELECT ON "SYSTEM"."DEF$_DESTINATION" TO "SYS" WITH GRANT OPTION
GRANT SELECT ON "SYSTEM"."DEF$_CALLDEST" TO "SYS" WITH GRANT OPTION
GRANT SELECT ON "SYSTEM"."REPCAT$_REPSCHEMA" TO "SYS" WITH GRANT OPTIO
Method 3
/* USER SYSTEM GRANTS */
SELECT a.grantee "USER", a.privilege, a.admin_option
FROM DBA_SYS_PRIVS a
INNER JOIN DBA_USERS c on c.USERNAME = a.GRANTEE
ORDER BY a.grantee, a.privilege;
/* USER OBJECT GRANTS */
SELECT a.table_name "OBJECT_NAME", b.object_type, a.privilege, a.grantee, a.grantor, c.username
FROM dba_tab_privs a
INNER JOIN dba_objects b on b.OBJECT_NAME = a.TABLE_NAME AND a.OWNER = b.OWNER
INNER JOIN DBA_USERS c on c.USERNAME = a.GRANTEE
ORDER by table_name;
/* ROLE SYSTEM GRANTS */
SELECT a.grantee "ROLE", a.privilege, a.admin_option
FROM DBA_SYS_PRIVS a
INNER JOIN DBA_ROLES c on c.ROLE = a.GRANTEE
ORDER BY a.grantee, a.privilege;
/* ROLE OBJECT GRANTS */
SELECT a.table_name "OBJECT_NAME", b.object_type, a.privilege, a.grantee, a.grantor
FROM dba_tab_privs a
INNER JOIN dba_objects b on b.OBJECT_NAME = a.TABLE_NAME AND a.OWNER = b.OWNER
INNER JOIN DBA_ROLES c on c.ROLE = a.GRANTEE
ORDER by table_name;
/* Use DBA_ROLE_PRIVS to find who is a member of each role */
SELECT *
FROM DBA_ROLE_PRIVS
ORDER BY GRANTEE, GRANTED_ROLE;
SQL> select distinct grantee as "USER",PRIVILEGE from dba_tab_privs where grantee not in ( select role from dba_roles)
union all
select grantee,PRIVILEGE from dba_sys_privs where grantee not in ( select role from dba_roles)
union all
select grantee,granted_role from dba_role_privs where grantee not in ( select role from dba_roles) order by 1 ;
WSDB_USER SELECT
WSI_USER SELECT
WSI_USER CREATE SESSION
WSI_USER EXECUTE
WSOMBW RIMS_LOGON_CORP
WSOMBW FLOODREPORTUSER
WSOMBW IEDITION
WWTS IEDITION
WWTS FLOODREPORTUSER
WWTS RIMS_LOGON_CORP
WW_USER SELECT
WW_USER CREATE SESSION
XDB EXECUTE
XDB JAVAUSERPRIV
XDB QUERY REWRITE
XDB CREATE INDEXTYPE
XDB CREATE PUBLIC SYNONYM
XDB UNLIMITED TABLESPACE
XDB CREATE LIBRARY
XDB CREATE OPERATOR
XDB DROP PUBLIC SYNONYM
XDB CREATE VIEW
XDB CREATE SESSION
Method 2
set heading off
set pages 0
set long 9999999
select dbms_metadata.get_granted_ddl('ROLE_GRANT', user)
from dual;
select dbms_metadata.get_granted_ddl('SYSTEM_GRANT', user)
from dual;
select dbms_metadata.get_granted_ddl('OBJECT_GRANT', user)
from dual;
GRANT ALTER TABLESPACE TO "SYS"
GRANT CREATE TABLESPACE TO "SYS"
GRANT RESTRICTED SESSION TO "SYS"
GRANT ALTER SESSION TO "SYS"
GRANT CREATE SESSION TO "SYS"
GRANT AUDIT SYSTEM TO "SYS"
GRANT ALTER SYSTEM TO "SYS"
SQL> 2
GRANT SELECT ON "OUTLN"."OL$" TO "SYS" WITH GRANT OPTION
GRANT SELECT ON "OUTLN"."OL$HINTS" TO "SYS" WITH GRANT OPTION
GRANT SELECT ON "OUTLN"."OL$NODES" TO "SYS" WITH GRANT OPTION
GRANT SELECT ON "SYSTEM"."DEF$_ERROR" TO "SYS" WITH GRANT OPTION
GRANT SELECT ON "SYSTEM"."DEF$_DESTINATION" TO "SYS" WITH GRANT OPTION
GRANT SELECT ON "SYSTEM"."DEF$_CALLDEST" TO "SYS" WITH GRANT OPTION
GRANT SELECT ON "SYSTEM"."REPCAT$_REPSCHEMA" TO "SYS" WITH GRANT OPTIO
Method 3
/* USER SYSTEM GRANTS */
SELECT a.grantee "USER", a.privilege, a.admin_option
FROM DBA_SYS_PRIVS a
INNER JOIN DBA_USERS c on c.USERNAME = a.GRANTEE
ORDER BY a.grantee, a.privilege;
/* USER OBJECT GRANTS */
SELECT a.table_name "OBJECT_NAME", b.object_type, a.privilege, a.grantee, a.grantor, c.username
FROM dba_tab_privs a
INNER JOIN dba_objects b on b.OBJECT_NAME = a.TABLE_NAME AND a.OWNER = b.OWNER
INNER JOIN DBA_USERS c on c.USERNAME = a.GRANTEE
ORDER by table_name;
/* ROLE SYSTEM GRANTS */
SELECT a.grantee "ROLE", a.privilege, a.admin_option
FROM DBA_SYS_PRIVS a
INNER JOIN DBA_ROLES c on c.ROLE = a.GRANTEE
ORDER BY a.grantee, a.privilege;
/* ROLE OBJECT GRANTS */
SELECT a.table_name "OBJECT_NAME", b.object_type, a.privilege, a.grantee, a.grantor
FROM dba_tab_privs a
INNER JOIN dba_objects b on b.OBJECT_NAME = a.TABLE_NAME AND a.OWNER = b.OWNER
INNER JOIN DBA_ROLES c on c.ROLE = a.GRANTEE
ORDER by table_name;
/* Use DBA_ROLE_PRIVS to find who is a member of each role */
SELECT *
FROM DBA_ROLE_PRIVS
ORDER BY GRANTEE, GRANTED_ROLE;
