Secure external password store (SEPS) implementation

Preamble

Warning: nothing recent in this blog post ! But as usual in our continuous improvement to secure our Oracle databases and the never ending requests of our preferred SOX auditors we have been looking for a solution to hide applicative accounts passwords from developers and end users.

You might argue that those applicative passwords should not be known by any non authorized person, and you were right except that in real life there might be some divergence on this basic rule. So how would they know those applicative accounts passwords ? Answer is by simply displaying batch job or display running processes for example (ps command). Introduced with Oracle 10gR2 Oracle secure external password store (SEPS) feature target is exactly answering to this problem: hiding clear text passwords in batch scripts and allowing people to access a database with an account without knowing the password.

The background of this feature is Oracle Wallet and we will store inside accounts and their associated password, in an encrypted way (3DES) of course, and the usage will be then password less on command line either interactively or in batch jobs.

Last but not least this feature does not require the Enterprise Advanced Security paid option. You can even use it for free on your Windows laptop client !

This blog post has been written using two virtual machines running Oracle 12cR2 (12.2.0.1.0) Enterprise Edition for the Oracle database and client part. Both machines are running Oracle Linux Server release 7.4. In below server1.domain.com is my database server while client part is running on server4.domain.com.

Wallet creation

Wallet management is made of three distinct tools:

Oracle Wallet Manager (OWM), only graphical tool in this list
orapki
mkstore

Very quickly you will realize that OWM has no menu to manage SEPS and even if you can create an empty wallet you cannot save if it is empty… We will need to work with command line tools…

The recommended tool to use is mkstore but orapki has the interesting -auto_login_local option which forbid the wallet to be copied to another machine. I will use OWM default directory i.e. $ORACLE_HOME/owm/wallets/oracle.

Create the wallet with:

[oracle@server4 ~]$ mkstore -wrl $ORACLE_HOME/owm/wallets/oracle -create
Oracle Secret Store Tool : Version 12.2.0.1.0
Copyright (c) 2004, 2016, Oracle and/or its affiliates. All rights reserved.
 
Enter password:
Enter password again:
[oracle@server4 ~]$ ll $ORACLE_HOME/owm/wallets/oracle
total 8
-rw------- 1 oracle dba 194 Feb  6 12:46 cwallet.sso
-rw------- 1 oracle dba   0 Feb  6 12:46 cwallet.sso.lck
-rw------- 1 oracle dba 149 Feb  6 12:46 ewallet.p12
-rw------- 1 oracle dba   0 Feb  6 12:46 ewallet.p12.lck

Or with orapki and -auto_login_local option:

[oracle@server4 ~]$ orapki wallet create -wallet $ORACLE_HOME/owm/wallets/oracle -auto_login_local
Oracle PKI Tool : Version 12.2.0.1.0
Copyright (c) 2004, 2016, Oracle and/or its affiliates. All rights reserved.
 
Enter password:
Enter password again:
Operation is successfully completed.
[oracle@server4 ~]$ ll $ORACLE_HOME/owm/wallets/oracle
total 8
-rw------- 1 oracle dba 194 Feb  6 12:48 cwallet.sso
-rw------- 1 oracle dba   0 Feb  6 12:48 cwallet.sso.lck
-rw------- 1 oracle dba 149 Feb  6 12:48 ewallet.p12
-rw------- 1 oracle dba   0 Feb  6 12:48 ewallet.p12.lck

You also need to modify your client sqlnet.ora to specify where is your wallet with WALLET_LOCATION and tell the client to override the credential with he one stored in the wallet with SQLNET.WALLET_OVERRIDE:

[oracle@server4 ~]$ cat $ORACLE_HOME/network/admin/sqlnet.ora
WALLET_LOCATION=
  (SOURCE=
      (METHOD=file)
      (METHOD_DATA=
         (DIRECTORY=/u01/app/oracle/product/12.2.0/client_1/owm/wallets/oracle)))
 
SQLNET.WALLET_OVERRIDE=true

Secure External Password Store credentials creation

