Using Secure External Password Store - 11gR2

As all DBAs use some sort of shell scripts and have connections to the database which can be a major security issue if these scripts contain the database connection/passowrd details.

Instead of having these passwords in scripts, you can store password credentials for connecting to databases by using a client-side Oracle wallet. An Oracle wallet is a secure software container that stores authentication and signing credentials.
When this feature is configured, application code, batch jobs, and scripts no longer need embedded user names and passwords. This reduces risk because the passwords are no longer exposed, and password management policies are more easily enforced without changing application code whenever user names or passwords change.

How it works?

When clients are configured to use the secure external password store, applications can connect to a database with the following CONNECT statement syntax, without specifying database login credentials:

CONNECT /@db_connect_string
CONNECT /@db_connect_string AS SYSDBA
CONNECT /@db_connect_string AS SYSOPER

In this specification, db_connect_string is a valid connection string to access the intended database, such as the service name, URL, or alias as shown in the earlier examples. Each user account must have its own unique connection string; you cannot create one connection string for multiple users.

Do It Yourself

1- Create a wallet on the client

mkstore -wrl wallet_location -create

wallet_location is the path to the directory where you want to create and store the wallet. It is specified in sqlnet.ora file. Below is the sample sqlnet.ora for this exercise. Please create the required folder also.

sqlnet.ora
SQLNET.AUTHENTICATION_SERVICES = (NTS)

WALLET_LOCATION =(SOURCE =(METHOD = FILE)(METHOD_DATA =(DIRECTORY=C:\app\ibukhary\product\11.2.0\dbhome_1\wallets)))

SQLNET.WALLET_OVERRIDE = TRUE

SSL_CLIENT_AUTHENTICATION = FALSE

C:\app\ibukhary\product\11.2.0\dbhome_1\BIN>mkstore -wrl C:\app\ibukhary\product\11.2.0\dbhome_1\wallets -create

Oracle Secret Store Tool : Version 11.2.0.1.0 - Production
Copyright (c) 2004, 2009, Oracle and/or its affiliates. All rights reserved.

Enter password:

Enter password again:


C:\app\ibukhary\product\11.2.0\dbhome_1\BIN>

OR

C:\app\ibukhary\product\11.2.0\dbhome_1\BIN>orapki wallet create -wallet "C:\app\ibukhary\product\11.2.0\dbhome_1\wallets" -pwd "welcome1" -auto_login_local
Oracle PKI Tool : Version 11.2.0.1.0 - Production
Copyright (c) 2004, 2009, Oracle and/or its affiliates. All rights reserved.

2- Create database connection credentials in the wallet

mkstore -wrl wallet_location -createCredential db_connect_string username

db_connect_string is the TNS alias you use to specify the database in the tnsnames.ora file or any service name you use to identify the database on an Oracle network. Below is the tns entry for this exercise.

tnsnames.ora
SCOTTDBT11=
  (DESCRIPTION=
    (ADDRESS=
      (PROTOCOL=TCP)
      (HOST=192.169.126.11)
      (PORT=1621)
    )
    (CONNECT_DATA=
      (SERVICE_NAME=DBT)
    )
  )



C:\app\ibukhary\product\11.2.0\dbhome_1\BIN>mkstore -wrl C:\app\ibukhary\product\11.2.0\dbhome_1\wallets -creteCredential SCOTTDBT11 scott
Oracle Secret Store Tool : Version 11.2.0.1.0 - Production
Copyright (c) 2004, 2009, 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

3- View contents of a client wallet external password store,

C:\app\ibukhary\product\11.2.0\dbhome_1\BIN>mkstore -wrl C:\app\ibukhary\product\11.2.0\dbhome_1\wallets -listCredential
Oracle Secret Store Tool : Version 11.2.0.1.0 - Production
Copyright (c) 2004, 2009, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:

List credential (index: connect_string username)
1: SCOTTDBT11 scott

4- Test external password store

C:\app\ibukhary\product\11.2.0\dbhome_1\BIN>sqlplus /@SCOTTDBT11

SQL*Plus: Release 11.2.0.1.0 Production on Wed May 25 14:46:25 2016

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

SQL>

5- Manage external password store.

C:\app\ibukhary\product\11.2.0\dbhome_1\BIN>mkstore -wrl C:\app\ibukhary\product\11.2.0\dbhome_1\wallets -modifyCredential SCOTTDBT11 scott

Oracle Secret Store Tool : Version 11.2.0.1.0 - Production

Copyright (c) 2004, 2009, 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:

Modify credential

Modify 1

C:\app\ibukhary\product\11.2.0\dbhome_1\BIN>mkstore -wrl  C:\app\ibukhary\product\11.2.0\dbhome_1\wallets -deleteCredential SCOTTDBT11

Oracle Secret Store Tool : Version 11.2.0.1.0 - Production

Copyright (c) 2004, 2009, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:

Delete credential

Delete 1

Things to remember

a) Any user that has access to the wallet can use any password stored in the wallet. Therefore it is recommended that you create one wallet per user, rather than using a common wallet.

b) The security of the wallet is only file based. Thus the security of the wallet is only marginally better than a hard-coded password within a shell script as both methods depend on OS file and directory permissions for their security. 

c) 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. Once the wallet is created, it can be modified using the "mkstore" command.