| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: dbms_rls and 815E on NT [LONG]
Jonathan Lewis wrote:
>
> Just to add the icing (dream topping, etc).
>
> If you are using the an RLS function that
> generates a predicate of the form:
> dept_id = sys_context('application_name','legal_department')
>
> and a logon trigger that uses
> dbms_session.set_context()
> to set the context for the user, then your trigger can
> read
> >create trigger my_trigger
> >before insert on T for each row
> >begin
> > :new.dept_id := sys_context('application_name','legal_department')
> >end;
> >/
>
> (at least I assume it should be able to,
> I haven't tested it yet).
>
> --
>
> Jonathan Lewis
>
> Thomas Kyte wrote in message <5lee8s4gcp9vhrmce1ilelhocdhi82krfh_at_4ax.com>...
>
> >you use dbms_rls to restrict access to data.
> >
> >You can use a trigger to supply non-defaultable values.
> >
> >You would code:
> >
> >create trigger my_trigger
> >before insert on T for each row
> >begin
> > :new.dept_id := Your_Function_That_Returns_The_Correct_id_For_This_User;
> >end;
> >/
Hmm - a certain mixture of terms here. In fact, you don't need RLS to do this at all. An application context variable is all that is required. Then this variable can be set when the user logons, and then referenced in the insert trigger - this saves the function (and potential lookup) from being executed for each insert operation. The application context variable can then also be used in the RLS returned predicate. See below for a longish example
First we will create an application context. To do this we give our context owner (in this case Scott) the create context privilege. Typically you would have a dedicated schema owner for application contexts.
SQL> connect system/manager_at_oe
Connected.
SQL> grant create any context to scott;
Grant succeeded.
Now create the context and the associated package that sets a variable in the context - in this case, the users DEPTNO
SQL> connect scott/tiger_at_oe
Connected.
SQL> create context scott using scott.context_package;
Context created.
SQL> create or replace package context_package as
2 procedure set_context;
3 end;
4 /
Package created.
SQL> create or replace package body context_package is
2 procedure set_context is
3 this_employees_deptno number;
4 begin
5 select deptno into this_employees_deptno from emp
6 where ename = sys_context('userenv','session_user');
7
dbms_session.set_context('scott','deptno',this_employees_deptno);
8 exception
9 when no_data_found then
10 dbms_session.set_context('scott','deptno',0);
11 end set_context;
Package body created.
So we have created a context variable called DEPTNO, that is set whenever the set_context procedure is called. If we find the user that is logging on in the EMP table, we look up and set DEPTNO to the corresponding deptno in the emp table. If the logging on user is not found in the table, we simply set DEPTNO to a 'invalid' value - in this case 0.
We only want to set DEPTNO once for each user, so we use logon triggers to call and set the DEPTNO for the user that is logging on.
SQL> connect system/manager_at_oe
Connected.
SQL> create or replace trigger scott.set_application_context
2 after logon on database
3 begin
4 scott.context_package.set_context;
5 end;
6 /
Trigger created.
Now let's test this as the user scott.
SQL> connect scott/tiger_at_oe
Connected.
SQL> select sys_context('scott','deptno') from dual;
SYS_CONTEXT('SCOTT','DEPTNO')
Cool. Lets complete the rest of the example by building a test table to attach the insert triggers to.
SQL> create table test_insert as select ename,deptno from emp where 1=2;
Table created.
SQL> desc test_insert;
Name Null? Type ----------------------------------------------------- -------- -------------- ENAME VARCHAR2(10) DEPTNONUMBER(2) In the insert trigger I can refer to the DEPTNO application context variable, which is now automatically set each time a user logs on. I don't have to look up this user each time I do an insert - we have stored the relevant DEPTNO for this user once in this session. Real good for scalability
SQL> create or replace trigger insert_trigger
2 before insert on test_insert for each row
3 begin
4 :new.deptno := sys_context('scott','deptno');
5 end;
6 /
Trigger created.
SQL> insert into test_insert values('fred',null);
1 row created.
SQL> insert into test_insert values('barney',0);
1 row created.
SQL> insert into test_insert values('wilma',30);
1 row created.
SQL> select * from test_insert;
ENAME DEPTNO
---------- ---------
fred 20 barney 20 wilma 20
All working correctly. Now lets try it for a user that is not in the EMP table
SQL> connect system/manager_at_oe
Connected.
SQL> select * from scott.test_insert;
ENAME DEPTNO
---------- ---------
fred 20 barney 20 wilma 20
SQL> insert into scott.test_insert values('fred',null)
1 row created.
SQL> insert into scott.test_insert values('barney',0);
1 row created.
SQL> insert into scott.test_insert values('wilma',30);
1 row created.
SQL> select * from scott.test_insert;
ENAME DEPTNO
---------- ---------
fred 20 barney 20 wilma 20 fred 0 barney 0 wilma 0
6 rows selected.
These inserts get the 'invalid' deptno.
If I want to prevent inserts of invalid details altogether, I can then create a security policy that prevents the user from inserting a value that does not match their corresponding DEPTNO in their application context.
First I create a security package that returns the perdicate I want used
SQL> connect scott/tiger_at_oe
Connected.
SQL> create or replace package security_package as
2 function insert_security(owner varchar2, objname varchar2) return
varchar2;
3 function select_security(owner varchar2, objname varchar2) return
varchar2;
4 end security_package;
5 /
Package created.
SQL> create or replace package body security_package as
2 function insert_security(owner varchar2, objname varchar2) return
varchar2 is
3 begin
4 return 'DEPTNO<>0';
5 end insert_security;
6 function select_security(owner varchar2, objname varchar2) return
varchar2 is
7 begin
8 return 'DEPTNO=sys_context(''scott'',''deptno'')';
9 end select_security;
10 end security_package;
11 /
Package body created.
Package body created.
Not that I am only allowing inserts of a deptno value other the 0. However, I am allowing the user to select any rows that match their store DEPTNO. Referring to the context variable here is better than having the predicate hard code a deptno value - as all users can share the same SQL statement. Way good for scalability.
Now we simply use the DBMS_RLS functions to apply these security policies to my target table
SQL> connect system/manager_at_oe
Connected.
SQL> begin
2 dbms_rls.add_policy('scott','test_insert','insert_security',
3
'scott','security_package.insert_security','insert',true);
4 dbms_rls.add_policy('scott','test_insert','select_security',
5
'scott','security_package.select_security','select');
6 end;
7 /
PL/SQL procedure successfully completed.
Now test the new select and insert policies. If you remember, here's the current data in the table
ENAME DEPTNO
---------- ---------
fred 20 barney 20 wilma 20 fred 0 barney 0 wilma 0
As user scott, I can only see the following
SQL> connect scott/tiger_at_oe
Connected.
SQL> select * from test_insert;
ENAME DEPTNO
---------- ---------
fred 20 barney 20 wilma 20
As another 'non valid' user, I can only see the following
SQL> connect system/manager_at_oe
Connected.
SQL> select * from scott.test_insert;
ENAME DEPTNO
---------- ---------
fred 0 barney 0 wilma 0
Now lets try the insert
SQL> connect scott/tiger_at_oe
Connected.
SQL> insert into test_insert values('bam bam',null);
1 row created.
SQL> select * from test_insert;
ENAME DEPTNO
---------- ---------
fred 20 barney 20 wilma 20 bam bam 20
SQL> connect system/manager_at_oe
Connected.
SQL> insert into scott.test_insert values('bam bam',null);
insert into scott.test_insert values('bam bam',null)
*
SQL> select * from scott.test_insert;
ENAME DEPTNO
---------- ---------
fred 0 barney 0 wilma 0
As this user is not in the emp table, their DEPTNo value is always going to be 0. And the security polciy is specifcally preventing inserts of colummns with a value of zero. Hence only vaid employees can insert data, and their correct deptno is always used. There is no way to cirumvent this at all. No matter what application or end user tool the
user is using. Cooooooooooooooool !!
--
Regards,
Mark Townsend
Senior Product Manager Ph: (650) 633 5764 Server Division Fx: (650) 506 7222 Oracle Corporation Email: mtownsen_at_us.oracle.comReceived on Thu Jan 20 2000 - 16:16:33 CST
 |
 |