Re: Row level secrity

From: Thomas J Kyte <tkyte_at_us.oracle.com>
Date: 1995/06/20
Message-ID: <3s7kol$lfo_at_inet-nntp-gw-1.us.oracle.com>#1/1


whitney_at_en.com (Nancy C. Whitney) wrote:
>
>
>I would like to achieve row-level security on a per-user basis. I got a
>quote for Trusted Oracle, but at $60,000 (for our site with the number of
>user we have according to a sales rep at Oracle :-( ) I decided to see
>what others are doing. Any ideas?
>
>Regards,
>
>Nancy
>

Well, not only would you have to get Trusted Oracle, but you would have to port to another OS. Whatever OS you are running on now does not support MAC controls like you are asking for. I think you would find the hardware/software (OS and such) even more costly.

You can use before INSERT or UPDATE or DELETE for each row triggers to enforce whatever rowlevel policy you want for those operations.

For SELECT what you can do is..... Simulate a row level trigger on the table:

1.) create a stored function that performs a row level check. For example, I will create one for the SCOTT.EMP table:

  • This function will return 'false' if the user is XXX and salary
  • is greater then $5,000. It will return 'false' if the user
  • is YYY and salary is greater then $6,000. It will return true
  • otherwise create or replace function emp_row_check( sal in number ) return number as begin if ( user = 'XXX' and sal > 5000 ) then return 0; -- FALSE elsif ( user = 'YYY' and sal > 6000 ) then return 0; -- FALSE else return 1; end if; end; /

2.) create a view on the emp table as such:

create or replace view empv
as
select * from emp
where emp_row_check(sal) = 1
/

3.) do not grant anything on the EMP table to anybody. Grant all priveleges on the VIEW. This view can be inserted into or whatever.


I will not promise 'stellar' performance. You will of course take a performance hit. One very easy way to increase the performance of the above is to short circut the evaluation before it gets to the pl/sql code. For example, in the above code, if the sal column is < 5000, the PL/SQL function *never* needs to be called. In that case you can rewrite the view as such:

create or replace view empv
as
select * from emp
where decode( sign( nvl(sal,0)-5000 ), -1, 1, emp_row_check(sal) ) = 1 /

The decode will return 1 immediately for all sal's < 5000 (or null sal's), it will invoke the pl/sql function only when needed.

You can use ALTER SESSION SET SQL_TRACE=TRUE and check out the trace file to verify that the pl/sql only gets called when the sal>=5000. This will dramatically increase performance if many rows are <5000.

Hope this helps.

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government Received on Tue Jun 20 1995 - 00:00:00 CEST

Original text of this message