ORA-01931 Again

In the previous post, I said that you could not grant unlimited tablespace to a role in Oracle 11.1.0.6.0. Today I discovered something else so I have repeated the example below, this time on Solaris:

ORACLE 11.1 > sqlplus /

SQL*Plus: Release 11.1.0.6.0 - Production on Fri Sep 14 16:06:15 2012

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

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>

I had a look at the Oracle 11.1 documentation and this appears to be intentional:

ORA-01931: cannot grant string to a role

Cause: UNLIMITED TABLESPACE, REFERENCES, INDEX, SYSDBA or SYSOPER privilege cannot be granted to a role.

Action: Grant privilege directly to the user.

In Oracle 11.2.0.1.0, this is no longer the case:

ORACLE 11.2 > sqlplus /

SQL*Plus: Release 11.2.0.1.0 Production on Fri Sep 14 16:08:53 2012

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create role role1;

Role created.

SQL> grant unlimited tablespace to role1;

Grant succeeded.

SQL> select privilege from dba_sys_privs

  2  where grantee = 'ROLE1';

PRIVILEGE

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

UNLIMITED TABLESPACE

SQL>

I had a look at the Oracle 11.2 documentation and again, this appears to be intentional:

ORA-01931: cannot grant string to a role

Cause: REFERENCES, INDEX, SYSDBA, SYSOPER or SYSASM privilege could not be granted to a role.

Action: Grant privilege directly to the user.