OracleÖ®¹ÜÀíȨÏÞ

ȨÏÞ(Privilege)ÊÇÖ¸Ö´ÐÐÌض¨ÀàÐÍSQLÃüÁî»ò·ÃÎÊÆäËû·½°¸¶ÔÏóµÄȨÀû,ȨÏÞ°üÀ¨ÏµÍ³È¨Ï޺ͶÔÏóȨÏÞÁ½ÖÖÀàÐÍ.ϵͳȨÏÞ(System Privilege)ÊÇÖ¸Ö´ÐÐÌض¨ÀàÐÍSQLÃüÁîµÄȨÀû,ËüÓÃÓÚ¿ØÖÆÓû§¿ÉÒÔÖ´ÐеÄÒ»¸ö»òÒ»×éÊý¾Ý¿â²Ù×÷.³£ÓõÄϵͳȨÏÞ:
CREATE SESSION Á¬½Óµ½Êý¾Ý¿â
CREATE TABLE ½¨±í
CREATE VIEW ½¨Á¢ÊÓͼ
CREATE PUBLIC SYNONYM ½¨Á¢Í¬Òå´Ê
CREATE SEQUENCE ½¨Á¢ÐòÁÐ
CREATE PROCEDURE ½¨Á¢¹ý³Ì,º¯ÊýºÍ°ü
CREATE TRIGGER ½¨Á¢´¥·¢Æ÷
CREATE CLUSTER ½¨Á¢´Ø
CREATE TYPE ½¨Á¢¶ÔÏóÀàÐÍ
CREATE DATABASE LINK ½¨Á¢Êý¾Ý¿âÁ´
ÁíÍâ,oracle»¹ÌṩÁËÒ»ÀàANYϵͳȨÏÞ,µ±Óû§¾ßÓиÃÀàϵͳȨÏÞʱ,¿ÉÒÔÔÚÈκη½°¸ÖÐÖ´ÐÐÏàÓ¦²Ù×÷.Èç¹ûÓû§¾ßÓÐSELECT ANY TABLEϵͳȨÏÞ,ÄÇôÓû§¿ÉÒÔ²éѯÈκη½°¸µÄ±í.
ÐèҪעÒâ,µ±³õʼ»¯²ÎÊýO7_DICTIONARY_ACCESSIBILITY±»ÉèÖÃΪFALSEʱ,³ýÁËSYSDBAºÍDBAÓû§Íâ,¼´Ê¹Óû§¾ßÓÐSELECT ANY TABLEȨÏÞ,Ò²²»ÄÜ·ÃÎÊÊý¾Ý×Öµä»ù±íºÍÊý¾Ý×ÖµäÊÓͼDBA_XXX.
¶ÔÏóȨÏÞ(Object privilege)ÊÇÖ¸·ÃÎÊÆäËû·½°¸¶ÔÏóµÄȨÀû,ËüÓÃÓÚ¿ØÖÆÓû§¶ÔÆäËû·½°¸¶ÔÏóµÄ·ÃÎÊ.
¹ÜÀíϵͳȨÏÞ
ÊÚÓèϵͳȨÏÞ
GRANT system_priv[,system_priv,…]
To {user | role | PUBLIC},[,{user | role | PUBLIC} ] …
[WITH ADMIN OPTION];
System_priv ÓÃÓÚÖ¸¶¨ÏµÍ³È¨ÏÞ.ÊÚȨÓû§×éPUBLIC,ÔÚÊÚÓèϵͳȨÏÞʱ¿ÉÒÔ´øÓÐWITH ADMIN OPTIONÑ¡Ïî,´øÓиÃÑ¡Ïîºó,±»ÊÚȨµÄÓû§,½ÇÉ«»¹¿ÉÒÔ½«ÏàÓ¦µÄȨÏÞÊÚÓèÆäËûÓû§,½ÇÉ«.ÐèҪעÒâ,ϵͳȨÏÞUNLIMITED TABLESPACE²»ÄÜÊÚÓè½ÇÉ«.
CRANT CREATE SESSION,CREATE TABLE TO blake WITH ADMIN OPTION;
GRANT GREATE VIEW TO blake;
ÏÔʾϵͳȨÏÞ.
1,ÏÔʾËùÓÐϵͳȨÏÞ.
SELECT name from system_privilege_map;
2,ÏÔʾÓû§»ò½ÇÉ«Ëù¾ßÓеÄϵͳȨÏÞ.
²éѯÊý¾Ý×ÖµäÊÓͼDBA_SYS_PRIVS,¿ÉÒÔÏÔʾËùÓÐÓû§»ò½ÇÉ«Ëù¾ßÓеÄϵͳȨÏÞ.ͨ¹ý²éѯÊý¾Ý×ÖµäÊÓͼUSER_SYS_PRIVS,¿ÉÒÔÏÔʾµ±Ç°Óû§Ëù¾ßÓеÄϵͳȨÏÞ.
SELECT * from dba_sys_privs WHERE GRANTEE=’BLAKE’;
GRANTEEÓÃÓÚ±êʶȨÏÞÓµÓÐÕß(Óû§»ò½ÇÉ«),PRIVILEGEÓÃÓÚ±êʶϵͳȨÏÞ.ADMIN_OPTIONÓÃÓÚ±êʶϵͳȨÏÞ´«ÊÚÑ¡Ïî,ÆäÖÐYES±íʾ¿ÉÒÔתÊÚ¸ÃϵͳȨÏÞ,NO±íʾ²»ÄÜתÊÚ¸ÃϵͳȨÏÞ.
3,ÏÔʾµ±Ç°»á»°Ëù¾ßÓеÄϵͳȨÏÞ.
ͨ¹ý²éѯÊý¾Ý×ÖµäÊÓͼsession_privs,¿ÉÒÔÏÔʾµ±Ç°»á»°Ëù¾ßÓеÄϵͳȨÏÞ.
SELECT * from session_privs;
ÊÕ»ØϵͳȨÏÞ
ÊÕ»ØϵͳȨÏÞÒ»°ãÊÇ