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”

ralf September 1, 2011 at 08:54

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

Donat Callens September 1, 2011 at 11:57

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

Laurent Schneider Post authorSeptember 1, 2011 at 13:11

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

Donat Callens September 1, 2011 at 13:44

@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…

Laurent Schneider

Oracle Certified Master

List of table and column privileges, including those via roles

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

Leave a Reply Cancel reply