Path: newssvr20.news.prodigy.com!newsmst01.news.prodigy.com!prodigy.com!pd2nf1so.cg.shawcable.net!residential.shaw.ca!sn-xit-03!sn-xit-01!sn-xit-09!supernews.com!postnews1.google.com!not-for-mail
From: ronald@intvelt.com (Ronald In 't Velt)
Newsgroups: comp.databases.oracle.server
Subject: Fine-grained Access Control and constraint violations
Date: 8 Aug 2003 04:46:37 -0700
Organization: http://groups.google.com/
Lines: 40
Message-ID: <945a3a61.0308080346.67708a83@posting.google.com>
NNTP-Posting-Host: 212.123.206.71
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
X-Trace: posting.google.com 1060343198 27564 127.0.0.1 (8 Aug 2003 11:46:38 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: 8 Aug 2003 11:46:38 GMT
Xref: newssvr20.news.prodigy.com comp.databases.oracle.server:240089

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?