前言 在11gR2环境中，假如用户同时被授予了connect和resource角色后，即可登录数据库创建对象。但是在12c中，如果用户只是被授予了这两个角色，可以创建对象，但是无法插入数据。 实验 下面做一个小实验：11g环境：(1)创建表空间CREATE TABLESPACE test DATAFILE '/u01/app/oracle/oradata/bond/test01.dbf' SIZE 5242880 AUTOEXTEND ON NEXT 1310720 MAXSIZE 32767M LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;(2)创建用户并授权create user test identified by "test" default tablespace test;grant resource,connect to test;(3)创建对象并插入数据 [oracle@bond ~]$ sqlplus test/test SQL*Plus: Release 11.2.0.4.0 Production on Sun Aug 18 13:56:26 2019 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Oracle Label Security, OLAP, Data Mining, Oracle Database Vault and Real Application Testing options SQL> create table t_dict as 2 select * from dict where rownum <=100; Table created. 插入数据成功！ 12c环境 (1)创建表空间CREATE TABLESPACE test DATAFILE '/u01/app/oracle/oradata/bond/test01.dbf' SIZE 5242880 AUTOEXTEND ON NEXT 1310720 MAXSIZE 32767M LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO; (2)创建用户并授权create user test identified by "test" default tablespace test;
grant resource,connect to test;
(3) 建对象并插入数据
[oracle@bond ~]$ sqlplus test/test SQL*Plus: Release 12.2.0.1.0 Production on Fri Sep 6 19:53:16 2019 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production SQL> create table t_dict as 2 select * from dict where rownum <=100; select * from dict where rownum <=100 * ERROR at line 2: ORA-01950: no privileges on tablespace 'TEST'
插入数据失败。
原因分析
对比11g和12c的角色权限及用户权限可知，当用户被授予connect和resource权限后，11g默认会授予用户UNLIMITED TABLESPACE权限，而12c并没有默认授予该权限。
查询官网得知，11g R2的这种现象貌似是一个bug，而12c修复了这个bug。官网解释如下：
The
UNLIMITED TABLESPACE
system privilege will be removed from the
RESOURCE
role in a future Oracle Database release (reference Bug 7614645).
思考
既然12c不能像11g那样直接授予用户connect和resource权限即可使用，那么该怎么去创建用户并分配权限呢？
以下给个创建普通用户的示例：
CREATE TABLESPACE test DATAFILE '/u01/app/oracle/oradata/bond/test01.dbf' SIZE 5242880 AUTOEXTEND ON NEXT 1310720 MAXSIZE 32767M LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;
CREATE TEMPORARY TABLESPACE test_temp TEMPFILE '/u01/app/oracle/oradata/bond/test_temp01.dbf' SIZE 33554432 AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1048576; create user test identified by "test"default tablespace testquota 30G on testtemporary tablespace test_temp;
