Search code examples
firebirdproceduresql-managerfirebird-psql

Error occurs: "Using GRANT OPTION on procedures not allowed" when creating a PSQL variable


We are using SQL manager for interbase-firebird and also Firebird 3.0.5. We are creating a procedure and that works fine.

However from the moment we add a psql variable to the procedure we get below error. When we remove the variable the procedure can be executed again. Below the variable that has been added (see picture below as well).

  Declare variable ikke integer;

The error message is saying:

USING GRANT OPTION ON PROCEDURE NOT ALLOWED.

The detailed error information says:

GRANT REFERENCES ON "Text" TO PROCEDURE GETTEXTCONTAINING WITH GRANT OPTION;

So it seems that by using the psql variable a reference from the table "TEXT" to the stored procedure is added, and which is apparently not allowed.

So, it sounds like a permission things. However when we grant a permission to the procedure the reference areas is grayed out so we can't grant permission, like we did on the execution level.

**enter image description here** And if we look at the permission level on the table text, a reference permission is given, however it's is not possible to add "a permission with grant" (hand icon).

enter image description here

I don't know what the problem is and how to resolve it, any ideas?

UPDATE 27/02/2012 If I add the same code into an execution block this works well. Any ideas?

execute block
RETURNS(ID BIGINT, TEXTNAME VARCHAR(1000))
AS
DECLARE ikke VARCHAR(50);
BEGIN
 Id = 0;
 Textname = '';
 ikke = '%a%';
  FOR
 SELECT "ID", "enUS" FROM "Text" WHERE "enUS" like :ikke INTO :Id, :Textname
 DO
 BEGIN
    SUSPEND;
 END
END;

Below some additional printscreens of the errors.

Error message when executing the stored procedure

Detailed error info


Solution

  • So it's not clear why the "privilege" is granted automatically when I add a psql variable, but when I uncheck the granted permission in the compile pop-up and then do a rollback/recompile it works.