Re: Fine-grained Access Control and constraint violations

On 8 Aug 2003 04:46:37 -0700, Ronald In 't Velt <ronald_at_intvelt.com> 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?

Not sure why you'd think using a unique constraint is 'clunky'.

What's clunky is the way you've designed this. If uniqueness of names is that important to you, create a sequence, and append the sequence to whatever name the user enters, regardless of whether it's the first instance of that name or not.

Either that, or think things through: what's the primary key on this table (I'd have thought it was the ID column)? If that's the primary key, then what does it matter if a name duplicates? And if it *does* matter that the name duplicates, then ID is not the primary key (or shouldn't be), but name itself should be (at least part of) the primary key.

Fix the design, not hunt around for workarounds that really will be clunky.

~QM Received on Fri Aug 08 2003 - 07:36:17 CDT