Oracle 权限介绍及管理

- 日理万妓 2022-03-19 13:06 633阅读 0赞

Oracle权限分为两种:  
分别是  
**系统权限(system privileges): ** 包括创建/删除表 , 创建删除用户等.  
**对象权限(object privileges): ** 操控数据库特定对象的 **内容**,例如select某张表,插入数据到某张表等.  
  
另外介绍个概念:  
**方案(schema):** is a collection of database objects (used by a user.).  
就是对象的集合啦,例如表,试图,,索引的集合.  
  
  
**1.系统权限(system privileges)**  
对用户和角色来讲,oracle大概有100多个不同的系统权限,系统权限一般由dba提供.  
典型的系统权限如下表:

![44412702013100321121101.jpg][]  
  
**1.1 创建用户**  
语法如下:  
**CREATE USER** username  
**IDENTIFIED BY** passwd;  
  
前提系你必须具有CREATE USER的权限, 一般只有DBA才有这个权限啦~  
如下图,创建1个用户Bill

![444127020131003211628058.jpg][]

**1.2 使用系统权限**  
一旦1个用户被创建,DBA就能指定系统权限给这个用户.  
语法如下:  
**GRANT PRIVILEGE** \[privilege,.....\]  
**TO ** \[user1,user2...| role, PUBLIC\];  
  
其中role就是角色啦, 至于PUBLIC就是之所有用户啦,慎用啊.  
一般来讲1个程序员帐号需要如下权限:  
CREATE SESSION (连接数据库啊必要啊)/ CREATE TABLE/ CREATE SEQUENCE /CREATE VIEW/ CREATE PROCEDURE  
  
下图给用户BILL分配这些权限:

![444127020131003211746018.jpg][]

一旦dba分配权限给用户,用户就立即可使用它们.  
如下图oracle这时已经可以用BILL账户登陆了:

![444127020131003212121038.jpg][]

**1.2.1 查看用户默认表空间和临时表空间**  
可以查看user\_users 视图来查看用户的默认表空间和临时表空间:

![444127020131003212724099.jpg][]

可看出BILL账户的默认表空间是USERS, 临时表空间是TEMP啦  
  
**1.2.2 修改用户默认表空间和临时表空间**  
注意这个动作一般只有DBA可以执行, ALTER USER的权限. 下图把BILL账户的默认表空间改成了TS\_EXAMPLE

![444127020131003212850068.jpg][]  
  
  
这时我们用BILL账户尝试建几张表,  
如下图, 当建表语句没有制定表空间时,那张表默认就存放在TS\_EXAMPLE.

![444127020131003212957076.jpg][]1003213  
  
那么如果在建表时指定表空间呢,例如想在SYSTEM表空间建1张表,允吴允许咧?  
答案是肯定的,1个具有CREATE TABLE权限的用户可以在任何表空间建立新表. 如下图

![444127020131003213128097.jpg][]

**1.2.3 表空间配额 quota**  
   
那么就是否1个居有CREATE table的user就可以无限制的使用任何表空间呢? 答案是否定的.  
BILL虽然可以无限制地建新表,但他甚至无法插入数据到自己的默认表空间..

![444127020131003213313010.jpg][]

如上图呢个悲剧,系统表示BILL账户没有权限使用TS\_EXAMPLE, 其实也就是说BILL在TS\_EXAMPLE的配额空间为0;  
这时是无法插入新数据的.(查询另说)  
可以查看user\_tablespaces可以知道账户具有那些表空间的权限(插入数据的权限),如下图,BILL没有任何表空间的配额

![444127020131003213438070.jpg][]

这时就需要给BILL分配表空间配额,语法如下:  
**alter user** username **quota** xxx  **on** tablespace\_name;  
  
或者在创建用户时同时指定表空间配额:  
**CREATE USER** username  
**IDENTIFIED BY** passwd  
**DEFAULT TABLESPACE** tp\_name1  
**TEMPORARY TABLESPACE** tmp\_tp\_name  
**QUOTA** 100M **on** tp\_name1  
**QUOTA** 200M **on** tp\_name2  
**PROFILE** profilename;(PRO FILE 在这里暂不讨论)  
  
又或者:  
**grant unlimited tablespace to** username;  
这样的话所有表空间都无限制使用了,慎用啊  
  
如下图 我用SYS账户分配给BILL 100M表空间配额在TS\_EXAMPLE, 和10M表空间配额在USER:

