Shulou(Shulou.com)05/31 Report--

Editor to share with you how to create oracle12c public users and local users, I believe that most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's learn about it!

12C introduces pluggable libraries, and there are two types of administrative users: local users and public users. The local user refers to the user in the plug and unplug database (PDB), which is no different from the version before 12c. Public users only exist in the pluggable environment, and there is no such application in the traditional database environment. How to understand public users? You can simply think of it as a CDB user and can be used in all general and all pluggable databases (PDB). This user is like the property administrator of the community, appointed by the community property and can manage all users of the community.

If you log in to the CDB database for the first time, creating a user in the traditional way will report an error because the user created in the CDB environment is a public user and the syntax is different from the normal user.

1. Use sqlplus to log in to a pluggable database

[oracle@snow ~] $sqlplus / as sysdba

two。 Through con_name, you can see that the current login environment is the CDB root library, and CDB contains a pluggable library called PDB1.

SYS@cdb > show con_name pdbs

CON_NAME

-

CDB$ROOT

CON_ID CON_NAME OPEN MODE RESTRICTED

-

2 PDB$SEED READ ONLY NO

3 PDB1 READ WRITE NO

3. To create a public user bighero, you need to use either clocked # or clocked # as the beginning of the user name.

SYS@cdb > create user c##bighero identified by bighero

User created.

If you use the traditional method of creating a user, you will get an error.

SYS@cdb > create user bighero identified by bighero

Create user bighero identified by bighero

*

ERROR at line 1:

ORA-65096: invalid common user or role name

4. To create a public role, like a public user, you need to use Category # or censor # as the beginning of the role name.

SYS@cdb > create role c##myroles container = all

Grant the dba role to the public role c##myroles, applicable to all PDB

SYS@cdb > grant dba to c##myroles container = all

Grant public roles to public users, using all PDB

SYS@cdb > grant c##myroles to c##bighero container = all

The above operations can also be done with a single statement in the way of grant xx to user

SYS@cdb > grant dba to c##bighero identified by bighero container = all

5. Let's use public users to log in to CDB and PDB respectively to see if it is universal. Login to PDB uses the configured link string @ pdb1 in tnsnames

[oracle@snow ~] $sqlplus c##bighero/bighero

C##BIGHERO@cdb > show con_name

CON_NAME

-

CDB$ROOT

[oracle@snow ~] $sqlplus c##bighero/bighero@pdb1

C##BIGHERO@pdb1 > show con_name

CON_NAME

-

PDB1

The link string is as follows:

[oracle@snow admin] $cat tnsnames.ora

Pdb1=

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP) (HOST = 172.16.228.12) (PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = pdb1)

)

)

6. There is no difference between creating a user in PDB and creating an ordinary user

Create user baymax identified by baymax

Default tablespace baymax_comp

Temporary tablespace temp

Quota unlimited on baymax_comp

7. Switching mode between CDB and PDB

SYS@cdb > alter session set container = pdb1

SYS@cdb > alter session set container = cdb$root

The above is all the content of the article "how oracle12c creates public and local users". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.