To insert credentials into your wallet the only available tool is mkstore. When creating credential either you supply password on command line like first example or, better, you supply them interactively like second example. As you have guessed -deleteCredential is used to delete a credential and -listCredential to list them:

[oracle@server4 ~]$ mkstore
Oracle Secret Store Tool : Version 12.2.0.1.0
Copyright (c) 2004, 2016, Oracle and/or its affiliates. All rights reserved.
 
mkstore [-wrl wrl] [-create] [-createSSO] [-createLSSO] [-createALO] [-delete] [-deleteSSO] [-list] [-createEntry alias secret] [-viewEntry alias]
[-modifyEntry alias secret] [-deleteEntry alias] [-createCredential connect_string username password] [-listCredential]
[-modifyCredential connect_string username password] [-deleteCredential connect_string]  [-createUserCredential map key  <username> password]
[-modifyUserCredential map key username password]  [-deleteUserCredential map key] [-help] [-nologo]
[oracle@server4 ~]$ mkstore -wrl $ORACLE_HOME/owm/wallets/oracle -createCredential pdb1_yjaquier yjaquier secure_password
Oracle Secret Store Tool : Version 12.2.0.1.0
Copyright (c) 2004, 2016, Oracle and/or its affiliates. All rights reserved.
 
Enter wallet password:
[oracle@server4 ~]$ mkstore -wrl $ORACLE_HOME/owm/wallets/oracle -deleteCredential pdb1_yjaquier
Oracle Secret Store Tool : Version 12.2.0.1.0
Copyright (c) 2004, 2016, Oracle and/or its affiliates. All rights reserved.
 
Enter wallet password:
[oracle@server4 ~]$ mkstore -wrl $ORACLE_HOME/owm/wallets/oracle -createCredential pdb1_yjaquier yjaquier
Oracle Secret Store Tool : Version 12.2.0.1.0
Copyright (c) 2004, 2016, 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:
[oracle@server4 ~]$ mkstore -wrl $ORACLE_HOME/owm/wallets/oracle -listCredential
Oracle Secret Store Tool : Version 12.2.0.1.0
Copyright (c) 2004, 2016, Oracle and/or its affiliates. All rights reserved.
 
Enter wallet password:
List credential (index: connect_string username)
1: pdb1_yjaquier yjaquier

The orapki equivalent has no interest, same as -list option of mkstore:

[oracle@server4 ~]$ orapki wallet display -wallet /u01/app/oracle/product/12.2.0/client_1/owm/wallets/oracle
Oracle PKI Tool : Version 12.2.0.1.0
Copyright (c) 2004, 2016, Oracle and/or its affiliates. All rights reserved.
 
Requested Certificates:
User Certificates:
Oracle Secret Store entries:
oracle.security.client.connect_string1
oracle.security.client.password1
oracle.security.client.username1
Trusted Certificates:
[oracle@server4 ~]$ mkstore -wrl /u01/app/oracle/product/12.2.0/client_1/owm/wallets/oracle -list
Oracle Secret Store Tool : Version 12.2.0.1.0
Copyright (c) 2004, 2016, Oracle and/or its affiliates. All rights reserved.
 
Enter wallet password:
Oracle Secret Store entries:
oracle.security.client.connect_string1
oracle.security.client.connect_string2
oracle.security.client.password1
oracle.security.client.password2
oracle.security.client.username1
oracle.security.client.username2

Remark
If you want to modify or delete the entries you have mkstore -modifyCredential and -deleteCredential option !

You also need to insert a TNS entry in tnsnames.ora with exact same name as the credential you have just created:

[oracle@server4 ~]$ cat $ORACLE_HOME/network/admin/tnsnames.ora
pdb1_yjaquier =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = server1.domain.com)(PORT = 1531))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pdb1)
    )
  )
[oracle@server4 ~]$ tnsping pdb1_yjaquier
 
TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 06-FEB-2018 15:21:23
 
Copyright (c) 1997, 2016, Oracle.  All rights reserved.
 
Used parameter files:
/u01/app/oracle/product/12.2.0/client_1//network/admin/sqlnet.ora
 
 
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = server1.domain.com)(PORT = 1531)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = pdb1)))
OK (10 msec)

