权限
用户权限有两类:
系统权限:所谓系统权限指的是用户能够做什么事情的权限(系统权限是对用户而言)。
对象权限:某种权限用户对其他用户的表或试图的存取权限(是针对表或试图而言的)。
官方文档: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>
分享到:
相关推荐
这是一个ajax+jQuery+ssh+oracle的myeclipse级联实例,可以直接导入工具后运行。
这是Adf多表级联菜单实例其中保存创建Criteria知识,多表关系,以及在Vo中使用vo当前字段过滤结果
在ORACLE 数据库中有一种方法可以实现级联查询 select * //要查询的字段 from table //具有子接点ID与父接点ID的表 start with selfid=id //给定一个startid(字段名为子接点ID,及开始的ID号) connect by prior ...
看这里没有虚级联和相邻级联的中文资料,我给大家分享一点。适合初学者了解虚级联、VC-4相邻级联和虚级联的实现等。
mysql级联更新和级联删除,区分有级联和没级联操作的区别之处
方法一:创建约束时设定级联删除(但一般由于各种原因或出于各种考虑在创建数据库时没有设定级联删除)SQL语句: 代码如下:CREATE TABLE “U_WEN_BOOK”.”FARTAB” (“FAR_ID” NUMBER(10) NOT NULL,“FAR_NAME” ...
级联菜单级联菜单级联菜单级联菜单级联菜单级联菜单级联菜单级联菜单级联菜单级联菜单
用js实现三级级联,('s1','s2')"> <option value="" selected="selected">请选择</option> <option value="A" >A</option> <option value="B">B</option> ('s2','s3')"> <option value="" selected="selected...
级联菜单级联菜单级联菜单级联菜单级联菜单级联菜单级联菜单级联菜单级联菜单级联菜单级联菜单级联菜单级联菜单级联菜单级联菜单级联菜单级联菜单级联菜单级联菜单级联菜单级联菜单级联菜单级联菜单级联菜单级联菜单...
交换机的级联和堆叠有什么联系和区别,堆叠的特点,级联的特点。
Dom查找_表单验证01_ 点击链接_添加一个div _表单验证02 _创建节点_ Html dom中的select, option_ 级联下拉列表;
菜单级联菜单级联菜单级联菜单级联菜单级联菜单级联菜单级联
JSP级联效果JSP级联效果JSP级联效果JSP级联效果JSP级联效果JSP级联效果JSP级联效果JSP级联效果JSP级联效果JSP级联效果
iBATIS 级联iBATIS 级联iBATIS 级联
操作系统 centos 4 oracle版本 10.2.0.1.0
级联菜单级联菜单级联菜单级联菜单级联菜单级联菜单级联菜单级联菜单
级联菜单 动态级联菜单 可能动态取数据
一些常用到的在oracle中的sql语句,也是我在网上找到的,分享给大家
Web树形级联菜单,连接oracle,嵌套ajax,servlet
jsp实现级联菜单jsp实现级联菜单jsp实现级联菜单jsp实现级联菜单jsp实现级联菜单