How to Connect a PDB Directly by OS Authentication

  • by

OS Authentication

We usually connect to the database by / AS SYSDBA without providing any password for maintenance works, such as startup, shutdown and backup. This is because we are in OSDBA or OSOPER group, so we are naturally authenticated at OS-level and don't have to provide password to login.

Connect PDB

In multitenant environment, the thing becomes a little different, we can still connect to Container Database (CDB) without password, but not Pluggable Database (PDB).

If we want to connect to a PDB, we usually provide a connection string which includes credentials and a tnsname to login.

[oracle@test ~]$ sqlplus sys/password@orclpdb as sysdba
...
SQL> select count(*) from hr.employees;

  COUNT(*)
----------
       107

A problem comes, how do we connect to the pluggable database (PDB) without password? Let's see the second case.

[oracle@test ~]$ sqlplus / as sysdba
...
SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL> alter session set container=ORCLPDB;

Session altered.

SQL> show con_name

CON_NAME
------------------------------
ORCLPDB
SQL> select count(*) from hr.employees;

  COUNT(*)
----------
       107

As you can see, we set container in session in order to get into PDB. Honestly, both cases are somewhat inconvenient, we want to connect to the PDB directly by OS authentication.

Solution

Since 18c, we have another scheme to connect to a PDB directly without password. The secret ingredient is set an additional environment variable ORACLE_PDB_SID to let Oracle know what PDB we want to directly connect to.

[oracle@test ~]$ echo $ORACLE_SID
ORCLCDB
[oracle@test ~]$ export ORACLE_PDB_SID=ORCLPDB
[oracle@test ~]$ sqlplus / as sysdba
...
SQL> show con_name

CON_NAME
------------------------------
ORCLPDB
SQL> select count(*) from hr.employees;

  COUNT(*)
----------
       107

It's extremely useful when we want to directly export data from a PDB without providing any password. Moreover, utilities that need authentication are all applicable to this case.

Please note that, ORACLE_PDB_SID is a dependency of ORACLE_SID. That is, if there's no ORACLE_SID, Oracle still have no idea where to go.

If you found that you still can't do this in 18c or 19c database, maybe you should consider to apply Database Release Update (DBRU) on Oracle database 19c.

For releases before 18c, you may create a logon trigger to do it.

Leave a Reply

Your email address will not be published. Required fields are marked *