Monday, July 22, 2013

Oracle Database wallets for security

Introduction

Most of the production environments have to run multiple application scripts to access the RDBMS, to load and/or process the data. This is majorly achieved by executing pl/sql in either the shell scripts or the batch scripts, many a times by hard coding the DB credentials.

Even if the shell script characters were to be encrypted, we still run at risk of exposing the user credentials while executing these scripts. Oracle Wallet provides a mechanism to prevent the compromise of user credentials.

The following sections will describe how to configure the wallets in oracle RDBMS 10g/11g, and resolve some common errors faced while doing so

Steps to configure the wallet

This document takes example of Oracle 11gR1 RDMBS on Windows platform to demonstrate the oracle wallet configuration. We’ll create the user alias, which can be used in scripts and in sqlplus, to create a user session without exposing the credentials.

Below the important attributes -

Schema to be secured by wallet configuration: SH

Password for “SH” schema: Test#321

Alias name to be created for “SH” schema: SH_ORCL

 

On high level, the steps involved to achieve this are -

-      Preparing the server SQLNET.ORA file

-      Creating the repository for storing user credential alias, called Wallet

-      Adding alias for a particular user credential, to the wallet

 

1.    Preparing the SQLNET.ORA file

Open the sqlnet.ora file from $ORACLE_HOME\network\admin directory and add the following entries to the file

 

WALLET_LOCATION = (SOURCE = (METHOD = FILE)(METHOD_DATA =(DIRECTORY = C:\Users\sanjaymohanlal_d\Oracle\WALLETS)))

 

SQLNET.WALLET_OVERRIDE = TRUE

SSL_CLIENT_AUTHENTICATION = FALSE

SSL_VERSION = 0

For unix based platforms, the file can be found at $ORACLE_HOME/network/admin directory

The “SQLNET.WALLET_OVERRIDE = TRUE” will ignore/override the OS authentication if set earlier.

2.    Create secure alias repository i.e. Wallet

Use the mkstore tool to create the wallet. Ensure that you have proper file system on the directory that you specify to create the wallet.

 

C:\Users\sanjaymohanlal_d>mkstore -wrl "C:\Users\sanjaymohanlal_d\Oracle\WALLETS" -create

Enter password:

 

Enter password again:

 

(You’ll have to provide a password for the newly created empty wallet)

 

3.    Create alias for a user credential

 

The below sections shows the actual steps to achieve this

 

3.1         Adding SH_ORCL as alias to the wallet, for SH schema

C:\Users\sanjaymohanlal_d>mkstore -wrl "C:\Users\sanjaymohanlal_d\Oracle\WALLETS" -createCredential SH_orcl SH Test#321

Enter wallet password:

 

Create credential oracle.security.client.connect_string1

As you can see, running the mkstore tool prompts for the wallet password, while creating the wallet entry, and needs the

3.2         Modifying the TNSNAMES.ORA file

Add the following entry the TNSNAMES.ORA file

 

SH_ORCL =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = PUNITP95418L.ad.sanjaymd.com)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = ORCL)

)

)

 

Most Common Errors

-      ORA-12578: TNS: wallet open failed error; while creating the wallet alias

This suggests that the OS user doesn’t has required file permission on the doirectory where the wallet repository is being created

For Windows OS, ensure that user is part of Administrator & ORA_DBA group

For *ix OS, make sure you have rwx permission for the OS user “oracle” (or applicable OS user with sysdba role)

-      ORA-01017: invalid username/password; logon denied; while connecting to the database using alias

As weird as it may sound, there is actually no error in your configuration most of the times when you face this error. The problem here lies with the way sqlnet.ora file is read by oracle binaries. The WALLET_LOCATION value, when spanning multiple lines in sqlnet.ora file, is prone to file misread issues

To overcome this, ensure that the value of the WALLET_LOCATION is provided in a single line format, as per the steps provided in this document

 

Alias Usage & Examples for Modifying the Wallet

-      Usage in sqlplus

sqlplus /@SH_ORCL

In many applications, the variable $UP in declared on system and access as below

(unix)export UP=/@SH_ORCL

(Windows)set UP=/@SH_ORCL

 

sqlplus $UP

 

-      Listing the existing credentials stores in wallet

mkstore -wrl "C:\Users\sanjaymohanlal_d\Oracle\WALLETS" -listCredential

-      Modifying wallet credential/alias

mkstore -wrl "C:\Users\sanjaymohanlal_d\Oracle\WALLETS" -modifyCredential SH_orcl SH Test#321

-      Deleting the wallet credential/alias

mkstore -wrl "C:\Users\sanjaymohanlal_d\Oracle\WALLETS" -deleteCredential SH_orcl

-      Deleting the wallet repository

mkstore -wrl "C:\Users\sanjaymohanlal_d\Oracle\WALLETS" –delete

The above commands prompt for the wallet password (not to be confused with password of the schema, which are passed as argument)

 

Screen print demonstrating the mkstore commands run for actual wallet creation

 

References

1. http://docs.oracle.com/cd/B19306_01/network.102/b14268/asowalet.htm

2. Oracle [ID 1240824.1] The Impact of the Sqlnet Settings on Database Security (sqlnet.ora Security Parameters and Wallet Location)