Remark:
We see here that it’s important to setup a good naming convention in our credentials or it might quickly become a mess. Here I have chosen service name_account name.

Even if you cannot create those entries with OWM you can use it to display them, editing is also not available:

One “funny” thing is that you can still display passwords of the entries you have created in SEPS:

[oracle@server4 ~]$ mkstore -wrl /u01/app/oracle/product/12.2.0/client_1/owm/wallets/oracle -viewEntry oracle.security.client.username1
Oracle Secret Store Tool : Version 12.2.0.1.0
Copyright (c) 2004, 2016, Oracle and/or its affiliates. All rights reserved.
 
Enter wallet password:
oracle.security.client.username1 = yjaquier
[oracle@server4 ~]$ mkstore -wrl /u01/app/oracle/product/12.2.0/client_1/owm/wallets/oracle -viewEntry oracle.security.client.password1
Oracle Secret Store Tool : Version 12.2.0.1.0
Copyright (c) 2004, 2016, Oracle and/or its affiliates. All rights reserved.
 
Enter wallet password:
oracle.security.client.password1 = secure_password

Secure External Password Store testing

SQL*Plus

The most simple test is using SQL*Plus that is coming with my Linux client:

[oracle@server4 ~]$ sqlplus /@pdb1_yjaquier
 
SQL*Plus: Release 12.2.0.1.0 Production on Tue Feb 6 15:21:52 2018
 
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
 
Last Successful login time: Mon Feb 05 2018 15:02:21 +01:00
 
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
 
SQL> show user
USER is "YJAQUIER"

JDBC OCI driver

Using the JDBC OCI driver is the most simple when planning to use SEPS because you directly benefit from the Oracle client where you have configured SEPS through the Oracle wallet. The source code I have written is:

import java.sql.ResultSet;
import java.sql.Connection;
import java.sql.SQLException;
import oracle.jdbc.OracleDriver;
import java.sql.DriverManager;
 
public class seps_oci {
  public static void main(String[] args) throws Exception {
    Connection connection1 = null;
    String query1 = "select user from dual";
    ResultSet resultset1 = null;
 
    try {
      connection1 = DriverManager.getConnection("jdbc:oracle:oci:/@pdb1_yjaquier");
    }
    catch (SQLException e) {
      System.out.println("Connection Failed! Check output console");
      e.printStackTrace();
      System.exit(1);
    }
    System.out.println("Connected to Oracle database...");
 
    if (connection1!=null) {
      try {
        resultset1 = connection1.createStatement().executeQuery(query1);
        while (resultset1.next()) {
          System.out.println("Connected user: "+resultset1.getString(1));
        }
      }
      catch (SQLException e) {
        System.out.println("Query has failed...");
      }
    }
    resultset1.close();
    connection1.close(); 
  }
}

To execute it command line (I normally use Eclipse) do:

[oracle@server4 ~]$ javac -cp $ORACLE_HOME/jdbc/lib/ojdbc8.jar seps_oci.java
[oracle@server4 ~]$ java -cp $ORACLE_HOME/jdbc/lib/ojdbc8.jar:. seps_oci
Connected to Oracle database...
Connected user: YJAQUIER

JDBC Thin driver

Using JDBC Thin driver is a little more complex because all the part done by the installed Oracle client is not pre-configured as for the JDBC OCI driver. And here it’s a little weird as you are supposed not having a client (Thin driver) but you need one for libraries and wallet configuration. Please note that instant client is not enough to do the job.

The first property to set when trying to connect is wallet location with oracle.net.wallet_location, this is done by:

props.setProperty("oracle.net.wallet_location","(SOURCE=(METHOD=file)(METHOD_DATA=(DIRECTORY=/u01/app/oracle/product/12.2.0/client_1/owm/wallets/oracle)))");

After to specify connect string you have two options either you insert in SEPS the complete TNS entry with something like:

