Re: Newbie : Row constraint?

create a view on the table and add:
  where locationcode in (select locationcode from userlocation where userid = user )
  Grant Select,insert,update,delete on this view to user1, and user2

For the superUser, he can access the base table directly and update anything (all rows). Of course, this will fall apart if the user doesn't get all privileges for each location. I think this can be done a lot easier in 8i, but don't know the details yet.

Russell England <russ_at_css2.com> wrote in message news:Jxst5.6884$yG4.578420_at_news1.cableinet.net...
> I have a table with different locations.
> Some users can only update/delete/insert rows for their location.
> Some users can update rows for any location.
>
> So I have created a table called userlocation with the userid and
> locationcode:
> userid locationcode allowupdate allowinsert
 allowdelete
> ------ ------------- ------------ ----------- --------
 --
> -
> user1 location1 1 1
> 1
> user1 location2 1 1
> 1
> user2 location1 0 0
> 0
> user2 location2 1 1
> 1
>
> Then before performing an update, I check to see if the user can update
> records for that location:
> select count(*) as mycount from userlocation where userid=xxx and
> locationcode=yyy and allowupdate <> 0
>
> But I wondered if it was possible to achieve the same with a row
 constraint?
>
> This location table also serves as a 'parent table. So there would be
 other
> 'child' tables that would need to link to the userlocation table to check
 if
> the record could be updated.
>
> Thanks in advance
>
> Russ
>
>
Received on Thu Sep 07 2000 - 15:14:35 CDT