Oracle 12C中,賬號(hào)分為兩種,一種是公用賬號(hào),一種是本地賬號(hào)(亦可理解為私有賬號(hào))。共有賬號(hào)是指在CDB下創(chuàng)建,并在全部PDB中生效的賬號(hào),另一種是在PDB中創(chuàng)建的賬號(hào)。
成都創(chuàng)新互聯(lián)公司主要業(yè)務(wù)有網(wǎng)站營銷策劃、網(wǎng)站制作、網(wǎng)站設(shè)計(jì)、微信公眾號(hào)開發(fā)、重慶小程序開發(fā)、H5開發(fā)、程序開發(fā)等業(yè)務(wù)。一次合作終身朋友,是我們奉行的宗旨;我們不僅僅把客戶當(dāng)客戶,還把客戶視為我們的合作伙伴,在開展業(yè)務(wù)的過程中,公司還積累了豐富的行業(yè)經(jīng)驗(yàn)、營銷型網(wǎng)站資源和合作伙伴關(guān)系資源,并逐漸建立起規(guī)范的客戶服務(wù)和保障體系。
針對(duì)這兩種賬號(hào)的測試如下:
1.1 在PDB中創(chuàng)建測試賬號(hào)
SQL> alter session set container=pdb01;
SQL> select username from dba_users where username like 'GUI%';
SQL> CREATE USER TEST IDENTIFIED BY test;
SQL> grant dba to test;
SQL> show con_name
CON_NAME
------------------------------
PDB01
SQL> conn /as sysdba
Connected.
SQL> create user test identified by test;
create user test identified by test
*
ERROR at line 1:
ORA-65096: invalid common user or role name
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
結(jié)論:
如果在PDB中已經(jīng)存在一個(gè)用戶或者角色,則在CDB中不能創(chuàng)建相同的賬號(hào)或者角色名。
1.2 在CDB中創(chuàng)建測試賬號(hào)
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> create user C##GUIJIAN IDENTIFIED BY guijian; ------注意CDB中創(chuàng)建用戶一定要帶上c##
SQL> create user c#gui identified by gui;
create user c#gui identified by gui
*
ERROR at line 1:
ORA-65096: invalid common user or role name
SQL> select username from dba_users where username like '%GUI%';
USERNAME
--------------------------------------------------------------------------------
C##GUIJIAN
SQL> ALTER SESSION SET CONTAINER=PDB01;
SQL> select username from dba_users where username like '%GUI%';
USERNAME
--------------------------------------------------------------------------------
C##GUIJIAN
SQL> create user guijian identified by guijian;
同樣在CDB中創(chuàng)建賬號(hào)后不能在PDB中出現(xiàn)同名的賬號(hào),因CDB中的賬號(hào)對(duì)所有的PDB都是有效的。
SQL> create user c##guijian identified by guijian;
create user c##guijian identified by guijian
*
ERROR at line 1:
ORA-65094: invalid local user or role name
SQL> alter session set container=pdba;
Session altered.
SQL> show user
USER is "SYS"
SQL> alter user sys identified by sys;
alter user sys identified by sys
*
ERROR at line 1:
ORA-65066: The specified changes must apply to all containers
SQL> show con_name
CON_NAME
------------------------------
PDBA
SQL> conn /as sysdba
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> alter user sys identified by sys;
1.3 CDB下創(chuàng)建賬號(hào)的權(quán)限問題
SQL> conn / as sysdba
SQL> grant connect,create session to c##cdb;
SQL> conn c##cdb/cdb@pdba
ERROR:
ORA-01045: user C##CDB lacks CREATE SESSION privilege; logon denied
Warning: You are no longer connected to ORACLE.
SQL> a
SP2-0004: Nothing to append.
SQL> conn / as sysdba
Connected.
SQL> alter session set container=pdba;
SQL> grant resource,connect to c##cdb;
SQL> conn /as sysdba
SQL> conn c##cdb/cdb@pdba
SQL> conn / as sysdba
SQL> create user guijian identified by guijian container=current;
create user guijian identified by guijian container=current
*
ERROR at line 1:
ORA-65049: creation of local user or role is not allowed in CDB$ROOT
SQL> create user c##guijian identified by guijian container=current;
create user c##guijian identified by guijian container=current
*
ERROR at line 1:
ORA-65094: invalid local user or role name
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> create user c##guijian identified by guijian container=all;
SQL> create user c##guijian01 identified by guijian;
SQL> conn /as sysdba
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> grant dba to c##guijian01;
SQL> conn c##guijian01/guijian@pdba
ERROR:
ORA-01045: user C##GUIJIAN01 lacks CREATE SESSION privilege; logon denied
Warning: You are no longer connected to ORACLE.
SQL> conn /as sysdba
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> grant dba to c##guijian01 container=all;
SQL> conn c##guijian01/guijian@pdba
1.4 對(duì)象管理測試
對(duì)象管理測試中,我們簡單測試在共有賬號(hào)的數(shù)據(jù)對(duì)象的CDB和PDB下的不同。
1、在CDB下創(chuàng)建對(duì)象,在PDB下查看:
SQL> conn c##cdb/cdb
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> create table cdb as select * from dba_users;
SQL> commit;
可以看到,在CDB下的共有賬號(hào)創(chuàng)建的對(duì)象在PDB下是看不到的。
2、在PDB下的共有賬號(hào)創(chuàng)建對(duì)象,在CDB下查看:
SQL> show con_name
CON_NAME
------------------------------
PDBA
SQL> show user
USER is "C##CDB"
SQL> select object_name from user_objects;
SQL> create table cdb as select * from dba_users;
可以看出,針對(duì)同一個(gè)共有賬號(hào)在PDB下創(chuàng)建的賬號(hào)在CDB是看不到的,此外我們還注意到一個(gè)細(xì)節(jié),針對(duì)同一個(gè)共有賬號(hào),在PDB和CDB下創(chuàng)建的共有賬號(hào)因在CDB和PDB下被賦予了不同的含義,故在CDB下創(chuàng)建的對(duì)象和在PDB下創(chuàng)建的對(duì)象是可以同名的,反之也成立。
結(jié)論:
1、 如果在PDB中已經(jīng)存在一個(gè)用戶或者角色,則在CDB中不能創(chuàng)建相同的賬號(hào)或者角色名。
2、 同樣在CDB中創(chuàng)建賬號(hào)后不能在PDB中出現(xiàn)同名的賬號(hào),因CDB中的賬號(hào)對(duì)所有的PDB都是有效的。
3、 在CDB中創(chuàng)建的賬號(hào)將會(huì)在全部的PDB中出現(xiàn),但是在CDB中的授權(quán),如非特別指定的話,并不能傳遞到PDB中。
4、 針對(duì)同一個(gè)共有賬號(hào)在PDB下創(chuàng)建的賬號(hào)在CDB是看不到的。針對(duì)同一個(gè)共有賬號(hào),在PDB和CDB下創(chuàng)建的共有賬號(hào)因在CDB和PDB下被賦予了不同的含義,故在 CDB下創(chuàng)建的對(duì)象和在PDB下創(chuàng)建的對(duì)象是可以同名的,反之也成立。
網(wǎng)頁標(biāo)題:oracle12C創(chuàng)建用戶學(xué)習(xí)
URL分享:http://bm7419.com/article36/gocjsg.html
成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián),為您提供網(wǎng)頁設(shè)計(jì)公司、云服務(wù)器、微信小程序、App設(shè)計(jì)、全網(wǎng)營銷推廣、電子商務(wù)
聲明:本網(wǎng)站發(fā)布的內(nèi)容(圖片、視頻和文字)以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主,如果涉及侵權(quán)請(qǐng)盡快告知,我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場,如需處理請(qǐng)聯(lián)系客服。電話:028-86922220;郵箱:631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載,或轉(zhuǎn)載時(shí)需注明來源: 創(chuàng)新互聯(lián)