Oracle-权限管理

	权限相关的视图
	session_privs dba_sys_privs user_tab_privs_made user_tab_privs_recd user_col_privs_made user_col_privs_recd
	1、维护系统权限
	点评：每个人得到能工作的最小权限！ 该实验的目的是理解数据库的系统权限 你能做什么 Grant 授权 Revoke 回收
	授权： grant select any dictionary to scott; grant dba to scott;   回收： revoke dba from scott;
	查看系统权限
	--查看当前用户拥有的系统权限 scott@BEEDB> select * from session_privs; PRIVILEGE ---------------------------------------- CREATE SESSION CREATE TABLE CREATE CLUSTER CREATE SEQUENCE CREATE PROCEDURE CREATE TRIGGER CREATE TYPE CREATE OPERATOR CREATE INDEXTYPE   9 rows selected. --查看所有用户和角色的系统权限授予情况 sys@BEEDB> select grantee,count(*) from dba_sys_privs group by grantee order by 2; --查看connect角色的权限 sys@BEEDB> select * from dba_sys_privs where grantee='CONNECT';   GRANTEE                        PRIVILEGE                                ADM ------------------------------ ---------------------------------------- --- CONNECT                        CREATE SESSION                           NO --查看resource角色的权限 sys@BEEDB> select * from dba_sys_privs where grantee='RESOURCE';   GRANTEE                        PRIVILEGE                                ADM ------------------------------ ---------------------------------------- --- RESOURCE                       CREATE TRIGGER                           NO RESOURCE                       CREATE SEQUENCE                          NO RESOURCE                       CREATE TYPE                              NO RESOURCE                       CREATE PROCEDURE                         NO RESOURCE                       CREATE CLUSTER                           NO RESOURCE                       CREATE OPERATOR                          NO RESOURCE                       CREATE INDEXTYPE                         NO RESOURCE                       CREATE TABLE                             NO   8 rows selected.
	--with admin option   的系统权限
	sys@BEEDB> create user u1 identified by u1; sys@BEEDB> create user u2 identified by u2; sys@BEEDB> grant create session to u1,u2; --连带管理的系统权限 sys@BEEDB> grant select any table to u1 with admin option; sys@BEEDB> conn u1/u1 u1@BEEDB> grant select any table to u2; u1@BEEDB> conn / as sysdba sys@BEEDB> select * from dba_sys_privs where grantee in ('U1','U2');   GRANTEE                        PRIVILEGE                                ADM ------------------------------ ---------------------------------------- --- U1                             CREATE SESSION                           NO U1                             SELECT ANY TABLE                         YES U2                             SELECT ANY TABLE                         NO U2                             CREATE SESSION                           NO --转授人权限被回收 sys@BEEDB> revoke select any table from u1; --验证U2 的权限，没有被级连回收 sys@BEEDB> select * from dba_sys_privs where grantee in ('U1','U2');   GRANTEE                        PRIVILEGE                                ADM ------------------------------ ---------------------------------------- --- U1                             CREATE SESSION                           NO U2                             SELECT ANY TABLE                         NO U2                             CREATE SESSION                           N
	2、维护对象权限
	点评：对权限的细致控制！ 该实验的目的是理解数据库的对象权限 你在某个指定的对象上有什么权限 你在emp 表上是否有Select，update,Insert,Delete,Alter,index 等权限。     案例一：当你遇到一个对象，不知道有什么权限的时候。先把all 赋予一个用户，查看对象权限，然后回收，再赋给新的对象权限 grant all on emp to u1; 这句话的含义是把关于emp 表的所有权限都给了u1 用户。   scott@BEEDB> grant all on emp to u1; scott@BEEDB> select * from user_tab_privs_made where table_name='EMP' and grantee='U1'; GRANTEE    TABLE_NAME                     GRANTOR    PRIVILEGE                 GRA HIE ---------- ------------------------------ ---------- ------------------------- --- --- U1         EMP                            SCOTT      ALTER                     NO  NO U1         EMP                            SCOTT      DELETE                    NO  NO U1         EMP                            SCOTT      INDEX                     NO  NO U1         EMP                            SCOTT      INSERT                    NO  NO U1         EMP                            SCOTT      SELECT                    NO  NO U1         EMP                            SCOTT      UPDATE                    NO  NO U1         EMP                            SCOTT      REFERENCES                NO  NO U1         EMP                            SCOTT      ON COMMIT REFRESH         NO  NO U1         EMP                            SCOTT      QUERY REWRITE             NO  NO U1         EMP                            SCOTT      DEBUG                     NO  NO U1         EMP                            SCOTT      FLASHBACK                 NO  NO   11 rows selected. scott@BEEDB> revoke all on emp from u1; scott@BEEDB> grant alter,insert on emp to u1;
	表级的对象权限
	scott@BEEDB> conn scott/tiger scott@BEEDB> grant select on emp to u1; scott@BEEDB> select * from user_tab_privs_made;   --made 是付出的一方 GRANTEE    TABLE_NAME                     GRANTOR    PRIVILEGE                 GRA HIE ---------- ------------------------------ ---------- ------------------------- --- --- U1         EMP                            SCOTT      ALTER                     NO  NO U1         EMP                            SCOTT      INSERT                    NO  NO U1         EMP                            SCOTT      SELECT                    NO  NO   scott@BEEDB> conn u1/u1 u1@BEEDB> select * from user_tab_privs_recd;--recd 是获得的一方 OWNER      TABLE_NAME                     GRANTOR    PRIVILEGE                 GRA HIE ---------- ------------------------------ ---------- ------------------------- --- --- SCOTT      EMP                            SCOTT      SELECT                    NO  NO SCOTT      EMP                            SCOTT      INSERT                    NO  NO SCOTT      EMP                            SCOTT      ALTER                     NO  NO
	列级的对象权限
	--授权列级的对象权限 scott@BEEDB> grant update(sal) on emp to u1; --查看列级权限 scott@BEEDB> select * from user_col_privs_made; GRANTEE    TABLE_NAME COLUMN_NAM GRANTOR    PRIVILEGE                 GRA ---------- ---------- ---------- ---------- ------------------------- --- U1         EMP        SAL        SCOTT      UPDATE                    NO scott@BEEDB> conn u1/u1 u1@BEEDB> select * from user_col_privs_recd; OWNER      TABLE_NAME COLUMN_NAM GRANTOR    PRIVILEGE                 GRA ---------- ---------- ---------- ---------- ------------------------- --- SCOTT      EMP        SAL        SCOTT      UPDATE                    NO
	--with grant option   对象权限的级连回收问题
	With grant option:中间人的对象权限被回收后，被中间人授予的权限也会级连回收 With admin option:中间人的对象权限被回收后，被中间人授予的权限不会被级连回收   scott@BEEDB> grant delete on emp to u1 with grant option; scott@BEEDB> select * from user_tab_privs_made; GRANTEE    TABLE_NAME GRANTOR    PRIVILEGE                 GRA HIE ---------- ---------- ---------- ------------------------- --- --- U1         EMP        SCOTT      ALTER                     NO  NO U1         EMP        SCOTT      DELETE                    YES NO U1         EMP        SCOTT      INSERT                    NO  NO U1         EMP        SCOTT      SELECT                    NO  NO   scott@BEEDB> conn u1/u1 u1@BEEDB> grant delete on scott.emp to u2; u1@BEEDB> select * from user_tab_privs_recd; OWNER      TABLE_NAME GRANTOR    PRIVILEGE                 GRA HIE ---------- ---------- ---------- ------------------------- --- --- SCOTT      EMP        SCOTT      SELECT                    NO  NO SCOTT      EMP        SCOTT      INSERT                    NO  NO SCOTT      EMP        SCOTT      DELETE                    YES NO SCOTT      EMP        SCOTT      ALTER                     NO  NO   u1@BEEDB> select * from user_tab_privs_made; no rows selected   u1@BEEDB> conn u2/u2 u2@BEEDB> select * from user_tab_privs_recd;   OWNER      TABLE_NAME GRANTOR    PRIVILEGE                 GRA HIE ---------- ---------- ---------- ------------------------- --- --- SCOTT      EMP        U1         DELETE                    NO  NO   u2@BEEDB> conn scott/tiger scott@BEEDB> revoke delete on scott.emp from u1; scott@BEEDB> conn u2/u2 u2@BEEDB> select * from user_tab_privs_recd; no rows selected

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

转载于:http://blog.itpub.net/30024909/viewspace-1371677/