-- Minimum working example:
-- EXECUTE privilege required for mariadb-dump when using functions in views

CREATE DATABASE IF NOT EXISTS mwe;
USE mwe;

-- trivial function: returns its input
DELIMITER ;;
CREATE FUNCTION identity(input INT) 
RETURNS INT
BEGIN
    RETURN input;
END ;;
DELIMITER ;

-- View that uses a function in SELECT
CREATE VIEW v_select AS 
SELECT identity(1) AS col
;

-- View that uses a function in WHERE
CREATE VIEW v_where AS 
SELECT 1 AS col 
FROM DUAL
WHERE identity(1) = 1
;

-- User with read-only privileges
GRANT SELECT, LOCK TABLES, SHOW VIEW ON *.* TO backup_user IDENTIFIED BY 'backup_password';

-- Execute privilege on one specific function
-- Without this, backup_user cannot mariadb-dump this database.
-- GRANT EXECUTE ON FUNCTION mwe.identity TO backup_user;
