Get Oracle User Privileges: App, Custom, Default & All Users

6 min read

ADVERTISEMENT

Need to audit user privileges in your Oracle database? Whether you’re checking for app users, filtering out Oracle default accounts, or reviewing everything — these simple SQL scripts help you get the job done.

Each script includes:

  • System Privileges (DBA_SYS_PRIVS)
  • Object Privileges (DBA_TAB_PRIVS)
  • Granted Roles (DBA_ROLE_PRIVS)
  • Export to .csv using SPOOL

App Users Only – Clean Audit View

Only shows privileges for application users with active accounts, using custom tablespaces and excluding Oracle-maintained accounts.

SPOOL app_users_privileges.csv

SELECT grantee, privilege, table_name AS object_name, 'OBJECT PRIVILEGE' AS privilege_type
FROM dba_tab_privs
WHERE grantee IN (
    SELECT username
    FROM dba_users
    WHERE account_status = 'OPEN'
      AND default_tablespace NOT IN ('SYSTEM','SYSAUX')
      AND oracle_maintained = 'N'
)

UNION ALL

SELECT grantee, privilege, NULL AS object_name, 'SYSTEM PRIVILEGE' AS privilege_type
FROM dba_sys_privs
WHERE grantee IN (
    SELECT username
    FROM dba_users
    WHERE account_status = 'OPEN'
      AND default_tablespace NOT IN ('SYSTEM','SYSAUX')
      AND oracle_maintained = 'N'
)

UNION ALL

SELECT grantee, granted_role AS privilege, NULL AS object_name, 'ROLE GRANTED' AS privilege_type
FROM dba_role_privs
WHERE grantee IN (
    SELECT username
    FROM dba_users
    WHERE account_status = 'OPEN'
      AND default_tablespace NOT IN ('SYSTEM','SYSAUX')
      AND oracle_maintained = 'N'
)

ORDER BY grantee, privilege_type, privilege;

SPOOL OFF

Filtered Users – Excludes Oracle Default Users

Excludes Oracle system accounts and C## common users, but includes all remaining custom users.

SPOOL filtered_users_privileges.csv

WITH filtered_users AS (
  SELECT username FROM dba_users
  WHERE username NOT IN (
    'SYS', 'SYSTEM', 'OUTLN', 'DBSNMP', 'SYSMAN', 'MGMT_VIEW',
    'ORDPLUGINS', 'ORDDATA', 'ORDSYS', 'MDSYS', 'CTXSYS', 'XDB',
    'WMSYS', 'APPQOSSYS', 'APEX_PUBLIC_USER', 'FLOWS_FILES',
    'XS$NULL', 'AUDSYS', 'GSMUSER', 'REMOTE_SCHEDULER_AGENT',
    'ANONYMOUS', 'OJVMSYS', 'DVSYS', 'DVF', 'LBACSYS', 'GGSYS',
    'GSMADMIN_INTERNAL', 'DIP', 'PDBADMIN'
  )
  AND username NOT LIKE 'C##%'
)

SELECT grantee, privilege, table_name AS object_name, 'OBJECT PRIVILEGE' AS privilege_type
FROM dba_tab_privs
WHERE grantee IN (SELECT username FROM filtered_users)

UNION ALL

SELECT grantee, privilege, NULL AS object_name, 'SYSTEM PRIVILEGE' AS privilege_type
FROM dba_sys_privs
WHERE grantee IN (SELECT username FROM filtered_users)

UNION ALL

SELECT grantee, granted_role AS privilege, NULL AS object_name, 'ROLE GRANTED' AS privilege_type
FROM dba_role_privs
WHERE grantee IN (SELECT username FROM filtered_users)

ORDER BY grantee, privilege_type, privilege;

SPOOL OFF

Oracle Default Users Only – System Accounts Audit

This script shows system, object, and role privileges only for Oracle internal/default users, including those like SYS, SYSTEM, XDB, etc.

SPOOL oracle_default_users_privileges.csv

SELECT grantee, privilege, table_name AS object_name, 'OBJECT PRIVILEGE' AS privilege_type
FROM dba_tab_privs
WHERE grantee IN (
    SELECT username
    FROM dba_users
    WHERE oracle_maintained = 'Y'
       OR username IN (
           'SYS', 'SYSTEM', 'OUTLN', 'DBSNMP', 'SYSMAN', 'MGMT_VIEW',
           'ORDPLUGINS', 'ORDDATA', 'ORDSYS', 'MDSYS', 'CTXSYS', 'XDB',
           'WMSYS', 'APPQOSSYS', 'APEX_PUBLIC_USER', 'FLOWS_FILES',
           'XS$NULL', 'AUDSYS', 'GSMUSER', 'REMOTE_SCHEDULER_AGENT',
           'ANONYMOUS', 'OJVMSYS', 'DVSYS', 'DVF', 'LBACSYS', 'GGSYS',
           'GSMADMIN_INTERNAL', 'DIP', 'PDBADMIN'
       )
)

UNION ALL

SELECT grantee, privilege, NULL AS object_name, 'SYSTEM PRIVILEGE' AS privilege_type
FROM dba_sys_privs
WHERE grantee IN (
    SELECT username
    FROM dba_users
    WHERE oracle_maintained = 'Y'
       OR username IN (
           'SYS', 'SYSTEM', 'OUTLN', 'DBSNMP', 'SYSMAN', 'MGMT_VIEW',
           'ORDPLUGINS', 'ORDDATA', 'ORDSYS', 'MDSYS', 'CTXSYS', 'XDB',
           'WMSYS', 'APPQOSSYS', 'APEX_PUBLIC_USER', 'FLOWS_FILES',
           'XS$NULL', 'AUDSYS', 'GSMUSER', 'REMOTE_SCHEDULER_AGENT',
           'ANONYMOUS', 'OJVMSYS', 'DVSYS', 'DVF', 'LBACSYS', 'GGSYS',
           'GSMADMIN_INTERNAL', 'DIP', 'PDBADMIN'
       )
)

