Thursday, January 20, 2011

All in one privileges query

Need to see what objects (tables/views/procedures) a user has access to? Would you like to know what type of privilege they have on those objects? Would you also like to know where the grant came from (role/direct?). If so, i have a query for you:

SELECT A1.GRANTEE, 'System Priv' AS PRIV_TYPE, 'Direct' GRANT_TYPE, NULL AS ROLE, A1.PRIVILEGE AS PRIV, NULL AS TABLE_NAME
FROM DBA_SYS_PRIVS A1

WHERE LOWER(A1.GRANTEE) = LOWER('&ACCOUNT')

UNION ALL

SELECT A2.GRANTEE
,CASE
WHEN C2.TABLE_NAME IS NOT NULL THEN
'Table Priv'
ELSE
'System Priv'
END
,'Role'
,A2.GRANTED_ROLE
,B2.PRIVILEGE
,DECODE(C2.OWNER, NULL, NULL, C2.OWNER || '.' || C2.TABLE_NAME)

FROM DBA_ROLE_PRIVS A2
LEFT JOIN DBA_SYS_PRIVS B2
ON (B2.GRANTEE = A2.GRANTED_ROLE)
LEFT JOIN DBA_TAB_PRIVS C2
ON (C2.GRANTEE = A2.GRANTED_ROLE)

WHERE LOWER(A2.GRANTEE) = LOWER('&ACCOUNT')

UNION ALL

SELECT A3.GRANTEE, 'Table Priv', 'Direct', NULL, NULL, A3.OWNER || '.' || A3.TABLE_NAME
FROM DBA_TAB_PRIVS A3
WHERE LOWER(A3.GRANTEE) = LOWER('&ACCOUNT')
UNION ALL
SELECT E3.GRANTEE, 'Object Priv', 'Direct', NULL, E3.PRIVILEGE, DECODE(E3.OWNER, NULL, NULL, E3.OWNER || '.' || E3.TABLE_NAME)

FROM DBA_TAB_PRIVS E3

WHERE LOWER(E3.GRANTEE) = LOWER('&ACCOUNT')
AND E3.PRIVILEGE NOT IN ('SELECT', 'INSERT', 'DELETE', 'UPDATE')

ORDER BY 2, 3, 4, 5, 6

No comments:

Post a Comment