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 时间: 2017-12-12 17:50
11G在授予resource权限时，会附带授予unlimited tablespace权限，但是UNLIMITED TABLESPACE权限不属于resource角色。
我们可以通过查看resource权限看到：
SQL> select privilege from dba_sys_privs where grantee = 'RESOURCE';