UNION ALL

SELECT grantee, granted_role AS privilege, NULL AS object_name, 'ROLE GRANTED' AS privilege_type
FROM dba_role_privs
WHERE grantee IN (
    SELECT username
    FROM dba_users
    WHERE oracle_maintained = 'Y'
       OR username IN (
           'SYS', 'SYSTEM', 'OUTLN', 'DBSNMP', 'SYSMAN', 'MGMT_VIEW',
           'ORDPLUGINS', 'ORDDATA', 'ORDSYS', 'MDSYS', 'CTXSYS', 'XDB',
           'WMSYS', 'APPQOSSYS', 'APEX_PUBLIC_USER', 'FLOWS_FILES',
           'XS$NULL', 'AUDSYS', 'GSMUSER', 'REMOTE_SCHEDULER_AGENT',
           'ANONYMOUS', 'OJVMSYS', 'DVSYS', 'DVF', 'LBACSYS', 'GGSYS',
           'GSMADMIN_INTERNAL', 'DIP', 'PDBADMIN'
       )
)

ORDER BY grantee, privilege_type, privilege;

SPOOL OFF

All Users – Full System Audit

This script lists every privilege for every user — no filtering.

SPOOL all_users_privileges.csv

SELECT grantee, privilege, table_name AS object_name, 'OBJECT PRIVILEGE' AS privilege_type
FROM dba_tab_privs

UNION ALL

SELECT grantee, privilege, NULL AS object_name, 'SYSTEM PRIVILEGE' AS privilege_type
FROM dba_sys_privs

UNION ALL

SELECT grantee, granted_role AS privilege, NULL AS object_name, 'ROLE GRANTED' AS privilege_type
FROM dba_role_privs

ORDER BY grantee, privilege_type, privilege;

SPOOL OFF

All Table Privileges with User Account Info (Includes System Users)

-- Report 1: All table privileges along with user account info (including Oracle default/system users)
SELECT
    dp.grantee,
    dp.owner,
    dp.table_name,
    dp.grantor,
    dp.privilege,
    dp.grantable,
    -- dp.hierarchy,  -- Uncomment if your DB supports it
    u.common,
    u.account_status AS type
FROM
    dba_tab_privs dp
JOIN
    dba_users u
    ON dp.grantee = u.username;

Table Privileges Granted to Non-Oracle Maintained Users

-- Report 2: Table privileges granted to non-Oracle maintained users only
SELECT
    dp.grantee,
    dp.owner,
    dp.table_name,
    dp.grantor,
    dp.privilege,
    dp.grantable,
    u.common,
    u.account_status AS type
FROM
    dba_tab_privs dp
JOIN
    dba_users u
    ON dp.grantee = u.username
WHERE
    u.oracle_maintained = 'N';

Table Privileges Excluding Known Default Oracle Users (Manual List)

-- Report 3: Table privileges excluding known Oracle default/system users using explicit username list
SELECT
    dp.grantee,
    dp.owner,
    dp.table_name,
    dp.grantor,
    dp.privilege,
    dp.grantable,
    u.common,
    u.account_status AS type
FROM
    dba_tab_privs dp
JOIN
    dba_users u
    ON dp.grantee = u.username
WHERE
    dp.grantee NOT IN (
        'SYS', 'SYSTEM', 'OUTLN', 'DBSNMP', 'APPQOSSYS', 'AUDSYS', 'CTXSYS', 'DIP',
        'DVF', 'DVSYS', 'GSMADMIN_INTERNAL', 'LBACSYS', 'MDSYS', 'OJVMSYS',
        'ORACLE_OCM', 'REMOTE_SCHEDULER_AGENT', 'SYSBACKUP', 'SYSDG', 'SYSKM',
        'XDB', 'ANONYMOUS', 'WMSYS', 'ORDDATA', 'ORDPLUGINS', 'ORDSYS', 'SI_INFORMTN_SCHEMA',
        'OLAPSYS', 'EXFSYS', 'TSMSYS'
    );

Table Privileges for Application Users Created After Specific Date

-- Report 4: Table privileges for application users (non-maintained, open accounts, excluding SYS%, created after 2024-01-01)
SELECT
    dp.grantee,
    dp.owner,
    dp.table_name,
    dp.grantor,
    dp.privilege,
    dp.grantable,
    u.common,
    u.account_status AS type
FROM
    dba_tab_privs dp
JOIN
    dba_users u ON dp.grantee = u.username
WHERE
    u.oracle_maintained = 'N'
    AND u.account_status = 'OPEN'
    AND u.username NOT LIKE 'SYS%'
    AND u.created > TO_DATE('2024-01-01', 'YYYY-MM-DD');

Pro Tip: Run in SQL*Plus or SQLcl

Make sure you’re using a tool like SQL*Plus, SQL Developer, or SQLcl that supports the SPOOL command for exporting output. You can replace .csv with .html or .txt as needed.

ADVERTISEMENT

You might like

Leave a Reply

Your email address will not be published. Required fields are marked *