Table of contents
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)