DBA Tips Archive for Oracle

Secure Database Passwords in an Oracle Wallet

by Jeff Hunter, Sr. Database Administrator

Contents

• Introduction
• Create Oracle Wallet
• Store Database Credentials
• Test Database Credentials
• Manage Database Credentials in Wallet
• Command-Line Proxy Authentication
• About the Author

Introduction

The practice of writing scripts to automate routine database tasks is commonplace. This can include database backups, ETL jobs, or any type of batch processing that requires database access without user interaction. These scripts are typically held on the filesystem which depend on OS file permissions to protect the security credentials needed to log in to the database. The challenge has been how to adequately hide or obfuscate the username and password and not expose them in clear text and causing a potential security breach. A widely used practice has been to rely on OS Authentication, but starting with Oracle Database 10g Release 2, a more simplified and scalable solution would be to use a Secure External Password Store. This approach provides a secure method to store database credentials and reduces risk to security policies because the usernames and passwords no longer need to be exposed in clear text. This also avoids the need for the DBA or other security administrators to share passwords with developers and other non administrator users needing access to the database.

The secure external password store uses a client-side Oracle Wallet to store one or more user name/password combinations. The wallet is encrypted using the 3DES algorithm so the contents of the wallet are not readable. If the wallet is ever compromised, the database password for the user can be changed and a new wallet can be generated thus rendering the previous wallet unusable.

The best way to envision the password store is as a table with three columns: TNSALIAS, USERNAME, and PASSWORD. The TNSALIAS is basically the primary key that maps to a single user name/password combination. In most deployment scenarios, this means creating a new TNSALIAS entry for each stored credential.

TNSALIAS (PK) USERNAME PASSWORD --------------- ---------- ----------- TESTDB1 SCOTT TIGER ERPDB_APPS APPS APPL3PWD ERPDB_GL GL GL3XPWD ...

Consider the following example where a shell script includes a call to SQL*Plus using traditional username/password authentication:

sqlplus scott/tiger@tnsalias

Lack of adequate file system permissions in place for the script exposes the database credentials in clear text and creates a major security breach. With a secure external password store in place, the above SQL*Plus call could be replaced with:

sqlplus /@tnsalias

In the above example, the TNS connect string, along with the username and password are extracted from the password store (a client-side Oracle wallet) based on tnsalias. It should be noted that tnsalias in the above sqlplus call should not be thought of as an actual entry in the tnsnames.ora file, but rather as a lookup key in the password store. That key value in the password store should, however, be a resolvable entry in the tnsnames.ora file. Although the tnsalias value used for the database login (/@tnsalias) and the entry in the password store must be the same, it is important to distinguish between the two.

Create Oracle Wallet

A client-side Oracle Wallet will be created in this section which will be used for the secure external password store. Although the examples provided in this guide will be performed on a Linux client machine, the same procedures could be used on a Microsoft Windows client machine, another database server, or even from the database server hosting the target database.

About Oracle Wallet

An Oracle Wallet is nothing more than a protected logical container (a single file named ewallet.p12) that is used for the secure external password store. Multiple wallets may be created on a machine; however, each wallet should be contained in its own directory. Using a wallet for the secure external password store is not the only use of an Oracle wallet. The wallet can also be used to store encrypted keys needed by the Oracle database in order to access SSL sites as well as many of the more advanced security options in Oracle like Transparent Data Encryption (TDE) or PKI Credentials. These advanced options are part of Oracle's Advanced Security Option (ASO) and are only available when using Oracle Enterprise Edition.

Note that creating an Oracle wallet for the secure external password store (and importing keys to access SSL sites) can be done using Oracle Standard Edition. It is only when using the advanced features like TDE or PKI credentials that require the Advanced Security Option and Enterprise Edition.

Oracle Wallet Location

The first step is to decide on the location of the Oracle wallet. In this example, the wallet will be created in the ORACLE_HOME/network/admin directory on a Linux application server with the Oracle Client software installed. Another popular location for the wallet is ORACLE_HOME/wallets; however, the wallet can be located anywhere on the file system that is accessible by Oracle.

Add the following entry to the sqlnet.ora on your client machine so that Oracle Net knows where to look for the wallet. The location directory for the wallet must be an absolute path, end with right parentheses, and be an existing directory. Make certain that there are no spaces or invisible characters at the end of the directory path as this may cause Oracle to not recognize the directory.

