Row Level Security using VPD and ADF

Suppose you have an ADF web application, and you want multiple users to look at the same database table, but to make certain rows invisible for some users. Also, you might want the possibility to have some of the visible rows (but not all) be read-only, depending on the user.

In this blog post, I will explain how we used VPD with JHeadstart and ADF Business Components in my latest project to get such row level security in our web application.

Key Ring Image:    Contents
What is VPD?
The Application User Problem
The Global Application Context Feature
Only Authorized IP Addresses
The Set Context Procedure
Setting the Context from ADF BC
Making Rows Invisible
Making Rows Read Only

What is VPD?

VPD or Virtual Private Database is a feature of the Enterprise Edition of the Oracle Database. It is also known as Fine-Grained Access Control (FGAC), and some call it Row Level Security (RLS). The database package used for VPD is called DBMS_RLS.

The main functionality of VPD is to automatically add extra where clauses to SQL statements. Depending on how you defined your VPD policies, it can be applied to select, update, insert, and/or delete statements of specific tables.

Quote from Lonneke Dikman's article Implementing Row-Level Security in (TopLink) Java Applications:

The database dynamically alters the query [or DML statements] based on the security policy
and session context. [...] 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

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.

To make the VPD policies user / role specific, VPD must know which end user is currently accessing the database. This might seem trivial, but it isn't if you want to use connection pools in the application server.

The Application User Problem

As also described in Fine-Grained Auditing for Real-World Problems, Part 2, by Arup Nanda, in web-based applications, the application server usually connects to the database using a fixed user -- for example, APPUSER. The users of the application are authenticated by the application. Therefore VPD cannot rely on the current database user for its policies, we need to have some mechanism to pass the application user name to VPD.

To make VPD aware of the current end user of an ADF web application when all database connections use the same application user, you can use the Global Application Context feature of the Oracle database, as described in the Oracle whitepaper Leveraging Oracle Database Security with J2EE Container Managed Persistence.
If you want to use Oracle Label Security (OLS) and you can use Oracle Internet Directory to identify the users, you might be
interested in the article How
to integrate Oracle Label Security with Oracle Internet
, which describes how to take the users and the OLS policies
from OID. In other cases, you have more flexibility if you use generic VPD in the database with the Global Application Context feature.

The Global Application Context Feature

To apply this technique you define a global context object in the database, which is like a namespace that has key/value pairs. The only way to set a context is through the PL/SQL package that is bound to that context, for example MY_VPD_CONTEXT_PCK. This method is also explained in the abovementioned article by Arup Nanda and in Pete Finnigan's article Oracle Row Level Security: Part 1.

Only Authorized IP Addresses

To ensure that the end user name is only set in the database context when that user has successfully logged in to the web application, you can add security checks to procedure that sets the context. A simple but effective check is to verify that the call comes from an authorized IP-address like the application server. It relies on the fact that by other means you will have made sure that no unauthorized persons can execute database calls from the application server. This security check is also described in the abovementioned article by Pete Finnigan.

Here is the example code to perform such an IP address check: the function IS_AUTHORIZED_IP_ADDRESS in package MY_VPD_CONTEXT_PCK returns true if the current IP address occurs in a table of authorized IP addresses.

Is Authorised IP Address Code Image:

You can use this function just before setting the context. In addition you could create an AFTER DATABASE LOGON  trigger in which you use the same function to ensure that only authorized IP addresses can log on as the APPUSER.

In the interests of security the MY_VPD_CONTEXT_PCK package, and the MY_AUTHORIZED_IP_ADDRESSES table should not be owned by the APPUSER schema. Instead you can create an APPOWNER schema to which you cannot connect, which owns all application database objects, and only grants privileges that are needed by the web application to the APPUSER schema, for example grant execute on MY_VPD_CONTEXT_PCK. The APPUSER should certainly not be able to modify the MY_AUTHORIZED_IP_ADDRESSES table.

The Set Context Procedure

Now, we can add a procedure SET_CONTEXT to the package MY_VPD_CONTEXT_PCK, that first checks the IP address and then puts the user name in the context, so that they are available for use in VPD policies.

Set VPD Context Code Image:

Note 1 This procedure also sets the Client Identifier. This is a kind of session attribute that is used in Fine Grained Auditing, to audit who has done what. Of course we want to know the end user name in the auditing trails, and not the APPUSER schema name.

Note 2 The SET_CONTEXT procedure uses a global package variable g_database_user that is initialised with the value of the current database user: in our example that would be 'APPUSER'. This value is used to identify the key/value pairs in the context object.

Note 3 In this example one key/value pair are placed in the context: username. Of course you could also pass other parameters to the procedure and store them also as key/value pairs. You could also maintain a cache for frequently used information in the VPD policies, depending on the passed parameters.

Setting the Context from ADF BC

