hvlad schrieb am 18.05.2010 um 06:24:47 (-0000):

> --- In firebird-support@yahoogroups.com, PenWin <penwin@...> wrote:
>
> > 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.
>
> GRANT INSERT ON LOGTABLE TO TRIGGER LOGTRIGGER_SOMETABLE

Thanks, Vlad. That still gives an error message.

\,,,/
(o o)
------oOOo-(_)-oOOo------
SQL> input 'G:\dev\sql\firebird\sp-trig-perm.sql';
Database: localhost/fb15:xong, User: MILU
GRANT INSERT ON PERM_TBL TO USER PERM_PROC
GRANT INSERT ON PERM_LOG TO TRIGGER PERM_TRG
GRANT EXECUTE ON PROCEDURE PERM_PROC TO ROLE GURKO
GRANT GURKO TO GURKI
Database: localhost/fb15:xong, User: GURKI, Role: GURKO
Statement failed, SQLCODE = -551

no permission for insert/write access to COLUMN A
Database: localhost/fb15:xong, User: MILU
-------------------------

Here's the test script one can reproduce the error with. I have one
user called MILU who owns the database, and another called GURKI, who
is granted the GURKO role, which is the role to configure with minimal
privileges, avoiding direct INSERT. Replace as suitable for your setup.

\,,,/
(o o)
------oOOo-(_)-oOOo------
COMMIT;

CONNECT localhost/fb15:xong USER MILU PASSWORD test;

SET TERM !! ;
SET AUTODDL OFF !!

DROP TRIGGER PERM_TRG !!
DROP PROCEDURE PERM_PROC !!
DROP TABLE PERM_LOG !!
DROP TABLE PERM_TBL !!
DROP ROLE GURKO !!
COMMIT !!

CREATE TABLE PERM_TBL (
a INTEGER NOT NULL,
ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL
) !!

CREATE TABLE PERM_LOG (
a INTEGER NOT NULL,
ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL
) !!

CREATE TRIGGER PERM_TRG FOR PERM_TBL
ACTIVE AFTER INSERT POSITION 0
AS
BEGIN
INSERT INTO PERM_LOG (a) VALUES (NEW.a + 11);
END !!

CREATE PROCEDURE PERM_PROC
AS
BEGIN
INSERT INTO PERM_TBL (a) VALUES (77);
END !!

COMMIT !!

CREATE ROLE GURKO !!
-- INSERT-Rechte für Rolle möglichst vermeiden.
-- GRANT INSERT ON TABLE PERM_LOG TO GURKO !!
-- GRANT INSERT ON TABLE PERM_TBL TO GURKO !!
-- INSERT-Rechte für Trigger und Prozedur sollten reichen.
GRANT INSERT ON TABLE PERM_TBL TO PERM_PROC !!
GRANT INSERT ON TABLE PERM_LOG TO TRIGGER PERM_TRG !!
GRANT EXECUTE ON PROCEDURE PERM_PROC TO GURKO !!
GRANT GURKO TO USER GURKI !!
COMMIT !!
SET TERM ; !!

SHOW GRANT PERM_TBL;
SHOW GRANT PERM_LOG;
SHOW GRANT PERM_PROC;
SHOW GRANT GURKO;
COMMIT;

CONNECT localhost/fb15:xong USER GURKI ROLE GURKO PASSWORD test;
EXECUTE PROCEDURE PERM_PROC;
COMMIT;

CONNECT localhost/fb15:xong USER MILU PASSWORD test;
SELECT * FROM PERM_TBL;
SELECT * FROM PERM_LOG;
COMMIT;
-------------------------

If you uncomment the INSERT grants to the role GURKO, it all works.

--
Michael Ludwig