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 Comments

  • 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

  • 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

  • @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?

  • @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.