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.