Developer: Java
 Oracle TopLink
 Oracle JDeveloper 10g
 Oracle Database
 Sample Code
java, security, All

Implementing Row-Level Security in Java Applications

by Lonneke Dikmans, Oracle Fusion Middleware Regional Director

Learn how to enforce row-level security across your JEE application, from database to cache.

Published July 2007

Security is a very important aspect of applications. Users need to be authenticated, and data needs to be protected against unauthorized access. Oracle provides an out-of-the-box solution for row-level security in the database: Oracle Label Security. By using the open-source Oracle TopLink as your ORM solution, you can also ensure that this row-level security is maintained in the cache and other parts of the application as well. This article shows how to make this work via a very simple sample application.

Sample Application

The sample application is a simple Web application that uses the HR Schema. Users can log in to it and view all locations that they are authorized to see.

The schematic overview of the application shows four parts:

  • Datasource layer. The datasource layer is the HR Schema that is part of the Oracle RDBMS. In this application, we use only the Countries, the Locations, and the LOCATION_SEQ objects. The policy that is defined for the LOCATIONS table is explained in the next section.
  • Domain layer. The domain layer uses Java, Oracle TopLink, and an EJB session facade to implement simple operations on the given domain classes. This functionality is exposed through both a local and a remote interface.
  • Presentation layer. The presentation layer consists of one JavaServer Faces (JSF) page showing all locations that a user is allowed to view. It uses basic HTTP authentication to keep the example simple and ADF data bindings to connect to the domain layer.
  • Integration test. This component contains integration tests for the domain layer with the datasource layer. The remote interface to the domain layer is used.

Figure 1
Figure 1 Schematic overview of the sample application

Oracle Label Security in a Nutshell

Oracle provides different types of access control in the database. Let's explore each in detail.

Discretionary Access Control (DAC). With DAC, object-level permissions are granted to database users. Access is granted or denied to the entire object. For example, the database user SKING can be granted permission to select from locations with the following statement:

grant select to hr.locations to sking;
SKING can now select all rows in the LOCATIONS table. Instead of using database users, you can define roles. Instead of granting the select permission to the database user, you grant the permission to the role and then grant the role to the database user.
create role emp_role;
grant connect to emp_role;
grant select to hr.locations to emp_role;
grant emp_role to SKING;
Fine-Grained Access Control (FGAC). FGAC, also called row-level security, is a method where you can restrict access based on the content of the data. The Oracle Database solution for this type of requirement is called Virtual Private Database (VPD), which is a feature of Enterprise Edition. The database dynamically alters the query based on the security policy and session context. Apart from restricting the rows that are returned, you can also mask columns.

Security policies can be created using stored procedures. Such policies restrict access by using the content of application data stored in Oracle Database or context variables, such as user name or IP address.

Oracle Label Security (OLS), an Enterprise Edition option, is an implementation of VPD. Using OLS, administrators can create policies without writing PL/SQL. Access to data is mediated based on four factors:

  • Label of the row
  • Label of the user session
  • Policy privileges of the session
  • Policy enforcement options for the table

Row Label

Each row of a table can be labeled based on its level of confidentiality. Every label contains three components:

  1. A single level (sensitivity) ranking
  2. Zero or more horizontal compartments or categories
  3. Zero or more hierarchical groups

In the sample application, you will use a policy named "access_locations" that determines who can see what location. You won't use compartments or groups. Levels are 'public' ('pub'), 'confidential' ('conf'), and 'sensitive' ('sens').

User Label

Users can be granted label authorizations that determine the kind of access (read or write) they have to the rows that are labeled. When a label has been applied to a row, only users authorized for access to that label can see it or change it.

Whenever a user connects to the database, the level of the user session is matched to that of the rows.


OLS supports special privileges that allow authorized users to bypass certain parts of the policy. HR is the owner of the schema and has been granted FULL privileges in the sample application. This allows full read and write access to all data protected by the policy.

Policy Enforcement

When a user writes data, he or she can set the label for that row. The level of this label can be set to any level within the range specified by the administrator. When a user writes data without specifying its label, a row label is assigned automatically using the user's session label.

The following user labels are defined in the sample application:


Max read

Max write

Min write

Default label

Default row

SKING 'sens' 'sens' 'conf' 'sens' 'sens'
KPARTNER 'conf' 'conf' 'pub' 'conf' 'conf'
LDORAN 'pub' 'pub' 'pub' 'pub' 'pub'

Combining DAC and FGAC. The figure below shows what happens when a user queries the LOCATIONS table:

Figure 2
Figure 2. FLOW with DAC and FGAC

First, the database determines if the user has sufficient privileges based on the DAC. If the user is allowed to select data from hr.locations, the database checks if the user has any privileges that bypass the policy. If the user has no special privileges, access is mediated based on the policy. If the user has special privileges, mediation is bypassed and the query is executed based on the privileges.

