A more secure connection to a pdb with the Oracle Wallet

A more secure connection to a pdb with the Oracle Wallet

Wallets are cool! Since 9i oracle is pushing us towards services for connecting to a database. Since pdb’s have arrived, you get less and less choice to connect without a service. Recently I discovered passwords stored in environment variables in order to connect to the pdb. A short talk to the author of the script learned me that he just wants to connect to a pdb, so I offered the solution to store the password in the oracle wallet. Here is how I did it on my lab.

So an empty cdb:

SQL> select * from v$pdbs;

CON_ID DBID CON_UID GUID NAME OPEN_MODE RES OPEN_TIME CREATE_SCN
---------- ---------- ---------- -------------------------------- ------------------------------ ---------- --- --------------------------------------------------------------------------- ----------
TOTAL_SIZE BLOCK_SIZE RECOVERY SNAPSHOT_PARENT_CON_ID
---------- ---------- -------- ----------------------
2 1511209156 1511209156 4C691F1742090AA7E0533E01000A8D29 PDB$SEED READ ONLY NO 05-APR-17 01.28.46.189 PM +02:00 207
892338176 8192 ENABLED 0

SQL>

As you can see, this is a very new, clean cdb. So first step is to create a pdb:

SQL> CREATE PLUGGABLE DATABASE mypdb ADMIN USER pdbadm IDENTIFIED BY password;

Pluggable database created.

SQL>

And then check:

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL> select CON_ID,name,open_mode from v$pdbs;

CON_ID NAME OPEN_MODE
---------- ------------------------------ ----------
2 PDB$SEED READ ONLY
3 MYPDB MOUNTED

SQL>

the pdb must still be opened:

SQL> alter pluggable database mypdb open;

Pluggable database altered.

SQL>

and then you can log into it:


SQL> alter session set container=mypdb;

Session altered.

SQL> show con_name

CON_NAME
------------------------------
MYPDB
SQL>

Then I created a tnsnames.ora entry and I specified the name of the service to which the pdb will listen to:

mypdbsrv =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = labvms02n01.labo.internal.stepi.net)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = mypdbsrv)(UR=A)
)
)

so that means that we have to teach the pdb to listen to this service as well. If you are connected to the pdb, and then create the service, then the service is associated to that pdb:


SQL> show con_name

CON_NAME
------------------------------
MYPDB
SQL> BEGIN
DBMS_SERVICE.CREATE_SERVICE(
service_name => 'mypdbsrv',
network_name => 'mypdbsrv');
END;
/
2 3 4 5 6
PL/SQL procedure successfully completed.

SQL>

Don’t forget to start the service and check it in the listener:


SQL> exec DBMS_SERVICE.START_SERVICE('mypdbsrv');

PL/SQL procedure successfully completed.

SQL> !lsnrctl status

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 06-APR-2017 14:52:40

Copyright (c) 1991, 2014, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=labvms02n01.labo.internal.stepi.net)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date 05-APR-2017 16:14:06
Uptime 0 days 22 hr. 38 min. 34 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/12.1.0.2/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/labvms02n01/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=labvms02n01.labo.internal.stepi.net)(PORT=1521)))
Services Summary...
Service "mypdb" has 1 instance(s).
Instance "raclabsi", status READY, has 1 handler(s) for this service...
Service "mypdbsrv" has 1 instance(s).
Instance "raclabsi", status READY, has 1 handler(s) for this service...
Service "raclabsi2" has 1 instance(s).
Instance "raclabsi", status READY, has 1 handler(s) for this service...
Service "raclabsiXDB" has 1 instance(s).
Instance "raclabsi", status READY, has 1 handler(s) for this service...
The command completed successfully

SQL>

Then we can configure the wallet. First step is to create one. This is done by creating a directory and storing the wallet in it. I created the directory /home/oracle/wallet. To create the wallet in this location:

[oracle@labvms02n01 ~]$ pwd
/home/oracle
[oracle@labvms02n01 ~]$ mkdir wallet
[oracle@labvms02n01 ~]$ cd wallet/
[oracle@labvms02n01 wallet]$
[oracle@labvms02n01 wallet]$ mkstore -wrl $PWD -create
Oracle Secret Store Tool : Version 12.1.0.2
Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.

Enter password:
Enter password again:
[oracle@labvms02n01 wallet]$

make sure the password is complex enough! Then the client sqlnet.ora (the one hosting the wallet) must be updated to teach oracle where the wallet is:

[oracle@labvms02n01 wallet]$ cat $ORACLE_HOME/network/admin/sqlnet.ora
WALLET_LOCATION =
(SOURCE =
(METHOD = FILE)
(METHOD_DATA =
(DIRECTORY = /home/oracle/wallet)
)
)
SQLNET.WALLET_OVERRIDE = TRUE
[oracle@labvms02n01 wallet]$

the last line is important if you are dealing with a client wallet. Basically it’s only one line which needs to be adapted if you stored the wallet in another location and that’s the directory path.

So that’s almost it, we’re almost done. Now we can store the user which we want to let login without a password in the wallet. This can be done using mkstore as well:

[oracle@labvms02n01 wallet]$ mkstore -wrl /home/oracle/wallet -createCredential mypdbsrv pdbadm
Oracle Secret Store Tool : Version 12.1.0.2
Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.

Your secret/Password is missing in the command line
Enter your secret/Password:
Re-enter your secret/Password:
Enter wallet password:
Create credential oracle.security.client.connect_string1
[oracle@labvms02n01 wallet]$

the createCredential option needs 2 things. The tnsnames.ora alias from the pdb and the user you want to connect to that alias. In our case we are connecting to the mypdbsrv entry using the pdbadm user.

And then it’s time to test!

[oracle@labvms02n01 wallet]$ sqlplus /@mypdbsrv

SQL*Plus: Release 12.1.0.2.0 Production on Thu Apr 6 14:56:28 2017

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

Last Successful login time: Thu Apr 06 2017 14:53:02 +02:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> show con_name

CON_NAME
------------------------------
MYPDB
SQL> show user
USER is "PDBADM"
SQL>

So, as you can see, it is fairly easy to get rid of hard coded passwords. Only 2 things to mention.
The wallet uses the tns-alias to store his data. Suppose that you want to store a second user, you need to specify a second tns-alias. Maybe there are workarounds, but I did not find them yet.
Second important thing. Manage your TNS_ADMIN variable. So if you’re not using a default location of sqlnet.ora, make sure tns_admin is pointing to the directory where the sqlnet.ora containing the reference to the wallet directory can be found.

More information can also be found in the oracle documentation.

As always, questions, remarks? find me on twitter @vanpupi

 

Comments are closed.