How do I find out which users have the rights, or privileges, to access a given object ?

The Oracle (tm) Users' Co-Operative FAQ

How do I find out which users have the rights, or privileges, to access a given object ?

Information on user object and system access privileges is contained in the rdbms data dictionary tables. For this specific question the most likely dictionary table of interest is DBA_TAB_PRIVS:

	Name                         Null?    Type
	---------------------------- -------- ----------------------------
	GRANTEE                      NOT NULL VARCHAR2(30)	<== Receiver of privilege
	OWNER                        NOT NULL VARCHAR2(30)
	TABLE_NAME                   NOT NULL VARCHAR2(30)
	GRANTOR                      NOT NULL VARCHAR2(30)	<-- Giver of privilege
	PRIVILEGE                    NOT NULL VARCHAR2(40)
	GRANTABLE                             VARCHAR2(3)	<-- Grantee has ability to grant privilege to others 
复制

A description of the column values in available in the Oracle 8i Reference manual, but they should all be pretty obvious. Since the DBA_TAB_PRIVS dictionary table (view) contains all grants on all objects in the database this table is suitable for being queried for any Oracle object: tables, views, stored code, etc.... This also means this view is a good source for SQL to generate grant statements for tables, views, stored code, etc.... Example code will follow later.

Before continuing any farther I want to note that privileges are divided into two classes: user access or DML access privileges to Oracle objects (tables, indexes, views...) and system privileges (create session, create table, create user...). In general you should restrict users to possessing only those privileges necessary for them to use their authorized applications and those privileges should be inherited through roles set up to support the application.

Privileges are issued with the GRANT command revoked with the REVOKE command. Examples:

	GRANT select, insert, update, delete, references ON my_table TO user_joe ;
	REVOKE insert, delete ON my_table FROM user_joe ;
	GRANT create public synonym TO user_joe ;
复制

Some other useful security related dictionary views are:

ALL_TAB_PRIVS		All object grants where the user or public is grantee
ALL_TAB_PRIVS_MADE	All object grants made by user or on user owned objects
ALL_TAB_PRIVS_RECD	All object grants to user or public
DBA_SYS_PRIVS		System privileges granted to users and roles
DBA_ROLES		List of all roles in the database
DBA_ROLE_PRIVS		Roles granted to users and to other roles
ROLE_ROLE_PRIVS		Roles granted to other roles
ROLE_SYS_PRIVS		System privileges granted to roles
ROLE_TAB_PRIVS		Table privileges granted to roles
SESSION_PRIVS		All privileges currently available to user
SESSION_ROLES		All roles currently available to user
USER_SYS_PRIVS		System privileges granted to current user
USER_TAB_PRIV		Grants on objects where current user is grantee, grantor, or owner
复制

WARNING the three dictionary views that start with ROLE only show privileges on objects the user has privilege on.

UT1> l
  1  select   grantee,
  2           privilege,
  3           grantable  "Adm",
  4           owner,
  5           table_name
  6  from     sys.dba_tab_privs
  7  where    grantee  =  upper('&usernm')
  8* order by grantee, owner, table_name, privilege
  GRANTEE      PRIVILEGE  Adm OWNER        TABLE_NAME
  ------------ ---------- --- ------------ -------------------------
  SEFIN        DELETE     NO  SYSTEM       SRW_FIELD
               INSERT     NO  SYSTEM       SRW_FIELD
               SELECT     NO  SYSTEM       SRW_FIELD
               UPDATE     NO  SYSTEM       SRW_FIELD
复制

Note that break on grantee is in effect to suppress repeating the user name.

 set echo off
 rem
 rem  19980729  M D Powell   New script.
 rem
 set verify off
 set pagesize 0
 set feedback off
 spool grt_&&owner._&&table_name..sql
 select 'REM  grants on &&owner..&&table_name'
 from sys.dual ;
 select 'grant '||privilege||' on '||lower(owner)||'.'||
         lower(table_name)||' to '||grantee||
         decode(grantable,'YES',' with grant option',NULL)||
         ' ;'
 from   sys.dba_tab_privs
 where  owner      = upper('&&owner')
 and    table_name = upper('&&table_name')
 order by grantee, privilege ;
 spool off
 undefine owner
 undefine table_name 
复制

Sample output:

 grant INDEX on jit.wo_master to EDSJIT ;
 grant INSERT on jit.wo_master to EDSJIT with grant option ;
 grant REFERENCES on jit.wo_master to EDSJIT ;
 grant SELECT on jit.wo_master to EDSJIT with grant option ;
复制

Addendum (7th Jan 2002) - Nagendra Prasad

The script above can be particularly useful when you are in a development environment and use export/import as means of making copies of a test bed across machines, this script comes in pretty handy to recreate the privileges bit if you have lost them for whatever reason. It is a nice piece of code to actually reverse engineer scripts from a production database.

Further reading: For a list of all system privileges see the Oracle verson# SQL manual. For information on managing user privileges see the DBA Administration manual. Starting with version 7.3 see the Oracle ver# Reference Manual for information on the dictionary tables (views) and for more information on using the dictionary see the FAQ for How do I find information about a database object: table, index, constraint, view, etc... in Oracle ?