Re: Row Level Security in Oracle ???

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1998/01/24
Message-ID: <34d043db.40698060_at_192.86.155.100>#1/1

On Sat, 24 Jan 1998 16:31:43 GMT, cr_at_post.dk (Christian Roig) wrote:

>Could anyone please point me to tools that will allow me to grant/deny Row Level
>access to information stored in an Oracle database ?!?!
>
>TIA - Christian
>
>
>*-*-* Disclaimer *-*-*
>The Questions, Answers, Opinions etc. stated in this
>message are my own !

the only real tools (besides Trusted Oracle, with is a B1 multi level secure implementation of Oracle and requires specialized operating systems) are views and cursor variables.

these views can get pretty complex, here is an example or two:

create view empview
as
select * from emp
 where ename = USER

    or mgr = ( select empno from emp where ename = USER ) with check option
/

This view restricts the rows someone can see to be just their record or the records of anyone that works for them. the with check option makes it impossible for them to create a record that they cannot see (eg: they can only create their record or records for people that work for them, they cannot create records for people for work for someone else).

If your logic for including or excluding a row is very complex, you could do something like the following. lets assume there are two columns in the table you want to look at. If both columns are NULL, everyone can see the row. If either is not NULL, some complex logic should be invoked to return 'true' or 'false' as to whether the row should be viewed or not. You could:

create function check_values( a in varchar2, b in varchar2 ) return number as
begin

    ..... whatever ....
    return 0 to deny access, 1 to allow access. end;
/

create view empview
as
select * from emp
 where decode( a||b, NULL, 1, check_values(a,b) ) = 1 /

Now this view will look at a and b, if they are NULL, return 1, else return check_values(a,b) which is whatever logic you want (you write it).

Another option, available in 7.2 and up, is cursor variables. You can use these sort of like views. It would work like this:

create or replace package types
as

    type curs is ref cursor;
end types;
/

create or replace procedure show_cursor( p_cursor in out types.curs,

                                         p_input  in     varchar2 default NULL)

as
begin

    if ( user = 'TKYTE' ) then

        open p_cursor for
          select *
            from emp
           where ename like upper(p_input);
    else
        open p_cursor for
          select *
            from emp
           where ename like upper(p_input)
             and (   ename = USER
                  or mgr = ( select empno from emp where ename = USER )
                 );

    end if;
end;
/

variable C refcursor
exec show_cursor( p_cursor => :C, p_input => '%' ) print C

---

So, you have some procedural logic that will determine what query a user will actually get to use. The stored procedure decides how to open up the cursor, not the end user or the application.

hope this helps...       

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD  

http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Sat Jan 24 1998 - 00:00:00 CET