Jason's Blog

Oracle Virtual Private Databases, how I’ve implemented them, and a bug I hit

by on Sep.05, 2006, under Technical

Ok, just to note – this post probably won’t mean a whole lot for most people, as it will be pretty technical in nature.  SO, for those who are not interested in “tech” details, feel free to read something else.  I’d suggest Slashdot but that can be as “geeky” at times too.  Continue to read at your own peril wink

First, for those not aware, I do a LOT of development in Oracle databases.  I’ve been using Oracle on Linux servers since RedHat 6, and Oracle 8 was out.  I’ve hit all kinds of bugs, oddities, etc. in that time.  Every so often I find a behavior that drives me nuts, or that I have to ask “WHY did you guys do something like this???” Today is another one of those days where the Oracle developers have me “fuming.”

Before I get into the bug I hit, let me explain what’s going on and what I’ve been working on most of the past year.  I’ve been working on upgrading an application for the University of Missouri.  I’m moving an application from 4 distinct databases and schemas, to one centralized database, as well as doing a complete rewrite of the database structure, and the code which accesses that data.  The application I’m working on tracks project information for each of the University of Missouri System campuses.  The application, called “Projex”, tracks every bit of a project from start to complete, including funding, cost analysis, payments, warranties, clients & managers, agreement details, contract details, cost breakdowns, and everything else under the sun.  Each University of Missouri campus, currently, has their own database separate from any other campus’s.  The reasons for this are varied, but the biggest reason was the application was not originally written to handle multiple separate entities sharing a set of data, yet having distinct parts.  Thus, the major rewrite of the application to merge these databases, add some extra functionality and security for separating out data, as well as move to a web platform for ease of access.  Needless to say – I’ve been BUSY.  However, the changes should add a lot of functionality, and prevent some of the duplication issues I’ve seen with the separate databases. 

Having all of our data in a centralized database is a wonderful thing for reports, maintenance, and other tasks, but it also presents a security concern – how do we prevent Rolla users from seeing Columbia users data.  How do we allow System users access to everything.  Well, the way I’ve implemented this security is by implementing a feature in Oracle called VPD – Virtual Private Databases. Virtual Private Databasing is one of those technologies that is amazing, does it’s job well, and in the right place is a wonderful feature.  It’s what is allowing us to use a centralized database yet still make sure “Bob in Rolla” or “John Doe” in Columbia can only see the things they’re supposed to see.  The trick, which leads to the bug I’ve been working on, is that Virtual Private Databases are complex to implement effectively.  Here’s how I’ve “implemented” my solution for VPD’s and the one “bug” I’ve hit:

First, I’ve created a couple of tables for keeping track of users, groups, and the permissions groups have on various tables.  To keep it simple, I created a view to have a one-stop query for detailing that information.  Here’s a rough example of what I’m doing:


create table users (
     
username varchar2(20),
     
is_admin number(1),
     
email_address varchar2(50),
     
primary key (username)
);

insert into users values ('MCINTOSHJ',1'MyEmailHere') ;

create table group_permissions(
    
groupname varchar2(30),
    
table_name varchar2(30),
    
site_id varchar2(20),
    
permissions varchar2(20),
    
primary key (groupnametable_name)
);
insert into group_permissions values ('COLUMBIA_ADMINS','COLUMBIA','PROJECTS','READWRITE') ;
insert into group_permissions values ('ROLLA_ADMINS','ROLLA','PROJECTS','READWRITE') ;

create table user_groups (
    
username varchar2(30),
    
groupname varchar2(30),
    
primary key (usernamegroupname)
) ;
/* Of course I'm going to be an admin */
insert into user_groups values ('MCINTOSHJ','COLUMBIA_ADMINS') ;
insert into user_groups values ('DOEJOHN','ROLLA_ADMINS') ;

create table projects (
    
project_number varchar2(10),
    
site_id varchar2(20),
    
building varchar2(200),
    
primary key (project_number)
) ;
insert into projects values ('TEST123','COLUMBIA','Test Building') ;
insert into projects values ('RTST123','ROLLA','Test Building for Rolla') ;

create view vw_user_group_permissions as (
    
select A.usernameB.groupnameB.table_nameB.site_id 
    from user_groups A
group_permissions B where A.username B.username
);

The above creates the basic table structure for the database (of course this is all examples), and setting the default security.  Notice, my username is in the “COLUMBIA_ADMINS” group, but NOT the ROLLA_ADMINS group.  With the security I’ll be creating, this means that I won’t be able to see Rolla’s data, but I WILL be able to see Columbia’s data.  Note too that SITE_ID is on the projects table – this “SITE_ID” is how security through-out the system will be set. 

Now, we’ve created our database tables.  The next trick is when getting it so that when I run a query such as “select * from projects” I only see the data in my database.  This is where Oracle Virtual Private Databases come into play.  If you read the article up above on VPD, you can get a MUCH better overview of how things work, so I’ll keep it simple with just examples of the code I used to setup the VPD settings:


create or replace package vpd_policy as
    function 
vpd_read (schema_name in varchar2object_name in varchar2) return varchar2;
    function 
vpd_write (schema_name in varchar2object_name in varchar2) return varchar2;
end;
/
create or replace package body vpd_policy as

   function vpd_read (schema_name in varchar2object_name in varchar2)
   return 
varchar2
   is
       predicate varchar2
(1000) := '';
   
