When dumping a view in which a function appears in the column list or a condition, mariadb-dump fails with error 1370 unless the user has execute privileges on that function.

How to reproduce

The attached mwe.sql contains a minimum working example. After loading it, try to dump the database mwe like so:
mariadb-dump --user backup_user --password --routines --databases mwe
The command will fail with the error message mentioned below.

Then, execute GRANT EXECUTE ON FUNCTION mwe.identity TO backup_user and retry. The command will now succeed.

Error messages

Calling mariadb-dump --user backup_user --password --databases mwe produces:
mariadb-dump: Couldn't execute 'SHOW FIELDS FROM `v_select`': execute command denied to user 'backup_user'@'%' for routine 'mwe.identity' (1370)

There is no entry in the server's error log.

Minimum working example

See the attached mwe.sql.
In the database mwe, two views use a trivial function identity(INT). The view v_select uses it in a column expression, while v_where uses in a where condition. A user backup_user has global SELECT, SHOW VIEW, and LOCK TABLES privileges. This user can see the view definition with SHOW CREATE VIEW, but SHOW COLUMNS fails with insufficient privileges.

Also attached is the test case columns_from_view_with_function.test for the same scenario. This test fails if SHOW COLUMNS or a select from INFORMATION_SCHEMA.COLUMNS fail, as discussed below.

Environment

For operating system and server hardware see separate box.
I have attached redacted versions of my.cnf and the output of SHOW VARIABLES: As I'm not allowed to publish server names and concrete paths, these values have been replaces by ##REDACTED##.

Context and further information

Our nightly backups run with a read-only user. They fail whenever a database contains a view which uses a function. We have not found a general solution to make nightly backups work in these cases, as they succeed only after granting EXECUTE on the specific functions. Requiring execute privileges for what should be a read-only operation seems wrong to me.

There is also no way to grant EXECUTE on all functions, including future ones, so I don't have a workaround, either. As a database administrator, I can't know what our developers do in their databases. Coming in to work to find a random bunch of failed backups is no stable situation.

As far as I can tell, the problem appears because mariadb-dump cannot determine column information for views, even though the view definition is available. Mariadb-dump creates a dummy for all views in a database, such that the referenced objects for each view exist at the respective creation time. To create these dummy views, SHOW COLUMNS is executed, which fails if the view uses a function.

Without the execute privilege, this happens:

MariaDB [mwe]> SHOW COLUMNS FROM mwe.v_select;

ERROR 1370 (42000): execute command denied to user 'backup_user'@'%' for routine 'mwe.identity'

MariaDB [mwe]> SHOW CREATE VIEW mwe.v_select;

| View     | Create View                                                                                                            | character_set_client | collation_connection |

| v_select | CREATE ALGORITHM=UNDEFINED DEFINER=`adm52sc`@`%` SQL SECURITY DEFINER VIEW `v_select` AS select `identity`(1) AS `col` | utf8mb3              | utf8mb3_general_ci   |

1 row in set (0.000 sec)

MariaDB [mwe]> SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'mwe' AND TABLE_NAME = 'v_select';

Empty set, 1 warning (0.001 sec)

MariaDB [mwe]> SHOW WARNINGS;

| Level   | Code | Message                                                                     |

| Warning | 1370 | execute command denied to user 'backup_user'@'%' for routine 'mwe.identity' |

1 row in set (0.000 sec)