script_87.txt

orale培训教材包括了所有的sql说明和实例

---------- object_privs.txt ----------
/*
范例名称：对对象进行授权和对权限的控制
文件名称：object_privs.txt
*/



/*本练习是为了实践对对象进行授权和对权限的控制*/
sqlplus system/manager  --登陆数据库system用户
sql>
drop user peter1;       --删除用户
drop user peter2;
drop user peter3;

--peter1
create user peter1 identified by peter1; --创建用户
grant connect to peter1;                 --对用户进行授权connect,resouce在后面讲解
grant resource to peter1;
--peter2
create user peter2 identified by peter2;
grant connect to peter2;
grant resource to peter2;
--peter3
create user peter3 identified by peter3;
grant connect to peter3;
grant resource to peter3;


--Grant之后，被赋予权限的用户如何引用对象呢？
sql>connect peter1/peter1    --登陆到数据库peter1用户
create table test1           --创建表
(
name varchar2(100)
);

sql>connect peter2/peter2
--select * from test1;  结果会怎么样？
sql>connect peter1/peter1
grant select on test1 to peter2;       
--把peter1中的表test1的select权限授权给peter2

sql>connect peter2/peter2
--select * from test1;  结果会怎么样？
select * from peter1.test1;


-- with grant option的使用
sql>connect peter1/peter1
grant select on test1 to peter2 with grant option; 
--把peter1中的表test1的select权限授权给peter2,并且peter2具有再授权的能力

sql>connect peter2/peter2
grant select on peter1.test1 to peter3;            
--peter2把peter1中的表test1的select权限授权给peter3
--以connect peter3/peter3,测试peter3的
--select * from peter1.test1;


--revoke:peter1能从直接peter3中收回权限吗？
sql>connect peter1/peter1
--revoke select on test1 from peter3;  --结果是怎样？peter1能从直接peter3中收回权限吗？

revoke select on test1 from peter2;
sql>connect peter3/peter3
--select * from peter1.test1; 结果是怎样？



--测试procedure的执行权限-------------------------------------------
sql>connect peter1/peter1
--创建procedure
create or replace procedure test1_inst(i_name varchar2)  
begin
insert into test1(name) values(i_name);
commit;
end;
/


grant execute on test1_inst to peter2;    --把对test1_inst的执行权限授权给peter2

sql>connect peter2/peter2

exec peter1.test1_inst('Your name'); --通过授权的procedure往peter1.test1表insert 数据

--直接往peter1.test1表insert 数据结果是怎样？
--insert into peter1.test1(name) values('your name');  

sql>connect peter1/peter1
select * from test1;           --检查结果