Multitenant- Create Local Users on current PDB

Multitenant- Create  Local Users on current PDB

• Current status of users with PDB name

SQL> set lines 200
SQL> col USERNAME for a40
SQL> SELECT U.USERNAME, P.PDB_NAME
FROM CDB_USERS U, CDB_PDBS P
WHERE U.CON_ID = P.CON_ID AND COMMON='NO'
ORDER BY 2,1;

USERNAME                                 PDB_NAME
---------------------------------------- ----------
INFODBA                                  PLM2
PDB1ADMIN                                PLM2
INFODBA                                  PLM4
TESTUSER                                 PLM4
INFODBA                                  PLM5

• Try to create common user on PLM2 PDB database using CONTAINER=ALL;  it will through error.

SQL> CREATE USER C##USER2 IDENTIFIED BY x CONTAINER=ALL;
CREATE USER C##USER2 IDENTIFIED BY x CONTAINER=ALL
*
ERROR at line 1:
ORA-65050: Common DDLs only allowed in root.

• Try to create local user with mentioning CONTAINER=CURRENT on PLM2 pdb database

SQL> CREATE USER  LUSER2 IDENTIFIED BY  LUSER2 CONTAINER=CURRENT;

User created.

• Test Connecting  luser2 onPLM2 pluggable database. 

SQL>
oracle@srv3:/u01/app/oracle/product/12.2.0/dbhome_1/network/admin> sqlplus sys/system123@SMRCDB as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Thu Aug 13 18:57:35 2020

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> connect LUSER2/ LUSER2@PLM2
Connected.
SQL> sho user
USER is "LUSER2"

• It will not connect as luser2 was created on plm2 db using container=current

SQL> connect LUSER2/ LUSER2@PLM3
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified


Warning: You are no longer connected to ORACLE.
SQL> connect LUSER2/ LUSER2@SMRCDB
ERROR:
ORA-01017: invalid username/password; logon denied