begin
      lv_predicate 
:= 'site_id in ( select site_id from vw_user_group_permissions ' || 
                                  
' where username = USER and table_name = '''||upper(object_name)||''' and permissions = ''READ'')' ;
      return 
lv_predicate;
   
end;

   function vpd_write (schema_name in varchar2object_name in varchar2)
   return 
varchar2
   is
       predicate varchar2
(1000) := '';
   
begin
      lv_predicate 
:= 'site_id in ( select site_id from vw_user_group_permissions '|| 
                                  
' where username = USER and table_name = '''||upper(object_name)||''' and permissions = ''READWRITE'')';
      return 
lv_predicate;
   
end;

end;
/

begin
dbms_rls
.add_policy (object_schema => 'MY_SCHEMA'function_schema=> 'MY_SCHEMA'
  
policy_name => 'VPD_READ'policy_function => 'VPD_POLICY.VPD_READ'
statement_types=>'select'object_name => 'PROJECTS') ;

dbms_rls.add_policy (object_schema => 'MY_SCHEMA'function_schema=> 'MY_SCHEMA'
  
policy_name => 'VPD_WRITE'policy_function => 'VPD_POLICY.VPD_WRITE'
statement_types => 'update, insert, delete'object_name => 'PROJECTS') ;

Now, to simplify, all the above statement does is essentially append a query to ANY statement the user makes against the tables which the policy is applied to.  For example, the “select * from projects” would be changed into “select * from projects where site_id in (select site_id from vw_user_group_permissions where username = ‘MCINTOSHJ’ and ……”.  This is done automatically, hidden from the user, and makes it so I don’t have to try and implement some sort of interface for all data access.  Users can use their own accounts to create ad-hoc reports on the data, the application itself doesn’t need to know the database security, etc.  Overall, a MUCH easier system to maintain.  The only thing I need to make sure of is that SITE_ID exists on any tables using this system – that SITE_ID is what splits apart the tables, in essence.

This is where some of the trickiness comes in and where I got nailed with a bug.  The above works FINE.  It works great, and I haven’t had any real problems with the way it’s separating data, implementing information, etc.  The only trick I’ve hit was when all of a sudden a procedure was failing to execute.  I received a “ORA-28113: policy predicate has error” message with very little details.  Checking the trace file on the server was equally sparse on information just giving me a line number for what was failing to execute.  Looking at the line, I saw this:


select from projects for update;

This is a rough approximation of course of what I saw, but I couldn’t understand why when everything thus far had been working, all of a sudden the above statement failed.  The policy objects were all working fine, as was my application doing updates, queries, etc. of the data.  Thus, the last three days of debugging the message, finding out why the procedure was failing to execute.  I was tearing my hair out, trying to figure out why the “select for update” was causing the policy to fail totally. 

Eventually, I worked it out to the string I was added to make sure the query only selected from a given site.  As listed above here’s the string – “site_id in (select site_id from vw_user…))” – what I discovered was that the sub query, the “select site_id from vw_user_groups” was what was causing the function to fail.  The “for update” clause apparently only works if you don’t have any sub queries.  Now, this isn’t tied to VPD necessarily, nor to the application or security I was implementing.  This is more of a SQL “bug” than anything else.  The trick is, I ONLY hit it once I implemented VPD’s,due to them adding the query to the select for update.  And, I didn’t hit the bug until I started running my procedures which did database updates.  It was moderately elusive, and a bit of a pain to figure out why things were going wrong – especially since the query DID work minus the for update, and I could also update the database using “update projects” statements.  It’s only the one specific case where I called select for update that things failed.

What did I learn out of this?  That sometimes it’s the obscure, oddball things that will nail you hard.  That Oracle VPD rocks, minus the debugging and complexity of implementing it effectively.  Still, it’s all working, finally, and I can get back to doing Java application code.


3 Comments for this entry

  • Rebecca

    I didn’t know Oracle uses Sequel…learn something new everyday I guess.  I have used Sequel with Access (*ahem* with Chris’ assistance) but was not aware of its Oracle application.

    UMR Alumni suggestion:

    Change ‘DoeJohn’,’Rolla_Admin’ to ‘MinerJoe’,’Rolla_Admin’

  • Jason McIntosh

    *grin* Actually, it’s SQL – Structured Query Language, not Sequel.  Sequel is an entirely different word Access uses a form of it, Oracle uses it’s own form – any database uses a form of SQL.  There’s a standard, SQL.v92 syntax that’s out, but every database does things a bit different to add features and such.

    Ok, I seriously don’t get the MinerJoe thing – a reference to the engineering side of Rolla or something else?

  • Rebecca

    The University of MO-Rolla was originally the Missouri School of Mines (MSM) and Joe Miner is the School mascot.  He carries a pick ax and a slide rule, I believe.

    Joe Miner represents the entire college.  When I began college, UMR was essentially an engineering school – Civil Engineering, Mechanical Engineering, etc.  Two chancellors later, additional non-science and engineering schools now exist.

    Guess I shouldn’t assume that all University of Missouri people would know the Rolla mascot.  It’s not exactly like the Mizzou Tiger that is advertised on I-70 via billboards and even has its own footprints on the entrance ramps into town

Looking for something?

Use the form below to search the site:

Still not finding what you're looking for? Drop a comment on a post or contact us so we can take care of it!

Blogroll

A few highly recommended websites...