![444127020131003213641037.jpg][]  
  
注意上图的100m是指设定在100m 而不是增加100m哦  
  
还有,ORACLE 吾允许分配临时表空间的配额...临时表空间自动供大家使用..

![444127020131003213759066.jpg][]

依家再用BILL查看user\_tablespaces视图,就可以见到bill有权限使用上面的两个表空间了

![444127020131003213900033.jpg][]

至于要查看每个表空间具体有几多配额,就需要查看user\_ts\_qaotas视图,如下图:

![444127020131003214006063.jpg][]  
  
这时就应该可以往tb\_bill\_test插入数据了,重新执行一次insert 语句. 成功!

![444127020131003214058090.jpg][]

而且见到已经使用了8个block,  64kb..

**1.3 查看系统权限**  
用户可以 利用user\_sys\_privs视图来查看自己有什么权限  
 ![44412702013100321423409.jpg][]

可以见到 BILL的5个系统权限, 都是上面SYS分配给他的啊

至于最后面那个ADMIN\_OPTION 要注意,  
这里的ADMIN\_OPTION都是NO, 也就是说这些系统权限BILL都只能自己用啦,不能分配给别人用啦~  
  
如果SYS分配这些权限给BILL同时带上with admin option 这条语句, 那么BILL就有不但只可以使用这些权限,还可以分配给他人使用.  
**GRANT PRIVILEGE** \[privilege,.....\]  
**TO ** \[user1,user2...| role, PUBLIC\]  
**WITH ADMIN OPTION**  
  
  
**1.4 回收系统权限**  
对应GRANT,回收的关键字是REVOKE啦,语法如下:  
**REVOKE PRIVILEGE** \[privilege,.....\]  
**from ** \[user1,user2...| role, PUBLIC\]  
  
如下图,我用SYS帐号回收了BILL的Create view的系统权限,那么bill就不能创建视图了.

![444127020131003214340019.jpg][]

**1.5 修改自己的密码**  
BILL 是可以通过  
Alter user BILL identified by xxxxxxx  
  
来修改自己的密码的,即使BILL没有Alter USER的权限, 但是要修改其他选项的话,就必须要有ALTER USER权限了.  
其他选项一般由DBA执行修改的啦~  
  
**2.** **角色(Role)**  
  
      角色是命名的可以授予用户的相关权限的组,该方法使权限的授予,撤回更加容易管理.  
  
**2.1 创建角色:**  
  语法:  
  **  CREATE ROLE** rolename;  
  
当然前提是有CREATE ROLE的权限啦,一般都是由DBA去执行啦~  
而且角色名不能与 **存在的用户名**相同啊, oracle提示创建失败的, 想想也是,不然分配权限时就会混绕了.  
  
下图我创建了1个角色manager:

![444127020131003214453042.jpg][]  
  
**2.2 分配权限给角色**

语法跟分配权限给用户是一样的,只不过用角色名去取代用户名啦:  
下图: 我将create view, select any table 分给manager,  
那么manager这个角色就可以创建试图,和select任何模式(任何用户)下的表了.

![444127020131003215233086.jpg][]

**2.3 分配角色给用户**  
  
到最后, 要将角色分给用户,那么用户就有了这个角色的权限.  
语法:  
**grant** rolename **to** username;  
  
如下图: 我将manager 角色分给BILL, 那么bill就拥有了manager角色的权限(create view, select any table)

![444127020131003214741077.jpg][]

可以见到,BILL虽然拥有了manager,BILL的系统权限并没有增加.  
因为角色权限视图要联合另外的试图: user\_role\_privs ,来查看:

![444127020131003214921033.jpg][]

![444127020131003215022018.jpg][]

或者直接查看role\_sys\_privs视图:

![444127020131003215529036.jpg][]

注意亲测过:  
**在11g中用户需要重新登陆才能使用新分配角色的权限..**  
  
如下图,BILL已经可以查看scott用户下的表了.  
![444127020131003215621044.jpg][]

**2.4 回收角色.**  
  
这个一样啦,语法  
**REVOKE** rolename **from** username;  
  
  
**3.** **对象权限(object privileges)**  
  
         对象权限是 指在指定的表,视图,序列或过程上执行制定动作的权限或权利, 每1个对象都有1个特殊的可以授予的权限集. 下图列出了各种对象的权限集合.

![444127020131003215745028.jpg][]

