Oracle 19c 直接连接pdb

Oracle Database - Enterprise Edition - Version 19.3.0.0.0 and later
Information in this document applies to any platform.

GOAL

The purpose of the note is to introduce that the method to connect to PDB by OS authentication in 19c container database environment.

SOLUTION

Because there is no bequeath connection available in PDB, it cannot connect to PDB directly by OS authentication.
But through logon trigger SYS or other external user can connect to PDB without password.

1. SYS user can connect to PDB by predefined trigger DBMS_SET_PDB and environment variable ORACLE_PDB_SID.

For example:
 

-bash-4.1$ export ORACLE_PDB_SID=PDB1
-bash-4.1$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Nov 17 13:25:04 2020
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> show con_name

CON_NAME
------------------------------
PDB1
SQL> quit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
-bash-4.1$ unset ORACLE_PDB_SID
-bash-4.1$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Nov 17 13:27:49 2020
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> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL> quit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
-bash-4.1$

2.For Non SYS user, connect to PDB by new defined trigger SET_PDB and environment variable ORACLE_PDB.

For example:
 

sqlplus / as sysdba
--Modify initial parameter os_authent_prefix to the same value of common_user_prefix
show parameter os_authent_prefix
alter system set os_authent_prefix='C##' scope=spfile;

--Create common user and grant necessary privilege, e.g create session,set container, etc.
create user c##<OS_USER_NAME> identified externally container=all;
grant create session,set container,<Other Privilege> to c##<OS_USER_NAME>;

----Restartup database
shutdown immediate
startup

--Create logon trigger for this common user.
sqlplus / as sysdba
CREATE OR REPLACE TRIGGER SYS.set_pdb
AFTER LOGON ON DATABASE
WHEN (USER like '&USERNAME')
DECLARE
pdb_name varchar(64);
BEGIN
 sys.DBMS_SYSTEM.get_env('ORACLE_PDB',pdb_name);
 if(pdb_name is not null) then
  EXECUTE IMMEDIATE 'alter session set container='||'"'||pdb_name||'"';
 end if;
EXCEPTION when others then
 null;
END set_pdb;
/

--sqlnet.ora:
SQLNET.AUTHENTICATION_SERVICES = (ALL)


--Connect test
export ORACLE_PDB=<Target PDB name>
sqlplus /
show con_name
show user

Test log:

-bash-4.1$ export ORACLE_PDB=<Target PDB name>
-bash-4.1$ sqlplus /

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Nov 17 14:20:25 2020
Version 19.3.0.0.0

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

Last Successful login time: Tue Nov 17 2020 14:19:40 +00:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> show con_name

CON_NAME
------------------------------
<Target PDB name>
SQL> show user
USER is "C##<OS_USER_NAME>"
SQL>
SQL> quit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
-bash-4.1$

-----------
 

After export ORACLE_PID_SID variable, it's connecting to CDB$ROOT instead of specified PDB.

export ORACLE_PID_SID=PDB1
$ sqlplus "/ as sysdba"

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Dec 1 21:12:46 2021
SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
 

CAUSE

Logon trigger is not invoking while connecting due to _system_trig_enabled = FALSE.
 
 

SOLUTION

After setting the parameter " _system_trig_enabled " to TRUE the issue got fixed.

SQL> show parameter _system_trig

NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
_system_trig_enabled boolean TRUE