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 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
TNS_ADMIN environment variable to point to your local TNS configuration.
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
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: $
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
$ 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
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
$ 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:
- Secure External Password Store
- OS Authentication
- orapki wallet create (11gR2)
- Using The Secure External Password Store (Doc ID 340559.1)
Hope this helps. Regards Tim...