ORACLE|How to create new user for ORACLE 19c (CDB & PDB) 数据库|oracle|sql|数据库高级管

ORACLE 19c新建用户数据据库、用户、CDB与PDB之间的关系
文章图片

通常在CDB上建立的用户是common user，新建用户名前要加C##。在PDB上创建的用户是local user。

在CDB上创建用户 1.Guarante this action under the CDB enviroment.
[oracle@MaxwellDBA ~]$ sqlplus system/system as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jun 29 10:12:29 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle.All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> select name,cdb from v$database;
NAMECDB
--------- ---
ORCLCDBYES

2. Create New User
SQL> create user C##test identified by testpass;
User created.
3.setting authority
SQL> grant dba,connect,resource,create view to C##test;
Grant succeeded.
SQL> grant create session to C##test;
Grant succeeded.
SQL> grant select any table to C##test;
Grant succeeded.
SQL> grant update any table to C##test;
Grant succeeded.
SQL> grant insert any table to C##test;
Grant succeeded.
SQL> grant delete any table to C##test;
Grant succeeded.
4. Drop user
SQL> drop user C##test cascade;
User dropped.
SQL>
Create new user on PDB

check the PDB name

SQL> select pdb_id,pdb_name,dbid,status,creation_scn from dba_pdbs; PDB_ID ---------- PDB_NAME -------------------------------------------------------------------------------- DBID STATUSCREATION_SCN ---------- ---------- ------------ 3 ORCLPDB1 634705280 NORMAL21561432 PDB$SEED 1473614105 NORMAL2014329PDB_ID ---------- PDB_NAME -------------------------------------------------------------------------------- DBID STATUSCREATION_SCN ---------- ---------- ------------SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs; CON_IDDBID ---------- ---------- NAME -------------------------------------------------------------------------------- OPEN_MODE ---------- 2 1473614105 PDB$SEED READ ONLY3634705280 ORCLPDB1 READ WRITECON_IDDBID ---------- ---------- NAME -------------------------------------------------------------------------------- OPEN_MODE ----------

Local User PDB Name is ORCLPDB1
Enter into the PDB enviroment
SQL> alter session set container=ORCLPDB1;

SQL> alter session set container=ORCLPDB1 2; Session altered.

New User

SQL> create user test2 identified by test2pass; User created.SQL>

Setting authority

grant dba,connect,resource,create view to test2; grant select any table to test2; grant update any table to test2; grant insert any table to test2; grant delete any table to test2; grant create session to test2;

Setting the TNS
If not setting the TNS, it will be failed . error message is user/password error , due to the user on CDB defaultly. need to change the file tnsname.ora on remote linux oracle path network/admin（/opt/oracle/product/19c/dbhome_1/network/admin）

ORACLE|How to create new user for ORACLE 19c (CDB & PDB)

文章图片

# tnsnames.ora Network Configuration File: /opt/oracle/product/19c/dbhome_1/network/admin/tnsnames.ora # Generated by Oracle configuration tools.ORCLCDB = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = MaxwellDBA)(PORT = 1521)) (CONNECT_DATA = https://www.it610.com/article/(SERVER = DEDICATED) (SERVICE_NAME = ORCLCDB) ) )LISTENER_ORCLCDB = (ADDRESS = (PROTOCOL = TCP)(HOST = MaxwellDBA)(PORT = 1521))ORCLPDB1 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = MaxwellDBA)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORCLPDB1) ) )LISTENER_ORCLPDB1 = (ADDRESS = (PROTOCOL = TCP)(HOST = MaxwellDBA)(PORT = 1521))