1)系统权限:是针对oracle系统操作而言,例如登陆create session,创建表:create table等;
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”如是。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/685769/viewspace-745307/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/685769/viewspace-745307/