How to Show All Privileges for a User in Oracle Database

Sometimes DBA needs to generate the report to provide audit team or management about the users access information on database. In this article, I will show how to get list of access for oracle user.

Query to find all privileges for all users in Database:

SQL> COL USERNAME FOR A15
COL PRIVILEGE FOR A25
COL OWNER FOR A15
COL TABLENAME FOR A30
COL COLUMN_NAME FOR A25
COL ADMIN_OPTION FOR A15
SET LINESIZE 200

SQL> SELECT A.*
    FROM (SELECT GRANTEE          USERNAME,
                 GRANTED_ROLE     PRIVILEGE,
                 '--'             OWNER,
                 '--'             TABLENAME,
                 '--'             COLUMN_NAME,
                 ADMIN_OPTION     ADMIN_OPTION,
                 'ROLE'           ACCESS_TYPE
            FROM DBA_ROLE_PRIVS RP JOIN DBA_ROLES R ON RP.GRANTED_ROLE = R.ROLE
           WHERE GRANTEE IN (SELECT USERNAME FROM DBA_USERS)
          UNION
          SELECT GRANTEE          USERNAME,
                 PRIVILEGE        PRIVILEGE,
                 '--'             OWNER,
                 '--'             TABLENAME,
                 '--'             COLUMN_NAME,
                 ADMIN_OPTION     ADMIN_OPTION,
                 'SYSTEM'         ACCESS_TYPE
            FROM DBA_SYS_PRIVS
           WHERE GRANTEE IN (SELECT USERNAME FROM DBA_USERS)
          UNION
          SELECT GRANTEE        USERNAME,
                 PRIVILEGE      PRIVILEGE,
                 OWNER          OWNER,
                 TABLE_NAME     TABLENAME,
                 '--'           COLUMN_NAME,
                 GRANTABLE      ADMIN_OPTION,
                 'TABLE'        ACCESS_TYPE
            FROM DBA_TAB_PRIVS
           WHERE GRANTEE IN (SELECT USERNAME FROM DBA_USERS)
          UNION
          SELECT DP.GRANTEE      USERNAME,
                 PRIVILEGE       PRIVILEGE,
                 OWNER           OWNER,
                 TABLE_NAME      TABLENAME,
                 COLUMN_NAME     COLUMN_NAME,
                 '--'            ADMIN_OPTION,
                 'ROLE'          ACCESS_TYPE
            FROM ROLE_TAB_PRIVS RP, DBA_ROLE_PRIVS DP
           WHERE     RP.ROLE = DP.GRANTED_ROLE
                 AND DP.GRANTEE IN (SELECT USERNAME FROM DBA_USERS)
          UNION
          SELECT GRANTEE         USERNAME,
                 PRIVILEGE       PRIVILEGE,
                 GRANTABLE       ADMIN_OPTION,
                 OWNER           OWNER,
                 TABLE_NAME      TABLENAME,
                 COLUMN_NAME     COLUMN_NAME,
                 'COLUMN'        ACCESS_TYPE
            FROM DBA_COL_PRIVS
           WHERE GRANTEE IN (SELECT USERNAME FROM DBA_USERS)) A
ORDER BY USERNAME,
         A.TABLENAME,
         CASE
             WHEN A.ACCESS_TYPE = 'SYSTEM' THEN 1
             WHEN A.ACCESS_TYPE = 'TABLE' THEN 2
             WHEN A.ACCESS_TYPE = 'COLUMN' THEN 3
             WHEN A.ACCESS_TYPE = 'ROLE' THEN 4
             ELSE 5
         END,
         CASE
             WHEN A.PRIVILEGE IN ('EXECUTE') THEN 1
             WHEN A.PRIVILEGE IN ('SELECT', 'INSERT', 'DELETE') THEN 3
             ELSE 2
         END,
         A.COLUMN_NAME,
         A.PRIVILEGE;

Output:

