Oleksandr Byelkin 6c951fea5f MDEV-39993 Use CREATE OR REPLACE for sys schema routines ...

mariadb-upgrade silently dropped EXECUTE grants on sys schema stored
functions and procedures. The sys schema install scripts reinstalled
every routine with DROP FUNCTION/PROCEDURE IF EXISTS followed by
CREATE. DROP cascades to delete the routine's rows in
mysql.procs_priv, so any EXECUTE grant a DBA had issued on e.g.
sys.table_exists or sys.quote_identifier was lost every time
mariadb-upgrade reinstalled the sys schema, even though the routine
itself came back unchanged.

Fix: replace DROP ... IF EXISTS + CREATE with CREATE OR REPLACE in
all 53 sys_schema function/procedure files and in the two templates
(templates/function.sql, templates/procedure.sql) so future routines
follow the same pattern. CREATE OR REPLACE PROCEDURE/FUNCTION goes
through sp_drop_routine_internal(), which only deletes the
mysql.proc row and never reaches sp_revoke_privileges() (that is
only called from the explicit DROP PROCEDURE/FUNCTION statement),
so mysql.procs_priv is left untouched and existing grants survive.
This mirrors the pattern already used by sys schema views
(CREATE OR REPLACE ... VIEW, since MDEV-9077), which never had this
problem.

Four of the converted files (functions/format_path.sql,
functions/ps_is_account_enabled_57.sql,
procedures/ps_setup_reset_to_default.sql,
procedures/ps_trace_thread_57.sql) are not referenced by
scripts/sys_schema/CMakeLists.txt; they were converted anyway for
consistency and have no behavioural effect.

As a side effect, a pre-existing UDF whose name collides with a sys
routine name is no longer destroyed before the reinstall fails:
DROP FUNCTION IF EXISTS resolved the UDF namespace first, silently
dropping the UDF and then failing on ER_SP_ALREADY_EXISTS anyway;
CREATE OR REPLACE fails immediately on ER_UDF_EXISTS with the UDF
intact.

scripts/maria_add_gis_sp.sql.in and the sys_config triggers were
deliberately left untouched: the GIS procedures are only
re-installed at bootstrap time (mariadb-upgrade instead patches
their definer in place via UPDATE), and triggers carry no
procs_priv rows, so neither is on the code path this bug is about.

Added mysql-test/main/mysql_upgrade_sys_routine_grants.test, which
grants EXECUTE on sys.table_exists and sys.quote_identifier,
overwrites both routine bodies with a marker to prove the upgrade
actually reinstalls them (rather than the sys schema install being
skipped), runs mariadb-upgrade, and checks both that the grants
survived and that the real routine bodies came back.

Co-Authored-By: Claude Sonnet 5 <noreply@anthropic.com>

2026-09-21 17:07:02 +02:00

1.7 KiB

Raw Permalink Blame History

View Raw