Re: Fine-grained Access Control and constraint violations

In this foul year of our lord Sat, 09 Aug 2003 07:06:19 +1000, quarkman <quarkman_at_myrealbox.com> proclaimed:

>On Fri, 08 Aug 2003 22:10:55 +0200, Jared of Europa
><jared_at_intnospamvelt.com> wrote:
>
>> In this foul year of our lord Fri, 08 Aug 2003 22:36:17 +1000,
>> quarkman <quarkman_at_myrealbox.com> proclaimed:
>>
>>> Not sure why you'd think using a unique constraint is 'clunky'.
>>
>> Not the constraint per sé is clunky, but having to attempt to insert a
>> name (along with valid values for other constrained fields and foreign
>> keys) to see if the name is unique. In some cases we'd want to verify
>> uniqueness of the name without actually doing the insert.
>>
>
>Without doing the insert presumably means 'by visual inspection'.
>
>Impossible. You're (ie, your client is) trying to square the circle here.
>You don't want them to see every row. But you want them to be able to
>visually inspect for possible duplicates before entering data. I can't see
>how you can ever hope to satisfy both requirements.

Not quite... The user will not visually inspect each row to check for duplicates. We want a function to do that (just like the current design has). The user cannot see all records, but he can call the function, pass it a name, and get a unique name in return (the same name, or the name with a counter tacked on if the name already exists).

The idea is that the user calls a trusted function. The data is still secure from the user, but the user can check for uniqueness against all records. Unfortunately Oracle 8i does not support the notion of trusted functions in combination with fgac.

>> The ID column is the primary key and is what other tables use as FK.
>> The constraint on the name is something that is subject to change in
>> the future... for example, enforce a unique name only per department,
>> per user group, or even allow duplicates. It is not a suitable
>> primary key.
>
>On its own, obviously not. But in combination with these other factors,
>probably it is or should be.

The problem is that it's a changing requirement. I figure we best stick with the existing ID as our PK, and use a constraint to enforce the uniqueness. If (more like 'when') they decide that the new constraint should be name + department, I do not want to be the one to have to change the PK and all FKs to this new combination.

>I let myself be persuaded once (by a lawyer, so there were mitigating
>circumstances) that the catchwords he uses to categorise his legal cases
>should not be the primary key of the catchwords table, but instead to use a
>sequence. That database is now a total mess, with at the last count 51
>different occurences of 'NEGLIGENCE', for example; and 126 occurences of
>'TORT'. When he wants that data cleansed, I am going to make a fortune.

*grins* Looks like you know the secret to being a well-fed consultant. ;-)
-*-
Ronald In 't Velt Received on Fri Aug 08 2003 - 16:56:06 CDT