Oracle Object Privileges Version 10.1(zt)

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.

1.Data Dictionary Objects Related To Object Privileges
objpriv$

all_col_privs
all_col_privs_made
all_col_privs_recd

all_tab_privs
all_tab_privs_made
all_tab_privs_recd

column_privileges

dba_col_privs
dba_col_privs_made
dba_col_privs_recd

dba_tab_privs
dba_tab_privs_made
dba_tab_privs_recd

table_privileges
table_privilege_map

user_col_privs
use_col_privs_made
user_col_privs_recd

user_tab_privs
user_tab_privs_made
user_tab_privs_recd

2.Object Privileges
ALTER
DEBUG
DELETE
EXECUTE
FLASHBACK
INDEX
INSERT
ON COMMIT REFRESH
QUERY REWRITE
READ
REFERENCES
SELECT
UNDER
UPDATE
WRITE
 
3.Granting Object Privileges
--Grant A Single Privilege
GRANT ON TO

e.g.
CREATE TABLE test (testcol VARCHAR2(20));

GRANT SELECT ON test TO abu;

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 abu/abu

SELECT grantor, table_name, privilege
FROM user_tab_privs_recd;

--Grant Multiple Privileges
GRANT ON TO
conn abc/abc

GRANT INSERT, DELETE ON test TO abu;

SELECT grantee, table_name, privilege
FROM user_tab_privs_made;

conn abu/abu

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

SELECT grantee, table_name, privilege
FROM user_tab_privs_made;

conn abu/abu

SELECT grantor, table_name, privilege
FROM user_tab_privs_recd;

--Grant Execute
GRANT EXECUTE ON TO
conn abu/abu

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;
 
4.Revoking Object Privileges
--Revoke A Single Privilege
REVOKE ON FROM
conn abc/abc

REVOKE SELECT ON test FROM abu;

SELECT grantee, table_name, privilege
FROM user_tab_privs_made;

conn abu/abu

SELECT grantor, table_name, privilege
FROM user_tab_privs_recd;

--Revoke Multiple Privileges
REVOKE ON FROM
conn abc/abc

REVOKE INSERT, DELETE ON test FROM abu;

SELECT grantee, table_name, privilege
FROM user_tab_privs_made;

conn abu/abu

SELECT grantor, table_name, privilege
FROM user_tab_privs_recd;

--Revoke All Privileges
REVOKE ALL ON FROM
conn abc/abc

REVOKE ALL ON test FROM abu;

SELECT grantee, table_name, privilege
FROM user_tab_privs_made;

conn abu/abu

SELECT grantor, table_name, privilege
FROM user_tab_privs_recd;

--Revoke Execute
REVOKE EXECUTE ON FROM
conn abu/abu

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;
 
5.Granting Column Privileges

--Grant Privilege To A Single Column
GRANT () ON TO ; 
GRANT UPATE (first_name, last_name)
ON person
TO uwclass; 
 
6.Revoking Column Privileges

--Revoke Privilege From A Single Column
REVOKE () ON FROM  ; 
REVOKE UPDATE (first_name, last_name)
ON person
FROM uwclass;
 
7.Object Privilege Related Query
  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;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29987/viewspace-51724/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29987/viewspace-51724/