This one is more on a curious side =) but somehow interesting.
Not too long ago Mike Dietrichde published a post on to connect directly to a PDB without a password.
After it, lots of DBAs started to post their testing showing the same, But how it works? As there is only a bequeath connection available to a CDB?
Well, I was doing some digging for another project and stumbled on a curious thing. A logon trigger called DBMS_SET_PDB
To my surprise, this trigger alters the session´s container to the one specified by the ORACLE_PDB_SID variable. Similar to Tanel Poder´s script called cc.sql or ccr.sql which basically are short cuts to avoid a lot of typing =)
SYS@CDB19c>select DBMS_Metadata.Get_DDL('TRIGGER',t.trigger_name,t.owner) From DBA_Triggers t Where TRIGGER_NAME ='DBMS_SET_PDB'; 2 3 4 5 6 DBMS_METADATA.GET_DDL('TRIGGER',T.TRIGGER_NAME,T.OWNER) -------------------------------------------------------------------------------- CREATE OR REPLACE NONEDITIONABLE TRIGGER "SYS"."DBMS_SET_PDB" after logon on database WHEN (user = 'SYS' or user = 'SYSTEM') declare pdb_name varchar2(64); begin DBMS_SYSTEM.get_env ('ORACLE_PDB_SID', 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 dbms_set_pdb; ALTER TRIGGER "SYS"."DBMS_SET_PDB" ENABLE
Also is mentioned in the article that this is this would only work on versions 18.8 and above. But well, as we have the code for the trigger, should it possible to work on 12c as well?
Let´s try it| =) the oldest container which I have at the moment is 12r2 but that should do it.
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production SYS@CDB12CR2>select * from v$version; BANNER CON_ID -------------------------------------------------------------------------------- ---------- Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 0 PL/SQL Release 12.2.0.1.0 - Production 0 CORE 12.2.0.1.0 Production 0 TNS for Linux: Version 12.2.0.1.0 - Production 0 NLSRTL Version 12.2.0.1.0 - Production 0 SYS@CDB12CR2>CREATE OR REPLACE NONEDITIONABLE TRIGGER "SYS"."DBMS_SET_PDB_EFA" after logon on database 2 WHEN (user = 'SYS' or user = 'SYSTEM') declare 3 pdb_name varchar2(64); begin 4 5 DBMS_SYSTEM.get_env ('ORACLE_PDB_SID', pdb_name); 6 if(pdb_name is not null) 7 then 8 EXECUTE IMMEDIATE 'alter session set container = ' || '"' || pdb_name 9 || '"'; 10 end if; exception when others then NULL; end DBMS_SET_PDB_EFA; 11 12 13 14 15 / Trigger created. SYS@CDB12CR2>ALTER TRIGGER "SYS"."DBMS_SET_PDB_EFA" ENABLE 2 / Trigger altered. SYS@CDB12CR2>show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB12CR2 READ WRITE NO SYS@CDB12CR2>exit Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production [oracle@server01 ~]$ export ORACLE_PDB_SID=PDB12CR2; [oracle@server01 ~]$ CDB12CR2 The Oracle base remains unchanged with value /oraadm/oracle LD_LIBRARY_PATH=/oraadm/oracle/product/12.2.0.1/lib ORACLE_BASE=/oraadm/oracle ORACLE_HOME=/oraadm/oracle/product/12.2.0.1 ORACLE_PDB_SID=PDB12CR2 ORACLE_SID=CDB12CR2 ORAENV_ASK=NO [oracle@server01 ~]$ sqlplus / as sysdba SQL*Plus: Release 12.2.0.1.0 Production on Thu Jul 30 11:30:34 2020 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production SYS@CDB12CR2>show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 3 PDB12CR2 READ WRITE NO SYS@CDB12CR2>alter session set container=cdb$root; Session altered. SYS@CDB12CR2>show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB12CR2 READ WRITE NO SYS@CDB12CR2>
And as you can see, the trigger works as expected right, it’s not a rocket science as it gets the context environment variable value and alters the session´s container.
Thanks and until next time.
Cheers.
Elisson Almeida
Note:
Right after I wrote this post, I went to look for more information on ORACLE_PDB_SID and found another post from Mike Dietrichde which also shows the trigger code and some pitfalls that you must be aware.