List of table and column privileges, including those via roles

I could not find this quickly enough in google so I wrote it myself.

The list of table privileges, with a connect by subquery.

 COL roles FOR a60
COL table_name FOR a30
col privilege for a9
set lin 200 trims on pages 0 emb on hea on newp none

  SELECT *
    FROM (    SELECT CONNECT_BY_ROOT grantee grantee,
                     privilege,
                     REPLACE (
                        REGEXP_REPLACE (SYS_CONNECT_BY_PATH (granteE, '/'),
                                        '^/[^/]*'),
                        '/',
                        ' --> ')
                        ROLES,
                     owner,
                     table_name,
                     column_name
                FROM (SELECT PRIVILEGE,
                             GRANTEE,
                             OWNER,
                             TABLE_NAME,
                             NULL column_name
                        FROM DBA_TAB_PRIVS
                       WHERE owner NOT IN
                                ('SYS',
                                 'SYSTEM',
                                 'WMSYS',
                                 'SYSMAN',
                                 'MDSYS',
                                 'ORDSYS',
                                 'XDB',
                                 'WKSYS',
                                 'EXFSYS',
                                 'OLAPSYS',
                                 'DBSNMP',
                                 'DMSYS',
                                 'CTXSYS',
                                 'WK_TEST',
                                 'ORDPLUGINS',
                                 'OUTLN',
                                 'ORACLE_OCM',
                                 'APPQOSSYS')
                      UNION 
                      SELECT PRIVILEGE,
                             GRANTEE,
                             OWNER,
                             TABLE_NAME,
                             column_name
                        FROM DBA_COL_PRIVS
                       WHERE owner NOT IN
                                ('SYS',
                                 'SYSTEM',
                                 'WMSYS',
                                 'SYSMAN',
                                 'MDSYS',
                                 'ORDSYS',
                                 'XDB',
                                 'WKSYS',
                                 'EXFSYS',
                                 'OLAPSYS',
                                 'DBSNMP',
                                 'DMSYS',
                                 'CTXSYS',
                                 'WK_TEST',
                                 'ORDPLUGINS',
                                 'OUTLN',
                                 'ORACLE_OCM',
                                 'APPQOSSYS')
                      UNION 
                      SELECT GRANTED_ROLE,
                             GRANTEE,
                             NULL,
                             NULL,
                             NULL
                        FROM DBA_ROLE_PRIVS
                       WHERE GRANTEE NOT IN
                                ('SYS',
                                 'SYSTEM',
                                 'WMSYS',
                                 'SYSMAN',
                                 'MDSYS',
                                 'ORDSYS',
                                 'XDB',
                                 'WKSYS',
                                 'EXFSYS',
                                 'OLAPSYS',
                                 'DBSNMP',
                                 'DMSYS',
                                 'CTXSYS',
                                 'WK_TEST',
                                 'ORDPLUGINS',
                                 'OUTLN',
                                 'ORACLE_OCM',
                                 'APPQOSSYS')) T
          START WITH grantee IN (SELECT username FROM dba_users)
          CONNECT BY PRIOR PRIVILEGE = GRANTEE)
   WHERE table_name IS NOT NULL AND grantee != OWNER
ORDER BY grantee,
         owner,
         table_name,
         column_name,
         privilege;

sample output


GRANTEE PRIVILEGE ROLES           OWNER  TABLE_NAME COLUMN_NAME
------- --------- --------------- ------ ---------- -----------
U       UPDATE     --> R          SCOTT  DEPT       DNAME      
U       SELECT                    SCOTT  EMP                   
U2      UPDATE     --> R2 --> R   SCOTT  DEPT       DNAME      

4 thoughts on “List of table and column privileges, including those via roles”

  1. I’ve tested it with a db with many roles and the output had over 600 000 lines. (a lot to do for the db ;-) But then I saw some duplicated lines. So I looked in toad to see what’s the problem is. There are two different grantors for the same privileg in this roles. Because I’m not a dba I’m not sure if this is normal. But I think that a “select distinct * …” could help.

    regards

    Ralf

  2. You are missing something in this query: recurrence on roles grants!
    Hereby a query I use to display the same kind of information. The output is presented so that privileges granted on roles are indented in a tree-like view. This query is part of my Users report inside the RaptorReports :

    --Objects Privileges
    WITH user_roles AS
    (SELECT granted_role FROM dba_role_privs START WITH grantee =:user_name CONNECT BY grantee = prior granted_role
    UNION ALL
    SELECT username FROM dba_users WHERE username =:user_name
    )
    SELECT lpad(' ', 2 *(level - 1)) || privilege "Privilege",
    type AS "Privilege_Type",
    object_type AS "Object_Type",
    owner AS "Owner",
    table_name AS "Name",
    grantable AS "Grantable",
    grantor AS "Grantor",
    hierarchy AS "Hierarchy"
    FROM
    (SELECT grantee,
    privilege,
    'Object' AS type,
    do.object_type,
    dtp.owner,
    dtp.table_name,
    dtp.grantable,
    dtp.grantor,
    dtp.hierarchy
    FROM dba_tab_privs dtp
    JOIN dba_objects DO
    ON dtp.owner = do.owner
    AND dtp.table_name = do.object_name
    JOIN user_roles
    ON dtp.grantee = user_roles.granted_role
    WHERE NOT object_type LIKE '%_BODY'
    UNION ALL
    SELECT grantee,
    privilege,
    'Column' AS type,
    'TABLE COLUMN' AS object_type,
    owner,
    table_name || ' (' || column_name || ')' AS table_name,
    grantable,
    grantor,
    'NO' AS hierarchy
    FROM dba_col_privs
    JOIN user_roles
    ON dba_col_privs.grantee = user_roles.granted_role
    UNION ALL
    SELECT grantee,
    granted_role AS privilege,
    'Role' AS type,
    NULL AS object_type,
    NULL AS owner,
    NULL AS table_name,
    NULL AS grantable,
    NULL AS grantor,
    NULL AS hierarchy
    FROM dba_role_privs
    )
    START WITH grantee =:user_name
    CONNECT BY grantee = prior privilege
    

  3. @ralf Very interesting. I will try to find out why. Maybe if you get a role from two grantors and one of grantor got dropped, you will still have the role. I removed the ALL in my UNION ALL query

    @Donat in my example I had a role granted to a role granted to a user. What kind of recurrence are you talking about?

  4. @Laurent. Right you are. Had not taken into account the filtering of SYS’s (and friends) objects and hence could not see some of the grants. I then jumped to conclusions…

Leave a Reply

Your email address will not be published.


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>