用户序列的权限只有select 和 alter. update, references和insert 权限可以通过指定1个可更新列的的子集被限制.  
      select的权限可以通过创建带1个列子集的视图并且只授予select权限来限制.  
      1个同义词上被授予的权限将转换为由同义词引用的基表的权限.  
  
**3.1 分配和查看对象权限**  
  
语法:  
***GRANT***    object\_priv\[(columns)\]   /  ALL  
**ON  **        object  
**to **          \{user|role|PUBLIC\}  
**\[WITH GRANT OPTION\]**  
  
  
其中  
object\_prive    是指被授予的对象权限,注意不带对象名哦  
ALL                指定对象所有的对象权限  
columns           从1个表或视图中指定被授予权限的列  
ON object       是被授予权限所属于的对象  
TO                  指定权限被授予谁  
PUBLIC            授予权限给所有用户  
WITH GRANT OPTION    允许被授予权限的人再授予这个权限给其他用户或角色.  
  
  
  **3.1.1 表对象的权限**  
如下图,首先我撤销BILL的manager角色,这样他是不能select SCOTT的表的

![444127020131003215915014.jpg][]

接下来我用SCOTT帐号分配emp表的select 权限给BILL

![444127020131003220033094.jpg][]

再分配update ename, deptno的权限给bill

![444127020131003220119050.jpg][]

这样bill 能查看emp表内容,而且能update ename,deptno列,但是update sal列是失败的

![444127020131003220207016.jpg][]

表的对象权限可以通过查看如下视图.  
作为GRANTEE  
BILL 可以通过查看 **USER\_TAB\_PRIVS\_RECD** 或 **USER\_TAB\_PRIVS**来查看分配了给自己的对象权限:  
              通过查看 **USER\_COL\_PRIVS\_RECD** 来查看分配了给自己的对象的 **列**的权限.

![444127020131003220412079.jpg][]

还可以通过查看 **ROLE\_TAB\_PRIVS** 来查看自己的 **角色**所拥有的 **对象权限和对象列**的权限:

![444127020131003220617098.jpg][]

而作为GRANTOR  
SCOTT可以通过 **USER\_TAB\_PRIVS\_MADE**  **  **来查看自己分配了什么表对象权限给了什么用户或角色.  
         可以通过 **USER\_COL\_PRIVS\_MADE**  **  **来查看自己分配了什么列对象权限给了什么用户或角色.

![444127020131003220753066.jpg][]

注意上图TL是角色名  
  
  **3.1.2 PROCEDURE对象的权限**  
  
**执行权限:**  
有上面的表格可见到, 对于PROCEDURE来讲, 只有1个EXECUTE的权限..  
例如BILL这时是无法执行scott的stored proc  USP\_EMP\_LIST的:

![444127020131003221016074.jpg][]

接下来我用scott帐号分配execute权限给BILL:

![444127020131003221119032.jpg][]

此时BILL就可以执行usp\_emp\_list 了, 而且也可以从user\_tab\_privs\_recd里查看到相关信息.

![444127020131003221247019.jpg][]

**查看代码权限:**  
但是BILL要查看该stored proc的代码要什么权限呢?  
我们知道, 如果scott要查看自己的存储过程代码, 就要查看视图usr\_source.  
  
然而如果BILL要查看scott的存储过程,就必须要查看视图 **dba\_source**了.  
用dba分配这个视图查看权限比BILL:

![444127020131003221423096.jpg][]

这样的话, bill就可以查看scott的所有PROCEDURE代码了,  谁知道怎么分配指定1个存储过程代码查看权限的可告诉我~

![44412702013100322170902.jpg][]

那为什么不是只把scott的视图user\_source分配给BILL就可以了吗?  因为dba\_source包括了其他人的信息.  
答案是 user\_, dba\_, all\_ 开头的数据字典并不适用模式前序.  
user\_ 开头的数据字典只适用于当前session查看当前用户的信息. 不能share给其他用户.  
参考  
https://forums.oracle.com/forums/thread.jspa?threadID=295697&messageID=3482583  
即使scott能无错执行分配user\_source给bill:

![444127020131003221936087.jpg][]

**但是BILL并不能查看scott.user\_source**...

![444127020131003222044026.jpg][]

**修改权限:**  
我打算用BILL去修改scott的这个存储过程USP\_EMP\_LIST;

![444127020131003222216038.jpg][]

当我执行这个脚本时, 提示我无权限了..

![444127020131003222349042.jpg][]

