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:

  1. 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

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

    Regards,

    Andrew

    ReplyDelete