Technology Network Community
Oracle Database
Fusion Middleware
Development Tools
Server & Storage Systems
Enterprise Management
Berkeley DB Family
Cloud Computing
Big Data
Business Intelligence
Migration and Modernization
E-Business Suite
PeopleSoft Enterprise
JD Edwards World
JD Edwards EnterpriseOne
User Productivity Kit Pro (UPK) and Tutor
Governance, Risk & Compliance (GRC)
Master Data Management (MDM)
Oracle CRM On Demand
On Demand: SaaS and Managed Applications
AutoVue Enterprise Visualization
Agile PLM
Endeca Experience Management
Fusion Applications
Archived Forums



General Questions

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"

Related Links

Retrieve Oracle tables
Relationship between amount of users and IOPS
User Schema Migration.
confused with blocks
ora 12500
Special Character Set in oracle
Reg: .trc and .log files
TNS-12500: TNS:listener failed to start a dedicated server process
CONTAINS problem
how do complete recovery work if we lost all control files
URGENT : To identify indexes with high level of updates
ORA-02298 and ORA-02293 in enabling constraints
Proper database layout