ORACLE resource角色

最新推荐文章于 2021-11-10 15:06:16 发布

转载最新推荐文章于 2021-11-10 15:06:16 发布 · 587 阅读

oracle 专栏收录该内容

0 篇文章

订阅专栏

本文介绍了 Oracle 中 RESOURCE 角色的权限详情，包括创建集群、序列、触发器等，并解释了将此角色授予用户时额外提供的 unlimited tablespace 权限。

ORACLE resource角色都有什么样的权限呢？下面就为您介绍如何查看ORACLE resource角色的权限的方法，

 SQL> select * from v$version where rownum<=1;  
 
BANNER  
--------------------------------------------------------------------------------  
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi

从role_sys_privs中看ORACLE resource角色的权限

 SQL> select PRIVILEGE from role_sys_privs where role='RESOURCE';  
 
PRIVILEGE  
--------------------------------------------------------------------------------  
CREATE CLUSTER  
CREATE SEQUENCE  
CREATE TRIGGER  
CREATE TABLE  
CREATE PROCEDURE  
CREATE TYPE  
CREATE OPERATOR  
CREATE INDEXTYPE  
 
8 rows selected.

当把ORACLE resource角色授予一个user的时候，不但会授予ORACLE resource角色本身的权限，而且还有unlimited tablespace权限。

 SQL> conn desk  
Enter password:  
Connected.  
SQL> select PRIVILEGE from user_sys_privs;  
no rows selected  
SQL> conn / as sysdba  
Connected.  
SQL> grant resource to desk;  
Grant succeeded.  
SQL> conn desk  
Enter password:  
Connected.  
SQL> select PRIVILEGE from user_sys_privs;  
PRIVILEGE  
----------------------------------------  
UNLIMITED TABLESPACE  
SQL>

当把resource授予一个role时，就不会授予unlimited tablespace权限

 SQL> show user  
USER is "SYS"  
SQL> create role testrole identified using testrole;  
Role created.  
SQL> revoke resource from desk;  
Revoke succeeded.  
SQL> grant resource to testrole;  
Grant succeeded.  
SQL> grant testrole to desk;  
Grant succeeded.  
SQL> conn desk  
Enter password:  
Connected.  
SQL> select privilege from user_sys_privs;  
no rows selected  
SQL>