Oracle Database User Privilege

Buğra PARLAYAN

9 months ago

In today’s article, we will be learning how to list which user has which privileges on Oracle Database.

For this we will use the following query.

SELECT A.*, 'GRANTED TO USER' TYPE
  FROM DBA_SYS_PRIVS A, DBA_USERS B
 WHERE     A.GRANTEE = B.USERNAME
       AND GRANTEE NOT IN ('ANONYMOUS',
                           'CTXSYS',
                           'DBSNMP',
                           'EXFSYS',
                           'LBACSYS',
                           'MDSYS',
                           'MGMT_VIEW',
                           'OLAPSYS',
                           'OWBSYS',
                           'ORDPLUGINS',
                           'ORDSYS',
                           'OUTLN',
                           'SI_INFORMTN_SCHEMA',
                           'SYS',
                           'SYSMAN',
                           'SYSTEM',
                           'TSMSYS',
                           'WK_TEST',
                           'WKSYS',
                           'WKPROXY',
                           'WMSYS',
                           'XDB',
                           'APEX_PUBLIC_USER',
                           'DIP',
                           'FLOWS_30000',
                           'FLOWS_FILES',
                           'MDDATA',
                           'ORACLE_OCM',
                           'SPATIAL_CSW_ADMIN_USR',
                           'SPATIAL_WFS_ADMIN_USR',
                           'XS$NULL',
                           'OWBSYS_AUDIT',
                           'ORDDATA',
                           'APEX_030200',
                           'APPQOSSYS',
                           'DVSYS',
                           'DVF')

SELECT A.*, 'GRANTED TO USER' TYPE

FROM DBA_SYS_PRIVS A, DBA_USERS B

WHERE A.GRANTEE = B.USERNAME

AND GRANTEE NOT IN ('ANONYMOUS',

'CTXSYS',

'DBSNMP',

'EXFSYS',

'LBACSYS',

'MDSYS',

'MGMT_VIEW',

'OLAPSYS',

'OWBSYS',

'ORDPLUGINS',

'ORDSYS',

'OUTLN',

'SI_INFORMTN_SCHEMA',

'SYS',

'SYSMAN',

'SYSTEM',

'TSMSYS',

'WK_TEST',

'WKSYS',

'WKPROXY',

'WMSYS',

'XDB',

'APEX_PUBLIC_USER',

'DIP',

'FLOWS_30000',

'FLOWS_FILES',

'MDDATA',

'ORACLE_OCM',

'SPATIAL_CSW_ADMIN_USR',

'SPATIAL_WFS_ADMIN_USR',

'XS$NULL',

'OWBSYS_AUDIT',

'ORDDATA',

'APEX_030200',

'APPQOSSYS',

'DVSYS',

'DVF')