Oracle Object Privileges
Version 12.1.0.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.
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 (editioning)
7 LOCK 20 ENQUEUE 30 FLASHBACK ARCHIVE
8 RENAME 21 DEQUEUE    
Data Dictionary Objects
ALL_COL_PRIVS CDB_TAB_PRIVS TABLE_PRIVILEGE_MAP
ALL_COL_PRIVS_MADE COLUMN_PRIVILEGES USER_COL_PRIVS
ALL_COL_PRIVS_RECD DBA_COL_PRIVS USER_COL_PRIVS_MADE
ALL_TAB_PRIVS DBA_TAB_PRIVS USER_COL_PRIVS_RECD
ALL_TAB_PRIVS_MADE OBJAUTH$ USER_TAB_PRIVS
ALL_TAB_PRIVS_RECD OBJPRIV$ USER_TAB_PRIVS_MADE
CDB_COL_PRIVS TABLE_PRIVILEGES USER_TAB_PRIVS_RECD
 
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;
 
Column Level 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 <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,
MAX(DECODE(privilege, 'EXECUTE', 'EXECUTE')) AS execute_priv
FROM dba_tab_privs
WHERE grantee IN (
  SELECT role
  FROM dba_roles)
GROUP BY table_name, grantee
ORDER BY 2,1;

Related Topics
DCL
Security
Snyonyms
System Events
System Privileges

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved