CDB和PDB關於使用者建立和使用者許可權區別

lusklusklusk發表於2020-02-08

1、COMMON USERS普通使用者(使用者名稱以C##或c##開頭)僅建立在CDB層；建立在PDB層會報錯ORA-65094: invalid local user or role name

2、LOCAL USERS本地使用者僅建立在PDB層，建立的時候必須指定CONTAINER；建立在CDB層會報錯ORA-65096: invalid common user or role name

3、CDB建立的C##使用者可以在PDB檢視到，即CDB中建立公用使用者預設情況下是在所有PDB下建立了相同的使用者

4、使用者許可權在CDB和PDB中是隔離的，也就是說在CDB中的許可權不會同步到PDB，除非在CDB中授權時加上container=all，則在CDB中授權的許可權會同步到PDB，在PDB中授權後許可權也不會同步到CDB，在PDB中授權不能加container=all，否則會報錯ORA-65040: operation not allowed from within a pluggable database

5、CDB建立的C##使用者可以在PDB登入，但是有兩個前提，一是要在PDB中單獨授權，二是要加上PDB對應的tns。不加tns預設連線的是CDB

6、CDB刪除C##使用者時，就算PDB中該使用者的許可權和CDB不一樣，該使用者也自動在PDB中也刪除了，PDB無法直接刪除C##使用者，會報錯ORA-65040: operation not allowed from within a pluggable database

7、PDB中已經有C##test1使用者時，PDB還可以建立test1

8、sys和system使用者是普通使用者，是全域性的，所以直接在PDB中conn sys、system使用者時不加PDB的tns，預設連線到CDB;sys、system的密碼只能在CDB修改且新密碼會自動在PDB生效，PDB修改sys、system密碼會報錯





普通使用者C##開頭的僅建立在CDB層，本地使用者僅建立在PDB層

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

         2 PDB$SEED                       READ ONLY  NO

         3 POCP1                          READ WRITE NO

         4 POCP2                          READ WRITE NO

SQL> show con_name

CON_NAME

------------------------------

CDB$ROOT

SQL> create user test1 identified by 123456;

create user test1 identified by 123456

            *

ERROR at line 1:

ORA-65096: invalid common user or role name

SQL> create user C##test1 identified by 123456;

User created.

SQL> col username format a20

SQL> select username,created from dba_users where created>sysdate-1;

USERNAME             CREATED

-------------------- ---------

C##TEST1             25-JAN-20

SQL> alter session set container=POCP1;

Session altered.

SQL> select username,created from dba_users where created>sysdate-1;

USERNAME             CREATED

-------------------- ---------

C##TEST1             25-JAN-20

SQL> create user test2 identified by 123456;

User created.

SQL> create user C##T1 identified by 123456;

create user C##T1 identified by 123456

            *

ERROR at line 1:

ORA-65094: invalid local user or role name




使用者許可權在CDB和PDB中是隔離的,CDB中的許可權沒有同步到PDB，除非授權時加上container=all

SQL> show con_name

CON_NAME

------------------------------

CDB$ROOT

SQL> create user C##user1 identified by 123456;

User created.

SQL> grant dba to C##user1;

Grant succeeded.

SQL> alter session set container=POCP1;

Session altered.

SQL> conn C##user1/123456@pocp1

ERROR:

ORA-01045: user C##USER1 lacks CREATE SESSION privilege; logon denied

SQL> alter session set container=POCP1;

Session altered.

SQL> grant dba to  C##user1;

Grant succeeded.

SQL> grant resource to C##user1 container=all;

grant resource to C##user1 container=all

*

ERROR at line 1:

ORA-65040: operation not allowed from within a pluggable database

SQL> conn C##user1/123456@pocp1

Connected.




CDB建立的C##使用者可以在PDB登入，但是必須加上PDB的tns，不加tns預設連線CDB

SQL> show con_name

CON_NAME

------------------------------

CDB$ROOT

SQL> alter session set container=POCP1;

Session altered.

SQL> show con_name

CON_NAME

------------------------------

POCP1

SQL> conn C##USER1/123456

Connected.

SQL> show con_name

CON_NAME

------------------------------

CDB$ROOT

SQL> conn C##USER1/123456 as sysdba

Connected.

SQL> alter session set container=POCP1;

Session altered.

SQL> conn C##USER1/123456@pocp1;

Connected.

SQL> show con_name

CON_NAME

------------------------------

POCP1




PDB無法刪除C##使用者

SQL>  alter session set container=POCP1;

Session altered.

SQL> drop user C##USER1;

drop user C##USER1

*

ERROR at line 1:

ORA-65040: operation not allowed from within a pluggable database




PDB中已經有C##test1使用者時，PDB還可以建立test1

SQL> show con_name

CON_NAME

------------------------------

POCP1

SQL> create user user1 identified by 123456;

User created.

SQL> col username format a20

SQL> select username,created from dba_users where created>sysdate-1;

USERNAME             CREATED

-------------------- ---------

C##USER1             29-JAN-20

USER1                29-JAN-20




sys和system使用者是全域性的，所以直接在PDB中conn sys、system使用者時不加PDB的tns，預設連線到CDB;sys、system的密碼只能在CDB修改且新密碼會自動在PDB生效，PDB修改sys、system密碼會報錯

SQL> show con_name

CON_NAME

------------------------------

POCP1

SQL> conn system/123456

Connected.

SQL> show con_name

CON_NAME

------------------------------

CDB$ROOT

SQL> conn system/123456@pocp1

Connected.

SQL> show con_name

CON_NAME

------------------------------

POCP1

SQL> alter user system identified by 666666;

alter user system identified by 666666

*

ERROR at line 1:

ORA-65066: The specified changes must apply to all containers

SQL> alter user system identified by 666666container=all;

alter user system identified by 666666 container=all

*

ERROR at line 1:

ORA-65040: operation not allowed from within a pluggable database


SQL> show con_name

CON_NAME

------------------------------

CDB$ROOT

SQL> alter user system identified by 666666;

User altered.

SQL> conn system/666666@pocp1

Connected.

SQL> show con_name

CON_NAME

------------------------------

POCP1

SQL> conn sys/888888 as sysdba

Connected.

SQL> show con_name

CON_NAME

------------------------------

CDB$ROOT

SQL> conn sys/888888@pocp1 as sysdba

Connected.

SQL> show con_name

CON_NAME

------------------------------

POCP1

SQL> alter user sys identified by 666666;

alter user sys identified by 666666

*

ERROR at line 1:

ORA-65066: The specified changes must apply to all containers

SQL> alter user sys identified by 666666 container=all;

alter user sys identified by 666666 container=all

*

ERROR at line 1:

ORA-65040: operation not allowed from within a pluggable database


SQL> show con_name

CON_NAME

------------------------------

CDB$ROOT

SQL> alter user sys identified by 888888;

User altered.


來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/30126024/viewspace-2674802/，如需轉載，請註明出處，否則將追究法律責任。

相關文章