Using Oracle Proxy Authentication with JPA (EclipseLink-Style)

Security is a very intriguing topic. You will find it everywhere and you need to implement it everywhere. Yes, you need. Unfortunately, one can easily forget it while implementing the last mile.

The Last Mile

In a multi-tier application it is a common practice to use connection pools between the business layer and the database layer. Connection pools are quite useful to speed database connection creation and to split the load. Another very common practice is to use a specific, often called technical, user to connect to the database. This user has authentication and authorization rules that apply to all application users. Imagine you've put every effort to define roles for different types of users that use your application. These roles are necessary to differentiate between normal users, premium users, and administrators (I bet you will find or already have more roles in your application). While these user roles are pretty well used within your application, once the flow of execution enters the database everything is gone. Each and every user just has one role and is the same database user.

Issues? What Issues?

As long as things go well, this is not a real issue. However, things do not go well all the time. Once your application becomes famous performance decreases in certain situations or, more importantly, current and upcoming regulations and laws require that your application must be able to apply different security measures on a per user role basis at every stage of your application. If you only have a bunch of users with the same name and role you are not able to find the application usage profile that causes the performance issue, or which user has accessed data that he/she is not allowed to. Another thread to your role concept is that databases tend to be used by different applications and tools. These tools can be developer tools like SQL*Plus, SQL Developer, etc. or end user applications like BI Publisher, Oracle Forms and so on. These tools have no idea of your applications role concept and access the database the way they think is appropriate. A big oversight for your perfect role model and a big nightmare for your Chief Security Officer. Speaking of the CSO, brings up another issue: Password management. Once your technical user account is compromised, every user is able to do things that he/she is not expected to do from the design of your application.

Counter Measures

In the Oracle world a common counter measure is to use Virtual Private Database (VPD). This restricts the values a database user can see to the allowed minimum. However, it doesn't help in regard of a connection pool user, because this one is still not the real user.

Oracle Proxy Authentication

Another feature of the Oracle database is Proxy Authentication. First introduced with version 9i it is a quite useful feature for nearly every situation. The main idea behind Proxy Authentication is, to create a crippled database user who has only connect rights. Even if this user is compromised the risks are well understood and fairly limited. This user can be used in every situation in which you need to connect to the database, no matter which tool or application (see above) you use.The proxy user is perfect for multi-tier connection pools.

But what if you need to access real data? Well, this is the primary use case, isn't it? Now is the time to bring the application's role concept into play. You define database roles that define the grants for your identified user groups. Once you have these groups you grant access through the proxy user with the application role to the specific user.

Now, hr has permission to connect to the database through the proxy user. Through the role you can restrict the hr's rights the are needed for the application only. If hr connects to the database directly all assigned role and permissions apply.

Testing the Setup

To test the setup you can use SQL*Plus and connect to your database:

Java Persistence API

The Java Persistence API (JPA) is a fairly easy means to build applications that retrieve data from the database and put it into Java objects. You use plain old Java objects (POJOs) and mixin some Java annotations that define how the attributes of the object are used for storing data from the database into the Java object. Here is a sample for objects from the HR sample schema EMPLOYEES table. When using Java annotations you only specify what can not be deduced from the code. If your Java class name is Employee but the table name is EMPLOYEES, you need to specify the table name, otherwise it will fail.

JPA could be used in standalone applications and Java EE containers. In both worlds you normally create a Facade to retrieve or store the values of the Entities to or from the database. The Facade does this via an EntityManager which will be injected by the Java EE container. Here is sample Facade Session Bean for a Java EE container.

Putting Both Together

To use Proxy Authentication with JPA and within a Java EE container you have to take care of the additional requirements:
  • Use an OCI JDBC driver
  • Provide the user name that connects through the proxy user

Use an OCI JDBC driver

To use the OCI JDBC driver you need to set up your JDBC data source file to use the correct JDBC URL.

Additionally you need to make sure that the version of the shared libraries of the OCI driver match the version of the JDBC driver in your Java EE container or Java application and are within your PATH (on Windows) or LD_LIBRARY_PATH (on most Unix-based systems). Installing the Oracle Database Instance Client software works perfectly.

Provide the user name that connects through the proxy user

This part needs some modification of your application software and session facade.

Session Facade Changes

In the Session Facade we must ensure that every call that goes through the EntityManager must be prepared correctly and uniquely assigned to this session. The second is really important, as the EntityManager works with a connection pool and can not guarantee that we set the proxy user on the connection that will be used for the database activities. To avoid changing every method call of the Session Facade we provide a method to set the username of the user that connects through the proxy user. This method needs to be called by the Facade client bfore doing anything else.

Next we provide a means to instruct the TopLink EntityManager Delegate to use Oracle Proxy Authentication. (I love small helper methods to hide the nitty-gritty details and avoid repeating myself.)

The final step is use the EJB 3.0 AroundInvoke interceptor. This interceptor will be called around every method invocation. We therefore check whether the Facade methods will be called or not. If so, we set the user for proxy authentication and the normal method flow continues.


Using Oracle Proxy Authentification has a number of additional benefits appart from implementing the role model of your application:
  • Fine grained access control for temporary users of the account, without compromising the original password.
  • Enabling database auditing and logging.
  • Better identification of performance bottlenecks.



Isn't the use of a class member variable in a stateless session bean considered to be global to all clients just like static class member?

Posted by Peter Johansson on May 06, 2010 at 05:39 AM CEST #

I must admit, this code is not perfect and has several issues. The member variable is one, thread safety and resource leaks are others. These issues need to be fixed. Thank you for pointing this out. --olaf

Posted by Olaf Hiemburger on May 06, 2010 at 05:50 AM CEST #

Hello, Olaf, is the OCI driver necessary? I've found in Oracle DB Documentation that thin driver will be good too.

Posted by Andrey on June 03, 2010 at 03:52 AM CEST #

I was curious if there was an answer to Andrey's question as to whether the thin driver could be used? Some simple tests seen to indicate that it is, but I was wondering if there are any negatives beside not gaining the performance from the oci calls? Thanks, Dave

Posted by David D on October 29, 2010 at 01:13 PM CEST #

It works with JDBC 11g thin as well. --olaf

Posted by olaf.heimburger on January 14, 2011 at 06:03 AM CET #

Hi, I am trying to achieve VPD(OLS) filtering via JPA. One difference between what I am doing, and your blog entry, is that I am NOT creating real separate users using the "create user" command, I am only associating privileges with the user name as a string, for example, like this:



When I execute my query via JPA, I see that it tries to proxy over to 'username', but then I get ORA-01017: invalid username/password; logon denied.

What I really want to happen in the EntityManager is something like this:


Are there any properties I can set in EntityManagerProperties to make that happen?

Posted by Kevin Pauli on February 23, 2012 at 08:14 PM CET #

Olaf - do you know if this would work with Hibernate or EclipseLink as well or only TopLink?


Posted by john archer on May 16, 2012 at 01:12 AM CEST #

This works for EclipseLink and TopLink. Hibernate uses a different setup which I haven't tested yet.

Posted by Olaf Heimburger on May 16, 2012 at 08:29 AM CEST #

Post a Comment:
  • HTML Syntax: NOT allowed

This is my blog for things I found useful during my work as Senior Solution Architect for Identity Management and Service-oriented Architecture at Oracle. All text expressed on this blog is my own opinion and not related to my employer.


Top Tags
« June 2016