Oracle Database User Privilege

Buğra PARLAYAN August 17, 2023 ORACLE

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')

Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux

Oracle Database User Privilege

About Buğra PARLAYAN

Leave a Reply Cancel reply