Scripts --: Listing database users with granted privileges.

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;