Firebird SQL SECURITY DEFINER routines and PUBLIC table grants — privilege elevation at the routine–table boundary

Integration boundary

Firebird 4.0 introduced SQL SECURITY DEFINER for stored procedures and triggers, allowing routines to execute with the creator's privileges rather than the caller's. At the same time, the implicit PUBLIC role grants any privileges assigned to it to every connected user. The interoperability question is how these two mechanisms interact when a DEFINER routine references tables that carry PUBLIC grants.

Precise goal

Determine whether a SQL SECURITY DEFINER procedure can inadvertently expose table data to callers who lack direct privileges on the underlying tables, solely because those tables have GRANT SELECT ... TO PUBLIC in place. The engine's current behavior suggests the DEFINER context inherits the creator's full rights, but the documentation does not clarify whether PUBLIC grants on referenced tables are evaluated in the DEFINER or INVOKER context during routine execution.

Constraints and uncertainty

• Firebird 4.0 through 5.0; behavior may differ from 3.0 where DEFINER/INVOKER syntax was not available.
• Third-party admin tools sometimes add blanket GRANT ALL ON ALL TABLES TO PUBLIC during schema edits, increasing the attack surface.
• Column-level PUBLIC grants survive table-level REVOKE, potentially leaving residual access paths.
• No official test matrix covers the combination of DEFINER routines, PUBLIC table grants, and column-level privileges.

Questions

1. When a SQL SECURITY DEFINER procedure selects from a table that has only PUBLIC grants (no explicit grant to the procedure owner), does the engine allow the selection?
2. If the answer is yes, is this considered intended behavior or a privilege-elevation bug?
3. Does the presence of column-level PUBLIC grants change the outcome compared to table-level grants?