I'm using Interbase 6.0.0.627
CURRENT_ROLE doesn't work. Are you using Firebird?

----- Original Message -----
From: <hans@...>
To: <ib-support@yahoogroups.com>
Sent: Saturday, February 09, 2002 8:55 PM
Subject: Re: [ib-support] Role/permission question


> Jörg Schiemann wrote:
> >
> > I've tried that.
> > Here are the grants which the procedure got.
> > GRANT SELECT, UPDATE, REFERENCES ON DETAIL TO PROCEDURE P_EDIT_COMMENT;
> > and execute grants to role of course.
> >
> > It's no problem with the detail table. The problem is the master table.
> > There are 2 triggers in the detail table CHECK_4 and CHECK_5 which I did
not
> > created. I think
> > they have something to do with the foreign key. Thes two triggers are
the
> > only triggers who fires if I give the role select and update access to
the
> > master table.
> >
> > It doesn't help to set grants on the master table to procedure
> > p_edit_comment
> >
> > I got ISC Error No: 335544352: no permission for read/select access to
table
> > master.
> >
> > If I grant the role select and update on the master table all works, but
the
> > role may not have this kind of access to the master table.
> >
> > /Jörg
> >
> > ----- Original Message -----
> > From: "Martijn Tonies" <m.tonies@...>
> > To: <ib-support@yahoogroups.com>
> > Sent: Saturday, February 09, 2002 7:43 PM
> > Subject: Re: [ib-support] Role/permission question
> >
> > > Hi,
> > >
> > > you can also grant the procedure/trigger rights to the tables.
> > >
> > > Martijn Tonies
> > > InterBase Workbench - the developer tool for InterBase and Firebird
> > > http://www.interbaseworkbench.com
> > >
> > > Upscene Productions
> > > http://www.upscene.com
> > >
> > > "This is an object-oriented system.
> > > If we change anything, the users object."
> > >
> > >
> > >
> > >
> > >
> > > Could you explain a bit more?
> > >
> > > This role can't even select the master and the detail table.
> > > Therefore I created a view with these two fields and the key with
select,
> > > update and reference rights.
> > > But now I get the error message: No read/write access to master table.
> > > I think that's because of the foreign key.
> > >
> > > I also tried with a SP, but it's the same here.
> > >
> > > /Jörg
> > >
> > > ----- Original Message -----
> > > From: <hans@...>
> > > To: <ib-support@yahoogroups.com>
> > > Sent: Saturday, February 09, 2002 7:19 PM
> > > Subject: Re: [ib-support] Role/permission question
> > >
> > >
> > > > Simplest might be to use a 'before update trigger'
> Maybe something like:
>
> CTREATE TRIGGER TABLE_BEFORE_INSERT
> BEFORE INSERT
> POSITION 0
> AS
> BEGIN
> IF (CURRENT_ROLE <> 'MYROLE') THEN
> BEGIN
> IF (NEW.VALUE1 <> OLD.VALUE1) THEN
> NEW.VALUE1 = OLD.VALUE1;
>
> IF (NEW.VALUE2 <> OLD.VALUE2) THEN
> NEW.VALUE2 = OLD.VALUE2;
> END
> END
>
> or
> IF (NEW.VALUE1 <> OLD.VALUE1) THEN
> EXCEPTION DONT_TOUCH_ME;
>
> if VALUE1 can be NULL, Add code to take care of
> that condition.
> ===
> > > >
> > > > ===
> > > > Jörg Schiemann wrote:
> > > > >
> > > > > Hi,
> > > > >
> > > > > how can I accomplis that a role can only update two fields in a
detail
> > > > > table?
> > > > > It seems to be a problem with the foreign key.
> > > > >
> > > > > Thanks in advance
> > > > >
> > > > > Jörg Schiemann