Oracle 级联with admin option 和 with grant option 区别

浏览数：23 / 时间：2015年06月12日

权限

用户权限有两类：

系统权限：所谓系统权限指的是用户能够做什么事情的权限（系统权限是对用户而言）。

对象权限：某种权限用户对其他用户的表或试图的存取权限（是针对表或试图而言的）。

官方文档：SQL Refference ---> 18 SQL Statements:DROP SEQUENCE to ROLLBACK 或搜索 "GRANT"

· 查看系统都具备哪些权限

select privilege from dba_sys_privs；

· 查看当前会话都具备哪些权限

select * from session_privs;//查看当前会话的系统权限

select * from user_tab_privs; //查看当前会话的对象权限

· dba_sys_privs 表字段含义

desc dba_sys_privs
Name Null? Type
----------------------------------------- -------- ----------------------------
GRANTEE NOT NULL VARCHAR2(30)//角色和用户
PRIVILEGE NOT NULL VARCHAR2(40)//权限
ADMIN_OPTION VARCHAR2(3)

· 授权通过grant

语法：GRANT object_priv[(columns)][ON object] TO {user|role|public} [WITH GRANT OPTION]

· 回收通过revoke

语法：REVOKE object_priv[(columns)][ON object] FROM {user[,user...]|role|public} [CASCADE CONSTRINTS]

撤销具有ADMIN OPTION 的系统权限（权限回收无级联适用系统权限和角色）

撤销具有GRANT OPTION 的对象权限（权限回收有级联适用对象权限）

实验测试两种级联授权

-----WITH ADMIN OPTION

[oracle@whgg ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Mar 5 11:09:53 2014

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SYS@ORCL>create user tyger1 identified by tyger1 account unlock;

User created.

SYS@ORCL>create user tyger2 identified by tyger2 account unlock;

User created.

SYS@ORCL>grant connect to tyger1 with admin option; // 级联授予tyger1 connect 权限

Grant succeeded.

SYS@ORCL>conn tyger1/tyger1
Connected.
TYGER1@ORCL>grant connect to tyger2;

Grant succeeded.

TYGER1@ORCL>conn tyger2/tyger2
Connected.
TYGER2@ORCL>conn / as sysdba
Connected.
SYS@ORCL>select * from dba_role_privs where grantee in (‘TYGER1‘,‘TYGER2‘);

GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
TYGER1 CONNECT YESYES
TYGER2 CONNECT NO YES

SYS@ORCL>revoke connect from tyger1;

Revoke succeeded.

SYS@ORCL>conn tyger1/tyger1
ERROR: // error 说明 tyger1的connect 权限已经被收回
ORA-01045: user TYGER1 lacks CREATE SESSION privilege; logon denied

Warning: You are no longer connected to ORACLE.
@>conn tyger2/tyger2
Connected.
TYGER2@ORCL> // tyger2 的 connect 还有
TYGER2@ORCL>select * from user_sys_privs;

no rows selected

TYGER2@ORCL>conn / as sysdba
Connected.
SYS@ORCL>select * from dba_role_privs where grantee in (‘TYGER1‘,‘TYGER2‘);

GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
TYGER2 CONNECT NO YES

------WITH GRANT OPTION

继续赋予tyger1 的connect 权限做实验

SYS@ORCL>grant connect to tyger1;

Grant succeeded.

SYS@ORCL>select * from dba_role_privs where grantee in (‘TYGER1‘,‘TYGER2‘);

GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
TYGER1 CONNECT NO YES
TYGER2 CONNECT NO YES

SYS@ORCL>grant select on scott.emp to tyger1 with grant option; // 赋予tyger1查询scott用户emp表的权限级联

Grant succeeded.

TYGER1@ORCL>select count(*) from scott.emp;

COUNT(*)
----------
14

TYGER1@ORCL>grant select on scott.emp to tyger2; // 通过用户tyger1 赋予查询scott 用户 emp 表的权限

Grant succeeded.

TYGER1@ORCL>conn tyger2/tyger2
Connected.
TYGER2@ORCL>select count(*) from scott.emp;

COUNT(*)
----------
14

TYGER2@ORCL>conn / as sysdba
Connected.
SYS@ORCL>desc dba_tab_privs;
Name Null? Type
----------------------------------------- -------- ----------------------------
GRANTEE NOT NULL VARCHAR2(30)
OWNER NOT NULL VARCHAR2(30)
TABLE_NAME NOT NULL VARCHAR2(30)
GRANTOR NOT NULL VARCHAR2(30)
PRIVILEGE NOT NULL VARCHAR2(40)
GRANTABLE VARCHAR2(3)
HIERARCHY VARCHAR2(3)

SYS@ORCL>col grantee for a20
SYS@ORCL>col owner for a10
SYS@ORCL>col table_name for a10
SYS@ORCL>col grantor for a10
SYS@ORCL>col privilege for a10
SYS@ORCL>l
1* select * from dba_tab_privs where grantee in (‘TYGER1‘,‘TYGER2‘)
SYS@ORCL>/

GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRA HIE
-------------------- ---------- ---------- ---------- ---------- --- ---
TYGER1 SCOTT EMP SCOTT SELECT YES NO
TYGER2 SCOTT EMP TYGER1 SELECT NO NO

SYS@ORCL>revoke select on scott.emp from tyger1; // 从用户tyger1中回收查询scott用户表emp的权限

Revoke succeeded.

SYS@ORCL>select * from dba_tab_privs where grantee in (‘TYGER1‘,‘TYGER2‘); // 用户tyger2 的查询权限级联回收

no rows selected

SYS@ORCL>conn tyger2/tyger2
Connected.
TYGER2@ORCL>select count(*) from scott.emp;
select count(*) from scott.emp
*
ERROR at line 1:
ORA-00942: table or view does not exist

TYGER2@ORCL>