关于ORACLE RESOURCE角色

关于ORACLE RESOURCE角色

授予用户resource角色会隐式的授予用户 UNLIMITED TABLESPACE权限,下面是一个测试.

创建测试帐号

SQL> create user test identified by test;

 

User created.

查看用户权限

SQL> select * from dba_sys_privs where grantee='TEST';

 

no rows selected

查看RESOURCE角色所拥有的权限

SQL>  select * from dba_sys_privs where grantee='RESOURCE';

 

GRANTEE                        PRIVILEGE                                ADM

------------------------------ ---------------------------------------- ---

RESOURCE                       CREATE TRIGGER                           NO

RESOURCE                       CREATE SEQUENCE                          NO

RESOURCE                       CREATE TYPE                              NO

RESOURCE                       CREATE PROCEDURE                         NO

RESOURCE                       CREATE CLUSTER                           NO

RESOURCE                       CREATE OPERATOR                          NO

RESOURCE                       CREATE INDEXTYPE                         NO

RESOURCE                       CREATE TABLE                             NO

8 rows selected.

上的查询中并没有UNLIMITED TABLESPACE权限

授予test用户resource角色

SQL> GRANT RESOURCE TO TEST;

 

Grant succeeded.

再次查看test用户的权限

SQL> select * from dba_sys_privs where grantee='TEST';

 

GRANTEE                        PRIVILEGE                                ADM

------------------------------ ---------------------------------------- ---

TEST                           UNLIMITED TABLESPACE                     NO

验证:

授予test用户创建会话的权限

SQL> grant create session to test;

 

Grant succeeded.

SQL> CONN TEST

Enter password:

Connected.

SQL> CREATE TABLE TEST (ID NUMBER);

 

Table created.

收回unlimited tablespace权限

SQL> conn / as sysdba

Connected.

SQL> REVOKE UNLIMITED TABLESPACE FROM TEST;

 

Revoke succeeded.

 

再次查看test用户的权限

SQL> select * from dba_sys_privs where grantee='TEST';

 

GRANTEE                        PRIVILEGE                                ADM

------------------------------ ---------------------------------------- ---

TEST                           CREATE SESSION                           NO

现在已经不可用创建表

SQL> CONN TEST

Enter password:

Connected.

 

SQL> CREATE TABLE TEST1 (ID NUMBER);

CREATE TABLE TEST1 (ID NUMBER)

*

ERROR at line 1:

ORA-01536: space quota exceeded for tablespace 'USERS'

 

注：查看一个用户拥有那些角色可以通过DBA_ROLE_PRIVS视图

SQL> select * from dba_role_privs where grantee='TEST';

 

GRANTEE                        GRANTED_ROLE                   ADM DEF

------------------------------ ------------------------------ --- ---

TEST                           RESOURCE                       NO  YES

查看一个用户在那个表空间有配额可以通过DBA_TS_QUOTAS视图

SQL> select TABLESPACE_NAME ,USERNAME ,MAX_BYTES from dba_ts_quotas;

 

TABLESPACE_NAME                USERNAME                        MAX_BYTES

------------------------------ ------------------------------ ----------

NDX_OAK                        OAK                                    -1

SYSAUX                         OLAPSYS                                -1

TBS_EMS                        EMS                                    -1

SYSAUX                         SYSMAN                                 -1

NDX_ECARD                      ECARD                                  -1

TBS_OAK                        OAK                                    -1

SYSAUX                         DMSYS                           209715200

TEST                           TEST                                   -1

-1代表无限制

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7419833/viewspace-567341/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7419833/viewspace-567341/