尝试用scott分配alter权限给bill?  
  
  
提示procedure只有execute 和 debug这个两个对象权限..  
  
亲自测试过,  修改他人的存储过程需要的是 **CREATE ANY PROCEDURE**  
用sys帐号分配这个权限给BILL

![444127020131003222451047.jpg][]

bill就可以执行修改脚本了:

![444127020131003222605051.jpg][]  
  
测试下 修改成功:

![444127020131003222710017.jpg][]  
  
  
不过这个权限貌似很不安全 慎用啊~

**3.2 回收对象权限**  
  
呢个简单啦:  
***Revoke***   object\_priv\[(columns)\]   /  ALL  
**ON  **        object  
**from**           \{user|role|PUBLIC\}  
  
  
**4.关于with grant option和 with admin option字句.**  
           关于呢两个字句的作用上面已经介绍过了, 这里只不过做个实验.  
           若SCOTT将自己的表dept 的select权限分配给bill, 并且带上with grant option字句.  
     这时bill再将这个权限分配给Calvin, 这样bill 和 Calvin都有权限查看这张表.  
     但是当scott从bill回收这个权限时, calvin还有没有这个权限呢?  
  
1. scott 分配 select on dept 给bill:

![444127020131003222902065.jpg][]

2.这时bill查看自己的对象权限,

![444127020131003223332078.jpg][]

3.BIll 再分给Calvin

![444127020131003223459056.jpg][]

4.Calvin 这时查看自己的权限:

![444127020131003223600090.jpg][]

可以见到 GRANTOR 系BILL啊, 分配的人是BILL啦  
  
5. 这时Scott 回收bill的这个权限..

![444127020131003223653045.jpg][]  
  
6.这时再查看calvin的权限:

![444127020131003223755085.jpg][]

没了...  
  
  
也就是说,在Oracle里 A回收了B的某个权限,  **会同时回收B分配给其他用户角色的对应权限...**