USERNAME        PRIVILEGE                 OWNER           TABLENAME                      COLUMN_NAME               ADMIN_OPTION    ACCESS
--------------- ------------------------- --------------- ------------------------------ ------------------------- --------------- ------
BI              ALTER SESSION             --              --                             --                        NO              SYSTEM
BI              CREATE CLUSTER            --              --                             --                        NO              SYSTEM
BI              CREATE DATABASE LINK      --              --                             --                        NO              SYSTEM
BI              CREATE SEQUENCE           --              --                             --                        NO              SYSTEM
BI              CREATE SESSION            --              --                             --                        NO              SYSTEM
BI              CREATE SYNONYM            --              --                             --                        NO              SYSTEM
BI              CREATE TABLE              --              --                             --                        NO              SYSTEM
BI              CREATE VIEW               --              --                             --                        NO              SYSTEM
BI              UNLIMITED TABLESPACE      --              --                             --                        NO              SYSTEM
BI              RESOURCE                  --              --                             --                        NO              ROLE
BI              SELECT                    OE              BOMBAY_INVENTORY               --                        NO              TABLE
BI              SELECT                    SH              CAL_MONTH_SALES_MV             --                        NO              TABLE
BI              SELECT                    SH              CHANNELS                       --                        NO              TABLE
BI              SELECT                    SH              COSTS                          --                        NO              TABLE
BI              SELECT                    SH              COUNTRIES                      --                        NO              TABLE
BI              SELECT                    SH              CUSTOMERS                      --                        NO              TABLE
BI              SELECT                    OE              CUSTOMERS                      --                        NO              TABLE
BI              SELECT                    SH              FWEEK_PSCAT_SALES_MV           --                        NO              TABLE
BI              SELECT                    OE              INVENTORIES                    --                        NO              TABLE
BI              SELECT                    OE              ORDERS                         --                        NO              TABLE
BI              SELECT                    OE              ORDER_ITEMS                    --                        NO              TABLE
BI              SELECT                    OE              PRODUCTS                       --                        NO              TABLE
BI              SELECT                    SH              PRODUCTS                       --                        NO              TABLE
BI              SELECT                    OE              PRODUCT_DESCRIPTIONS           --                        NO              TABLE
BI              SELECT                    OE              PRODUCT_INFORMATION            --                        NO              TABLE
BI              SELECT                    OE              PRODUCT_PRICES                 --                        NO              TABLE
BI              SELECT                    OE              PROMOTIONS                     --                        NO              TABLE
BI              SELECT                    SH              PROMOTIONS                     --                        NO              TABLE
BI              SELECT                    SH              SALES                          --                        NO              TABLE
BI              SELECT                    OE              SYDNEY_INVENTORY               --                        NO              TABLE
BI              SELECT                    SH              TIMES                          --                        NO              TABLE
BI              SELECT                    OE              TORONTO_INVENTORY              --                        NO              TABLE
BI              SELECT                    OE              WAREHOUSES                     --                        NO              TABLE
HR              ALTER SESSION             --              --                             --                        NO              SYSTEM
HR              CREATE DATABASE LINK      --              --                             --                        NO              SYSTEM
HR              CREATE SEQUENCE           --              --                             --                        NO              SYSTEM
HR              CREATE SESSION            --              --                             --                        NO              SYSTEM
HR              CREATE SYNONYM            --              --                             --                        NO              SYSTEM
HR              CREATE VIEW               --              --                             --                        NO              SYSTEM
HR              UNLIMITED TABLESPACE      --              --                             --                        NO              SYSTEM
HR              RESOURCE                  --              --                             --                        NO              ROLE
HR              EXECUTE                   SYS             DBMS_STATS                     --                        NO              TABLE
SCOTT           UNLIMITED TABLESPACE      --              --                             --                        NO              SYSTEM
SCOTT           CONNECT                   --              --                             --                        NO              ROLE

Multiple users access information:

SQL> COL USERNAME FOR A15
COL PRIVILEGE FOR A25
COL OWNER FOR A15
COL TABLENAME FOR A30
COL COLUMN_NAME FOR A25
COL ADMIN_OPTION FOR A15
SET LINESIZE 200

