Shulou(Shulou.com)06/01 Report--

In Oracle 12C, there are two kinds of accounts, one is public account, the other is local account (which can also be understood as private account). A joint account refers to an account created under CDB and valid in all PDB, and the other is an account created in PDB.

The tests for these two accounts are as follows:

1.1 create a test account in PDB

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

Conclusion:

If a user or role already exists in PDB, the same account or role name cannot be created in CDB.

1.2 create a test account in CDB

SQL > show con_name

CON_NAME

-

CDB$ROOT

SQL > create user C##GUIJIAN IDENTIFIED BY guijian;-Note that users who are created in CDB must bring caches #

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

Similarly, after creating an account in CDB, an account with the same name cannot appear in PDB, because the account in CDB is valid for all 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 the permission to create an account under CDB

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 object Management Test

In the object management test, we simply test the differences under the CDB and PDB of the data objects under the common account.

1. Create an object under CDB and view it under 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

As you can see, objects created by joint accounts under CDB are not visible under PDB.

2. Create an object under the joint account under PDB and view it under 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

You can see that the account created under PDB for the same joint account cannot be seen in CDB. In addition, we also noticed a detail. For the same common account, the joint account created under PDB and CDB is given different meanings under CDB and PDB, so the object created under CDB and the object created under PDB can have the same name, and vice versa.

Conclusion:

1. If a user or role already exists in PDB, the same account or role name cannot be created in CDB.

2. After creating an account in CDB, you cannot have an account with the same name in PDB, because the account in CDB is valid for all PDB.

3. Accounts created in CDB will appear in all PDB, but authorization in CDB, unless specifically specified, cannot be passed to PDB.

4. Accounts created under PDB for the same joint account cannot be seen in CDB. For the same common account, the joint account created under PDB and CDB has different meanings under CDB and PDB, so the object created under CDB and the object created under PDB can have the same name, and vice versa.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.