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.
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
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.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.
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
Directory, 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.
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.
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):
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:
- 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.
- 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.
