Stored procedure isn't able to execute statement 'GRANT' [CORE6502]

[firebird-automations]

Submitted by: @romansimakov

The engine executing GRANT from a stored procedure with SQL SECURITY DEFINER runned from another user with EXECUTE privilege raises the error "User cannot write to RDB$USER_PRIVILEGES"

Commits: 3b1e8c8

[firebird-automations]

Modified by: @romansimakov

assignee: Roman Simakov [ roman-simakov ]

[firebird-automations]

Commented by: @romansimakov

To reproduce use a database:

CREATE GENERATOR G;
SET TERM ^ ;

CREATE PROCEDURE P SQL SECURITY DEFINER
AS
begin
execute statement 'grant alter any generator to "u3"';
end ^

SET TERM ; ^
GRANT EXECUTE ON PROCEDURE P TO USER US;

and then in a connection from another user run:

roman@roman-ubuntu % ./isql localhost:/tmp/1.fdb -U us -P pas
Database: localhost:/tmp/1.fdb, User: US
SQL> execute procedure p;
Statement failed, SQLSTATE = 27000
unsuccessful metadata update
-GRANT failed
-action cancelled by trigger (0) to preserve data integrity
-User cannot write to RDB$USER_PRIVILEGES
-At procedure 'P' line: 4, col: 3

In this case GRANTOR is equal to the OWNER of the PROCEDURE but CURRENT_USER is different. There's nothing wrong here. But system trigger for RDB$USER_PRIVILEGES checks if GRANT=CURRENT_USER but not effective one.
I'm sure it's possible to fix the trigger. However is it really needed? It's prohibited to modity system tables directly but DdlNodes have theirown checks.

I suggest to remove the trigger completely

[firebird-automations]

Commented by: @romansimakov

#313