Secure External Password Store - Client Side

It is often necessary to make connections to the database from shell scripts held on the filesystem. This can be a major security issue if these scripts contain the database connection details. One solution is to use OS Authentication, but Oracle 10g Release 2 gives us the option of using a secure external password store where the Oracle login credentials are stored in a client-side Oracle wallet. This allows scripts to contain connections using the "/@db_alias" syntax.

Configure Secure External Password Store

First, decide on the location of the Oracle wallet and your local TNS configuration. In this example I will use an OS user called "myuser", so my directories will we as follows.

mkdir -p /home/myuser/wallet
mkdir -p /home/myuser/tns

Use the TNS_ADMIN environment variable to point to your local TNS configuration.

export TNS_ADMIN=/home/myuser/tns

Add the following entries into the client "/home/myuser/tns/sqlnet.ora" file, with your preferred wallet location.

WALLET_LOCATION =
   (SOURCE =
     (METHOD = FILE)
     (METHOD_DATA =
       (DIRECTORY = /home/myuser/wallet)
     )
   )

SQLNET.WALLET_OVERRIDE = TRUE
SSL_CLIENT_AUTHENTICATION = FALSE
SSL_VERSION = 0

The SQLNET.WALLET_OVERRIDE entry allows this method to override any existing OS authentication configuration. This setting causes all CONNECT /@db_connect_string statements to use the information in the wallet at the specified location to authenticate to databases.

Create an Oracle wallet in the previously specified location using the mkstore utility with the -create option. The wallet is password protected, but is defined with the "Auto Login" property enabled so connection attempts by the user who created the wallet do not require a password.

$ mkstore -wrl "/home/myuser/wallet" -create
Enter password:

Enter password again:


$

Wallets can be copied to different machines, which can represent a security risk. In 11g Release 2, you can prevent the auto login functionality of the wallet from working if it is copied to another machine by creating a local wallet using the "orapki" command, instead of the "mkstore" command.

$ orapki wallet create -wallet "/home/myuser/wallet" -pwd "MyPassword1" -auto_login_local

The wallet is created with only read/write permissions for the current user, so it can't be read by any other user.

Once the wallet is created, it can be modified using the "mkstore" command described below.

Add the password credentials to the wallet using the -createCredential option.

$ mkstore -wrl "/home/myuser/wallet" -createCredential db10g scott tiger
Enter password:

Create credential oracle.security.client.connect_string1

$

The db_alias, in this case "db10g", is the identifier used in the "/@db_alias" syntax, and must have a matching entry in the "/home/myuser/tnsnames.ora" file.

DB10G =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = DB10G.WORLD)
    )
  )

The credentials present in the wallet are listed using the -listCredential option.

$ mkstore -wrl "/home/myuser/wallet" -listCredential
Enter password:

List credential (index: connect_string username)
1: db10g scott

$

With the wallet created and the password credentials in place, connect to the database without specifying the username and password, as shown below.

$ sqlplus /@db10g

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jul 19 08:15:09 2007

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


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options

SQL> show user
USER is "SCOTT"
SQL>

That's fine if you only ever connect as a single user to each database, but what if you connect as multiple users? Simply add a new entry into the wallet using a different db_alias and make sure the alias is present in the "tnsnames.ora" file. So if we have a user called "test" on the "db10g" database, we create a new entry in the wallet.

$ mkstore -wrl "/home/myuser/wallet" -createCredential db10g_test test test
Enter password:

Create credential oracle.security.client.connect_string1

$

Make a new entry for the "db10g" database in the client "/home/myuser/tns/tnsnames.ora" file.

DB10G_TEST =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = DB10G.WORLD)
    )
  )

Now connect to the "test" user as shown below.

$ sqlplus /@db10g_test

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jul 19 10:17:47 2007

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


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options

SQL> show user
USER is "TEST"
SQL>

Using Wallets with Data Pump

The wallet works fine with data pump also.

$ expdp /@db10g_test tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp logfile=expdpEMP_DEPT.log

To use the secure external password store from a Java application you must use the OCI driver, which in turn means you must have an Oracle client installed. To make the connection use a custom URL like this.

Connection conn = DriverManager.getConnection ("jdbc:oracle:oci:/@db10g_test");

Password credentials of existing wallet entries can be modified or deleted using the following commands.

mkstore -wrl <wallet_location> -modifyCredential <dbase_alias> <username> <password>
mkstore -wrl <wallet_location> -deleteCredential <db_alias>

 Managing External Password Store Credentials 


1) Listing the contents of the external password store: 

mkstore -wrl /home/mseibt/pstore -listCredential
Enter password: 

List credential (index: connect_string username)
1: N102 scott
2) Adding database login credentials to an existing client wallet:

mkstore -wrl /home/mseibt/pstore -createCredential N101 scott tiger
Enter password: 

Create credential oracle.security.client.connect_string2

mkstore -wrl /home/mseibt/pstore -listCredential
Enter password: 

List credential (index: connect_string username)
1: N102 scott
2: N101 scott
3) Modifying database login credentials in a wallet: 

mkstore -wrl /home/mseibt/pstore -modifyCredential N102 scott newpassword 
Enter password: 

Modify credential 
Modify 1
4) Deleting database login credentials from a wallet: 

mkstore -wrl /home/mseibt/pstore -deleteCredential N101
Enter password: 

Delete credential 
Delete 2
5) Listing wallet entries: 

mkstore -wrl /home/mseibt/pstore -list
Enter password: 

Oracle Secret Store entries: 
oracle.security.client.connect_string1
oracle.security.client.password1
oracle.security.client.username1
6) Listing entry values: 

mkstore -wrl /home/mseibt/pstore -viewEntry oracle.security.client.connect_string1
Enter password: 

oracle.security.client.connect_string1 = N102

mkstore -wrl /home/mseibt/pstore -viewEntry oracle.security.client.username1
Enter password: 

oracle.security.client.username1 = scott

mkstore -wrl /home/mseibt/pstore -viewEntry oracle.security.client.password1
Enter password: 

oracle.security.client.password1 = tiger
7) Modifying entry values: 

mkstore -wrl /home/mseibt/pstore -modifyEntry oracle.security.client.password1 newpass
Enter password: 

mkstore -wrl /home/mseibt/pstore -viewEntry oracle.security.client.password1
Enter password: 

oracle.security.client.password1 = newpass