11G的rsource角色與Unlimited Tablespace

[20141103]11G的rsource角色與Unlimited Tablespace.txt

--11G下resource角色,使用者自動獲得Unlimited Tablespace的許可權,應該引起注意.自己做一個測試:

SCOTT@test> @ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.3.0     Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

SCOTT@test> select grantee, privilege, admin_option from dba_sys_privs where grantee='CONNECT';
GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
CONNECT                        CREATE SESSION                           NO

--connect角色僅僅有create session的許可權.

SCOTT@test> select grantee, privilege, admin_option 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.

--resource角色有8個許可權.

--建立使用者test12並且授權resource角色.

SCOTT@test> grant resource to test12 identified by test12;
Grant succeeded.

SCOTT@test> select * from dba_role_privs where grantee = 'TEST12';
GRANTEE                        GRANTED_ROLE                   ADM DEF
------------------------------ ------------------------------ --- ---
TEST12                         RESOURCE                       NO  YES

SCOTT@test> select * from dba_sys_privs where grantee='TEST12';
GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
TEST12                         UNLIMITED TABLESPACE                     NO

--可以發現test12使用者擁有UNLIMITED TABLESPACE的許可權.如果可能應該收回.

SCOTT@test> revoke unlimited tablespace from test12;
Revoke succeeded.

SCOTT@test> select * from dba_sys_privs where grantee='TEST12';
no rows selected

--安全需要,要注意這個問題!