Thursday, September 13, 2012

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. I had an excellent comment from Laurent Schneider, the author of the book above. Then 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.

2 comments:

Anonymous said...

11.2.0.4 changed it back and this privilege can no longer be granted to a role. The GRANT statement works on 11.2.0.3 but not on 11.2.0.4, and the restriction is now listed in the latest Database Error Messages: http://docs.oracle.com/cd/E11882_01/server.112/e17766/e1500.htm#ORA-01931

Andrew Reid said...

Thank you for taking the time to check that and clarify it for others who might read this post.

Kind Regards,

Andrew