The sample application contains a test script (that runs in SQL*Plus) that shows what happens when different users log in and execute the same query.

Select all locations with different users by opening SQL*Plus and typing

You will see that the same query has different results, depending on the user who connects to the database.

Oracle TopLink Sessions

An Oracle TopLink session is the communication mechanism with the Oracle TopLink runtime. There are different types of sessions. A session consists of the following components:

  • Java Object builder. Oracle TopLink converts the result of a read from the datasource into objects and converts objects to queries in case of write operations.
  • Query mechanism. The session executes all persistence operations on objects.
  • Connection pool. The connection pool is a collection of reusable connections to a single datasource. It improves the performance of your application significantly, because it reduces the overhead that is associated with creating connections. Oracle TopLink can use internal connection pools or the external connection pools provided by a Java Platform, Enterprise Edition (JEE) server or JDBC driver.
  • Shared cache. The cache holds all objects that are read from or written to the database. All client sessions that are acquired from the server session share this cache. This is also important from a performance point of view.
In the following paragraphs, we will look at Oracle TopLink sessions in a "regular" Java EE application, where no VPD or OLS is used.

Reading Data. To read data from a datasource, several interactions with the Oracle TopLink runtime occur:

  • Acquire a client session from the server session
  • Build the query using the query builder
  • Read the data by checking the cache, or read it from the database if the objects are not in the cache
  • Convert the result into objects
