Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Fine-grained access stuff and preventing wrong inserts/updates...

Re: Fine-grained access stuff and preventing wrong inserts/updates...

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Sat, 4 Feb 2006 12:10:41 +0000 (UTC)
Message-ID: <ds25k1$iam$1@nwrdmz01.dmz.ncs.ea.ibs-infra.bt.com>

"Spendius" <spendius_at_muchomail.com> wrote in message news:1138982163.512512.293120_at_g14g2000cwa.googlegroups.com...
> Hi,
> I've implemented a basic security policy: all tables of schema SSSS
> contain
> an AREA column numbered from 1 to 19. The app logins are of the form
> ASSS_01,
> ASSS_02, ... ASSS_19. I've used a context policy, AFTER LOGON ON
> DATABASE
> trigger and the usual stuff to filter the data that a certain login
> gets when he does
> selects (I add the clause "AREA = 1" if the current user is ASSS_01,
> "AREA = 11"
> if it is ASSS_11 etc.).
> I'd like to know if I can *prevent* the users to insert/update/delete
> in tables with
> AREA numbers different from that they're related to (ASSS_02 should
> only
> be able to insert data with AREA = 2, update a row only if AREA = 2,
> and
> delete from a table only where AREA = 2 as well of course):
> -do I have to implement these rules using triggers
> (=> CREATE TRIGGER x
> BEFORE INSERT ON t FOR EACH ROW
> BEGIN
> :new.area := substr(<current user name>, 6, 2) [etc etc.])
> for example to avoid inserting an erroneous value,
> -or should I play a little more subtly with DBMS_RLS, contexts and
> stuff to avoid
> wrong operations ?
>
> Thanks.
> Regards,
> Spendius
>

You can define the security profile to apply for select, insert, update, and delete using the statement_types parameter of add_policy - then the update_check will (I think) allow you to avoid the issue of being able to update data that doesn't match the policy.

However, if your area-code is how you are going to implement security, then you don't really need to use this stuff at all.

Create a logon trigger that sets the context for a user, e.g. with a call in the context procedure like:

 dbms_session.set_context(

      namespace => 'security_context,
      attribute => 'area_code',
      value => {your codes}

 );

context security_contexts, variable
area_code, then define a public view:

create private_view v_tableX as
select {all columns except area)
from tableX
where area = sys_context('security_context','area_code') with check option
;

then create your before-row trigger which populates the area on the table with the current value from the user's context.

So you get one view definition, which always shows the correct data for the user, one table trigger which always get the right data onto the table, and one logon trigger that sets the correct context for all users.

-- 
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
Cost Based Oracle: Fundamentals

http://www.jlcomp.demon.co.uk/appearances.html
Public Appearances - schedule updated 2nd Feb 2006
Received on Sat Feb 04 2006 - 06:10:41 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US