CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle Object Privileges
Version 11.1
 
General Information
Note: While the data dictionary objects are named tab, as in table, the privileges that can be granted and revoked extend to other object types including functions, packages, and procedures.
Data Dictionary Objects Related To Object Privileges
objauth$ objpriv$  
dba_col_privs all_col_privs user_col_privs
- all_col_privs_made user_col_privs_made
- all_col_privs_recd user_col_privs_recd
dba_tab_privs all_tab_privs user_tab_privs
- all_tab_privs_made user_tab_privs_made
- all_tab_privs_recd user_tab_privs_recd
column_privileges table_privileges table_privilege_map
Object Privileges
0 ALTER 9 SELECT 22 UNDER
1 AUDIT 10 UPDATE 23 ON COMMIT REFRESH
2 COMMENT 11 REFERENCES 24 QUERY REWRITE
3 DELETE 12 EXECUTE 26 DEBUG
4 GRANT 16 CREATE 27 FLASHBACK
5 INDEX 17 READ 28 MERGE VIEW
6 INSERT 18 WRITE 29 USE  (for 11gR2 - not 11gR1)
7 LOCK 20 ENQUEUE 30 FLASHBACK ARCHIVE
8 RENAME 21 DEQUEUE    
Note: Privilege 29, USE, was introduced in preparation for a new feature that will first appear in 11gR2.
 
Granting Object Privileges

Grant A Single Privilege
GRANT <privilege_name> ON <object_name> TO <schema_name>
conn uwclass/uwclass

CREATE TABLE test (
testcol VARCHAR2(20));

GRANT SELECT ON test TO abc;

set linesize 100
col grantee format a30
col table_name format a30
col privilege format a20

SELECT grantee, table_name, privilege
FROM user_tab_privs_made;

conn abc/abc

SELECT grantor, table_name, privilege
FROM user_tab_privs_recd;

Grant Multiple Privileges
GRANT <privilege_name_list> ON <object_name> TO <schema_name>
conn uwclass/uwclass

GRANT INSERT, DELETE ON test TO abc;

SELECT grantee, table_name, privilege
FROM user_tab_privs_made;

conn abc/abc

SELECT grantor, table_name, privilege
FROM user_tab_privs_recd;

Grant All Privileges
GRANT ALL ON <object_name> TO <schema_name>
conn abc/abc

GRANT ALL ON test TO uwclass;

SELECT grantee, table_name, privilege
FROM user_tab_privs_made;

conn uwclass/uwclass

SELECT grantor, table_name, privilege
FROM user_tab_privs_recd;

Grant Execute
GRANT EXECUTE ON <object_name> TO <schema_name>
conn uwclass/uwclass

GRANT EXECUTE ON getosuser TO abc;

SELECT grantee, table_name, privilege
FROM user_tab_privs_made;

conn abc/abc

SELECT grantor, table_name, privilege
FROM user_tab_privs_recd;
 
Revoking Object Privileges

Revoke A Single Privilege
REVOKE <privilege_name> ON <object_name> FROM <schema_name>
conn uwclass/uwclass

REVOKE SELECT ON test FROM abc;

SELECT grantee, table_name, privilege
FROM user_tab_privs_made;

conn abc/abc

SELECT grantor, table_name, privilege
FROM user_tab_privs_recd;

Revoke Multiple Privileges
REVOKE <privilege_name_list> ON <object_name> FROM <schema_name>
conn uwclass/uwclass

REVOKE INSERT, DELETE ON test FROM abc;

SELECT grantee, table_name, privilege
FROM user_tab_privs_made;

conn abc/abc

SELECT grantor, table_name, privilege
FROM user_tab_privs_recd;

Revoke All Privileges
REVOKE ALL ON <object_name> FROM <schema_name>
conn uwclass/uwclass

REVOKE ALL ON test FROM abc;

SELECT grantee, table_name, privilege
FROM user_tab_privs_made;

conn abc/abc

SELECT grantor, table_name, privilege
FROM user_tab_privs_recd;

Revoke Execute
REVOKE EXECUTE ON <object_name> FROM <schema_name>
conn uwclass/uwclass

REVOKE EXECUTE ON getosuser FROM abc;

SELECT grantee, table_name, privilege
FROM user_tab_privs_made;

conn abc/abc

SELECT grantor, table_name, privilege
FROM user_tab_privs_recd;
 
Granting Column Privileges
Grant Column Privileges GRANT <privilege_name> (<column_name>) ON <table_name> TO <schema_name>;
GRANT UPDATE (first_name, last_name) ON person TO uwclass;
 
Revoking Column Privileges
Revoke Column Privilege REVOKE <privilege_name> (<column_name>) ON <table_name> FROM  <schema_name>;
REVOKE UPDATE (first_name, last_name) ON person FROM uwclass;
 
Object Privilege Related Query

Show privileges by object
set linesize 121
col select_priv format a10
col insert_priv format a10
col update_priv format a10
col delete_priv format a10

SELECT table_name, grantee,
MAX(DECODE(privilege, 'SELECT', 'SELECT')) AS select_priv,
MAX(DECODE(privilege, 'DELETE', 'DELETE')) AS delete_priv,
MAX(DECODE(privilege, 'UPDATE', 'UPDATE')) AS update_priv,
MAX(DECODE(privilege, 'INSERT', 'INSERT')) AS insert_priv
FROM dba_tab_privs
WHERE grantee IN (
  SELECT role
  FROM dba_roles)
GROUP BY table_name, grantee;
 
Related Topics
DCL
Snyonyms
System Privileges
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [201 users online]    © 2010 psoug.org