oracle 12c resource角色去掉了unlimited tablespace 权限

郑全

In this Document

Symptoms
Cause
Solution


Applies to:
Oracle Database - Enterprise Edition - Version 12.1.0.1 and later
Information in this document applies to any platform.
Symptoms
In Oracle Database 12c,the following error is seen when trying to insert the data into a table,though the RESOURCE role is granted to the user
ORA-01950: no privileges on tablespace <tablespace name>
SQL> create user test identified by oracle123;
User created.
SQL> grant create session,resource,create table to test;
Grant succeeded.
SQL> conn test/oracle123
Connected.
SQL> create table example(name varchar2(10),num number);
Table created.
SQL> insert into example values('abc',10);
insert into example values('abc',10)
            *
ERROR at line 1:
ORA-01950: no privileges on tablespace 'USERS'

Cause
This is an expected behavior.
In Oracle Database 12c,RESOURCE role no longer grants UNLIMITED TABLESPACE system privilege by default.
Solution
Grant UNLIMITED TABLESPACE system privilege to the user manually.
SQL> conn / as sysdba
Connected.
SQL> grant unlimited tablespace to test;
Grant succeeded.
SQL> conn test/oracle123
Connected.
SQL> insert into example values('abc',10);
1 row created.
SQL> commit;
Commit complete.

zhouwei1990

11G在授予resource权限时，会附带授予unlimited tablespace权限，但是UNLIMITED TABLESPACE权限不属于resource角色。
我们可以通过查看resource权限看到：

SQL>     select privilege from dba_sys_privs where grantee = 'RESOURCE';

PRIVILEGE
----------------------------------------
CREATE TRIGGER
CREATE SEQUENCE
CREATE TYPE
CREATE PROCEDURE
CREATE CLUSTER
CREATE OPERATOR
CREATE INDEXTYPE
CREATE TABLE

8 rows selected.

而在12C中，已经取消了附带授予的unlimited tablespace权限，所以需要单独授予unlimited tablespace才能在相应表空间创建段。