Oracle 10gR2 introduced the possibility to define which rows and/or columns a user is allowed to view, add or modify during his/her database session. This subset of the database appears to the user as his/her private part of the database. The user is unaware of the rest of the data. The other records and/or attributes are not accessible to the user. The virtual private database (VPD) is a powerful and exiting feature. It enables fine-grained security and enhances performance. In this blog I like to introduce VPD and show how to set it up in Oracle. Next I want show a solution to the question how to set a context in the database session with the ORM framework iBATIS. Finally I like to show the architectural benefits of VPD for an existing car lease application.
Let’s assume there is a company whose business is leasing cars (a well-known example for IT employees). The company has an application that stores the lease contracts in a table t_contracts:
Quite boring. But there is a challenge: the company has departments in nine countries. The table is growing fast. Performance goes down. When reporting business results, data of different countries might get mixed. Departments are not allowed to access each other data. The Virtual Private Database will fix these kind of issues. First step is adding a country code column:
Virtual Private Database is also known as fine grained access control (FGAC). In this case we are going to define which rows users have access to. This is done by defining a policy. A special database library named dbms_rls (row level security), owned by user system, is available for us:
This call sets a policy on table t_contracts. This table is an object in a given schema. The policy puts a where clause on the table that is defined by a function in a package. This where clause defines the result set that can be selected, updated or deleted. This function is named predicate and is member of a package:
Rather simple, isn’t it? After compiling this package (for example in the free tool Oracle SQL Developer), we can call procedure set_country_code as follows:
The outcome of this call is that the package variable g_country_code has received the value ‘NL’. It is important to note that this variable has session scope. It has no effect on other sessions. Other sessions can not access this variable. After g_country_code is set in the session, calling function predicate will return the where clause:
Our policy contract_policy (see above) appends all select, update and delete statements with this where clause. Now we can play with it and have fun: (a) fill table t_contracts with a couple of records for country code NL, UK, D for instance, (b) call vpd_pkg.set_country_code, (c) issue select * from t_contracts (without where clause!) and discover that different result sets are returned.
Once the policy is set, it stays active during the whole session. When the database connection is closed, the policy is gone. On the other hand the policy should be defined as the first thing after a new session has started. The policy should be active before any select, update or delete statement could have been issued. Oops! How can we do that?
One solution is using a database trigger. For instance:
At the first glance this looks like a good solution: the trigger is fired right after database logon. There is a table t_user that has the country code for the session user (built-in function user returns the username of the session user). After the country code is fetched, the policy is set and we are in business. But….. the database is accessed by a Java web application that has a pool of connections and that all have the same application/schema user. Goodbye Virtual Private Database for web applications. Or not?
I tried to find a solution for this issue thru Google search but I didn’t succeed. I discovered the UserCredentialsDataSourceAdapter class of Spring. But to my humble opinion this doesn’t bring the solution because we have, and we want to have, only a single schema user. Doesn’t make sense to be able to connect multiple users to the database, because that implies that all those users must exist in the database as user (by DDL create user). In web applications we do not want that. We want a distinction between end-users (many) and database users (one or at least not many). On the SpringSource Forum site I found my problem formulated in Februari 2011 (see Links below). Nobody has suggested a solution so far. I found a solution. Maybe it’s not the best solution, but it works. I want to show this idea below.
The challenge is that we want to do something in the database after connect has succeeded (see above) and (most important) before any select, update or delete statement. I was thinking that the Spring class SqlMapClientCallback, to be used with SqlMapClientTemplate’s execute method, could help. I created a VpdSqlMapClientTemplate adapter (click image to enlarge):
This template is returned by the subclass (click image to enlarge):
Therefore DAO classes will extend the VpdSqlMapClientDaoSupport instead of the SqlMapClientDaoSupport, for example (click image to enlarge):
Please note that the SQL map definitions do not change, for example ContractMap.xml has still the same getAllContracts query:
The trick is that method queryForList is now called from an adapter class, namely VpdSqlMapClientTemplate. The only thing this adapter does is calling private method setVpdContext, before calling the original queryForList method. The setVpdContext method calls the stored procedure vpd_pkg.set_country_code:
As has been explained in the Oracle section above, this call sets the Virtual Private Database context. After this call all select, update and delete statements will relate to the country code of the current web application user. Please note again that this user is a different user than the user that is currently connected to the database!
The VpdSqlMapClientTemplate subclass has some elegance, because it is unaware of the database statements that are issued. It just adds the stored procedure call before each of them. Critical remark might be that it involves some typing because SqlMapClientTemplate contains 13 query methods, 3 update methods, and 3 delete methods. But for adapters that’s not unusual. Calling a method right before another method smells like AOP. Indeed defining a point cut on the SqlMapClientTemplate methods just mentioned and calling setVpdContext method would an interesting project.
AOP would be very nice, however my more primitive piece of code works. I created a simple unit test and it is cool to play with it. An existing car lease application could benefit from it.
Car Lease application
An existing application developed by Chess is a web frontend to a central back office system (created in PL/SQL). Every night relevant data is imported into the frontend application. There is quite some complexity here that is not important for the point I want to make now (like for example refreshing of materialized views). Simply said: for each country there is a separate set of tables that are defined in separate database schema’s : 9 countries and 9 schema’s. Nothing wrong with that. The challenge is however that a web application user must be directed to the right database schema, after he/she logs into the system. This has been solved by Spring configuration. Each country has it’s own set of application context configuration XML files:
In total about 94 files and 1.5 MB code for configuration only (I didn’t count the number of code lines). The maintainability of the application configuration is suboptimal. Therefore the architectural change I like to propose is to use the VpdSqlMapClientTemplate. This implies that all tables will be defined within a single database schema. Tables will get a country_code column. In a transparent way VpdSqlMapClientTemplate will take care that end-users will see only the data of their own country. This will simplify the car lease application in a tremendous way.
This post shows how the database could benefit the Java web application. During writing I stumbled on an interesting blog of Toon Koppelaars, named The Helsinki Declaration (IT-Version). He writes: “But as of the new millenium we are pushing all application logic out of the DBMS into middle tier servers. The functionality of stuff implemented outside the DBMS has exploded, and the feature rich DBMS is hardly used for anything but row-storage. Here’s a picture from my presentation showing this..”