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 -> Fine-grained access stuff and preventing wrong inserts/updates...

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

From: Spendius <spendius_at_muchomail.com>
Date: 3 Feb 2006 07:56:03 -0800
Message-ID: <1138982163.512512.293120@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 Received on Fri Feb 03 2006 - 09:56:03 CST

Original text of this message

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