List all roles and users with access to a schema
Hi We have a large database with serveral schemas. Our auditors want a list of all users and roles which can access the factory schema. Is there a query I can run to find this. Through goolge I can get queries on tables but nothing for an entire schema. Thanks Jim
There is no such thing as a user that has access to a schema. Users are granted access to individual objects. Individual objects are owned by a particular schema. Does the auditor want a report of every Oracle user that has some access to any object owned by the schema in question (i.e. can SELECT from at least one table or view)? Or only if they have access to every object in the schema in question (which seems unlikely)? Or only if they have access to a certain number of objects in the schema? Justin
Hi Justin, Thanks for replying. Yes the auditors want a list of all users which can access any object in the schema: "The auditors have requested a list of all roles ( and associated users) and users and the level of access they have to the Factory schema's." They are also looking for the same information for all user who access oracle forms which use this schema but if I can get the ans to the first part I would be greatful. Kind Regards Jim Edited by: osheajim on Mar 8, 2013 4:23 PM
You probably want to take a look at the scripts Pete Finnigan has on his site. In particular, the who_has_priv, find_all_privs, and who_can_access scripts could all be used. And Pete has already implemented the logic to do all the recursive checks through roles granted to roles. Last I looked, I don't believe these scripts account for users that have the ANY privileges (i.e. SELECT ANY TABLE). Hopefully, you haven't granted anyone those privileges so that's not a big deal. Justin
Thanks Justin....I will give them a go... I also got this command from one of our DBA's : "select DISTINCT dtp.GRANTEE, drp.grantee, DTP.privilege from dba_tab_privs dtp, DBA_ROLE_PRIVS drp where owner = 'FACTORY' and dtp.grantee = drp.granted_role order by 1,2"