search

FirebirdSQL / firebird

Firebird server, client and tools
https://www.firebirdsql.org/
1.23k stars 212 forks source link

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

Open firebird-automations opened 3 years ago

firebird-automations commented 3 years ago

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: FirebirdSQL/firebird@3b1e8c813a9da341d0608875c3b0d15221447fe7

firebird-automations commented 3 years ago
Modified by: @romansimakov assignee: Roman Simakov \[ roman\-simakov \]
firebird-automations commented 3 years ago

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 3 years ago

Commented by: @romansimakov

https://github.com/FirebirdSQL/firebird/pull/313