PenWin schrieb am 17.05.2010 um 11:33:00 (+0200):

> This has been working without any issues for some time, until I
> updated the database with some logging triggers. The sequence that
> used to work was:
>
> 1) User INTERNET calls procedure INTERNET_INSERT_ROW.
> 2) Procedure INTERNET_INSERT_ROW validates data and writes it to
> SOMETABLE.
>
> Now with the loggins tables, I have this sequence:
>
> 1) User INTERNET calls procedure INTERNET_INSERT_ROW.
> 2) Procedure INTERNET_INSERT_ROW validates data and writes it to
> SOMETABLE.
> 3) AFTER INSERT trigger LOGTRIGGER_SOMETABLE fires.
> 4) LOGTRIGGER_SOMETABLE writes the new record from SOMETABLE to
> LOGTABLE.
>
> Unfortunately, at step #4 I am getting an error no permission for
> insert/write access to TABLE LOGTABLE, and after a number of failed
> attempts, I am at a loss what to do. I tried granting INSERT ON
> LOGTABLE to either INTERNET (the user) or INTERNET_INSERT_ROW (the
> procedure that does the inserting), but unfortunately it didn't
> change anything.

It works for me using the following GRANT statements:

GRANT INSERT ON TABLE PERM_LOG TO GURKO !!
GRANT INSERT ON TABLE PERM_TBL TO GURKO !!
-- GRANT INSERT ON TABLE PERM_TBL TO PERM_PROC !!
GRANT EXECUTE ON PROCEDURE PERM_PROC TO GURKO !!
GRANT GURKO TO USER GURKI !!
COMMIT !!

Legend:

* PERM_TBL is the primary table
* PERM_LOG is the log table the trigger writes to
* PERM_PROC is the procedure
* GURKO is the role
* GURKI is the otherwise unprivileged user

The statement I commented out is not needed.

I would have thought it preferable to only grant the INSERT to the
procedure and not to grant anything but EXECUTE to the role. But that
does not seem to be how it works.

> I am using Firebird 1.5 on Windows, if it matters.

I have used the same here.

--
Michael Ludwig