SQL> SELECT A.*
    FROM (SELECT GRANTEE          USERNAME,
                 GRANTED_ROLE     PRIVILEGE,
                 '--'             OWNER,
                 '--'             TABLENAME,
                 '--'             COLUMN_NAME,
                 ADMIN_OPTION     ADMIN_OPTION,
                 'ROLE'           ACCESS_TYPE
            FROM DBA_ROLE_PRIVS RP JOIN DBA_ROLES R ON RP.GRANTED_ROLE = R.ROLE
           WHERE GRANTEE IN (SELECT USERNAME FROM DBA_USERS WHERE USERNAME IN ('SCOTT','HR','BI'))
          UNION
          SELECT GRANTEE          USERNAME,
                 PRIVILEGE        PRIVILEGE,
                 '--'             OWNER,
                 '--'             TABLENAME,
                 '--'             COLUMN_NAME,
                 ADMIN_OPTION     ADMIN_OPTION,
                 'SYSTEM'         ACCESS_TYPE
            FROM DBA_SYS_PRIVS
           WHERE GRANTEE IN (SELECT USERNAME FROM DBA_USERS WHERE USERNAME IN ('SCOTT','HR','BI'))
          UNION
          SELECT GRANTEE        USERNAME,
                 PRIVILEGE      PRIVILEGE,
                 OWNER          OWNER,
                 TABLE_NAME     TABLENAME,
                 '--'           COLUMN_NAME,
                 GRANTABLE      ADMIN_OPTION,
                 'TABLE'        ACCESS_TYPE
            FROM DBA_TAB_PRIVS
           WHERE GRANTEE IN (SELECT USERNAME FROM DBA_USERS WHERE USERNAME IN ('SCOTT','HR','BI'))
          UNION
          SELECT DP.GRANTEE      USERNAME,
                 PRIVILEGE       PRIVILEGE,
                 OWNER           OWNER,
                 TABLE_NAME      TABLENAME,
                 COLUMN_NAME     COLUMN_NAME,
                 '--'            ADMIN_OPTION,
                 'ROLE'          ACCESS_TYPE
            FROM ROLE_TAB_PRIVS RP, DBA_ROLE_PRIVS DP
           WHERE     RP.ROLE = DP.GRANTED_ROLE
                 AND DP.GRANTEE IN (SELECT USERNAME FROM DBA_USERS WHERE USERNAME IN ('SCOTT','HR','BI'))
          UNION
          SELECT GRANTEE         USERNAME,
                 PRIVILEGE       PRIVILEGE,
                 GRANTABLE       ADMIN_OPTION,
                 OWNER           OWNER,
                 TABLE_NAME      TABLENAME,
                 COLUMN_NAME     COLUMN_NAME,
                 'COLUMN'        ACCESS_TYPE
            FROM DBA_COL_PRIVS
           WHERE GRANTEE IN (SELECT USERNAME FROM DBA_USERS WHERE USERNAME IN ('SCOTT','HR','BI'))) A
ORDER BY USERNAME,
         A.TABLENAME,
         CASE
             WHEN A.ACCESS_TYPE = 'SYSTEM' THEN 1
             WHEN A.ACCESS_TYPE = 'TABLE' THEN 2
             WHEN A.ACCESS_TYPE = 'COLUMN' THEN 3
             WHEN A.ACCESS_TYPE = 'ROLE' THEN 4
             ELSE 5
         END,
         CASE
             WHEN A.PRIVILEGE IN ('EXECUTE') THEN 1
             WHEN A.PRIVILEGE IN ('SELECT', 'INSERT', 'DELETE') THEN 3
             ELSE 2
         END,
         A.COLUMN_NAME,
         A.PRIVILEGE;

Access list for a user:

SQL> COL USERNAME FOR A15
COL PRIVILEGE FOR A25
COL OWNER FOR A15
COL TABLENAME FOR A30
COL COLUMN_NAME FOR A25
COL ADMIN_OPTION FOR A15
SET LINESIZE 200