[44412702013100321121101.jpg]: http://image15.poco.cn/mypoco/myphoto/20131003/21/44412702013100321121101.jpg
[444127020131003211628058.jpg]: http://image15.poco.cn/mypoco/myphoto/20131003/21/444127020131003211628058.jpg
[444127020131003211746018.jpg]: http://image15.poco.cn/mypoco/myphoto/20131003/21/444127020131003211746018.jpg
[444127020131003212121038.jpg]: http://image15.poco.cn/mypoco/myphoto/20131003/21/444127020131003212121038.jpg
[444127020131003212724099.jpg]: http://image15.poco.cn/mypoco/myphoto/20131003/21/444127020131003212724099.jpg
[444127020131003212850068.jpg]: http://image15.poco.cn/mypoco/myphoto/20131003/21/444127020131003212850068.jpg
[444127020131003212957076.jpg]: http://image15.poco.cn/mypoco/myphoto/20131003/21/444127020131003212957076.jpg
[444127020131003213128097.jpg]: http://image15.poco.cn/mypoco/myphoto/20131003/21/444127020131003213128097.jpg
[444127020131003213313010.jpg]: http://image15.poco.cn/mypoco/myphoto/20131003/21/444127020131003213313010.jpg
[444127020131003213438070.jpg]: http://image15.poco.cn/mypoco/myphoto/20131003/21/444127020131003213438070.jpg
[444127020131003213641037.jpg]: http://image15.poco.cn/mypoco/myphoto/20131003/21/444127020131003213641037.jpg
[444127020131003213759066.jpg]: http://image15.poco.cn/mypoco/myphoto/20131003/21/444127020131003213759066.jpg
[444127020131003213900033.jpg]: http://image15.poco.cn/mypoco/myphoto/20131003/21/444127020131003213900033.jpg
[444127020131003214006063.jpg]: http://image15.poco.cn/mypoco/myphoto/20131003/21/444127020131003214006063.jpg
[444127020131003214058090.jpg]: http://image15.poco.cn/mypoco/myphoto/20131003/21/444127020131003214058090.jpg
[44412702013100321423409.jpg]: http://image15.poco.cn/mypoco/myphoto/20131003/21/44412702013100321423409.jpg
[444127020131003214340019.jpg]: http://image15.poco.cn/mypoco/myphoto/20131003/21/444127020131003214340019.jpg
[444127020131003214453042.jpg]: http://image15.poco.cn/mypoco/myphoto/20131003/21/444127020131003214453042.jpg
[444127020131003215233086.jpg]: http://image15.poco.cn/mypoco/myphoto/20131003/21/444127020131003215233086.jpg
[444127020131003214741077.jpg]: http://image15.poco.cn/mypoco/myphoto/20131003/21/444127020131003214741077.jpg
[444127020131003214921033.jpg]: http://image15.poco.cn/mypoco/myphoto/20131003/21/444127020131003214921033.jpg
[444127020131003215022018.jpg]: http://image15.poco.cn/mypoco/myphoto/20131003/21/444127020131003215022018.jpg
[444127020131003215529036.jpg]: http://image15.poco.cn/mypoco/myphoto/20131003/21/444127020131003215529036.jpg
[444127020131003215621044.jpg]: http://image15.poco.cn/mypoco/myphoto/20131003/21/444127020131003215621044.jpg
[444127020131003215745028.jpg]: http://image15.poco.cn/mypoco/myphoto/20131003/21/444127020131003215745028.jpg
[444127020131003215915014.jpg]: http://image15.poco.cn/mypoco/myphoto/20131003/21/444127020131003215915014.jpg
[444127020131003220033094.jpg]: http://image15.poco.cn/mypoco/myphoto/20131003/22/444127020131003220033094.jpg
[444127020131003220119050.jpg]: http://image15.poco.cn/mypoco/myphoto/20131003/22/444127020131003220119050.jpg
[444127020131003220207016.jpg]: http://image15.poco.cn/mypoco/myphoto/20131003/22/444127020131003220207016.jpg
[444127020131003220412079.jpg]: http://image15.poco.cn/mypoco/myphoto/20131003/22/444127020131003220412079.jpg
[444127020131003220617098.jpg]: http://image15.poco.cn/mypoco/myphoto/20131003/22/444127020131003220617098.jpg
[444127020131003220753066.jpg]: http://image15.poco.cn/mypoco/myphoto/20131003/22/444127020131003220753066.jpg
[444127020131003221016074.jpg]: http://image15.poco.cn/mypoco/myphoto/20131003/22/444127020131003221016074.jpg
[444127020131003221119032.jpg]: http://image15.poco.cn/mypoco/myphoto/20131003/22/444127020131003221119032.jpg
[444127020131003221247019.jpg]: http://image15.poco.cn/mypoco/myphoto/20131003/22/444127020131003221247019.jpg
[444127020131003221423096.jpg]: http://image15.poco.cn/mypoco/myphoto/20131003/22/444127020131003221423096.jpg
[44412702013100322170902.jpg]: http://image15.poco.cn/mypoco/myphoto/20131003/22/44412702013100322170902.jpg
[444127020131003221936087.jpg]: http://image15.poco.cn/mypoco/myphoto/20131003/22/444127020131003221936087.jpg
[444127020131003222044026.jpg]: http://image15.poco.cn/mypoco/myphoto/20131003/22/444127020131003222044026.jpg
[444127020131003222216038.jpg]: http://image15.poco.cn/mypoco/myphoto/20131003/22/444127020131003222216038.jpg
[444127020131003222349042.jpg]: http://image15.poco.cn/mypoco/myphoto/20131003/22/444127020131003222349042.jpg
[444127020131003222451047.jpg]: http://image15.poco.cn/mypoco/myphoto/20131003/22/444127020131003222451047.jpg
[444127020131003222605051.jpg]: http://image15.poco.cn/mypoco/myphoto/20131003/22/444127020131003222605051.jpg
[444127020131003222710017.jpg]: http://image15.poco.cn/mypoco/myphoto/20131003/22/444127020131003222710017.jpg
[444127020131003222902065.jpg]: http://image15.poco.cn/mypoco/myphoto/20131003/22/444127020131003222902065.jpg
[444127020131003223332078.jpg]: http://image15.poco.cn/mypoco/myphoto/20131003/22/444127020131003223332078.jpg
[444127020131003223459056.jpg]: http://image15.poco.cn/mypoco/myphoto/20131003/22/444127020131003223459056.jpg
[444127020131003223600090.jpg]: http://image15.poco.cn/mypoco/myphoto/20131003/22/444127020131003223600090.jpg
[444127020131003223653045.jpg]: http://image15.poco.cn/mypoco/myphoto/20131003/22/444127020131003223653045.jpg
[444127020131003223755085.jpg]: http://image15.poco.cn/mypoco/myphoto/20131003/22/444127020131003223755085.jpg