Description:
If you have a view that selects against a table and also a custom function, if the table is swapped out via DROP TABLE followed by RENAME TABLE, and if the user executing DROP TABLE and RENAME TABLE does not have the EXECUTE grant against the custom function that the view selects against, then running SHOW FIELDS FROM against the view will result in an error:
ERROR 1356 (HY000): View 'test_database.test_view' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
I think what's happening is that mysql is trying to reconstruct the view's column data dictionary information at time of RENAME TABLE execution by relying on the executing user's permissions to run the view (and thus the custom function.) If the user lacks EXECUTE permission on the function that the view relies on, mysql can't recreate column data dictionary information (something akin to SQL SECURITY=INVOKER rather SQL SECURITY=DEFINER and relying on the view's definer permissions to recreate the data dictionary?)
The view structure is still okay. You can still SELECT against the view, run SHOW CREATE VIEW, etc. but the column information is missing. This issue can cause mysqldump to fail since it relies on SHOW FIELDS FROM.
How to repeat:
podman run --rm -d --replace --name mysql -p 3306:3306 -e MYSQL_ALLOW_EMPTY_PASSWORD=true mysql:8.0.33 --plugin-load-add=mysql_no_login.so
mysql -h 127.0.0.1 -u root
CREATE USER 'definer'@'localhost' IDENTIFIED WITH mysql_no_login;
GRANT SELECT, EXECUTE ON *.* TO 'definer'@'localhost';
CREATE USER 'applier'@'%';
GRANT SELECT, INSERT, CREATE, CREATE VIEW, ALTER, DROP ON *.* TO 'applier'@'%';
CREATE DATABASE test_database;
USE test_database;
CREATE TABLE test_table (
id INT NOT NULL PRIMARY KEY
);
CREATE DEFINER='definer'@'localhost' FUNCTION test_function ()
RETURNS INT DETERMINISTIC
RETURN 1;
-- 'definer'@'localhost' has permission to execute test_function
CREATE DEFINER='definer'@'localhost' VIEW test_view AS
SELECT test_function() FROM test_table
;
-- Success
SHOW FIELDS FROM test_view;
exit
# Now we switch to the 'applier' user
mysql -h 127.0.0.1 -u applier
USE test_database;
CREATE TABLE test_table_new LIKE test_table;
DROP TABLE test_table;
RENAME TABLE test_table_new TO test_table;
-- Error. Even though 'definer'@'localhost' has permission to execute test_function, 'applier'@'%' does not, and this seems to affect data dictionary generation.
SHOW FIELDS FROM test_view;
This should return an error:
ERROR 1356 (HY000): View 'test_database.test_view' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
If you re-run the above steps but add an additional "EXECUTE" grant for 'applier'@'%', then the final SHOW FIELDS FROM test_view will run successfully and not return an error
