您好,登錄后才能下訂單哦!
前言
? ? ?在11gR2環(huán)境中,假如用戶同時(shí)被授予了connect和resource角色后,即可登錄數(shù)據(jù)庫(kù)創(chuàng)建對(duì)象。但是在12c中,如果用戶只是被授予了這兩個(gè)角色,可以創(chuàng)建對(duì)象,但是無(wú)法插入數(shù)據(jù)。?
實(shí)驗(yàn)
? ? ?下面做一個(gè)小實(shí)驗(yàn):
11g環(huán)境:
(1)創(chuàng)建表空間
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)創(chuàng)建用戶并授權(quán)
create user test identified by "test" default tablespace test;
grant resource,connect to test;
(3)創(chuàng)建對(duì)象并插入數(shù)據(jù)
[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.
插入數(shù)據(jù)成功!
12c環(huán)境
(1)創(chuàng)建表空間
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)創(chuàng)建用戶并授權(quán)
create user test identified by "test" default tablespace test;
grant resource,connect to test;
(3)?建對(duì)象并插入數(shù)據(jù)
[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'
插入數(shù)據(jù)失敗。
原因分析?
? ? ?對(duì)比11g和12c的角色權(quán)限及用戶權(quán)限可知,當(dāng)用戶被授予connect和resource權(quán)限后,11g默認(rèn)會(huì)授予用戶UNLIMITED TABLESPACE權(quán)限,而12c并沒(méi)有默認(rèn)授予該權(quán)限。
? ? ?查詢官網(wǎng)得知,11g R2的這種現(xiàn)象貌似是一個(gè)bug,而12c修復(fù)了這個(gè)bug。官網(wǎng)解釋如下:
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權(quán)限即可使用,那么該怎么去創(chuàng)建用戶并分配權(quán)限呢?
以下給個(gè)創(chuàng)建普通用戶的示例:
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;
鄭州不孕不育醫(yī)院: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
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。