List of privileges for objects


SELECT ue.name GRANTEE, u.name OWNER, o.name TABLE_NAME, ur.name GRANTOR, tpm.name PRIVILEGE,
 decode(mod(oa.option$,2), 1, 'YES', 'NO') GRANTABLE,
 decode(bitand(oa.option$,2), 2, 'YES', 'NO') HIERARCHY,
 decode(o.TYPE#, 2, 'TABLE', 4, 'VIEW',
 6, 'SEQUENCE', 7, 'PROCEDURE',
 8, 'FUNCTION', 9, 'PACKAGE',
 13, 'TYPE', 22, 'LIBRARY',
 23, 'DIRECTORY', 24, 'QUEUE',
 28, 'JAVA SOURCE', 29, 'JAVA CLASS', 30, 'JAVA RESOURCE',
 32, 'INDEXTYPE', 33, 'OPERATOR',
 42, 'MATERIALIZED VIEW', 'UNDEFINED') object_type
FROM sys.objauth$ oa, sys.obj$ o, sys.user$ u, sys.user$ ur, sys.user$ ue,
 sys.table_privilege_map tpm
WHERE oa.obj# = o.obj#
 and oa.grantor# = ur.user#
 and oa.grantee# = ue.user#
 and oa.col# is null
 and oa.privilege# = tpm.privilege
 and u.user# = o.owner#
 and o.TYPE# in (2,4,6,7,8,9,13,22,24,28,29,30,32,33,42)
 and u.name in (<the list of required schemes>)
 ---and o.name not like 'BIN$%'
ORDER BY 1, 2, 3, 5;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: