{"id":3020,"date":"2025-04-08T04:16:13","date_gmt":"2025-04-08T04:16:13","guid":{"rendered":"https:\/\/w3buddy.com\/?p=3020"},"modified":"2026-01-15T13:20:39","modified_gmt":"2026-01-15T07:50:39","slug":"get-oracle-user-privileges-app-custom-default-all-users","status":"publish","type":"post","link":"https:\/\/w3buddy.com\/blog\/get-oracle-user-privileges-app-custom-default-all-users\/","title":{"rendered":"Get Oracle User Privileges: App, Custom, Default &amp; All Users"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">Need to audit user privileges in your Oracle database? Whether you\u2019re checking for app users, filtering out Oracle default accounts, or reviewing everything \u2014 these simple SQL scripts help you get the job done.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Each script includes:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>System Privileges<\/strong> (DBA_SYS_PRIVS)<\/li>\n\n\n\n<li><strong>Object Privileges<\/strong> (DBA_TAB_PRIVS)<\/li>\n\n\n\n<li><strong>Granted Roles<\/strong> (DBA_ROLE_PRIVS)<\/li>\n\n\n\n<li>Export to .csv using SPOOL<\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\">App Users Only \u2013 Clean Audit View<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Only shows privileges for <strong>application users<\/strong> with active accounts, using custom tablespaces and excluding Oracle-maintained accounts.<\/p>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">SPOOL app_users_privileges.csv\n\nSELECT grantee, privilege, table_name AS object_name, 'OBJECT PRIVILEGE' AS privilege_type\nFROM dba_tab_privs\nWHERE grantee IN (\n    SELECT username\n    FROM dba_users\n    WHERE account_status = 'OPEN'\n      AND default_tablespace NOT IN ('SYSTEM','SYSAUX')\n      AND oracle_maintained = 'N'\n)\n\nUNION ALL\n\nSELECT grantee, privilege, NULL AS object_name, 'SYSTEM PRIVILEGE' AS privilege_type\nFROM dba_sys_privs\nWHERE grantee IN (\n    SELECT username\n    FROM dba_users\n    WHERE account_status = 'OPEN'\n      AND default_tablespace NOT IN ('SYSTEM','SYSAUX')\n      AND oracle_maintained = 'N'\n)\n\nUNION ALL\n\nSELECT grantee, granted_role AS privilege, NULL AS object_name, 'ROLE GRANTED' AS privilege_type\nFROM dba_role_privs\nWHERE grantee IN (\n    SELECT username\n    FROM dba_users\n    WHERE account_status = 'OPEN'\n      AND default_tablespace NOT IN ('SYSTEM','SYSAUX')\n      AND oracle_maintained = 'N'\n)\n\nORDER BY grantee, privilege_type, privilege;\n\nSPOOL OFF<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Filtered Users \u2013 Excludes Oracle Default Users<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Excludes Oracle system accounts and C## common users, but includes all remaining custom users.<\/p>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">SPOOL filtered_users_privileges.csv\n\nWITH filtered_users AS (\n  SELECT username FROM dba_users\n  WHERE username NOT IN (\n    'SYS', 'SYSTEM', 'OUTLN', 'DBSNMP', 'SYSMAN', 'MGMT_VIEW',\n    'ORDPLUGINS', 'ORDDATA', 'ORDSYS', 'MDSYS', 'CTXSYS', 'XDB',\n    'WMSYS', 'APPQOSSYS', 'APEX_PUBLIC_USER', 'FLOWS_FILES',\n    'XS$NULL', 'AUDSYS', 'GSMUSER', 'REMOTE_SCHEDULER_AGENT',\n    'ANONYMOUS', 'OJVMSYS', 'DVSYS', 'DVF', 'LBACSYS', 'GGSYS',\n    'GSMADMIN_INTERNAL', 'DIP', 'PDBADMIN'\n  )\n  AND username NOT LIKE 'C##%'\n)\n\nSELECT grantee, privilege, table_name AS object_name, 'OBJECT PRIVILEGE' AS privilege_type\nFROM dba_tab_privs\nWHERE grantee IN (SELECT username FROM filtered_users)\n\nUNION ALL\n\nSELECT grantee, privilege, NULL AS object_name, 'SYSTEM PRIVILEGE' AS privilege_type\nFROM dba_sys_privs\nWHERE grantee IN (SELECT username FROM filtered_users)\n\nUNION ALL\n\nSELECT grantee, granted_role AS privilege, NULL AS object_name, 'ROLE GRANTED' AS privilege_type\nFROM dba_role_privs\nWHERE grantee IN (SELECT username FROM filtered_users)\n\nORDER BY grantee, privilege_type, privilege;\n\nSPOOL OFF<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Oracle Default Users Only \u2013 System Accounts Audit<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">This script shows system, object, and role privileges only for Oracle internal\/default users, including those like <strong>SYS<\/strong>, <strong>SYSTEM<\/strong>, <strong>XDB<\/strong>, etc.<\/p>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">SPOOL oracle_default_users_privileges.csv\n\nSELECT grantee, privilege, table_name AS object_name, 'OBJECT PRIVILEGE' AS privilege_type\nFROM dba_tab_privs\nWHERE grantee IN (\n    SELECT username\n    FROM dba_users\n    WHERE oracle_maintained = 'Y'\n       OR username IN (\n           'SYS', 'SYSTEM', 'OUTLN', 'DBSNMP', 'SYSMAN', 'MGMT_VIEW',\n           'ORDPLUGINS', 'ORDDATA', 'ORDSYS', 'MDSYS', 'CTXSYS', 'XDB',\n           'WMSYS', 'APPQOSSYS', 'APEX_PUBLIC_USER', 'FLOWS_FILES',\n           'XS$NULL', 'AUDSYS', 'GSMUSER', 'REMOTE_SCHEDULER_AGENT',\n           'ANONYMOUS', 'OJVMSYS', 'DVSYS', 'DVF', 'LBACSYS', 'GGSYS',\n           'GSMADMIN_INTERNAL', 'DIP', 'PDBADMIN'\n       )\n)\n\nUNION ALL\n\nSELECT grantee, privilege, NULL AS object_name, 'SYSTEM PRIVILEGE' AS privilege_type\nFROM dba_sys_privs\nWHERE grantee IN (\n    SELECT username\n    FROM dba_users\n    WHERE oracle_maintained = 'Y'\n       OR username IN (\n           'SYS', 'SYSTEM', 'OUTLN', 'DBSNMP', 'SYSMAN', 'MGMT_VIEW',\n           'ORDPLUGINS', 'ORDDATA', 'ORDSYS', 'MDSYS', 'CTXSYS', 'XDB',\n           'WMSYS', 'APPQOSSYS', 'APEX_PUBLIC_USER', 'FLOWS_FILES',\n           'XS$NULL', 'AUDSYS', 'GSMUSER', 'REMOTE_SCHEDULER_AGENT',\n           'ANONYMOUS', 'OJVMSYS', 'DVSYS', 'DVF', 'LBACSYS', 'GGSYS',\n           'GSMADMIN_INTERNAL', 'DIP', 'PDBADMIN'\n       )\n)\n\nUNION ALL\n\nSELECT grantee, granted_role AS privilege, NULL AS object_name, 'ROLE GRANTED' AS privilege_type\nFROM dba_role_privs\nWHERE grantee IN (\n    SELECT username\n    FROM dba_users\n    WHERE oracle_maintained = 'Y'\n       OR username IN (\n           'SYS', 'SYSTEM', 'OUTLN', 'DBSNMP', 'SYSMAN', 'MGMT_VIEW',\n           'ORDPLUGINS', 'ORDDATA', 'ORDSYS', 'MDSYS', 'CTXSYS', 'XDB',\n           'WMSYS', 'APPQOSSYS', 'APEX_PUBLIC_USER', 'FLOWS_FILES',\n           'XS$NULL', 'AUDSYS', 'GSMUSER', 'REMOTE_SCHEDULER_AGENT',\n           'ANONYMOUS', 'OJVMSYS', 'DVSYS', 'DVF', 'LBACSYS', 'GGSYS',\n           'GSMADMIN_INTERNAL', 'DIP', 'PDBADMIN'\n       )\n)\n\nORDER BY grantee, privilege_type, privilege;\n\nSPOOL OFF<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">All Users \u2013 Full System Audit<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">This script lists every privilege for every user \u2014 no filtering.<\/p>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">SPOOL all_users_privileges.csv\n\nSELECT grantee, privilege, table_name AS object_name, 'OBJECT PRIVILEGE' AS privilege_type\nFROM dba_tab_privs\n\nUNION ALL\n\nSELECT grantee, privilege, NULL AS object_name, 'SYSTEM PRIVILEGE' AS privilege_type\nFROM dba_sys_privs\n\nUNION ALL\n\nSELECT grantee, granted_role AS privilege, NULL AS object_name, 'ROLE GRANTED' AS privilege_type\nFROM dba_role_privs\n\nORDER BY grantee, privilege_type, privilege;\n\nSPOOL OFF<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">All Table Privileges with User Account Info (Includes System Users)<\/h2>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">-- Report 1: All table privileges along with user account info (including Oracle default\/system users)\nSELECT\n    dp.grantee,\n    dp.owner,\n    dp.table_name,\n    dp.grantor,\n    dp.privilege,\n    dp.grantable,\n    -- dp.hierarchy,  -- Uncomment if your DB supports it\n    u.common,\n    u.account_status AS type\nFROM\n    dba_tab_privs dp\nJOIN\n    dba_users u\n    ON dp.grantee = u.username;<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Table Privileges Granted to Non-Oracle Maintained Users<\/h2>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">-- Report 2: Table privileges granted to non-Oracle maintained users only\nSELECT\n    dp.grantee,\n    dp.owner,\n    dp.table_name,\n    dp.grantor,\n    dp.privilege,\n    dp.grantable,\n    u.common,\n    u.account_status AS type\nFROM\n    dba_tab_privs dp\nJOIN\n    dba_users u\n    ON dp.grantee = u.username\nWHERE\n    u.oracle_maintained = 'N';<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Table Privileges Excluding Known Default Oracle Users (Manual List)<\/h2>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">-- Report 3: Table privileges excluding known Oracle default\/system users using explicit username list\nSELECT\n    dp.grantee,\n    dp.owner,\n    dp.table_name,\n    dp.grantor,\n    dp.privilege,\n    dp.grantable,\n    u.common,\n    u.account_status AS type\nFROM\n    dba_tab_privs dp\nJOIN\n    dba_users u\n    ON dp.grantee = u.username\nWHERE\n    dp.grantee NOT IN (\n        'SYS', 'SYSTEM', 'OUTLN', 'DBSNMP', 'APPQOSSYS', 'AUDSYS', 'CTXSYS', 'DIP',\n        'DVF', 'DVSYS', 'GSMADMIN_INTERNAL', 'LBACSYS', 'MDSYS', 'OJVMSYS',\n        'ORACLE_OCM', 'REMOTE_SCHEDULER_AGENT', 'SYSBACKUP', 'SYSDG', 'SYSKM',\n        'XDB', 'ANONYMOUS', 'WMSYS', 'ORDDATA', 'ORDPLUGINS', 'ORDSYS', 'SI_INFORMTN_SCHEMA',\n        'OLAPSYS', 'EXFSYS', 'TSMSYS'\n    );<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>Table Privileges for Application Users Created After Specific Date<\/strong><\/h2>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">-- Report 4: Table privileges for application users (non-maintained, open accounts, excluding SYS%, created after 2024-01-01)\nSELECT\n    dp.grantee,\n    dp.owner,\n    dp.table_name,\n    dp.grantor,\n    dp.privilege,\n    dp.grantable,\n    u.common,\n    u.account_status AS type\nFROM\n    dba_tab_privs dp\nJOIN\n    dba_users u ON dp.grantee = u.username\nWHERE\n    u.oracle_maintained = 'N'\n    AND u.account_status = 'OPEN'\n    AND u.username NOT LIKE 'SYS%'\n    AND u.created > TO_DATE('2024-01-01', 'YYYY-MM-DD');<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Pro Tip: Run in SQL*Plus or SQLcl<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Make sure you&#8217;re using a tool like <strong>SQL*Plus<\/strong>, <strong>SQL Developer<\/strong>, or <strong>SQLcl<\/strong> that supports the <strong>SPOOL <\/strong>command for exporting output. You can replace <strong>.csv<\/strong> with <strong>.html<\/strong> or <strong>.txt<\/strong> as needed.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Need to audit user privileges in your Oracle database? Whether you\u2019re checking for app users, filtering out Oracle default accounts, or reviewing everything \u2014 these simple SQL scripts help you get the job done. Each script includes: App Users Only \u2013 Clean Audit View Only shows privileges for application users with active accounts, using custom [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"googlesitekit_rrm_CAowu461DA:productID":"","footnotes":""},"categories":[1225],"tags":[],"class_list":["post-3020","post","type-post","status-publish","format-standard","hentry","category-database"],"_links":{"self":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/3020","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/comments?post=3020"}],"version-history":[{"count":3,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/3020\/revisions"}],"predecessor-version":[{"id":3029,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/3020\/revisions\/3029"}],"wp:attachment":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/media?parent=3020"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/categories?post=3020"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/tags?post=3020"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}