DB: 11gR2
OS: Linux
Friends,
I'm trying to write a query to find what all USERS have direct or indirect access (via role) to schema objects and also list object_type?
Schema = A
With the help of dba_tab_privs and dba_objects I can get all users and roles list with the object_type but need to also get another column with all the users who has access to the role.
SELECT t.grantee, r.granted_role, t.owner, t.table_name, o.object_type, t.privilege
FROM dba_tab_privs t, dba_role_privs r, dba_objects o
WHERE t.owner = 'A'
AND t.owner = o.owner
AND t.table_name = o.object_name
AND t.grantee IN
( SELECT grantee from dba_role_privs)
order by 1,3;
Above query is incorrect, was trying to give sample query.
Basically
t.grantee = USERS (replace roles with users who have access to the role)
r.granted_role = role name if t.grantee was role and replaced with the user name
t.owner = schema owner name
t.table_name = table name
o.object_type = object type (table, package, function)
t.;privilege = privilege on the object (insert, update, delete)