This was tested on Oracle 11.1.0.6.0 running on Windows XP. You can grant UNLIMITED TABLESPACE to a user:
SQL> create user user1
2 identified by user1;
User created.
SQL> grant unlimited tablespace to user1;
Grant succeeded.
SQL>
... but if you try to grant it to a role, you get an ORA-01931. Apparently this is because it is a space-related privilege:
SQL> create role role1;
Role created.
SQL> grant unlimited tablespace to role1;
grant unlimited tablespace to role1
*
ERROR at line 1:
ORA-01931: cannot grant UNLIMITED TABLESPACE to a role
SQL>
but there is a workaround...
ReplyDeleteSQL> create role r;
Role created.
SQL> grant resource to r;
Grant succeeded.
SQL> select PRIVILEGE from dba_sys_privs where grantee='R';
PRIVILEGE
----------------------------------------
UNLIMITED TABLESPACE
Cheers
Laurent