Path: newssvr20.news.prodigy.com!newsmst01.news.prodigy.com!prodigy.com!prodigy.com!rip!chi1.webusenet.com!news.webusenet.com!newsfeed-east.nntpserver.com!nntpserver.com!news1.optus.net.au!optus!spool01.syd.optusnet.com.au!spool.optusnet.com.au!not-for-mail
Newsgroups: comp.databases.oracle.server
Subject: Re: Fine-grained Access Control and constraint violations
References: <945a3a61.0308080346.67708a83@posting.google.com>
Message-ID: <oprtk6mrzvzkogxn@haydn>
Content-Type: text/plain; charset=iso-8859-15; format=flowed
From: quarkman <quarkman@myrealbox.com>
MIME-Version: 1.0
Date: Fri, 08 Aug 2003 22:36:17 +1000
User-Agent: Opera7.11/Win32 M2 build 2880
Lines: 60
NNTP-Posting-Host: 203.164.6.33
X-Trace: 1060346246  14560 203.164.6.33
Xref: newssvr20.news.prodigy.com comp.databases.oracle.server:240093

On 8 Aug 2003 04:46:37 -0700, Ronald In 't Velt <ronald@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