oracle external password store and jdbc driver – for the new oracle dba

you are a new oracle dba and you would like to know how to use an oracle external password store with a jdbc driver.

In this Document
Goal
Solution
More Information

Applies to:

Oracle Server – Enterprise Edition – Version: 10.2.0.1
JDBC – Version: 10.2.0.0
Information in this document applies to any platform.

Goal

For large-scale deployments where applications use password credentials to connect to databases, it is possible to store such credentials in a client-side Oracle wallet. An Oracle wallet is a secure software container that is used to store authentication and signing credentials.

This article shows how this can be done from a JDBC program and the steps required to configure the wallet and the Database Server to achieve this.

Solution

Note: In this example the Wallet is created in the directory “D:\oracle\product\10.2.0\db_1\NETWORK\ADMIN” and it’s required that $ORACLE_HOME/bin be in the PATH environment variable in order for the command mkstore to be used.

1. Create a wallet on the client by using the following syntax at the command line.

mkstore -wrl D:\oracle\product\10.2.0\db_1\NETWORK\ADMIN -create
2. Create database connection credentials in the wallet, which basically determine which SQLNet alias will be used and what the username/password for that alias will be used by the client.

mkstore -wrl D:\oracle\product\10.2.0\db_1\NETWORK\ADMIN -createCredential TEST_SECURE scott tiger

3. Add the following to the sqlnet.ora file as follows.

SQLNET.WALLET_OVERRIDE = TRUE
WALLET_LOCATION =
 (SOURCE=
   (METHOD = FILE)
     (METHOD_DATA = (DIRECTORY=D:\oracle\product\10.2.0\db_1\NETWORK\ADMIN)
   )
 )
4. Add the following entry to the tnsnames.ora for the NET alias TEST_SECURE used in step 2, this basically is the entry point for the Database.

TEST_SECURE =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = papicell-au)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = apples)
    )
  )
5. Connect as follows without suppling a username/password and that user will connect
as SCOTT automatically.

 D:\>sqlplus /@TEST_SECURE
 
 SQL*Plus: Release 10.2.0.1.0 - Production on Mon Dec 18 10:23:58 2006
 
 Copyright (c) 1982, 2005, Oracle.  All rights reserved.
 
 
 Connected to:
 Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
 With the Partitioning, OLAP and Data Mining options
 
 SCOTT@TEST_SECURE>
6. With this working in SQLPlus we can simply create a JDBC program to test this with as follows.

import java.sql.*;
import oracle.jdbc.OracleDriver;

public class TestIt
{
 
  public TestIt()
  {
  }
 
  public static Connection getConnection() throws SQLException
  {

    DriverManager.registerDriver(new OracleDriver());  

    Connection conn =
      DriverManager.getConnection ("jdbc:oracle:oci:/@test_secure");
     
    conn.setAutoCommit(false);
    return conn;
  }
 
  public void run () throws SQLException
  {
    Connection conn = getConnection();  

    // Create a Statement
    Statement stmt = conn.createStatement ();

    // Select the ENAME column from the EMP table
    ResultSet rset = stmt.executeQuery ("select ENAME from EMP");

    // Iterate through the result and print the employee names
    while (rset.next ())
      System.out.println (rset.getString (1));

    // Close the RseultSet
    rset.close();

    // Close the Statement
    stmt.close();

    // Close the connection
    conn.close();  
     
  }
 
  public static void main(String[] args)
  {
    try
    {
      TestIt test = new TestIt();
      test.run();
    }
    catch (SQLException ex)
    {
      ex.printStackTrace();
    }
   
  }
 
}

7. Compile and run the JDBC program as follows ensuring that the JDBC driver and the current directory is part of the classpath. The following example is for windows, which adds the JDBC driver to the classpath to enable it to compile and run.

>javac -cp D:\jdev\jdcbdrivers\102\10202\ojdbc14.jar TestIt.java

> java -cp D:\jdev\jdcbdrivers\102\10202\ojdbc14.jar;. TestIt

8 Output would be as follows, against the SCOTT schema.

SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER

More Information

Oracle® Database JDBC Developer’s Guide and Reference
10g Release 2 (10.2)
Part Number B14355-02
http://download-west.oracle.com/docs/cd/B19306_01/java.102/b14355/clntsec.htm#CIHBIEHA
Secure External Password Store

Oracle® Database Security Guide
10g Release 2 (10.2)
Part Number B14266-01
http://download-west.oracle.com/docs/cd/B19306_01/network.102/b14266/cnctslsh.htm#i1006413
9 Secure External Password Store

<<End_of_Article>> FOLDER:ST.Middleware.JDBC TOPIC:Security DOCUMENT-ID:403744.1 ALIAS: SOURCE:AWIZ 6042966.993 DOCUMENT-TYPE:HOWTO ZCXTECH TITLE:How to Use an External Password Store With The JDBC Driver IMPACT:LOW SKILL-LEVEL:NOVICE STATUS:MODERATED DISTRIBUTION:EXTERNAL ZCXPUBLIC ZCXCURRENT AUTHOR:PAPICELL.AU AUTHOR:TGAZZARA.US KEYWORD:SECURE KEYWORD:WALLET_OVERRIDE KEYWORD:AUTHENTICATION PRODID-5 COMPONENT:RDBMS.SECURITY MINVER:10.2.0.1 MAXVER: PRODID-972 COMPONENT:THIN MINVER:10.2.0.0.0 MAXVER: PORTID-0 FDRSEG-607 FDRSEG-557 FDRSEG-465

Back to top

Rate this document