Oracle Object Privileges

Oracle Object Privileges Version 12.1.0

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
FROM dba_tab_privs
WHERE grantee IN (
SELECT role
FROM dba_roles)
GROUP BY table_name, grantee;