MySQL Bugs: #117014: EXECUTE grant required when SHOW CREATE VIEW over view with json

Bug #117014	EXECUTE grant required when SHOW CREATE VIEW over view with json_table
Submitted:	18 Dec 2024 14:30	Modified:	19 Dec 2024 11:08
Reporter:	David Ducos	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	mysqldump

Description:
If a VIEW has the function json_table, it is required for the backup user to have the EXECUTE privilege when we execute SHOW CREATE VIEW.  

How to repeat:
1- Create a user with minimal privileges:

create user david@'%' identified by 'david';
GRANT LOCK TABLES, REPLICATION CLIENT ON *.* TO `david`@`%`;
GRANT BACKUP_ADMIN,FLUSH_TABLES ON *.* TO `david`@`%`;
GRANT SELECT, SHOW VIEW ON `mydumper_test`.* TO `david`@`%`;

2- Create the view that is causing the issue:

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `helper_view_2` AS select `helper_view_1`.`original_id` AS `original_id`,`helper_view_1`.`text_data` AS `text_data`,`ITEM_DATA`.`quantity` AS `quantity`,`ITEM_DATA`.`name` AS `item_name` from (`helper_view_1` join json_table(json_extract(`helper_view_1`.`nested_json_data`,'$.items'), '$[*]' columns (`row_id` for ordinality, `name` varchar(32) character set utf8mb4 collate utf8mb4_unicode_ci path '$.name', `quantity` int path '$.quantity')) `ITEM_DATA`);

3- Connect to the database with the user without the EXECUTE privilege:

$ mysql -h $MYSQL_HOST -u david -pdavid

mysql> show create view `helper_view_2`;
ERROR 1235 (42000): This version of MySQL doesn't yet support 'SHOW CREATE VIEW on a view that references a non-existent table and a table function.'

4- Grant the user the EXECUTE privilege:

mysql> grant EXECUTE on *.* to david@'%';
Query OK, 0 rows affected (0.01 sec)

5- Check again the SHOW CREATE VIEW and it will work:

$ mysql -h $MYSQL_HOST -u david -pdavid

mysql> show create view `helper_view_2`;

Suggested fix:
I think that EXECUTE privilege should not be required when we execute SHOW CREATE VIEW, and if it is OK and expected, then this should be documented in https://dev.mysql.com/doc/refman/8.4/en/show-create-view.html where says that only SHOW VIEW is required.

Hi Mr. Ducos,

Thank you for your bug report.

However, we can not repeat your test case, since you are using tables that are not defined in your test case.

Can't repeat.

sql file to reproduce the issue

Attachment: mydumper_test.sql.txt (text/plain), 3.15 KiB.

Hello, 
Attached you will find the remaining table structures needed to reproduce the issue.

Hi Mr. Ducos,

We still cannot repeat your test case.

We get the error that is totally, totally expected:

ERROR 1142 (42000): SELECT command denied to user 'david'@'localhost' for table 'helper_view_2'

We also get lot's of errors with your dump and we shall definitely not edit any of your data so that we would get read of the syntax errors.

Can't repeat.

test file after removing definer

Attachment: mydumper_test.sql (application/octet-stream, text), 3.08 KiB.

How to repeat:

1- Create the database with root user:
mysql> CREATE DATABASE mydumper_test;

2- Import the file using your root user:

$ mysql mydumper_test < mydumper_test.sql

3- Create a user with minimal privileges:

mysql> CREATE USER david@'%' IDENTIFIED BY 'david';
mysql> GRANT LOCK TABLES, REPLICATION CLIENT ON *.* TO `david`@`%`;
mysql> GRANT BACKUP_ADMIN,FLUSH_TABLES ON *.* TO `david`@`%`;
mysql> GRANT SELECT, SHOW VIEW ON `mydumper_test`.* TO `david`@`%`;

4- Connect to the database with the user without the EXECUTE privilege:

$ mysql -h $MYSQL_HOST -u david -pdavid

mysql> show create view `helper_view_2`;
ERROR 1235 (42000): This version of MySQL doesn't yet support 'SHOW CREATE VIEW on a view that references a non-existent table and a table function.'

4- With your root user, grant the EXECUTE privilege to the new user:

mysql> GRANT EXECUTE ON *.* TO david@'%';

5- Check again the SHOW CREATE VIEW and it will work:

$ mysql -h $MYSQL_HOST -u david -pdavid

mysql> show create view `helper_view_2`;

I hope that now you can reproduce the issue.

Hi Mr. Ducos,

We repeated your bug report.

This is now a verified bug for our Documentation team.

Verified for version 8.0 and all supported versions.