Re: Theoretical Question about Row Level Security.

--CELKO-- wrote:
>
> >> 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

The difficulty is that this system has over 17,000 users and 70,000 'problems'. I was about to say that keeping track of the view definitions would be an intractable problem. Then I decided to reread your comment, and saw that you were going to use a table to help manage it. However, there is still the practical problem that these views would have to be created dynamically from a web application, and doing (what Oracle calls) DDL in a stored procedure is not a Good Thing in Oracle.

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

My feelings exactly. Since my boss may force me into doing the opposite, I was hoping there were some reasons out there, just to salve my guilty conscience.  

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

And I've been accused of not being a team player because I refuse to call predicates filters, rows records, or columns fields.

But, the only potential alternative to Oracle (a year down the road) is DB2. Last time I looked at it, it wasn't any better. Have things changed?  

> 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,
> problem_desc VARCHAR(40) NOT NULL DEFAULT '{unknown}',
> user_class CHAR(2) NOT NULL DEFAULT 'a'
> CHECK (user_class IN ('a', 'b', 'c'..))
> );
>
> 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.

Agreed that 'WITH CHECK OPTION' is greatly underused in real-world (meaning Big Money) applications.  

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

This is what I was beginning to suspect as I composed this note. The original post actually took me two days to write, as I was trying to describe a feature of the system which did not appear to make this mistake. What I posted was the most defensible example I could find.  

> CREATE TABLE Notes
> (problem_id INTEGER NOT NULL
> REFERENCES Problems(problem_id)
> ON DELETE CASCADE
> ON UPDATE CASCADE,
> note_nbr INTEGER NOT NULL,
> note_text VARCHAR(512) NOT NULL,
> PRIMARY KEY (problem_id, note_nbr));
> (By the way, "LONG RAW" sounds like something I would find websurfing
> porno sites...)

It was Oracle's first stab at a BLOB. And it has nothing to do with porno. S&M maybe (especially if you are the DBA).  

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

Honest question here: of the vendors with market share (I suppose that would be DB2, Oracle, SQL Server, Sybase, Informix, mysql and Postgres), which ones do not force 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.

Thank you for reminding me that there was such a thing as DCL. OracleSpeak does not use the term (probably because Digital had already co-opted it for their use).

-- 
Phil Singer                |    psinger1_at_chartermi.net
Oracle DBA

Go Wings!!!!!!!