Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Fine-grained access stuff and preventing wrong inserts/updates...
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
Thanks.
Regards,
Spendius
Received on Fri Feb 03 2006 - 09:56:03 CST