8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 10g » Here

Secure External Password Store

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 from Oracle 10g Release 2 onward we can use 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.

When using the multitenant architecture in later releases, OS authentication doesn't work, so a secure external password store is a great alternative.

Related articles.

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.

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:


$

The mkstore utility doesn't have an option to specify a password. If you need to script the creation, you can fake the user entry for the password and confirmation as follows.

$ mkstore -wrl "/home/myuser/wallet" -create <<EOF
MyPassword1
MyPassword1
EOF

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

$

As before, the mkstore utility doesn't have an option to specify a password. If you need to script the creation, you can fake the user entry for the password as follows.

$ mkstore -wrl "/home/myuser/wallet" -createCredential db10g scott tiger <<EOF
MyPassword1
EOF

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>

For more information see:

Hope this helps. Regards Tim...

Back to the Top.