Oracle | Object Privileges

Oracle Object Privileges Version 23c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

Object Privileges

0	ALTER	10	UPDATE	23	ON COMMIT REFRESH
1	AUDIT	11	REFERENCES	24	QUERY REWRITE
2	COMMENT	12	EXECUTE	26	DEBUG
3	DELETE	16	CREATE	27	FLASHBACK
4	GRANT	17	READ	28	MERGE VIEW
5	INDEX	18	WRITE	29	USE (editioning)
6	INSERT	19	KEEP SEQUENCE	30	FLASHBACK ARCHIVE
7	LOCK	20	ENQUEUE	32	SIGN (blockchain table)
8	RENAME	21	DEQUEUE	33	COLLECT DEBUG INFO
9	SELECT	22	UNDER

Data Dictionary Objects

For historical purposes many of the data dictionary views are named "TAB" as in table. The objects and privileges in these views, however, reference all object types for which privileges may be granted.

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

General

List all object privileges conn / as sysdba SELECT privilege, name FROM table_privilege_map ORDER BY 1; PRIVILEGE NAME ---------- ------------------- 0 ALTER 1 AUDIT 2 COMMENT 3 DELETE 4 GRANT 5 INDEX 6 INSERT 7 LOCK 8 RENAME 9 SELECT 10 UPDATE 11 REFERENCES 12 EXECUTE 16 CREATE 17 READ 18 WRITE 19 KEEP SEQUENCE 20 ENQUEUE 21 DEQUEUE 22 UNDER 23 ON COMMIT REFRESH 24 QUERY REWRITE 26 DEBUG 27 FLASHBACK 28 MERGE VIEW 29 USE 30 FLASHBACK ARCHIVE 32 SIGN 33 COLLECT DEBUG INFO

Granting Object Privileges

Grant A Single Privilege GRANT <privilege_name> ON <object_name> TO <schema_name>;

conn uwclass/uwclass@pdbdev

      

      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@pdbdev

      

      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@pdbdev

      

      GRANT  INSERT, DELETE  ON test  TO abc;

      

      SELECT grantee, table_name, privilege

      FROM user_tab_privs_made;

      

      conn abc/abc@pdbdev

      

      SELECT grantor, table_name, privilege

      FROM user_tab_privs_recd;

Grant All Privileges GRANT ALL ON <object_name> TO <schema_name>;

conn abc/abc@pdbdev

      

      GRANT  ALL ON test  TO uwclass;

      

      SELECT grantee, table_name, privilege

      FROM user_tab_privs_made;

      

      conn uwclass/uwclass@pdbdev

      

      SELECT grantor, table_name, privilege

      FROM user_tab_privs_recd;

Grant Execute GRANT EXECUTE ON <object_name> TO <schema_name>;

conn uwclass/uwclass@pdbdev

      

      GRANT  EXECUTE ON getosuser  TO abc;

      

      SELECT grantee, table_name, privilege

      FROM user_tab_privs_made;

      

      conn abc/abc@pdbdev

      

      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@pdbdev

      

      REVOKE  SELECT ON test  FROM abc;

      

      SELECT grantee, table_name, privilege

      FROM user_tab_privs_made;

      

      conn abc/abc@pdbdev

      

      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@pdbdev

      

      REVOKE  INSERT, DELETE  ON test  FROM abc;

      

      SELECT grantee, table_name, privilege

      FROM user_tab_privs_made;

      

      conn abc/abc@pdbdev

      

      SELECT grantor, table_name, privilege

      FROM user_tab_privs_recd;

Revoke All Privileges REVOKE ALL ON <object_name> FROM <schema_name>;

conn uwclass/uwclass@pdbdev

      

      REVOKE  ALL ON test  FROM abc;

      

      SELECT grantee, table_name, privilege

      FROM user_tab_privs_made;

      

      conn abc/abc@pdbdev

      

      SELECT grantor, table_name, privilege

      FROM user_tab_privs_recd;

Revoke Execute REVOKE EXECUTE ON <object_name> FROM <schema_name>;

conn uwclass/uwclass@pdbdev

      

      REVOKE  EXECUTE ON getosuser  FROM abc;

      

      SELECT grantee, table_name, privilege

      FROM user_tab_privs_made;

      

      conn abc/abc@pdbdev

      

      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;