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)
