I'm running into the following problem. I'm sorry if this issue has been posted before, I did not find it. Please point me out if I'm wrong. I'm also sorry for the long post.

I'm using Firebird 2.1.1

Short description: I have a Table and a View on the table. The view is updatable, and I can verify that by inserting values into the view. A role does not have permissions for UPDATING one (only one) column in the table and view. I thing there is a problem with permissions when adding triggers to the view and making it not updatable.

I.E.

drop view B;
drop table A;

CREATE TABLE A (
FIELD_1 INTEGER not null,
FIELD_2 CHAR(3),
FIELD_3 INTEGER
);

alter table A
add constraint PK_A
primary key (FIELD_1);


/* View: B */
CREATE VIEW B(
FIELD_1,
FIELD_2,
FIELD_3)
AS
select FIELD_1, FIELD_2, FIELD_3 from A
where FIELD_1 > 100
;



grant select, delete, insert, update (FIELD_1, FIELD_2) on A to ROSHKA;

grant select, delete, insert, update (FIELD_1, FIELD_2) on b TO ROSHKA;


----------------------------------------------------------------

Inserting into the table works fine:


-- CONNECT USING ROLE ROSHKA
INSERT INTO A
(FIELD_1, FIELD_2, FIELD_3)
VALUES
(101, 'A', 1);

------------------

select * from A;

FIELD_1 FIELD_2 FIELD_3
101 A 1

--UPDATING FAILS AS EXPECTED
-- CONNECT USING ROLE ROSHKA
UPDATE a
SET FIELD_3 = 4
WHERE FIELD_1 = 101;
/*
This user does not have privilege to perform this operation on this object.
no permission for update/write access to COLUMN FIELD_3.
*/

Inserting into the view works fine too:

INSERT INTO B
(FIELD_1, FIELD_2, FIELD_3)
VALUES
(102, 'B', 1);

--UPDATING THE VIEW FAILS AS EXPECTED:
--CONNECT AS ROLE ROSHKA
UPDATE B
SET FIELD_3 = 4
WHERE FIELD_1 = 102;

--------------------------------------

--NOW, I'D LIKE TO SET THE FIELD 2 INFO IN A TRIGGER ON THE VIEW.
--WHEN CREATING TRIGGERS ON THE VIEW, THE VIEW DOES NOT AUTOMATICALLY INSERT VALUES ON THE TABLE (FIREBIRD FEATURE). I have to create triggers AI, AU, AD that perform data manipulation on the table.


SET TERM ^;

/* Trigger: B_AI */
CREATE TRIGGER B_AI FOR B
ACTIVE AFTER INSERT POSITION 0
AS
BEGIN
--not inserting field_3 on porpouse
insert into A
(field_1, field_2)
values
(new.field_1, new.field_2);
END
^


/* this is the trigger that puzzles me. */
CREATE TRIGGER B_AU FOR B
ACTIVE AFTER UPDATE POSITION 0
AS
BEGIN
update A
set a.field_2 = new.field_2
where a.field_1 = new.field_1;
END
^


/* Trigger: B_BI */
CREATE TRIGGER B_BI FOR B
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
NEW.field_2 = 'B';
END
^


SET TERM ; ^


-----------------------------------------

--connect as ROSHKA
--INSERT STILL WORKS FINE BUT DOES NOT INSERT FIELD_3 ON PORPUSE.
INSERT INTO B
(FIELD_1, FIELD_2, FIELD_3)
VALUES
(103, 'B', 1);

-- CONNECT AS ROLE ROSHKA
UPDATE B
SET FIELD_2 = 108
WHERE FIELD_1 = 103;

--GIVES THE FOLLOWING ERROR, EVEN IF I'M NOT UPDATING FIELD_3 AND FIELD_3 IS NOT MENTIONED IN THE TRIGGER.

--This user does not have privilege to perform this operation on this --object.
-- no permission for update/write access to COLUMN FIELD_3.

This does not make sense to me, since I'm not even metioning FIELD_3 on the trigger, and I'm not updating the field.

If I drop the trigger AU, and do the same update myself:

DROP TRIGGER B_AU;

And executing the same update again, works fine and updates the table.
--connect as role roshka
UPDATE B
SET FIELD_2 = 108
WHERE FIELD_1 = 103;

--
select * from b;

FIELD_1 FIELD_2 FIELD_3
103 108

----------------------------------------------

MY Conclusions:

When using triggers in views in firebird. The permission check is done using the "default" trigger that insert/update all the fields, even if the update does not contain all of them.

I've performed a few tests that are not included in this (simplified) example, but did not include them in this post because it is TOO long as it is.

I'm trying to change my database design to work around this issue. I'm just trying to see if this is a bug or not.

Thanks in advance for even reading the whole example.

Alejandro