|Author's name: Mark D Powell
Author's Email: Mark.Powell@eds.com
|Date written: 24th Sept 2001
Addendum: 7th Jan 2002
Oracle version(s): 7.0 - 22.214.171.124
|How do I find out which users have the rights, or privileges, to access a given object ?|
Back to index of questions
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
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 ;
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 ?
Back to top
Back to index of questions