| 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 <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; |
