Wednesday, September 12, 2012

ORA-01931

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>

2 comments:

Laurent Schneider said...

but there is a workaround...


SQL> 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

Andrew Stuart Reid said...

That's the kind of comment I like! Check out the next post for something I discovered today.

Regards,

Andrew