Thread: 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 ---------------------------------------------------------------