1

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

free
  • 153
  • 1
  • 2
  • 18
  • Which version of Firebird are you using, as in the full version number? – Mark Rotteveel Feb 27 '20 at 10:46
  • 1
    Granting privileges `WITH GRANT OPTION` to a stored procedure doesn't make much sense, because a stored procedure can't grant rights to other objects or users, but I would expect this to fail always, not just depending on the whether or not you have a variable declared. – Mark Rotteveel Feb 27 '20 at 10:51
  • @Mark Firebird 3.0.5 – free Feb 27 '20 at 11:14
  • Why are you even trying to give privilege with grant option? – Mark Rotteveel Feb 27 '20 at 11:34
  • @Mark, I'm not yet that familiar with firebird, but I get your point, its not needed. However the privilege is executed automatically when I execute the procedure.Why, I don't know! Now, when I run the same code in an execution block this runs well, See update, any idea's? Thx a lot. – free Feb 27 '20 at 11:49
  • 1
    frankly, this sounds like a bug in SQL Manager software, not in Friebird software. Try doing the same change in any other IDE like IBExpert, FlameRobin, dBeaver. Best of all, try to do it without any IDE at all in plain SQL in a ultra-simplistic tools like `isql`. Specifically i can not get why ALTERING procedure requires re-granting all the rights. Would SQL Manager instead DROP and CREATE the procedure anew - the new granting would be needed. But when the procedure is merely ALTERED - why on earth? That looks to me as SQL Manager bug – Arioch 'The Feb 27 '20 at 12:03
  • 1
    and, you may remove the checkmark from SQL Manager "grant" statements before executing the batch. That is why SQL Manager shows you them statements, to let you see what specifically it is going to do and make your final judgement, what you agree or not. Your screenshot even says so explicitly: "you can uncheck unnecessary statements" and "yout can change selected statement before compiling". SQL Manager might make a mistake, but it explicitly asks for human intervention to approve or fix the SQL commands draft it suggested you to run. It is up to you then to take that suggestion or not – Arioch 'The Feb 27 '20 at 12:09
  • 1
    @Arioch, I just ran into the solution and its also like you mentoined. See update 2. Thx for the feeback! – free Feb 27 '20 at 12:18
  • 1
    The problem is not with granting the privilege, the problem is with granting the privilege `WITH GRANT OPTION`. Using 'with grant option' means that the receiving object does not only receive the privilege, but also receives the additional privilege to grant that privilege to other objects. Unlike users, a stored procedure cannot grant other objects privileges, so this option doesn't make a lot of sense to apply. This sounds like a bug in the tool you're using to include `WITH GRANT OPTION` when generating privileges on objects, and it certainly doesn't make sense to do so by default. – Mark Rotteveel Feb 27 '20 at 12:33
  • Thx, Mark for the info. Not sure why SQL Manager does this automatically, seems like a bug. However a solution is found and by this there is closure! – free Feb 27 '20 at 12:36
  • Good to hear, consider moving the solution from your question to an answer. Self-answering your questions is perfectly fine. – Mark Rotteveel Feb 27 '20 at 12:43
  • @MarkRotteveel i *speculate* that once ago, before Firebird was hardened, someone did manage to create with-grant-option privilege in some prior version, and not SQL Manager just tries to repeat it verbatim without giving it a second thought. However, Free, i still think you have to register bug reports with SQL Manager. First, there seems to be no reason to repeat granting when merely ALTERing objects, second, if they do, they should check that grant options makes no sense and even was prohibited on later FB – Arioch 'The Feb 27 '20 at 15:01
  • **The problem is not with granting the privilege** - i disagree. Every new code is a chance for bugs. Both immediately and after yet unknown changes in future. Unneeded code / uneeded behavior thus is unneeded opportunities for extra bugs. – Arioch 'The Feb 27 '20 at 15:23

1 Answers1

0

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.

free
  • 153
  • 1
  • 2
  • 18