DBA Tips Archive for Oracle
No Title[an error occurred while processing this directive]
by Michael New, MichaelNew@earthlink.net, Gradation LLC
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.
Consider the following example where a shell script includes a call to SQL*Plus using traditional username/password authentication:
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:
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.
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.
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.
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.
In addition to the wallet location, specify the following:
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.
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.
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 a new Oracle wallet in the previously specified location by executing the mkstore command with the -create option.
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.
Verify the wallet was created. The same wallet file names will be created whether they were created using mkstore or the orapki command.
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.
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:
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.
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.
Test the new alias.
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.
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.
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.
Using SQL*Plus, connect to the target database using the "/@tnsalias" syntax.
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:
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:
Use the mkstore command with the -listCredential option to list the credentials present in the wallet.
You can also use the mkstore command to modify or delete password credentials for existing wallet entries.
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.
Alter the user HR to enable access through the new database account.
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.
Add the credentials for the proxy user to your wallet.
The batch program can now authenticate as HRPROC using the secure external password store and is allowed to proxy through the HR user:
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:
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 server environment. Jeff's other interests include mathematical encryption theory, tutoring advanced mathematics, 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 20 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 and Mathematics.
Copyright (c) 1998-2017 Jeffrey M. Hunter. All rights reserved.
All articles, scripts and material located at the Internet address of http://www.idevelopment.info is the copyright of Jeffrey M. Hunter and is protected under copyright laws of the United States. This document may not be hosted on any other site without my express, prior, written permission. Application to host any of the material elsewhere can be made by contacting me at firstname.lastname@example.org.
I have made every effort and taken great care in making sure that the material included on my web site is technically accurate, but I disclaim any and all responsibility for any loss, damage or destruction of data or any other property which may arise from relying on it. I will in no case be liable for any monetary damages arising from such loss, damage or destruction.
Last modified on
Tuesday, 04-Sep-2012 00:24:00 EDT
Page Count: 7047