oracle对象权限回收,【Privilege】Oracle对象权限级联收回现象测试

Oracle权限分为系统权限和对象权限。在文章《【Privilege】Oracle系统权限非级联收回现象测试》(http://space.itpub.net/519536/viewspace-709663)给大家演示了系统权限非级联收回现象。这里探讨关于Oracle对象权限级联回收效果，与系统权限效果正好相反：Oracle会级联回收对象权限本实验中共涉及到三个用户：secooler1用户、secooler2用户和secooler3用户。1.清理并初始化用户1)清理测试用户secooler1、secooler2和secooler3sys@secdb> conn / as sysdbaConnected.sys@secdb> drop user secooler1 cascade;User dropped.sys@secdb> drop user secooler2;User dropped.sys@secdb> drop user secooler3;User dropped.2)创建secooler1、secooler2和secooler3用户并授予最基本的系统权限(1)创建secooler1用户，并授予创建会话、创建表以及表空间使用权限，用于后续在secooler1用户中创建测试表。sys@secdb> create user secooler1 identified by secooler1;User created.sys@secdb> grant create session,create table,unlimited tablespace to secooler1;Grant succeeded.(2)创建secooler2用户并授予最基本的创建会话的权限sys@secdb> create user secooler2 identified by secooler2;User created.sys@secdb> grant create session to secooler2;Grant succeeded.(3)同样，创建secooler3用户并授予最基本的创建会话的权限sys@secdb> create user secooler3 identified by secooler3;User created.sys@secdb> grant create session to secooler3;Grant succeeded.2.创建数据库对象并完成对象权限授权1)在secooler1用户下创建表T并初始化数据。这里，在secooler1用户下创建表为例sys@secdb> conn secooler1/secooler1Connected.secooler1@secdb> create table t (x int);Table created.secooler1@secdb> insert into t values (1);1 row created.2)在secooler1用户下完成将查询T表的对象权限以with grant option选项授予给secooler2secooler1@secdb> grant select on t to secooler2 with grant option;Grant succeeded.3)在secooler2用户下将对象权限授予给secooler3secooler1@secdb> conn secooler2/secooler2Connected.secooler2@secdb> grant select on secooler1.t to secooler3;Grant succeeded.3.验证对象权限授予结果1)通过查询验证对象权限授予结果secooler2@secdb> select * from secooler1.t;X----------1secooler2@secdb> conn secooler3/secooler3secooler3@secdb> select * from secooler1.t;X----------1对象权限使用正常。2)通过查看对象权限确认secooler2@secdb> conn / as sysdbaConnected.sys@secdb> col GRANTEE for a9sys@secdb> col OWNER for a9sys@secdb> col TABLE_NAME for a5sys@secdb> col GRANTOR for a9sys@secdb> col PRIVILEGE for a9sys@secdb> select * from dba_tab_privs where grantee='SECOOLER2';GRANTEE   OWNER     TABLE GRANTOR   PRIVILEGE GRA HIE--------- --------- ----- --------- --------- --- ---SECOOLER2 SECOOLER1 T     SECOOLER1 SELECT    YES NOsys@secdb> select * from dba_tab_privs where grantee='SECOOLER3';GRANTEE   OWNER     TABLE GRANTOR   PRIVILEGE GRA HIE--------- --------- ----- --------- --------- --- ---SECOOLER3 SECOOLER1 T     SECOOLER2 SELECT    NO  NO查看结果，对象权限授予信息显示正常。4.在secooler1用户中回收secooler2用户的对象权限sys@secdb> conn secooler1/secooler1Connected.secooler1@secdb> revoke select on t from secooler2;Revoke succeeded.5.查看级联删除效果1)通过查询权限确认secooler3@secdb> conn / as sysdbaConnected.sys@secdb> select * from dba_tab_privs where grantee='SECOOLER2';no rows selectedsys@secdb> select * from dba_tab_privs where grantee='SECOOLER3';no rows selected可见，曾经secooler2授予给secooler3用户的对象权限也被级联收回。两个用户都已不具有对象权限。2)通过对象查询测试确认secooler1@secdb> conn secooler2/secooler2Connected.secooler2@secdb> select * from secooler1.t;select * from secooler1.t*ERROR at line 1:ORA-00942: table or view does not existsecooler2@secdb> conn secooler3/secooler3Connected.secooler3@secdb> select * from secooler1.t;select * from secooler1.t*ERROR at line 1:ORA-00942: table or view does not exist可见secooler2和secooler3的对象权限都被收回。6.小结secooler1回收secooler2的对象权限的同时也会收回secooler3的对象权限，这便是Oracle关于对象权限级联收回的策略。Good luck.secooler11.10.26-- The End --