SQL> SELECT A.*
    FROM (SELECT GRANTEE          USERNAME,
                 GRANTED_ROLE     PRIVILEGE,
                 '--'             OWNER,
                 '--'             TABLENAME,
                 '--'             COLUMN_NAME,
                 ADMIN_OPTION     ADMIN_OPTION,
                 'ROLE'           ACCESS_TYPE
            FROM DBA_ROLE_PRIVS RP JOIN DBA_ROLES R ON RP.GRANTED_ROLE = R.ROLE
           WHERE GRANTEE IN (SELECT USERNAME FROM DBA_USERS WHERE USERNAME = UPPER('&&USER_NAME'))
          UNION
          SELECT GRANTEE          USERNAME,
                 PRIVILEGE        PRIVILEGE,
                 '--'             OWNER,
                 '--'             TABLENAME,
                 '--'             COLUMN_NAME,
                 ADMIN_OPTION     ADMIN_OPTION,
                 'SYSTEM'         ACCESS_TYPE
            FROM DBA_SYS_PRIVS
           WHERE GRANTEE IN (SELECT USERNAME FROM DBA_USERS WHERE USERNAME = UPPER('&&USER_NAME'))
          UNION
          SELECT GRANTEE        USERNAME,
                 PRIVILEGE      PRIVILEGE,
                 OWNER          OWNER,
                 TABLE_NAME     TABLENAME,
                 '--'           COLUMN_NAME,
                 GRANTABLE      ADMIN_OPTION,
                 'TABLE'        ACCESS_TYPE
            FROM DBA_TAB_PRIVS
           WHERE GRANTEE IN (SELECT USERNAME FROM DBA_USERS WHERE USERNAME = UPPER('&&USER_NAME'))
          UNION
          SELECT DP.GRANTEE      USERNAME,
                 PRIVILEGE       PRIVILEGE,
                 OWNER           OWNER,
                 TABLE_NAME      TABLENAME,
                 COLUMN_NAME     COLUMN_NAME,
                 '--'            ADMIN_OPTION,
                 'ROLE'          ACCESS_TYPE
            FROM ROLE_TAB_PRIVS RP, DBA_ROLE_PRIVS DP
           WHERE     RP.ROLE = DP.GRANTED_ROLE
                 AND DP.GRANTEE IN (SELECT USERNAME FROM DBA_USERS WHERE USERNAME = UPPER('&&USER_NAME'))
          UNION
          SELECT GRANTEE         USERNAME,
                 PRIVILEGE       PRIVILEGE,
                 GRANTABLE       ADMIN_OPTION,
                 OWNER           OWNER,
                 TABLE_NAME      TABLENAME,
                 COLUMN_NAME     COLUMN_NAME,
                 'COLUMN'        ACCESS_TYPE
            FROM DBA_COL_PRIVS
           WHERE GRANTEE IN (SELECT USERNAME FROM DBA_USERS WHERE USERNAME = UPPER('&&USER_NAME'))) A
ORDER BY USERNAME,
         A.TABLENAME,
         CASE
             WHEN A.ACCESS_TYPE = 'SYSTEM' THEN 1
             WHEN A.ACCESS_TYPE = 'TABLE' THEN 2
             WHEN A.ACCESS_TYPE = 'COLUMN' THEN 3
             WHEN A.ACCESS_TYPE = 'ROLE' THEN 4
             ELSE 5
         END,
         CASE
             WHEN A.PRIVILEGE IN ('EXECUTE') THEN 1
             WHEN A.PRIVILEGE IN ('SELECT', 'INSERT', 'DELETE') THEN 3
             ELSE 2
         END,
         A.COLUMN_NAME,
         A.PRIVILEGE;

Output Log:

Enter value for user_name: BI
old  10:            WHERE GRANTEE IN (SELECT USERNAME FROM DBA_USERS WHERE USERNAME = UPPER('&&USER_NAME'))
new  10:            WHERE GRANTEE IN (SELECT USERNAME FROM DBA_USERS WHERE USERNAME = UPPER('BI'))
old  20:            WHERE GRANTEE IN (SELECT USERNAME FROM DBA_USERS WHERE USERNAME = UPPER('&&USER_NAME'))
new  20:            WHERE GRANTEE IN (SELECT USERNAME FROM DBA_USERS WHERE USERNAME = UPPER('BI'))
old  30:            WHERE GRANTEE IN (SELECT USERNAME FROM DBA_USERS WHERE USERNAME = UPPER('&&USER_NAME'))
new  30:            WHERE GRANTEE IN (SELECT USERNAME FROM DBA_USERS WHERE USERNAME = UPPER('BI'))
old  41:                  AND DP.GRANTEE IN (SELECT USERNAME FROM DBA_USERS WHERE USERNAME = UPPER('&&USER_NAME'))
new  41:                  AND DP.GRANTEE IN (SELECT USERNAME FROM DBA_USERS WHERE USERNAME = UPPER('BI'))
old  51:            WHERE GRANTEE IN (SELECT USERNAME FROM DBA_USERS WHERE USERNAME = UPPER('&&USER_NAME'))) A
new  51:            WHERE GRANTEE IN (SELECT USERNAME FROM DBA_USERS WHERE USERNAME = UPPER('BI'))) A

