ORA-01931: cannot grant UNLIMITED TABLESPACE to a role

The UNLIMITED TABLESPACE privileges cannot be granted to a role

    今天在做role測試的時候，遇到一個問題，當把unlimited tablespace privileges grant to role的時候，遇到錯誤：
    ORA-01931: cannot grant UNLIMITED TABLESPACE to a role
情景重現：
1.建立role-> HELLO
SQL> conn / as sysdba
Connected.
SQL> show parameter instance_name
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
instance_name                        string      devdb1
SQL> CREATE ROLE "HELLO"  NOT IDENTIFIED;
Role created.
2.給角色授權-> UNLIMITED TABLESPACE
SQL> GRANT UNLIMITED TABLESPACE TO "HELLO";
GRANT UNLIMITED TABLESPACE TO "HELLO"
*
ERROR at line 1:
ORA-01931: cannot grant UNLIMITED TABLESPACE to a role
3.原因
Text:   cannot grant UNLIMITED TABLESPACE, REFERENCES, or INDEX to a role
Cause:  The UNLIMITED TABLESPACE, REFERENCES, or INDEX or SYSDBA or SYSOPER privileges cannot be granted to a role.
Action: Grant these privileges directly to the user.
4.授權給使用者成功
SQL> grant unlimited tablespace to ecc_view;
Grant succeeded.

把問題進一步深入分析：
如果把resource 賦予一個role，然後把這個role授權給使用者，使用者仍然沒有UNLIMITED TABLESPACE的許可權
原因為：
    When the RESOURCE role is granted directly to a user, the UNLIMITED TABLESPACE system privilege is included in the role.  However, the UNLIMITED TABLESPACE privilege actually gives the user unlimited QUOTA on all tablespaces, which is a user attribute. 
    If the RESOURCE role is granted to another role, and that role granted to users,there is no way to propagate user attributes. Thus, the users will not have quota on any tablespaces.  Unlimited tablespace system privilege cannot be granted directly to a role
(ORA-1931).
1.建立角色hello，授予resource許可權
SQL> conn / as sysdba
Connected.
SQL> create role hello;
Role created.
SQL> grant resource to hello;
Grant succeeded.
2.建立普通使用者test
SQL> create user test identified by ecc ;      
User created.
3.普通使用者test授予connect, hello角色
SQL> grant connect , hello to test;
Grant succeeded.
4.test連線，在表空間無許可權建立物件，因為系統許可權沒有繼承resource的許可權
SQL> conn test/ecc@devdb1
Connected.
SQL> create table t1 (id varchar(10));
create table t1 (id varchar(10))
*
ERROR at line 1:
ORA-01950: no privileges on tablespace 'USERS'
5.把resources直接授權給使用者test
SQL> conn / as sysdba;
Connected.
SQL> grant resource to test;
Grant succeeded.
6. UNLIMITED TABLESPACE被授予使用者
SQL> conn test/ecc@devdb1
Connected.
SQL> create table t1 (id varchar(10));
Table created.

  Subject: OERR: ORA 1931 cannot grant UNLIMITED TABLESPACE, REFERENCES, or INDEX to a role
  Doc ID:  19219.1 Type:  REFERENCE
  Modified Date :  10-JUN-1999 Status:  PUBLISHED
 
  Subject: ORA-1950 When Creating an Object and Resource Role is Granted to the User
  Doc ID:  1005485.6 Type:  PROBLEM
  Modified Date :  30-MAR-2009 Status:  PUBLISHED