[oracle@server4 ~]$ mkstore -wrl $ORACLE_HOME/owm/wallets/oracle -createCredential "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=server1.domain.com)
(PORT=1531))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=pdb1)))" yjaquier
Oracle Secret Store Tool : Version 12.2.0.1.0
Copyright (c) 2004, 2016, 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:
[oracle@server4 ~]$ mkstore -wrl $ORACLE_HOME/owm/wallets/oracle -listCredential
Oracle Secret Store Tool : Version 12.2.0.1.0
Copyright (c) 2004, 2016, Oracle and/or its affiliates. All rights reserved.
 
Enter wallet password:
List credential (index: connect_string username)
2: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=server1.domain.com)(PORT=1531))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=pdb1))) yjaquier
1: pdb1_yjaquier yjaquier

Or you tell your Java program where is located tnsnames.ora file with oracle.net.tns_admin property:

System.setProperty("oracle.net.tns_admin","/u01/app/oracle/product/12.2.0/client_1/network/admin");

I have kept the two options in comment in my below Java code:

import java.sql.ResultSet;
import java.util.Properties;
import java.sql.Connection;
import java.sql.SQLException;
import oracle.jdbc.OracleDriver;
import java.sql.DriverManager;
 
public class seps_thin {
  public static void main(String[] args) throws Exception {
    Connection connection1 = null;
    String query1 = "select user from dual";
    String connect_string = "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=server1.domain.com)(PORT=1531))"+
                            "(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=pdb1)))";
    ResultSet resultset1 = null;
    Properties props = new Properties();
 
    try {
      props.setProperty("oracle.net.wallet_location","(SOURCE=(METHOD=file)(METHOD_DATA="+
                        "(DIRECTORY=/u01/app/oracle/product/12.2.0/client_1/owm/wallets/oracle)))");
      System.setProperty("oracle.net.tns_admin","/u01/app/oracle/product/12.2.0/client_1/network/admin");
      //connection1 = DriverManager.getConnection("jdbc:oracle:thin:/@" + connect_string, props);
      connection1 = DriverManager.getConnection("jdbc:oracle:thin:/@pdb1_yjaquier", props);
    }
    catch (SQLException e) {
      System.out.println("Connection Failed! Check output console");
      e.printStackTrace();
      System.exit(1);
    }
    System.out.println("Connected to Oracle database...");
 
    if (connection1!=null) {
      try {
        resultset1 = connection1.createStatement().executeQuery(query1);
        while (resultset1.next()) {
          System.out.println("Connected user: "+resultset1.getString(1));
        }
      }
      catch (SQLException e) {
        System.out.println("Query has failed...");
      }
    }
    resultset1.close();
    connection1.close(); 
  }
}

Execute it same as for JDBC Thin driver except that you need to add oraclepki.jar from $ORACLE_HOME/jlib directory (not the one from $ORACLE_HOME/oc4j/jlib directory):

[oracle@server4 ~]$ javac -cp $ORACLE_HOME/jdbc/lib/ojdbc8.jar:$ORACLE_HOME/jlib/oraclepki.jar seps_thin.java
[oracle@server4 ~]$ java -cp $ORACLE_HOME/jdbc/lib/ojdbc8.jar:$ORACLE_HOME/jlib/oraclepki.jar:. seps_thin
Connected to Oracle database...
Connected user: YJAQUIER

References

How to Use an External Password Store with the OCI JDBC Driver (Doc ID 403744.1)
Using a Secure External Password Store with the JDBC Thin Driver (Doc ID 1441745.1)
How To Use an External Password Store with JDBC/Thin (Doc ID 1123423.1)
Using The Secure External Password Store (Doc ID 340559.1)
Secure External Password Store
Using the orapki Utility to Manage PKI Elements
Configuring Authentication
Secure External Password Store
How To Configure Oracle External Password Store (Wallet) To Work With Oracle ODBC (Doc ID 1430666.1)

IT World

Secure external password store (SEPS) implementation

Table of contents

Preamble

Wallet creation

Secure External Password Store credentials creation

Secure External Password Store testing

SQL*Plus

JDBC OCI driver

JDBC Thin driver

References

About Post Author

Yannick Jaquier

Related Posts:

Leave a Reply Cancel Reply