角色resource在11g和12c中的區別

前言

? ? ?在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;

鄭州不孕不育醫院：http://yyk.39.net/zz3/zonghe/1d427.html

create user test identified by "test"

default tablespace test

quota 30G on test

temporary tablespace test_temp;

https://www.jianshu.com/p/3b9c64fdcec9