In our more and more connected world, security is a big concern. Storing password is a very important topic because if a machine is compromised, we can assume that any password stored in clear text are also compromised. I had the question from a customer how can we encrypt the password in our application and monitoring scripts? In this blog we will discuss one solution: using the Oracle client wallet

We find passwords for Oracle users in configuration files more often than we want. And in general, the configuration itself is a simple clear text file whitout encryption and we rely on file system permissions to deny read access to the file for other users.

The thing is that anyone having access to the user who owns the file can just open the file and grab the password to connect directly to the database from another machine. Our objective on this blog is to remove the clear text password from the script or from a configuration file.

This features doesn’t require an extra-cost option. You can use it without restriction on any Oracle editions (see note Using The Secure External Password Store – Doc ID 340559.1). This technique works for SQL Plus and JDBC connections so we should definitively use it more.

What is a wallet?

From the Oracle documentation, a wallet is a secure container that stores authentication and signing credentials. It means that a wallet can store user/password (authentication credentials) or certificates (signing and client authentication).
Physically, a wallet is a directory on a file system containing two files:

oracle@vmtestoel6:/home/oracle/ [DB121] ll
total 16
drwxr-xr-x 3 oracle oinstall 4096  6 janv.  2014 oradiag_oracle
-rw-r--r-- 1 oracle oinstall  248 23 janv. 09:11 sqlnet.ora
-rw-r--r-- 1 oracle oinstall 3007 23 janv. 09:08 tnsnames.ora
drwx------ 2 oracle oinstall 4096 23 janv. 09:05  14:15 wallets
oracle@vmtestoel6:/home/oracle/ [DB121] ll wallets/
total 8
-rw------- 1 oracle oinstall 3949 23 janv. 09:05 cwallet.sso
-rw------- 1 oracle oinstall 3872 23 janv. 09:05 ewallet.p12

Both files are only read/write for the current user and are not readable. You can try to use cat or strings command in Linux but you won’t be able to get a clear text password.

How to create a wallet?

Now we know what is a wallet, the main question is how can we create it?
It’s very easy, Oracle provides the command mkstore to manipulate wallets. Examples are done using a Linux environment but it also works in Windows. You don’t need to create the directory before running the command, you just have to choose where the wallet will be stored.

oracle@vmtestoel6:/home/oracle/ [DB121] mkstore -wrl /home/oracle/wallets -create
Oracle Secret Store Tool : Version 12.1.0.1
Copyright (c) 2004, 2012, Oracle and/or its affiliates. All rights reserved.

Enter password:       

PKI-01002: Invalid password:Passwords must have a minimum length of eight characters and contain alphabetic characters combined with numbers or special characters. 
Enter password:               

Enter password again:               

oracle@vmtestoel6:/home/oracle/ [DB121]

In that example, I stored the wallet in /home/oracle. The command asks for a password twice and the good thing is that the tool will force a “good enough” password with 8 characters minimum and at least one numeric or special character. So the famous “password” is not possible here.

Storing credentials in a wallet and use them

The same command allows us to store database credentials in the newly created wallet. When creating a credential we need to provide the TNS entry (db_alias in the documentation) and the username. Then the command asks for the user password twice and the wallet password.

oracle@vmtestoel6:/home/oracle/ [DB121] mkstore -wrl /home/oracle/wallets/ -createCredential DB121 NIJ
Oracle Secret Store Tool : Version 12.1.0.1
Copyright (c) 2004, 2012, Oracle and/or its affiliates. All rights reserved.

Your secret/Password is missing in the command line 
Enter your secret/Password:       

Re-enter your secret/Password:       

Enter wallet password:           

Create credential oracle.security.client.connect_string1

Caution, the command doesn’t validate the parameters. So neither the TNS entry neither the couple user/password is checked. If you made a mistake here, you will probably receive an error like “ORA-01017: invalid username/password; logon denied”

In order to use the wallet, we need to adapt the SQL*Net configuration. At this stage, the system doesn’t know that there is a wallet and where it’s located. We have to edit the sqlnet.ora file (located in our TNS_ADMIN directory) to add the following:

WALLET_LOCATION =
   (SOURCE =
     (METHOD = FILE)
     (METHOD_DATA =
       (DIRECTORY = /home/oracle/wallets)
     )
    )

SQLNET.WALLET_OVERRIDE = TRUE

We can use several wallets on the same system as we have the ability to have several sqlnet.ora files. The script has to correctly set the TNS_ADMIN variable to get the right wallet used. To use the credential from the wallet, we just need them to start our client using the syntax /@TNS_ENTRY

oracle@vmtestoel6:/home/oracle/ [DB121] tnsping DB121
TNS Ping Utility for Linux: Version 12.1.0.1.0 - Production on 17-MAR-2015 15:08:44
Copyright (c) 1997, 2013, Oracle.  All rights reserved.

Used parameter files: /home/oracle/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = vmtestoel6)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = DB121_SITE1)))
OK (0 msec)
oracle@vmtestoel6:/home/oracle/ [DB121] sqlplus /@DB121

SQL*Plus: Release 12.1.0.1.0 Production on Tue Mar 17 15:10:17 2015

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Last Successful login time: Tue Mar 17 2015 15:08:08 +01:00

Connected to:
Oracle Database 12c Release 12.1.0.1.0 - 64bit Production

SQL> show user
USER is "NIJ"
SQL> exit
Disconnected from Oracle Database 12c Release 12.1.0.1.0 - 64bit Production

You have probably noticed that we didn’t had to specify the password AND the username. The key is the TNS entry itself, so it’s a good idea to create dedicated TNS entry in tnsnames.ora if you have several distinct users that need to have the password secured in the wallet.

There are other useful commands when using a wallet to manage the credentials:

Change password/username: 
mkstore -wrl wallet_location -modifyCredential dbase_alias username

Delete a credential: 
mkstore -wrl wallet_location -deleteCredential db_alias

List the credentials: 
mkstore -wrl wallet_location -listCredential

Conclusion

Using a wallet doesn’t prevent people from accessing to the database. Anyone having access to the wallet can use the stored credentials without a password. However the passwords are stored in an encrypted way and we need the wallet password to change or delete credentials. So we now have control of password knowledge, if we don’t share the password we know that the team has to start the script only from the client where the wallet is. We need to stricly control who can access the wallet but it was the same case for the uncrypted way!

I like using this solution when we need to run some monitoring scripts. A dedicated TNS entry is created for a dedicated read user in the database and the password is stored in a secure way.