Operation not allowed when user has only indirect rights [CORE3224] #3598
Comments
|
Commented by: Sean Leyne (seanleyne) The current logic is correct. The error is raised because the trigger Foo_BI is attempting to delete the TAB_Bar entries directly. Since the operation is direct, the user rights/priviledges apply. Thus the operation is rejected. In order to have the operation succeed, it is necessary to create a DeleteBar procedure to which DELETE rights to the TAB_Bar table would need to be granted. This SP would then be invoked by the Foo_BI trigger. |
|
Commented by: Ain Valtin (ain) But the trigger Foo_BI has been granted DELETE right on TAB_Bar! What's the point of granting rights to the trigger then? |
|
Commented by: Yakov Hrebtov (jake) Sean Leyne, You are wrong here. Ain Valtin, |
|
Commented by: @dyemanov Yakov is absolutely correct. UPDATE/DELETE with the WHERE clause require both UPDATE/DELETE and SELECT permissions. |
Modified by: @pcisarstatus: Resolved [ 5 ] => Closed [ 6 ] |
|
Commented by: Ain Valtin (ain) Thanks, Yakov! I wanted to keep the rights as tight as possible, and because of misleading error message thought that the error happens in the "indirect select call". Some further testing reveals that the error happens much sooner indeed, on the DELETE statement... Dimitry, I see you plan to fix it for 3.0, any chanche for 2.5.x? Or at least improving the error message, mentionig that the actual cause is DELETE or UPDATE statement which requires SELECT access. I wasted quite a lot of time checking and rechecking the SELECT rights... |
|
Commented by: @dyemanov No promises regarding v2.5, as nothing has been done on the subject yet. If it would be possible to backport, it will be done. |
Modified by: @pavel-zotovQA Status: No test |
Modified by: @pavel-zotovstatus: Closed [ 6 ] => Closed [ 6 ] QA Status: No test => Deferred Test Details: Deferred until core-136 will be fixed or other solution will be approved on this problem (current FB 4.0 still produces "no permission for SELECT access to TABLE TEST"). |
Submitted by: Ain Valtin (ain)
Duplicates CORE136
Create these objects as some other user than "test":
CREATE TABLE TAB_Foo ( Fld INTEGER );
CREATE TABLE TAB_Bar ( Fld INTEGER );
SET TERM ^ ;
CREATE PROCEDURE GetBar(ID INTEGER) RETURNS(Bar INTEGER) AS
BEGIN
SELECT Fld FROM TAB_Bar WHERE(Fld = :ID) INTO :Bar;
SUSPEND;
END^
CREATE TRIGGER Foo_BI FOR TAB_Foo
ACTIVE BEFORE INSERT POSITION 15
AS
BEGIN
DELETE FROM TAB_Bar WHERE(Fld = NEW.Fld);
END^
CREATE TRIGGER Bar_BD FOR TAB_Bar
ACTIVE BEFORE DELETE POSITION 15
AS
DECLARE tmp INTEGER;
BEGIN
SELECT Bar FROM GetBar(OLD.Fld) INTO :tmp;
END^
SET TERM ; ^
GRANT ALL ON TAB_Foo TO test;
GRANT SELECT ON TAB_Bar TO PROCEDURE GetBar;
GRANT DELETE ON TAB_Bar TO TRIGGER Foo_BI;
GRANT EXECUTE ON PROCEDURE GetBar TO TRIGGER Bar_BD;
Then connect as user "test" and execute:
INSERT INTO TAB_Foo(Fld) VALUES(1);
this results in error:
<<
Starting transaction...
Preparing query: INSERT INTO TAB_Foo(Fld) VALUES(1)
Error: *** IBPP::SQLException ***
Context: Statement::Prepare( INSERT INTO TAB_Foo(Fld) VALUES(1) )
Message: isc_dsql_prepare failed
SQL Message : -551
This user does not have privilege to perform this operation on this object.
Engine Code : 335544352
Engine Message :
no permission for read/select access to TABLE TAB_BAR
>>
User "test" hasn't been granted right to select from TAB_Bar, but the procedure GetBar has that right. BTW using
GRANT EXECUTE ON PROCEDURE GetBar TO PUBLIC;
won't change the error, so it seems to me that there is a bug when checking rights in case of "long indirect call chain".
====== Test Details ======
Deferred until core-136 will be fixed or other solution will be approved on this problem (current FB 4.0 still produces "no permission for SELECT access to TABLE TEST").
The text was updated successfully, but these errors were encountered: