Theoretical Question about Row Level Security.

From: Phil Singer <psinger1_at_chartermi.invalid>
Date: Sun, 06 Oct 2002 16:55:08 -0400
Message-ID: <3DA0A32C.95977A8_at_chartermi.invalid>

Hello, Database Experts:

I am starting to work with an existing database. Access to individual rows in the tables are supposed to be restricted to certain users. This 'row level security' is currently implemented thru ASP code in the web server. The plan is to introduce more complexity to the security model, and that is why I am being dragged into this.

My prejudice is to try to put as much of this at possible (hopefully 100%) into the database engine. My reasons are (1) that this makes all applications, not just the one for which the security was originally writen, secure; (2) The type of declarations needed are usually easier to understand than what is in the ASP code, (3) the implementation should be simplier, and (4) it should perform better (for equal work). I realize that the database engine may not be able to implement the particular security model desired, in which case one has no choice but to stick the security into the application layer. I would appreciate comments on the pros and cons of putting row level security into the application layer, particularly if there are any pros.

But I do have a more specific question, which can best be asked in reference to some tables. The following tables illustrate my problem (note that this is in Oracle, so I am using somewhat Oracle specific DDL):

CREATE TABLE SYSTEM (
   system_id varchar2(8) not null,
   system_name varchar2(50),
   primary key (system_id)

                     )

CREATE TABLE PROBLEM (
   problem_id number not null,
   system_id varchar2(8) not null

      references SYSTEM,
   problem_desc varchar2(40) not null,
   primary key (problem_id)

)

As you may have guessed, this is a problem tracking system. Users of various systems can log problems into the system. There are the usual business rules about who can view what problems. What is important for my question is that this system allows one to file notes about a problem:

   CREATE TABLE NOTES (

       note_id  number not null,
       problem_id  number references PROBLEM,
       primary key (note_id)


                       )

 
   CREATE TABLE NOTE_FILE  (
       note_id  number not null references NOTES,
       note_text LONG RAW not null,
       primary key (note_id)
                            )

As you can see, these notes are stuck in their own table, and associated back to the PROBLEM table thru NOTES. If you wonder why we have 2 tables, it is because this system was originally developed in Oracle 7.3, and there that was the best way to do it (and my boss learned Oracle back at release 5, and is very uncomfortable doing anything that would not have worked in release 6).

There is another table (tables really) which contain the information needed to specify who can see what. For this discussion, please assume that they are defined perfectly, and we can get whatever we need from them.

Anyhow, we now have Oracle 8i, and it has an internal row level security mechanism. It works by internally adding a predicate to any query against a specified table. Since the security will be based on systems and problems, I can easily specify a predicate for the SYSTEM table and the PROBLEM table. I might even be able to specify a suitable predicate involving problem_id for the NOTES table. But, there is nothing on the NOTE_FILE table I can use.

My question is (and I think this is really a matter of theory rather than implementation): If one cannot tell if a given user is allowed to access a row purely from the attributes of that row, is that a signal that the table has not been defined correctly? In other words, I need to do several joins to do row level security (of the Oracle 8i flavor) on the NOTE_FILE table. Does this imply that my table design is wrong? Or, is this expected in the general case, and all this really means is that Oracle 8i does not have a perfect implementation of row level security?

thank you for plowing through all of this.

---

Phil Singer                |    psinger1ATchartermiDOTnet

Oracle DBA
Make the obvious changes to reply. Received on Sun Oct 06 2002 - 22:55:08 CEST