WALLET_LOCATION = (SOURCE = (METHOD = FILE) (METHOD_DATA = (DIRECTORY = /u01/app/oracle/product/11.2.0/dbhome_1/network/admin) ) ) SQLNET.WALLET_OVERRIDE = TRUE SSL_CLIENT_AUTHENTICATION = FALSE ADR_BASE = /u01/app/oracle NAMES.DIRECTORY_PATH= (TNSNAMES) NAMES.DEFAULT_DOMAIN = IDEVELOPMENT.INFO

In addition to the wallet location, specify the following:

1. Enter the SQLNET.WALLET_OVERRIDE parameter and set it to TRUE in order to override the current authentication methods and use the secure external password store feature.

   For example, setting SQLNET.WALLET_OVERRIDE = TRUE causes all "CONNECT /@db_connect_string" statements to use the information in the wallet at the specified location to authenticate to databases.

   The default value for SQLNET.WALLET_OVERRIDE is FALSE, allowing standard use of authentication credentials like Windows native authentication or Secure Sockets Layer (SSL) and disabling the secure external password store feature.

   Note: If an application uses SSL for encryption, then the sqlnet.ora parameter, SQLNET.AUTHENTICATION_SERVICES, specifies SSL and an SSL wallet is created. If this application wants to use secret store credentials to authenticate to databases (instead of the SSL certificate), then those credentials must be stored in the SSL wallet. After SSL authentication, if SQLNET.WALLET_OVERRIDE = TRUE, then the user names and passwords from the wallet are used to authenticate to databases. If SQLNET.WALLET_OVERRIDE = FALSE, then the SSL certificate is used.

2. The SSL_CLIENT_AUTHENTICATION parameter is used to specify whether or not a client is authenticated using the Secure Sockets Layer (SSL). The default value is TRUE.

3. Although not required for a secure external password store, I specify a default domain in the sqlnet.ora for all TNS entries (NAMES.DEFAULT_DOMAIN = IDEVELOPMENT.INFO).

Create Oracle Wallet

Create a new Oracle wallet in the previously specified location by executing the mkstore command with the -create option.

$ mkstore -wrl "/u01/app/oracle/product/11.2.0/dbhome_1/network/admin" -create Oracle Secret Store Tool : Version 11.2.0.3.0 - Production Copyright (c) 2004, 2011, Oracle and/or its affiliates. All rights reserved. Enter password: ********** Enter password again: **********

Although the wallet created above is password protected, it is defined with the "Auto Login" property enabled so that any connection attempt by the user who created the wallet is not required to supply the password.

Something to note about an Oracle wallet is that it can be copied to a different machine which imposes a serious risk to security. A user could create an account on their workstation with the same username as the wallet owner and obtain access to any of the database credentials stored in the wallet without a password. In Oracle Database 11g Release 2, you can prevent the auto login functionality from working if it is copied to another machine by creating a (local) wallet using the orapki command, instead of the mkstore command.

$ orapki wallet create -wallet "/u01/app/oracle/product/11.2.0/dbhome_1/network/admin" -pwd "myPassword" -auto_login_local Oracle PKI Tool : Version 11.2.0.3.0 - Production Copyright (c) 2004, 2011, Oracle and/or its affiliates. All rights reserved.

Verify the wallet was created. The same wallet file names will be created whether they were created using mkstore or the orapki command.

$ ls -l /u01/app/oracle/product/11.2.0/dbhome_1/network/admin total 48 -rw------- 1 oracle oinstall 3973 Jul 20 22:55 cwallet.sso -rw------- 1 oracle oinstall 3896 Jul 20 22:55 ewallet.p12 -rw-r--r-- 1 oracle oinstall 392 Feb 10 20:31 listener.ora drwxr-xr-x 2 oracle oinstall 4096 Feb 10 20:23 samples/ -rw-r--r-- 1 oracle oinstall 205 May 11 2011 shrept.lst -rw-r--r-- 1 oracle oinstall 20760 Jul 20 18:02 sqlnet.ora -rw-r--r-- 1 oracle oinstall 3337 Jul 20 22:43 tnsnames.ora

Since the wallet was created with the auto login functionality, the wallet will be exported into a file named cwallet.sso. Also, since the wallet is protected by a password, two files will be created; namely ewallet.p12 and cwallet.sso.

Oracle RAC

If a wallet is being created on the nodes in an Oracle RAC configuration, the wallet should be configured on all nodes in the sqlnet.ora file for the Database home and not the Grid Infrastructure home. Although it is possible to specify the location for the wallet in the sqlnet.ora for Grid home and even verify that the database credentials work from Grid home, the cluster database will fail to start:

