基本的,众所周知对于ORACLE的系统权限,由sysdba角色的用户赋予用户A该权限,并通过WITH ADMIN OPTION将级联赋权的权限赋予用户A,A又将该权限赋予用户B时,B的该系统权限既可被A用户收回,也可被sysdba角色的用户收回(简述为系统权限可跨用户收回)。
那么,对象权限是否可以进行与上述类似的跨用户收回?本实验旨在讨论这个问题
实验名称:ORACLE对象权限跨用户收回的可行性
实验目的:研究ORACLE对象权限是否可以进行跨用户收回
实验准备:
创建两个用户A和B并赋予密码,并且创建时两个用户均为非锁定状态:
CREATE USER a IDENTIFIED BY oracle ACCOUNT UNLOCK;
CREATE USER b IDENTIFIED BY oracle ACCOUNT UNLOCK;
实验过程:
// 先检查用户a和用户b都没有对hr.employees的SELECT权限
SQL>
SQL> SELECT GRANTEE,OWNER,TABLE_NAME,PRIVILEGE FROM user_tab_privs WHERE GRANTEE = 'A';
no rows selected
SQL> SELECT GRANTEE,OWNER,TABLE_NAME,PRIVILEGE FROM user_tab_privs WHERE GRANTEE = 'B';
no rows selected
// 登录hr,将对hr.employees的SELECT权限赋给用户a
SQL> CONN hr/hr
Connected.
SQL>
SQL>
SQL> GRANT SELECT ON employees TO a WITH GRANT OPTION;
Grant succeeded.
// 查询用户a的对象权限
SQL> SELECT GRANTEE,OWNER,TABLE_NAME,PRIVILEGE FROM user_tab_privs WHERE GRANTEE = 'A';
GRANTEE OWNER TABLE_NAME PRIVILEGE
-------------------- -------------------- -------------------- --------------------
A HR EMPLOYEES SELECT
//用a用户把对hr.employees的SELECT权限赋给用户b
SQL> CONN a/oracle
Connected.
SQL>
SQL> GRANT SELECT ON hr.employees TO b;
Grant succeeded.
// 查询用户b的对象权限
SQL> SELECT GRANTEE,OWNER,TABLE_NAME,PRIVILEGE FROM user_tab_privs WHERE GRANTEE = 'B';
GRANTEE OWNER TABLE_NAME PRIVILEGE
-------------------- -------------------- -------------------- --------------------
B HR EMPLOYEES SELECT
SQL>
SQL>
//用hr用户收回用户b对 hr.employees的SELECT权限
SQL> CONN hr/hr
Connected.
SQL>
SQL> REVOKE SELECT ON hr.employees FROM b;
REVOKE SELECT ON hr.employees FROM b
*
ERROR at line 1:
ORA-01927: cannot REVOKE privileges you did not grant
//用sys用户收回用户b对 hr.employees的SELECT权限
SQL> CONN sys/oracle as sysdba;
Connected.
SQL>
SQL>
SQL> REVOKE SELECT ON hr.employees FROM b;
REVOKE SELECT ON hr.employees FROM b
*
ERROR at line 1:
ORA-01927: cannot REVOKE privileges you did not grant
//查看用户b对hr.employees的SELECT权限的直接赋权者,是用户A
SQL> CONN b/oracle
Connected.
SQL>
SQL> SELECT GRANTEE,OWNER,TABLE_NAME,PRIVILEGE,GRANTOR FROM user_tab_privs WHERE GRANTEE='B';
GRANTEE OWNER TABLE_NAME PRIVILEGE GRANTOR
-------------------- -------------------- -------------------- -------------------- --------------------
B HR EMPLOYEES SELECT A
//用A用户收回用户b对 hr.employees的SELECT权限
SQL>
SQL> conn a/oracle;
Connected.
SQL>
SQL> REVOKE SELECT ON hr.employees FROM b;
Revoke succeeded.
//查看用户b对hr.employees的SELECT权限
SQL>
SQL> conn b/oracle;
Connected.
SQL> SELECT GRANTEE,OWNER,TABLE_NAME,PRIVILEGE,GRANTOR FROM user_tab_privs WHERE GRANTEE='B';
no rows selected
SQL>
实验结果:用 hr用户 把对 hr.employees的SELECT权限添加WITH GRANT OPTION 选项 赋给 用户a 并且 由 用户a 把对 hr.employees的SELECT权限赋给用户b以后,
无论是hr用户还是sys用户,都无法直接收回用户b对hr.employees的SELECT权限。
实验结论:对象权限不可以跨用户(包括sys用户)收回,仅可以被该权限的直接赋权者收回。