权限
用户权限有两类：
系统权限：所谓系统权限指的是用户能够做什么事情的权限（系统权限是对用户而言）。
对象权限：某种权限用户对其他用户的表或试图的存取权限（是针对表或试图而言的）。
官方文档：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>
Oracle 级联with admin option 和 with grant option 区别,布布扣,bubuko.com