$ srvctl start database -d racdb PRCR-1079 : Failed to start resource ora.racdb.db ORA-12578: TNS:wallet open failed CRS-5017: The resource action "ora.racdb.db start" encountered the following error: ORA-12578: TNS:wallet open failed . For details refer to "(:CLSN00107:)" in "/u01/app/11.2.0/grid/log/racnode2/agent/crsd/oraagent_oracle/oraagent_oracle.log". CRS-2674: Start of 'ora.racdb.db' on 'racnode2' failed ORA-12578: TNS:wallet open failed CRS-5017: The resource action "ora.racdb.db start" encountered the following error: ORA-12578: TNS:wallet open failed . For details refer to "(:CLSN00107:)" in "/u01/app/11.2.0/grid/log/racnode1/agent/crsd/oraagent_oracle/oraagent_oracle.log". CRS-2674: Start of 'ora.racdb.db' on 'racnode1' failed ORA-12578: TNS:wallet open failed CRS-2632: There are no more servers to try to place resource 'ora.racdb.db' on that would satisfy its placement policy ORA-12578: TNS:wallet open failed

The test above was from on Oracle RAC configured using Job Role Separation and that may have been why it failed. In any case, I see no reason why a secure external password store would be required from Grid home. In order for the cluster database to start, the wallet location (and other wallet parameters) will need to be removed from the sqlnet.ora file in Grid Infrastructure home while allowed to remain in the Database home.

Store Database Credentials

TNS Names Entry

Before storing database login credentials for a user in the wallet, create or modify an entry in your tnsnames.ora for the target database. For example, I want to create login credentials for the current reporting tools user (REPORT_USER) connecting to the target database TESTDB1. The database connect string (TNS alias) will be named REPORTING_TOOL.

REPORTING_TOOL.IDEVELOPMENT.INFO = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = testnode1.idevelopment.info)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = testdb1.idevelopment.info) ) )

Test the new alias.

$ tnsping reporting_tool TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 20-JUL-2012 22:46:17 Copyright (c) 1997, 2011, Oracle. All rights reserved. Used parameter files: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = testnode1.idevelopment.info)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = testdb1.idevelopment.info))) OK (0 msec)

Add Database Credentials to Wallet

After creating the Oracle wallet (using either mkstore or orapki) and verifying the database connect string, execute the mkstore command with the -createCredential option to add your database credentials.

$ mkstore -wrl "/u01/app/oracle/product/11.2.0/dbhome_1/network/admin" -createCredential reporting_tool report_user report_user_pwd Oracle Secret Store Tool : Version 11.2.0.3.0 - Production Copyright (c) 2004, 2011, Oracle and/or its affiliates. All rights reserved. Enter wallet password: ********** Create credential oracle.security.client.connect_string1

The TNS alias, in this case "reporting_tool", will be the identifier used in the "/@tnsalias" syntax, and must have a matching entry in the tnsnames.ora file.

Test Database Credentials

Now that the wallet has been created and the password credentials are stored in the wallet, use SQL*Plus, Toad, Java, or any other client application to test the secure external password store.

SQL*Plus

Using SQL*Plus, connect to the target database using the "/@tnsalias" syntax.

$ sqlplus /@reporting_tool SQL*Plus: Release 11.2.0.3.0 Production on Fri Jul 20 23:47:59 2012 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Oracle Label Security, OLAP, Data Mining and Real Application Testing options SQL> show user USER is "REPORT_USER" SQL>

The "/@tnsalias" syntax uses the wallet to lookup the username and password for the matching tnsalias and then passes those to the database for authentication.

If you want to connect to the same database, but as a different database user, make another TNS alias in your tnsnames.ora file and add a new entry to the wallet. For example:

TESTDB1_SCOTT.IDEVELOPMENT.INFO = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = testnode1.idevelopment.info)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = testdb1.idevelopment.info) ) )

$ cd /u01/app/oracle/product/11.2.0/dbhome_1/network/admin $ mkstore -wrl . -createCredential testdb1_scott scott tiger Oracle Secret Store Tool : Version 11.2.0.3.0 - Production Copyright (c) 2004, 2011, Oracle and/or its affiliates. All rights reserved. Enter wallet password: ********** Create credential oracle.security.client.connect_string1

