Re: Fine-grained Access Control and constraint violations

Ronald In 't Velt wrote:

> Hi all,
>
> We have recently implemented fine-grained access control on our Oracle
> 8.1.7 database, to restrict users' access to data on a row-by-row
> basis according to their privileges. We encountered the following
> problem:
>
> Most tables have the following structure:
> [item id] [item name] [item data] .... [data]
>
> In some cases we want to prevent the use of duplicate names, when a
> new record is created or when an existing record is copied. Our
> current strategy is to look in the table for the supplied name, and
> add a suffix to the name if it already exists, so that NAME becomes
> NAME(1). We keep trying with NAME(2) etc. until we find a name that
> is not yet in use.
>
> However, with fine-grained access control in place, this mechanism
> fails. When a record is created or copied, the supplied name is
> verified only against those names that the current user can access.
> The procedure that checks the name is executed with definer rights,
> unfortunately those rights do no extend to the row-level security
> policies, as these are based on CURRENT_USER which is the actual
> logged-in user in all cases.
>
> Is there a good solution for this?
> One way to get around this is to use a constraint on the table, and
> keep trying to insert different names until there is no constraint
> violation. However, this seems a rather cluncky solution. Also, we
> cannot verify unique names up front, without trying an actual insert
> which is quite hard on some tables, as numerous constraints have to be
> satisfied and foreign keys supplied.
>
> In Oracle 9 I could grant EXEMPT ACCESS POLICY to the database owner
> and bypass row level security that way (because procedures are
> executed with definer rights). However, I am using Oracle 8.1.7
>
> Is there an easy/elegant way to let a particular function get around
> the fine grained access control?

From my read of your posting you have thrown out all of the rules of relational database theory taught in lesson one on databases.

If you want this to work you will need to trash your design and go back to doing things the way they were intended to be done in a relational database. I'd start with reading Cobb.

--
Daniel Morgan
http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)