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

default tablespace
storage parameters
globale temporary table
correct me if wrong
Strange Tables - 10g
using sqlldr option
Error while installing Oracle 10 g
How to mention numbers in array while creating dynamic sql
oracle db
How to get current rowid in trigger when update statement is fired
before update trigger
How to pass an array in Oracle Procedure
Oracle 8i as windows service in win 2003
recover database with the backup of controlfile and datafiles
managing more than 1 TB size of Database