Oracle 级联with admin option 和 with grant option 区别

　　权限
　　用户权限有两类：
　　

　　撤销具有GRANT OPTION 的对象权限（权限回收有级联 适用对象权限）


　　

　　

　　实验测试两种级联授权
　　-----WITH ADMIN OPTION
　　[oracle@whgg ~]$ sqlplus / as sysdba


SQL*Plus: Release 10.2.0.1.0 - Production on Wed Mar 5 11:09:53 2014


Copyright (c) 1982, 2005, Oracle. All rights reserved.




Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options


SYS@ORCL>create user tyger1 identified by tyger1 account unlock;


User created.


SYS@ORCL>create user tyger2 identified by tyger2 account unlock;


User created.


SYS@ORCL>grant connect to tyger1 with admin option;        // 级联授予tyger1 connect 权限


Grant succeeded.


SYS@ORCL>conn tyger1/tyger1
Connected.
TYGER1@ORCL>grant connect to tyger2;


Grant succeeded.


TYGER1@ORCL>conn tyger2/tyger2
Connected.
TYGER2@ORCL>conn / as sysdba
Connected.
SYS@ORCL>select * from dba_role_privs where grantee in ('TYGER1','TYGER2');


GRANTEE            GRANTED_ROLE          ADM DEF
------------------------------ ------------------------------ --- ---
TYGER1             CONNECT            YESYES
TYGER2             CONNECT            NO YES


SYS@ORCL>revoke connect from tyger1;


Revoke succeeded.


SYS@ORCL>conn tyger1/tyger1
ERROR:                                // error 说明 tyger1的connect 权限已经被收回
ORA-01045: user TYGER1 lacks CREATE SESSION privilege; logon denied


Warning: You are no longer connected to ORACLE.
@>conn tyger2/tyger2
Connected.
TYGER2@ORCL>                         // tyger2 的 connect 还有
TYGER2@ORCL>select * from user_sys_privs;


no rows selected


TYGER2@ORCL>conn / as sysdba
Connected.
SYS@ORCL>select * from dba_role_privs where grantee in ('TYGER1','TYGER2');


GRANTEE            GRANTED_ROLE          ADM DEF
------------------------------ ------------------------------ --- ---
TYGER2             CONNECT            NO YES

　　

　　------WITH GRANT OPTION
　　继续赋予tyger1 的connect 权限做实验
　　

　　SYS@ORCL>grant connect to tyger1;


Grant succeeded.


SYS@ORCL>select * from dba_role_privs where grantee in ('TYGER1','TYGER2');


GRANTEE            GRANTED_ROLE          ADM DEF
------------------------------ ------------------------------ --- ---
TYGER1             CONNECT            NO YES
TYGER2             CONNECT            NO YES


SYS@ORCL>grant select on scott.emp to tyger1 with grant option;        // 赋予tyger1查询scott用户emp表的权限  级联


Grant succeeded.

TYGER1@ORCL>select count(*) from scott.emp;


COUNT(*)
----------
14


TYGER1@ORCL>grant select on scott.emp to tyger2;         // 通过用户tyger1 赋予 查询scott 用户 emp 表的权限


Grant succeeded.


TYGER1@ORCL>conn tyger2/tyger2
Connected.
TYGER2@ORCL>select count(*) from scott.emp;


COUNT(*)
----------
14


TYGER2@ORCL>conn / as sysdba
Connected.
SYS@ORCL>desc dba_tab_privs;
Name                   Null?  Type
----------------------------------------- -------- ----------------------------
GRANTEE                  NOT NULL VARCHAR2(30)
OWNER                   NOT NULL VARCHAR2(30)
TABLE_NAME                NOT NULL VARCHAR2(30)
GRANTOR                  NOT NULL VARCHAR2(30)
PRIVILEGE                 NOT NULL VARCHAR2(40)
GRANTABLE                     VARCHAR2(3)
HIERARCHY                     VARCHAR2(3)

SYS@ORCL>col grantee for a20
SYS@ORCL>col owner for a10
SYS@ORCL>col table_name for a10
SYS@ORCL>col grantor for a10
SYS@ORCL>col privilege for a10
SYS@ORCL>l
1* select * from dba_tab_privs where grantee in ('TYGER1','TYGER2')
SYS@ORCL>/


GRANTEE       OWNER   TABLE_NAME GRANTOR  PRIVILEGE GRA HIE
-------------------- ---------- ---------- ---------- ---------- --- ---
TYGER1        SCOTT   EMP    SCOTT   SELECT   YES NO
TYGER2        SCOTT   EMP    TYGER1   SELECT   NO NO


SYS@ORCL>revoke select on scott.emp from tyger1;       // 从用户tyger1中回收查询scott用户表emp的权限


Revoke succeeded.


SYS@ORCL>select * from dba_tab_privs where grantee in ('TYGER1','TYGER2');         // 用户tyger2 的查询权限 级联回收


no rows selected


SYS@ORCL>conn tyger2/tyger2
Connected.
TYGER2@ORCL>select count(*) from scott.emp;
select count(*) from scott.emp
*
ERROR at line 1:
ORA-00942: table or view does not exist




TYGER2@ORCL>