Обсуждение: Referential integrity question

Hi all,

Is this normal? - I have discovered the hard way that permissions
GRANTed to a user on one table influence that user's ability to
update another table in a foreign key relationship with it. To be
specific, here are the relevant bits of the two tables:

create table usertypes (
   typecode      varchar(16) primary key,
   description   varchar(64),
);

grant select on usertypes to webanon;

create table users (
   uid   varchar(16)      primary key,
   pwd   varchar(16),
   typecode   varchar(16) references usertypes
                          on update cascade
);

grant select,insert,update,delete on users to webanon;

Now, when user webanon tries to update a field in table "users", the
update doesn't happen.....however, it DOES happen if I grant update
permissions to webanon on table "usertypes".

Any comments welcome! - Many thanks.

--Ray.


----------------------------------------------------------------------
Raymond O'Donnell                         http://www.iol.ie/~rod/organ
rod@iol.ie (or rod@gti.ie)                   The Irish Pipe Organ Page
http://www.iol.ie/~rod
----------------------------------------------------------------------

This is a known problem with 7.0.x's ri implementation
(for a complete explanation check the archives), it
should be fixed in 7.1 due to go into beta soon.

Stephan Szabo
sszabo@bigpanda.com

On Sun, 5 Nov 2000, Raymond O'Donnell wrote:

> Hi all,
>
> Is this normal? - I have discovered the hard way that permissions
> GRANTed to a user on one table influence that user's ability to
> update another table in a foreign key relationship with it. To be
> specific, here are the relevant bits of the two tables:
>
> create table usertypes (
>    typecode      varchar(16) primary key,
>    description   varchar(64),
> );
>
> grant select on usertypes to webanon;
>
> create table users (
>    uid   varchar(16)      primary key,
>    pwd   varchar(16),
>    typecode   varchar(16) references usertypes
>                           on update cascade
> );
>
> grant select,insert,update,delete on users to webanon;
>
> Now, when user webanon tries to update a field in table "users", the
> update doesn't happen.....however, it DOES happen if I grant update
> permissions to webanon on table "usertypes".

In <200011051615.QAA78279@mail.iol.ie>, on 11/05/00
   at 10:12 AM, "Raymond O'Donnell" <rod@iol.ie> said:

>Hi all,

>Is this normal? - I have discovered the hard way that permissions
>GRANTed to a user on one table influence that user's ability to  update
>another table in a foreign key relationship with it. To be  specific,
>here are the relevant bits of the two tables:

>create table usertypes (
>   typecode      varchar(16) primary key,
>   description   varchar(64),
>);

>grant select on usertypes to webanon;

>create table users (
>   uid   varchar(16)      primary key,
>   pwd   varchar(16),
>   typecode   varchar(16) references usertypes
>                          on update cascade
>);

>grant select,insert,update,delete on users to webanon;

>Now, when user webanon tries to update a field in table "users", the
>update doesn't happen.....however, it DOES happen if I grant update
>permissions to webanon on table "usertypes".

>Any comments welcome! - Many thanks.

If I am looking at this you wouldn't want webanon to be able to update
table "usertypes". Table "usertypes" is a lookup table and as such should
not be modified by any entries in table "users".

How do you have the relationship between the two tables set up?

--
---------------------------------------------------------------
William H. Geiger III      http://www.openpgp.net
Geiger Consulting

Data Security & Cryptology Consulting
Programming, Networking, Analysis

PGP for OS/2:               http://www.openpgp.net/pgp.html
E-Secure:                   http://www.openpgp.net/esecure.html
---------------------------------------------------------------