David Marcos' Blog

Securing passwords on scripts using Oracle Wallet 21 August 2010

One thing I like about being an Oracle DBA is the never-ending possibilities of improving your systems.

I have been working on our backup strategy for some time, the main goal being to have a standard script to be used in all our system.

While working on it I decided to try to secure our backup scripts (and scripts in general) as the password is hard-coded (never a good idea, specially because the user to perform the backup needs to have sysdba privileges).

I decided to use Oracle Wallet. Below are the steps and the test I performed.

The problem:

Having the password hardcoded on any script is not a good idea, specially for RMAN backup scripts as the account being used needs sysdba privileges.

The solution:

The steps I followed to secure our scripts is Oracle Wallet. The steps I performed are:

1.- Create the wallet:

mkstore -wrl -create

I will recommend to store the wallet on a different directory rather than storing it on Oracle home. For example $ORACLE_BASE/wallet:

mkstore -wrl $ORACLE_BASE/wallet -create

The command above will create an Oracle wallet with auto login feature enabled. from now on, only the operating system user who created the wallet will be able to manage it.

You will be prompted to provide a password. This password will have to be provided any time a maintenance operation is performed on the wallet.

2.- Add database connection (including connetion_string, username and password):

mkstore -wrl -createCredential <db_connection_string> <username> <password>

db_connection_string is an entry on your tnsnames.ora or any service name to connect to the desired database.

An example:

mkstore -wrl $ORACLE_BASE/wallet -createCredential prod sys mypassword

mkstore -wrl $ORACLE_BASE/wallet -createCredential catalog rman myrmanpassword

3.- add the following code to your sqlnet.ora:

SQLNET.WALLET_OVERRIDE = TRUE
WALLET_LOCATION =
(SOURCE=
(METHOD = FILE)
(METHOD_DATA = (DIRECTORY=/u01/app/oracle/wallet)
)
)

where:

WALLET_LOCATION: points to the directory where the wallet resides.
SQLNET.WALLET_OVERRIDE: will force all connections as /@db_connection_string to use the information being stored on the wallet to authenticate to databases.

4.- Test the connection:
[oracle@xxxxx]rman
RMAN> connect TARGET /@<db_connection_string>;
connected to target database: xxxx (DBID=yyyyyyyyyyy)
RMAN> connect catalog /@<rman_connection_string>;
connected to recovery catalog database

At this point, we can replace all connections:
<username>/<password>@<db_connection_string>;

by

/@<db_connection_string>;

What options does Wallet offer?
mkstore offers you the following:
1.- list the content being stored on the wallet:
mkstore -wrl -listCredential

2.- Add credentials:
mkstore -wrl -createCredential <db_connection_string> <username> <password>

3.- Modify credentials:
mkstore -wrl -modigyCredential <db_connection_string> <username> <password>

4.- Delete credentials:
mkstore -wrl -deleteCredential <db_connection_string>

Note: if you need to store more than one combination of /
for the same database you may have to create different entries on your tnsnames.ora.

Note.- If you are using the Wallet, you will probably face the following error sooner or later:

startup failed: ORA-00000: normal, successful completion

this can be due to startup force nomount or to duplicate database using RMAN.

The reason is an Oracle Bug (Bug 7258404 currently under development). The workaround is to remove SQLNET.WALLET_OVERRIDE=TRUE from your sqlnet.ora.

We are using one sqlnet.ora when using Wallet, and the default one without wallet configuration, on our script we just configure TNS_ADMIN variable.

As always, comments are welcome.