2）对象权限：是针对对象操作的权限，例如对表的DML操作、sp的exec等；

1)   创建用户jeff和emi

SYS@ORA11GR2>create user jeff identified by jeff;

User created.

SYS@ORA11GR2>create user emi identified by emi;

User created.

SYS@ORA11GR2>

2)   只给jeff和emi用户connect角色

SYS@ORA11GR2>grant connect to jeff,emi;

Grant succeeded.

SYS@ORA11GR2>

3)   连接jeff用户成功，但无法创建表

SYS@ORA11GR2>conn jeff/jeff

Connected.

JEFF@ORA11GR2>create table t(x int);

create table t(x int)

*

ERROR at line 1:

ORA-01031: insufficient privileges

JEFF@ORA11GR2>

4)   连接emi用户成功，但无法创建表

JEFF@ORA11GR2>conn emi/emi

Connected.

EMI@ORA11GR2>create table t(x int);

create table t(x int)

*

ERROR at line 1:

ORA-01031: insufficient privileges

5)   利用sys用户将create table权限赋予jeff用户并且使用admin option子句

EMI@ORA11GR2>conn / as sysdba

Connected.

SYS@ORA11GR2>

SYS@ORA11GR2>grant create table to jeff with admin option;

Grant succeeded.

SYS@ORA11GR2>

6)   再次连接jeff用户并成功创建了表

SYS@ORA11GR2>conn jeff/jeff

Connected.

JEFF@ORA11GR2>create table t(x int);

Table created.

JEFF@ORA11GR2>

7)   Jeff用户将sys赋予它的create table权限转赋给emi用户，赋予成功

JEFF@ORA11GR2>grant create table to emi;

Grant succeeded.

JEFF@ORA11GR2>

8)   连接emi用户也成功的创建了表

JEFF@ORA11GR2>conn emi/emi

Connected.

EMI@ORA11GR2>create table t(x int);

Table created.

EMI@ORA11GR2>

9)   连接会sys用户，收回jeff的create table的权限

EMI@ORA11GR2>conn / as sysdba

Connected.

SYS@ORA11GR2>revoke create table from jeff;

Revoke succeeded.

SYS@ORA11GR2>

10)  第三次连接jeff用户，因为jeff用户已无create table权限，所以无权建表

SYS@ORA11GR2>conn jeff/jeff

Connected.

JEFF@ORA11GR2>create table t1(x int);

create table t1(x int)

*

ERROR at line 1:

ORA-01031: insufficient privileges

JEFF@ORA11GR2>

11)  再次连接到emi用户，emi的建表权限是jeff给的，虽然jeff已经 没有了建表的权限，不过由于当初jeff给emi建表权限的时候，jeff的建表权限是sys连通admin option一起赋予的，虽然jeff此时已经没有了建表权限，不过，emi依然还有建表的权限，就是因为，当初jeff的建表权限是带admin option子句的。

JEFF@ORA11GR2>conn emi/emi

Connected.

EMI@ORA11GR2>create table t1(x int);

Table created.

EMI@ORA11GR2>

1)   清理环境并重新创建用户，赋予create session权限(即connect角色中的唯一权限)，测试连接没问题并且都无权创建表

SYS@ORA11GR2>drop user jeff cascade;

User dropped.

SYS@ORA11GR2>drop user emi cascade;

User dropped.

SYS@ORA11GR2>create user jeff identified by jeff;

User created.

SYS@ORA11GR2>create user emi identified by emi;

User created.

SYS@ORA11GR2>grant create session to jeff,emi;

Grant succeeded.

SYS@ORA11GR2>conn jeff/jeff

Connected.

JEFF@ORA11GR2>

JEFF@ORA11GR2>select count(*) from scott.emp;

select count(*) from scott.emp

                           *

ERROR at line 1:

ORA-00942: table or view does not exist

JEFF@ORA11GR2>

JEFF@ORA11GR2>conn emi/emi

Connected.

EMI@ORA11GR2>

EMI@ORA11GR2>select count(*) from scott.emp;

select count(*) from scott.emp

                           *

ERROR at line 1:

ORA-00942: table or view does not exist

EMI@ORA11GR2>

2)   回到sys用户下，赋予jeff用户select on scott.emp的对象查询权限并且加上grant option子句

SYS@ORA11GR2>grant select on scott.emp to jeff with grant option;

Grant succeeded.

SYS@ORA11GR2>

SYS@ORA11GR2>conn jeff/jeff

Connected.

JEFF@ORA11GR2>select count(*) from scott.emp;

  COUNT(*)

----------

        14

JEFF@ORA11GR2>

3)   由于jeff用户select on scott.emp的对象查询权限是sys用户赋予的并且加上grant option子句，所以jeff有权利将select on scott.emp的对象查询权限给emi用户

JEFF@ORA11GR2>grant select on scott.emp to emi with grant option;

Grant succeeded.

JEFF@ORA11GR2>

JEFF@ORA11GR2>conn emi/emi

Connected.

EMI@ORA11GR2>select count(*) from scott.emp;

  COUNT(*)

----------

        14

EMI@ORA11GR2>

4)   回到sys用户，回收jeff用户select on scott.emp的对象查询权限

EMI@ORA11GR2>conn / as sysdba

Connected.

SYS@ORA11GR2>revoke select on scott.emp from jeff;

Revoke succeeded.

SYS@ORA11GR2>

5)   验证jeff是否还有查询scott用户的emp的权限，结果是没有

SYS@ORA11GR2>conn jeff/jeff

Connected.

JEFF@ORA11GR2>select count(*) from scott.emp;

select count(*) from scott.emp

                           *

ERROR at line 1:

ORA-00942: table or view does not exist

JEFF@ORA11GR2>

6)   验证emi是否依然还有查询scott用户的emp的权限，结果是也没有

JEFF@ORA11GR2>conn emi/emi

Connected.

EMI@ORA11GR2>select count(*) from scott.emp;

select count(*) from scott.emp

                           *

ERROR at line 1:

ORA-00942: table or view does not exist

EMI@ORA11GR2>

小结：

1.ADMIN OPTION：仅用于系统权限，在撤销权限时无级联效应；

2.GRANT OPTION：仅用于对象权限，在撤销权限是，级联应用；

3.系统权限不加ADMIN OPTION子句，那么被赋予的用户没有权利“转赠”他人；“GRANT OPTION”如是。