To ensure that the ADF web application sets the context correctly, the context must be set each time a different end user uses a database connection. When you use a pool of database connections, you can never be sure that the next request in a session will use the same database connection as the previous request. In ADF Business Components, the prepareSession() method gets invoked by the application module when it is used for the first time by a new user session (see the ADF Developer's Guide for Forms/4GL Developers: 8.6.3 How to Override prepareSession() to Set Up an Application Module for a New User Session). So that's the ideal method to override in your custom application module class to set the VPD context.

If you let the application server manage your users in the J2EE container, you can implement prepareSession() like this (see also Implement JAAS based Authentication and Authorization for ADF Faces applications on OC4J 10.1.3 by Lucas Jellema):

Prepare Session Code Image:

Note that in this example we assume that SET_CONTEXT has only one parameter: the username.

If you manage the application users in the ADF application itself, then you of course derive the username in some other way.

In my latest project we found out that setting the VPD context just in prepareSession() was not enough. We had implemented a custom authentication in a servlet filter (using the AuthenticationFilter class supplied in the JHeadstart Runtime library). If the AuthenticationFilter determines that the user has logged in and is not in the process of logging out, it loops over all active data controls and if they are of type JhsApplicationModule it calls setUser() on each application module. If you use JHeadstart you have this JHeadstart superclass in all your Application Modules. To make sure that each active Application Module sets the correct VPD context, even in case of web time outs or deep links, we have overridden the JHeadstart setUser() method and added a call to setVPDcontext() (see the above code example).

Making Rows Invisible

After doing all these preparations, to get Row Level Read Security all you need to do is add select policies on your tables. For instructions how to apply such policies, see the Oracle By Example tutorial Restricting Data Access Using Virtual Private Database. Only instead of calling sys_context('userenv','session_user'), you need to call a function in MY_VPD_CONTEXT_PCK that returns sys_context(g_database_user, 'USERNAME'). Depending on the policy, this will ensure that user A might see different rows than user B when the web application performs a certain database query.

If certain role information is needed often during the execution of the VPD policies, it is advisable to cache as much as you can, because VPD can have a serious impact on performance. Of course you must test carefully that the cache always contains the correct information.

Making Rows Read Only

The next step is to add Row Level Update Security to the application. This is more tricky, because we not only want to prevent certain users from updating certain rows (by showing an error message if they try), we also want to make the rows read-only in the screens (so that they won't even be able to try it). So we want to be able to use some boolean JSF expression to apply to the readOnly property of the JSF / ADF Faces components in our user interface. Furthermore, this expression must be row specific; for each row there could be a different return value.

We implemented this row-level boolean JSF expression using a special IsUpdateable attribute in the ADF BC View Objects. You could make this a transient attribute and code the logic in the getIsUpdateable() method of the ViewRowImpl class, but that get method would be executed each time the attribute is used in an expression, each time the page is displayed or refreshed. We implemented it as a calculated attribute instead, which means the calculation is only executed when the View Object's select-statement is executed in the database. This normally happens only once per user session, unless an explicit "Execute Query" is programmed for the View Object. For more information how calculated attribute values are cached, see the ADF Developer's Guide for Forms/4GL Developers, section 7.7 Understanding How View Objects and Entity Objects Cooperate at Runtime.

The calculated attribute is implemented by calling a database function specific for the table. For example: if we have an Orders table, and we want some Orders to be read-only to certain users, we would add a calculated attribute IsUpdateable to the Orders View Object. In the select-clause we would call a database function MY_UPDATEABLE_PCK.IS_ORDER_UPDATEABLE(p_ord_id number). We pass in the primary key of the row (the order id), and return a string 'TRUE' or 'FALSE'.

Then in the JSF pages, you can add the following expression to the readOnly property of each relevant UI component: readOnly="#{!bindings.IsUpdateable!='FALSE'}" or, in case of a table layout, readOnly="#{!row.IsUpdateable!='FALSE'}". Now you might argue, why this triple denial? Why not simply use something like #{bindings.IsReadOnly=='TRUE'}? Well, there are two reasons:
  1. When generating these pages using JHeadstart, the appropriate place to put this expression is the Update Allowed EL Expression property. To allow for easy-to-read expressions in this property, we prefer to write bindings.IsUpdateable instead of bindings.IsReadOnly. A side effect of using JHeadstart's Insert/Update/Delete allowed expressions is that the New and Save buttons will also be made invisible when they do not apply.
  2. When we used bindings.IsUpdateable=='TRUE', we found out that just after inserting a new row, that row would become read-only, even though it was supposed to be updateable. That's because calculated attributes are only refreshed when re-querying the database. So to make sure that in case the value is unknown (like it is just after insert), the row will be updateable, we use !='FALSE' instead of =='TRUE'.
So, in JHeadstart's Update Allowed EL Expression of a form layout Group we put #{bindings.OrdersIsUpdateable!='FALSE'}. And for an updateable table layout Group it becomes #{row.IsUpdateable!='FALSE'}. In the latter case you can create the effect that in the updateable table some rows are read-only and others can be updated.


Hi Sandra,

Thank you for this great article. What do you think of the following small improvement. I have a calculated attribute names 'IsRowUpdatable' which is defined on the EO or VO. Let's say it's defined on the EO with 'Selected in Query' option checked which contains a call to a function in a package : AXI_SECURITY.ISROWUPDATABLE(DEPARTMENT_ID). Then on the EO class I override the isAttributeUpdateable methode with the following code :
public boolean isAttributeUpdateable(int i) 
{ if (getAttribute("IsRowUpdatable") != null) return ("TRUE".equals(getAttribute("IsRowUpdatable").toString())
? true : false); return super.isAttributeUpdateable(i);
With that in place, you have nothing to do left in the view layer.

Posted by S�bastien POUILLET on November 05, 2007 at 01:50 PM PST #

Hi S´┐Żbastien,

That is a great idea, thank you for this suggestion! You are then enforcing the rule in the Business Components instead of the View layer, which has the added advantage that it applies to any page/VO that uses the same EO.

I think it could even be used if you have combined the IsUpdateable expression with other conditions in the JHeadstart insert/update allowed properties. Have you tried that?

I do wonder if this still allows you to calculate the updateability for each row separately. Is the built-in method isAttributeUpdateable re-calculated for every row? Have you tried if you can use this technique in a table layout and have some rows read-only while others are updateable?

kind regards,

Posted by Sandra Muller on November 12, 2007 at 03:53 AM PST #

Hi Sandra,

The updateability is recalculated for each row. To prove that I wrote a simple app that does not use VPD but instead allows to mark a row as read only depending on the value of the primary key. This sample was written on the HR DEPARTMENT TABLE. Here is my function in the DB :
 FUNCTION isRowUpdatable (dept_id in number)
 RETURN varchar2 AS
   var_return varchar2(3);
   IF dept_id in (103,104,105) THEN    RETURN 'FALSE';    END IF;  
RETURN 'TRUE';  END isRowUpdatable;
And my IsRowUpdatable attribute is defined with the following expression : AXI_SECURITY.ISROWUPDATABLE(DEPARTMENT_ID)

I then build a ADF Faces table on top of this and the rows with id 103,104,405 were in read only, others were updatable. Next step for me is to integrate VPD but it should behaves the same.


Posted by S�bastien POUILLET on November 12, 2007 at 08:08 AM PST #


That sounds good! It would definitely be an improvement from an architectural point of view.

Thanks again,

Posted by Sandra Muller on November 12, 2007 at 06:23 PM PST #

Dear Sandra, Thank you for a very interesting article. I had a number of remarks (btw we are on JDeveloper, JHeadstart 1) I tried to implement JAAS with Custom Login Module following the JHeadstart Developer's Guide but could not get it to work. It turns out upon perusing the JHeadstart New Features document (JHeadstart10132.htm) that JAAS is not yet (correctly) implemented: "Known Issues Following functionality not yet implemented or not yet working correctly JAAS-based security types not yet implemented. " I would recommend placing this remark in the "known issues" of the release notes, rather than as a footnote in the New Features document. When do you plan on implementing JAAS with CLM in JHeadstart (which version, when is it slated for delivery) 2) We selected the Security Type "Custom" using the tables created using the sql script generated by JHeadstart. We found out that settting the VPD context in the prepareSession() will not work because at that point the JhsUser object has not yet been created. We invoked the setVPDContext method in the overridden setUser() method in the application module impl instead (as your article suggests). There is one caveat, however, the setUser method is deprecated according to the API documentation, which entails that at some point in time, the setUser approach will not work any longer. My question is: should we override another method instead (e.g. the authenticateUser(String username, String password)) and call setVPDContext in that method? 3) When I first set the Security Type (in the JHeadstart dev guide this setting is referred to as Authentication Type) to JAAS with CLM, and then generated the application using the JAG, some elements were added to the web.xml deployment descriptor. Subsequently, when I changed the setting to "Custom" followed by JAG, these settings were not properly removed/replaced, causing errors (NullPointerExceptions etc). In general, I have the impression that when modifying certain settings followed by JAG invokations, certain files are not left in a consistent state (this goes for JDeveloper in general). Needless to say, this causes a lot of delays (and frustration). Many thanks and keep up the good work, Ibrahim

Posted by Ibrahim Ajubi on January 22, 2008 at 10:50 PM PST #


Thank you for your feedback.

Of your 3 questions, the appropriate place for the first and the last is the JHeadstart discussion forum at . Can you please post them there?

Regarding your second question: You can continue to use the setUser() method, it shouldn't have been deprecated.

kind regards,

Posted by Sandra Muller on January 29, 2008 at 01:47 AM PST #

Thank you for post. Very helpful as starting with ADF and VPD.

Posted by Ivan Budzel on September 13, 2012 at 06:10 AM PDT #

Worked for me.
Absolutely Excellent Explanation!!!

Posted by Bilal on January 19, 2013 at 11:35 PM PST #

Post a Comment:
  • HTML Syntax: NOT allowed

Java EE Consultants - JHeadstart, ADF, JSF


« July 2016