Re: Difficult problem with fine grain user access to table. Any ideas?

This looks like a job for 'fine grained access control', aka VPD or RLS.

Build a little table that equates userid with office

Create a context (set of environment variables)(

Create a packaged procedure to convert userid to office, and store the off in a variable in the context,

Create a logon trigger to call the packaged procedure

Create a function taking to generate a predicate of the form:

    'where office is null or office =
sys_context('office_context','office_name')'

Use dbms_rls to create a policy that assigns the function to the table for insert, update, and delete. Optionally use the 'valid => true' setting for the policy so that no-one can update a row from their office to someone else's office.

Job done. (I think, unless I've miss a couple of bits).

Every time the table is accessed for insert, update, or delete (in any SQL, not just simple one-table stuff), the tablename will be replace by an inline view that includes the predicate that restricts the table so that it appears to have only the data for the user's office, or where the office is blank.

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Next Seminar - UK, April 3rd - 5th
http://www.jlcomp.demon.co.uk/seminar.html

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

Author of:
Practical Oracle 8i: Building Efficient Databases


julio wrote in message <3c8d1ac9$0$77529$45beb828_at_newscene.com>...


>

>We have a table emergency_responders, with columns

>

>empnum

>emer_role

>date

>office

>

>the office column can have 7 different values (Dallas, Chicago, ..,

Detroit)


>or null.

>

>We want to give access as follows:

>Create 7 accounts - 1 for Dallas, 1 for Chicago, 1 for ...:

>each has full read access but write access only if the office column value

is


>blank or is the same as the city the acccount is for.

>

>So for example a row with an office of Dallas, can be read by all accounts

but


>only updated or deleted by the account set up for Dallas.

>

>A row with an office of null, can be read by all and updated or deleted by

>anybody

>

>

>Any idea of how this can be done.  Note that access might also be thru a

view


>to this table unless its impossible.

>

>we are using Oracle 8i version 8.1.6