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

最新推荐文章于 2026-06-06 09:03:50 发布

转载最新推荐文章于 2026-06-06 09:03:50 发布 · 471 阅读

本文通过实验演示了Oracle数据库中对象权限级联回收的过程。实验涉及secooler1、secooler2和secooler3三个用户，详细记录了从创建用户、授予对象权限到级联回收的步骤及验证结果。

Oracle权限分为系统权限和对象权限。在文章《【Privilege】Oracle系统权限非级联收回现象测试》（http://space.itpub.net/519536/viewspace-709663）给大家演示了系统权限非级联收回现象。
这里探讨关于Oracle对象权限级联回收效果，与系统权限效果正好相反：Oracle会级联回收对象权限
本实验中共涉及到三个用户：secooler1用户、secooler2用户和secooler3用户。

1.清理并初始化用户
1）清理测试用户secooler1、secooler2和secooler3
sys@secdb> conn / as sysdba
Connected.
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/secooler1
Connected.
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选项授予给secooler2
secooler1@secdb> grant select on t to secooler2 with grant option;

Grant succeeded.

3）在secooler2用户下将对象权限授予给secooler3
secooler1@secdb> conn secooler2/secooler2
Connected.
secooler2@secdb> grant select on secooler1.t to secooler3;

Grant succeeded.

3.验证对象权限授予结果
1）通过查询验证对象权限授予结果
secooler2@secdb> select * from secooler1.t;

         X
----------
         1

secooler2@secdb> conn secooler3/secooler3
secooler3@secdb> select * from secooler1.t;

         X
----------
         1

对象权限使用正常。

2）通过查看对象权限确认
secooler2@secdb> conn / as sysdba
Connected.
sys@secdb> col GRANTEE for a9
sys@secdb> col OWNER for a9
sys@secdb> col TABLE_NAME for a5
sys@secdb> col GRANTOR for a9
sys@secdb> col PRIVILEGE for a9
sys@secdb> select * from dba_tab_privs where grantee='SECOOLER2';

GRANTEE   OWNER     TABLE GRANTOR   PRIVILEGE GRA HIE
--------- --------- ----- --------- --------- --- ---
SECOOLER2 SECOOLER1 T     SECOOLER1 SELECT    YES NO

sys@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/secooler1
Connected.
secooler1@secdb> revoke select on t from secooler2;

Revoke succeeded.

5.查看级联删除效果
1）通过查询权限确认
secooler3@secdb> conn / as sysdba
Connected.
sys@secdb> select * from dba_tab_privs where grantee='SECOOLER2';

no rows selected

sys@secdb> select * from dba_tab_privs where grantee='SECOOLER3';

no rows selected

可见，曾经secooler2授予给secooler3用户的对象权限也被级联收回。两个用户都已不具有对象权限。

2）通过对象查询测试确认
secooler1@secdb> conn secooler2/secooler2
Connected.
secooler2@secdb> select * from secooler1.t;
select * from secooler1.t
                        *
ERROR at line 1:
ORA-00942: table or view does not exist

secooler2@secdb> conn secooler3/secooler3
Connected.
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.

secooler
11.10.26

-- The End --

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/519536/viewspace-709750/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/519536/viewspace-709750/

标签

#数据库