USERNAME        PRIVILEGE                 OWNER           TABLENAME                      COLUMN_NAME               ADMIN_OPTION    ACCESS
--------------- ------------------------- --------------- ------------------------------ ------------------------- --------------- ------
BI              ALTER SESSION             --              --                             --                        NO              SYSTEM
BI              CREATE CLUSTER            --              --                             --                        NO              SYSTEM
BI              CREATE DATABASE LINK      --              --                             --                        NO              SYSTEM
BI              CREATE SEQUENCE           --              --                             --                        NO              SYSTEM
BI              CREATE SESSION            --              --                             --                        NO              SYSTEM
BI              CREATE SYNONYM            --              --                             --                        NO              SYSTEM
BI              CREATE TABLE              --              --                             --                        NO              SYSTEM
BI              CREATE VIEW               --              --                             --                        NO              SYSTEM
BI              UNLIMITED TABLESPACE      --              --                             --                        NO              SYSTEM
BI              RESOURCE                  --              --                             --                        NO              ROLE
BI              SELECT                    OE              BOMBAY_INVENTORY               --                        NO              TABLE

USERNAME        PRIVILEGE                 OWNER           TABLENAME                      COLUMN_NAME               ADMIN_OPTION    ACCESS
--------------- ------------------------- --------------- ------------------------------ ------------------------- --------------- ------
BI              SELECT                    SH              CAL_MONTH_SALES_MV             --                        NO              TABLE
BI              SELECT                    SH              CHANNELS                       --                        NO              TABLE
BI              SELECT                    SH              COSTS                          --                        NO              TABLE
BI              SELECT                    SH              COUNTRIES                      --                        NO              TABLE
BI              SELECT                    OE              CUSTOMERS                      --                        NO              TABLE
BI              SELECT                    SH              CUSTOMERS                      --                        NO              TABLE
BI              SELECT                    SH              FWEEK_PSCAT_SALES_MV           --                        NO              TABLE
BI              SELECT                    OE              INVENTORIES                    --                        NO              TABLE
BI              SELECT                    OE              ORDERS                         --                        NO              TABLE
BI              SELECT                    OE              ORDER_ITEMS                    --                        NO              TABLE
BI              SELECT                    OE              PRODUCTS                       --                        NO              TABLE

USERNAME        PRIVILEGE                 OWNER           TABLENAME                      COLUMN_NAME               ADMIN_OPTION    ACCESS
--------------- ------------------------- --------------- ------------------------------ ------------------------- --------------- ------
BI              SELECT                    SH              PRODUCTS                       --                        NO              TABLE
BI              SELECT                    OE              PRODUCT_DESCRIPTIONS           --                        NO              TABLE
BI              SELECT                    OE              PRODUCT_INFORMATION            --                        NO              TABLE
BI              SELECT                    OE              PRODUCT_PRICES                 --                        NO              TABLE
BI              SELECT                    OE              PROMOTIONS                     --                        NO              TABLE
BI              SELECT                    SH              PROMOTIONS                     --                        NO              TABLE
BI              SELECT                    SH              SALES                          --                        NO              TABLE
BI              SELECT                    OE              SYDNEY_INVENTORY               --                        NO              TABLE
BI              SELECT                    SH              TIMES                          --                        NO              TABLE
BI              SELECT                    OE              TORONTO_INVENTORY              --                        NO              TABLE
BI              SELECT                    OE              WAREHOUSES                     --                        NO              TABLE

33 rows selected.

Leave a Reply