| 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. |
| 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 (editioning) |
| 7 |
LOCK |
20 |
ENQUEUE |
30 |
FLASHBACK ARCHIVE |
| 8 |
RENAME |
21 |
DEQUEUE |
|
|
|
| |
| 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; |
