Re: Fine-grained Access Control and constraint violations

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.

>> 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.
>
> Sure... but users will not accept that they are assigned a name that
> they did not enter. If they enter 'test', they expect to see 'test',
> not 'test23'. Only when a user copies a record or creates one using a
> name that is in use should he get 'test (2)'. They understand and
> accept that (especially since that's how it currently works)
>
>> 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.
>
> 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.

>
> Duplicate names can be confusing: users could inadvertedly open the
> wrong case in the application. You can be sure that many users will
> create a case called 'test'.... all in different departments, so they
> will not see each other's cases. But there are users who can see
> cases from multiple departments, and they will get confused. And even
> within one department this can happen. (Security is per user, not per
> department).
>
>> Fix the design, not hunt around for workarounds that really will be
>> clunky.
>
> No can do... I'm supposed to just add the security to an existing
> design. A redesign would be nice but out of the question.
>
> Ronald In 't Velt
>
> Jared of Europa
> -*-
> "I have often deprived myself of the necessities of life,
> but I have never consented to give up a luxury."

Well, you're up a certain creek without a propulsion mechanism then. If it was me, I'd tell the client that his request is not do-able in 8i. His options are upgrade to 9i, and allow the security exemption idea you've floated (which I don't like, but no matter), or tell him (and you'd be doing him a favour) that his logical design stinks and needs to be re- visited.

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.

~QM Received on Fri Aug 08 2003 - 16:06:19 CDT