The listing shows the code to read all countries:
* finds all countries
* @return List<Country> or an empty list if none are found
public List<Country> findAllCountries() {
        Session session = getSessionFactory().acquireSession(); 
        List<Country> results = 
         (List<Country>)session.executeQuery("findAllCountries", Country.class);
        results = (List<Country>)getSessionFactory().detach(results);

        return results;
The query is defined in the class descriptor, as can be seen below:
               <cache-usage>Check Cache by Primary Key</cache-usage>
               <lock-mode>Do Not Acquire Locks</lock-mode>
               <distinct-state>Uncomputed Distinct</distinct-state>
                        Throw Indirection Exception
session.executeQuery(...) will first try to fetch the countries from the shared cache. If the objects are not in the cache, they will be read from the database, using a connection that is defined in the sessions.xml.

Persisting Data. To write data to the datasource, similar steps need to be taken:

  1. Acquire a UnitOfWork using either the global JTA TX or a new one from the active session.
  2. Create the object in the unit of work.
  3. Commit the data to the database, using the query builder and the connection from the connection pool.
The listing below shows an example of how to persist an object:
* saves an entity
* @param entity that needs to persisted
* @return Object that is persisted.
public Object persistEntity(Object entity) {
        UnitOfWork uow = getSessionFactory().acquireUnitOfWork();
        Object existingObject = uow.readObject(entity);
       if (existingObject != null)
                throw new RuntimeException("Entity already exists");
        Object newInstance = uow.deepMergeClone(entity);

        return newInstance;
The UnitOfWork acts as a transactional unit and ensures that writes to the database are also written to the server cache. This way the correct updates are returned when a read fetches a changed country from the cache.

Connection Pools. Typically, when developing a JEE application, you use an external connection pool with Oracle TopLink. This means that you have one connection pool, for both reading and writing data.

By default, TopLink uses internal connection pools. There are two pools in that case: one for writing and one for reading data.

Figure 3
Figure 3 Toplink connection pool options

Oracle TopLink and VPD

In the previous section, we discussed regular Java EE application configuration with shared cache and connection pooling for performance purposes. For your sample application, you need a more sophisticated solution, however. First of all, you can't use a shared cache for all users, because not all users are authorized to see all data. Second, the database needs to have information about the user that is connected, to be able to execute mediation. Finally, you need to control the security level of new data that is inserted into the database.

Thus, to use VPD with Oracle TopLink, configure isolated client sessions and use proxy authentication.

Isolated Client Sessions. An isolated client session is a client session that provides its own session cache.

Figure 4
Figure 4 Isolated client session

Every table that uses VPD or OLS needs to be isolated. You can isolate either your entire project or just classes. References to shared classes from isolated classes are allowed; the other way around, obviously not. In the sample application, you reference countries from location, not the other way around. This means you can use isolation on the class level. There are two ways to isolate a class: declaratively using the workbench or in the Java code.

In the workbench, you can easily isolate a class through the following:
  1. Select Location in the navigator
  2. Click the Cache tab
  3. Select isolated
  4. Save your changes
The session.xml now looks like this:
<transactional-policy type="relational">
      <query-manager type="relational"/>
      <locking-policy type="relational"/>
In Oracle JDeveloper, you can't configure isolated caching with the mapping editor. You can customize the session in two different ways: Use the preLogin method of the session SessionEventListener, or use the getSession() method from the SessionManager.

To use the preLogin method, you need to implement the SessionEventListener and register this listener with the session.

public class LocationEventListener extends SessionEventAdapter {

   //other methods you want to override 

 * We isolate the Location class here.
 * @param event that is raised before the session is logged in.
 public void preLogin(SessionEvent event) {"in prelogin event");
   ClassDescriptor descriptor = event.getSession().getClassDescriptor(Location.class);
Add the class to the session in the mapping editor, as shown below.

Figure 5
Figure 5 Configure the session with an event listener

To use the SessionManager.getSession(...) method, when you acquire the session without logging in, you can set the class descriptor to false and log in after that.

private Server getSession(){
        Server server = (Server)sessionManager.getSession(xmlSessionConfigLoader, "hr", false);
        ClassDescriptor descriptor = server.getClassDescriptor(Location.class);
        //.... rest of code....


Since OC4J 10.1.3, there are two types of datasources: managed and native. A managed datasource is an OC4J-provided implementation of the java.sql.DataSource interface that acts as a wrapper to a JDBC driver or datasource. It can participate in global transactions and make use of a connection pool. Native datasources implement the java.sql.DataSource interface and are provided by a JDBC driver vendor.

In an Oracle TopLink project, you can define different server sessions. In the example below, you see an example of a managed datasource (jdbc/hrDS) and a native datasource. This is done in the sessions.xml:

<?xml version = '1.0' encoding = 'UTF-8'?>
<!DOCTYPE toplink-configuration PUBLIC "-//Oracle Corp.//DTD TopLink Sessions 9.0.4//EN" "sessions_9_0_4.dtd">
        <!-- managed datasource -->
     <!-- etc -->
        <!-- native datasource -->

The session factory can be used to acquire the correct session by name. The listing shows how you define the session you want to use.
this.sessionFactory =  new SessionFactory("META-INF/sessions.xml", "hr");
Session session = getSessionFactory().acquireSession();    
When you use the managed datasource, you need to add the logic that appends the VPD-specific SQL to the queries yourself. But there is an easier way: using proxy authentication. In this sample, all the users are known to the database, so you can use proxy authentication. OC4J 10g (10.1.3.x) supports this with Oracle JDBC native datasources. This cannot be configured with the workbench or Oracle JDeveloper; you need Java code to accomplish this.

In this case, we will use proxy authentication based on the username only. (See How-To Configure and Use Proxy-authentication with OC4J 10g (10.1.3) Data Sources for a detailed explanation.)

You need to take the following steps:

  1. Alter the user: alter user ldoran grant connect through hr;
  2. Acquire an isolated client session with proxy authentication by adding the following code:
    * Returns an isolated client session from the server session
    * @return isolated client session.
    private Session getSession() {
            Server server = 
                (Server)sessionManager.getSession(new XMLSessionConfigLoader(),  
           DatabaseLogin login = (DatabaseLogin)server.getLogin().clone();
            // this also sets isLazy flag to false
           ConnectionPolicy policy = new ConnectionPolicy(login);
           // Set proxy properties into connection policy's login
           JNDIConnector connector = (JNDIConnector)login.getConnector();
           login.setConnector(new OracleJDBC10_1_0_2ProxyConnector(connector.getName())); 
            String user = getUser();
            login.setProperty(OracleConnection.PROXY_USER_NAME, user);
            return server.acquireClientSession(policy);
    You can retrieve the user that logged in to the Web application from the context:
    * Gets the Principal that logged in from the context
    * @return the name of the caller principal. 
    private String getUser(){
       String user = ctx.getCallerPrincipal().getName();"user that logged in: " + user);
       return user;

Exclusive Connections. Typically, when you use VPD and OLS, you use exclusive connections in your application. An exclusive connection is allocated by Oracle TopLink to a client session for reading of isolated data and writing for the duration of the session's lifecycle. It is obtained from the server session's write connection pool. Oracle TopLink still acquires a shared connection from the read connection pool for reading nonisolated data—in the sample application to fetch countries, for example.

To use an exclusive connection, add the following code:

ConnectionPolicy policy = new ConnectionPolicy(login);
//... rest of the code

What's Next?

In this article, you have learned how to implement Oracle Label Security in a Java EE Web application via TopLink. In this sample, you used jazn to configure the security in the Web application and the database to define your security policies. This combination is very powerful: it uses security features of the Oracle Database as well as Java EE.

In a real application, it would make sense to define both in Oracle Internet Directory, making users, roles, and privileges easier to maintain. This example only reads data from the database; no data is persisted. When data is persisted to the database and optimistic locking is used, you need to add a handler for the no-rows modified event. This exception can be thrown when there is a security violation as well as for locking exceptions. In case of a lock, the application can try to persist the object again. In case of a security violation, an exception needs to be thrown.

Happy coding!

Lonneke Dikmans, an Oracle Fusion Middleware Regional Director and Oracle ACE, is managing partner at Approach Alliance in The Netherlands. She is an architect, specializing in SOA and agile development. Lonneke has been using JDeveloper since 2000, and has experience designing, developing, and deploying Java applications.