Oracle Wallet, JDBC & JPA configuration

I was trying to connect to a database using oracle wallet as it was one of the secured way of connecting to database.

I will divide this blog into 3 parts

1. Oracle Wallet Configuration
2. Connecting Database using a simple JDBC thin Client
3. Connecting Database using a JPA implementation.(I have used Eclipselink as JPA2.0 implementation)

Oracle Wallet Configuration

The following steps need to be performed on the server where oracle server installation is available
Commands should run irrespective of operating system if not please change accordingly

Create Wallet Directory:

mkdir -p wallet

Create Empty Wallet

To create an empty wallet use the following command
mkstore -wrl ./wallet -create
will prompt for a password (please keep it safe for future purposes)

Add User to Wallet

Following command will add the specific user to the wallet username password which you want to add to the store.

mkstore -wrl ./wallet -createCredential <dbase_alias> <username> <password>


Note: Assumption is user should exist before it can be added to the wallet.

Listing Wallet Credentials

mkstore -wrl ./wallet -listCredential

Some useful Wallet commands 

Modifying credentials stored in the wallet:
mkstore -wrl <wallet_location> -modifyCredential <dbase_alias> <username> <password>

Deleting credentials stored in the wallet:
mkstore -wrl <wallet_location> -deleteCredential <db_alias>

Deleting the whole wallet:
rm -rf <wallet_location>

Prerequisites

Once the wallet is created you can see the following files created in the wallet directory
cwallet.sso
cwallet.sso.lck
ewallet.p12
ewallet.p12.lck

While connecting to the database from a client you need to have these files at the client place.
copy and keep the files in a place where it can be accessed by the application

Libraries

Following jar files required for the client to connect to the database typically they are available as part of the Oracle Distribution (available in jlib directory)

oraclepki.jar

osdt_cert.jar

osdt_core.jar

Above three required and enough for connect with a thin client. Another library needed if you want to use any JPA implementation

ojdbc14.jar

Note : remove the dependency of ojdbc6/7 jar files if you already have as they cause connection refused exception

TNSNames.ora

You need to create a tnsnames.ora and place it with other wallet files listed above

typical tnsnames.ora should look something like 

orcl =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

(CONNECT_DATA =

(SID = orcl)

)

) 

Command line arguments 

following two command line arguments required by the java application to locate the tnsnames and wallet files.

-Doracle.net.tns_admin=<TNSNAME.ORA_Directory>

-Doracle.net.wallet_location=<Wallet_Directory>

Now you all set and ready to make a db connection from a remote client 

Connecting DB with thin Client

Typical jdbc thin driver connection string will look like the following 

jdbc:oracle:thin@<HOST>:<PORT>:<DATABASE_NAME>

Using password in the url as below

jdbc:oracle:thin:username/password@localhost:1521:xe

The main aim of wallet is to avoid username password to specify in the connection string.

so now the new JDBC thin driver url will be some think like

jdbc:oracle:thin:/@<dbase_alias> (look for the dbase_alias name while adding the credential store above)

Nothing else required to connect to the database.

Code should look some think similar below

Connection conn = null;              
Properties props = new Properties();
Class.forName("oracle.jdbc.OracleDriver");
//STEP 3: Open a connection
System.out.println("Connecting to database..." + url);
conn = DriverManager.getConnection(url, props);

Configuring Wallet for a JPA

(I have used Eclipselink here, procedure should work with any JPA)

Assuming you have a persistence.xml which looks similar to below

persistence.xml

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="1.0" xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/persistence persistence_1_0.xsd">
  <persistence-unit name="<YOUR_PERSISTENCENAME>" transaction-type="RESOURCE_LOCAL">
    <provider>org.eclipse.persistence.jpa.PersistenceProvider</provider>
    <class><your entity classes here></class>
  </persistence-unit>
</persistence>

Datasource Creation

As we are using custom way of connecting database i am using OracleDataSource  to connect to the database this should work both in standalone as well as in application server.

I have used a method here to return a OracleDataSourc which is implementation of Java.sql.DataSource

public DataSource getDataSource(String walletId)throws Exception{

        String url = "jdbc:oracle:thin:/@"+walletId ;

        OracleDataSource source = new OracleDataSource();

        source.setDriverType("oracle.jdbc.OracleDriver");

        source.setURL( url );

        source.setDataSourceName("jdbc/myOracleDatasource");

        return source;

    }

EnitytManager Creation

Following steps required to create the entity manager using the above details

Map properties = new HashMap();

      properties.put(PersistenceUnitProperties.NON_JTA_DATASOURCE,getDataSource());

      emf = Persistence.createEntityManagerFactory("<YOUR_PERSISTENCENAME_FROM_PERSISTENCE.XML>", properties);

       EntityManager em = emf.createEntityManager();

now you should be able to connect to a Oracle Wallet from JPA as well