Re: Theoretical Question about Row Level Security.

>> 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. <<

This sounds like a job for VIEWs that are taken from a single table with a non-key column for "user class" codes that defines the views

>> I would appreciate comments on the pros and cons of putting row
level security into the application layer, particularly if there are any pros. <<

I cannot think of a single pro. A new application can walk right thru the rules and get to the base tables. It is a bitch to port or maintain. Security is clearly a business rule and should be at the schema level. etc.

>> 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): <<

I guess that the powers that be would not consider getting a better product. Like one which at least attempts to look like Standard SQL-92. Sorry for the rant, but Oracle really stinks. Let me try a little re-write into SQL and put in some code that might not be possible -- hey, ten years on the Standards Committee and I don't think any other (it's like asking an English teacher to speak in Ebonics).  

CREATE TABLE Systems
(system_id VARCHAR(8) NOT NULL PRIMARY KEY,  system_name VARCHAR(50) NOT NULL DEFAULT '??' --should have a default  );

CREATE TABLE Problems
(problem_id INTEGER NOT NULL PRIMARY KEY,  system_id VARCHAR(8) NOT NULL

           REFERENCES Systems(system_id)
           ON DELETE CASCADE
           ON UPDATE CASCADE,

CREATE VIEW A-Problems
AS SELECT *
     FROM Problems
    WHERE user_class = 'a'
   WITH CHECK OPTION; CREATE VIEW B-Problems
AS SELECT *
     FROM Problems
    WHERE user_class = 'b'
   WITH CHECK OPTION; etc.

Then grant access to the views but not the base table. Most programmers do not know about the WITH CHECK OPTION and all the tricks you can do with it, but you only need a simple one without nested views, etc.

>> What is important for my question is that this system allows one to
 file notes about a problem: <<

The model is wrong. A note cannot exist without a problem. Number the notes within each problem instead of giving them an independent existence. Likewise, the text of the note cannot exist outside of a note, so it is an attribute of a note:  

CREATE TABLE Notes
(problem_id INTEGER NOT NULL

            REFERENCES Problems(problem_id)
            ON DELETE CASCADE
            ON UPDATE CASCADE,

(By the way, "LONG RAW" sounds like something I would find websurfing porno sites...)

I leave it to you to transalte this back into Oracle-speak.

>> 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 <<

How can they have market share with a product that forces you to write bad code?

>> 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. <<

Drop those tables and put this into the DCL. As I recall, Oracle has ROLEs which will help. Poor little Data Control Language does not get the respect that DDL and DML do, but he can be very useful. Received on Mon Oct 07 2002 - 17:41:57 CEST