$ sqlplus /@testdb1_scott SQL*Plus: Release 11.2.0.3.0 Production on Sat Jul 21 00:50:56 2012 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Oracle Label Security, OLAP, Data Mining and Real Application Testing options SQL> show user USER is "SCOTT" SQL>

Java Application

When using the secure external password store in a Java application, you must use the OCI (thick) JDBC driver which also means you need to install the Oracle client software. Use a URL similar to the following when connecting to the database:

Connection conn = DriverManager.getConnection ("jdbc:oracle:oci:/@testdb1_scott");

Manage Database Credentials in Wallet

Use the mkstore command with the -listCredential option to list the credentials present in the wallet.

$ mkstore -wrl "/u01/app/oracle/product/11.2.0/dbhome_1/network/admin" -listCredential Oracle Secret Store Tool : Version 11.2.0.3.0 - Production Copyright (c) 2004, 2011, Oracle and/or its affiliates. All rights reserved. Enter wallet password: ********** List credential (index: connect_string username) 2: testdb1_scott scott 1: reporting_tool report_user

You can also use the mkstore command to modify or delete password credentials for existing wallet entries.

mkstore -wrl <wallet_location> -modifyCredential <tnsalias> <username> <password> mkstore -wrl <wallet_location> -deleteCredential <tnsalias>

Command-Line Proxy Authentication

Another example for the secure external password store is the following scenario: A routine batch program running on a back-end server needs nightly access to the HR application schema, but new security policies have restricted direct access to the HR application schema. According to the new policy, the HR password will not be distributed and applications will not be allowed to log in directly as the HR application schema. How can the program authenticate to the database using credentials other than the application owner but still have the same level of access?

Solution: Create a separate database account for the program that uses command-line proxy authentication with the secure external password store. Using this method, applications can use traditional proxy authentication to authenticate as an end user (HRPROC in this example) and the proxy to the HR user.

Start by creating the database proxy user and granting CREATE SESSION privileges.

SQL> grant create session to hrproc identified by hrproc_password; Grant succeeded.

Alter the user HR to enable access through the new database account.

SQL> alter user hr grant connect through hrproc; User altered.

Configure the wallet and the tnsnames.ora file starting with the TNS alias entry. Add an entry to the tnsnames.ora file for the proxy user.

HRPROC.IDEVELOPMENT.INFO = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = testnode1.idevelopment.info)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = testdb1.idevelopment.info) ) )

Add the credentials for the proxy user to your wallet.

$ cd /u01/app/oracle/product/11.2.0/dbhome_1/network/admin $ mkstore -wrl . -createCredential hrproc hrproc hrproc_password Oracle Secret Store Tool : Version 11.2.0.3.0 - Production Copyright (c) 2004, 2011, Oracle and/or its affiliates. All rights reserved. Enter wallet password: xxxxxxxx Create credential oracle.security.client.connect_string3

The batch program can now authenticate as HRPROC using the secure external password store and is allowed to proxy through the HR user:

$ sqlplus [HR]/@hrproc SQL*Plus: Release 11.2.0.3.0 Production on Tue Jul 24 14:57:30 2012 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Oracle Label Security, OLAP, Data Mining and Real Application Testing options SQL> show user USER is "HR" SQL>

Although the secure external password store was used in the previous example, it is still possible to use the traditional username/password method with the proxy authentication functionality. For example:

$ sqlplus hrproc[HR]/hrproc_password@hrproc SQL*Plus: Release 11.2.0.3.0 Production on Tue Jul 24 15:26:33 2012 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Oracle Label Security, OLAP, Data Mining and Real Application Testing options SQL> show user USER is "HR" SQL>

About the Author

Jeffrey Hunter is an Oracle Certified Professional, Java Development Certified Professional, Author, and an Oracle ACE. Jeff currently works as a Senior Database Administrator for The DBA Zone, Inc. located in Pittsburgh, Pennsylvania. His work includes advanced performance tuning, Java and PL/SQL programming, developing high availability solutions, capacity planning, database security, and physical / logical database design in a UNIX, Linux, and Windows server environment. Jeff's other interests include mathematical encryption theory, programming language processors (compilers and interpreters) in Java and C, LDAP, writing web-based database administration tools, and of course Linux. He has been a Sr. Database Administrator and Software Engineer for over 18 years and maintains his own website site at: http://www.iDevelopment.info. Jeff graduated from Stanislaus State University in Turlock, California, with a Bachelor's degree in Computer Science.