Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Fine-grained access stuff and preventing wrong inserts/updates...
"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 2006Received on Sat Feb 04 2006 - 06:10:41 CST