Local users cannot use local connection under Oracle PDB
The problem that local users cannot use local connections under Oracle PDB
Simulation:
1. Local user local connection:
SQL > show con_name
CON_NAME
CDB$ROOT
SQL > alter session set container=pdb1
Session altered.
SQL > create user test identified by test
User created.
SQL > grant connect,resource to test
Grant succeeded.
Use the test user connection:
SQL > conn test/test
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
Solution:
Configure the PDB with tnsname:
Find out the service_name of the PDB
Select name,network_name,pdb from v$services where pdb='PDB1'
NAME NETWORK_NAME PDB
Pdb1 pdb1 PDB1
Tnsname.ora:
Pdb1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.238.14) (PORT = 1529))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = pdb1)
)
)
Try to connect:
SQL > alter session set container=pdb1
SQL > conn test/test @ pdb1
Connected.
Problem solved.
two。 Local connection for public users:
SQL > show con_name
CON_NAME
CDB$ROOT
SQL > create user c##abc identified by c##abc
SQL > grant connect,resource to c##abc
Go to PDB to view the public user:
SQL > show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
2 PDB$SEED READ ONLY NO
3 ABC READ WRITE NO
5 SALESPDB MOUNTED
SQL > alter session set container=abc
Session altered.
SQL > select username from dba_users where username='C##ABC'
USERNAME
C##ABC
SQL > conn c##abc/c##abc
Connected.
Can connect ~
Conclusion:
A local user created in PDB cannot use a local connection the user conn name/password needs to use a network connection to connect the public user created in CDB by the user conn name/password@tnsname will be automatically created under PDB, and the public user can be connected locally in PDB.
