Categories
dba security sql Uncategorized

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

By Laurent Schneider

Oracle Certified Master

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

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?

Leave a Reply

Your email address will not be published.