Oracle Object Privileges

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 ON TO
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 ON TO
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 TO
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 TO
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 ON FROM
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 ON FROM
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 FROM
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 FROM
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 () ON TO ;
GRANT UPDATE (first_name, last_name) ON person TO uwclass;

Revoking Column Privileges
Revoke Column Privilege REVOKE () ON FROM ;
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;