Fine-grained Access Control and constraint violations

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? Received on Fri Aug 08 2003 - 06:46:37 CDT