MySQL Bugs: #94305: Manual for SHOW CREATE PROCEDURE is not detailed enough

Bug #94305	Manual for SHOW CREATE PROCEDURE is not detailed enough
Submitted:	13 Feb 9:07	Modified:	13 Feb 9:47
Reporter:	Valeriy Kravchuk	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:	grants, missing manual, show

Description:
Manual page for SHOW CREATE PROCEDURE (https://dev.mysql.com/doc/refman/8.0/en/show-create-procedure.html) in MySQL 8.0 is notably changed to reflect changes imposed by the new data dictionary (compare to https://dev.mysql.com/doc/refman/5.7/en/show-create-procedure.html).

For older versions it stated:

"To use either statement, you must be the user named in the routine DEFINER clause or have SELECT access to the mysql.proc table. If you do not have privileges for the routine itself, the value displayed for the Create Procedure or Create Function field will be NULL. "

Now for 8.0 it says:

"To use either statement, you must have the global SELECT privilege."

The above is neither complte nor true. 

It's not true as a user that created procedure can still see it (as expected), no matter if she has a global SELECT privilege:

mysql> show grants;
+-----------------------------------------------------------------------------+
| Grants for u1@localhost                                                     |
+-----------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `u1`@`localhost`                                      |
| GRANT CREATE ROUTINE ON `db2`.* TO `u1`@`localhost`                         |
+-----------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> create procedure db2.proc3() select 3 as c;
Query OK, 0 rows affected (0.11 sec)

mysql> call db2.proc3();
+---+
| c |
+---+
| 3 |
+---+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> show create procedure db2.proc3\G
*************************** 1. row ***************************
           Procedure: proc3
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
    Create Procedure: CREATE DEFINER=`u1`@`localhost` PROCEDURE `proc3`()
select 3 as c
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
  Database Collation: utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

It's not complete as iut does not explain when the user may get NULL displayed for "Create Procedure" filed (see How to Repeat below).

As a side note, MySQL 8 now requires global SELECT privilege (for all tables!) for a user that needs a way to see source code of stored procedures. For me this looks like a secuity breach/relaxing comparing to older MySQL versions.

How to repeat:
One can easily check that user may be able to execute the procdure call and SHOW CREATE PROCEDURE, but still can not see the code. Test case is simple:

1. As a root suer, create new database, db2, and new user, u2@localhost, and procedure db2.proc1() with any code.

2. Grant GRANT EXECUTE ON PROCEDURE `db2`.`proc1` TO `u2`@`localhost` as root.

3. Then connect as u2@localhost and check as follows:

[openxs@fc23 8.0]$ bin/mysql -uu2 -pu2 --socket=/tmp/mysql8.sock
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 8.0.15 Source distribution

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show grants;
+--------------------------------------------------------------+
| Grants for u2@localhost                                      |
+--------------------------------------------------------------+
| GRANT USAGE ON *.* TO `u2`@`localhost`                       |
| GRANT EXECUTE ON PROCEDURE `db2`.`proc1` TO `u2`@`localhost` |
+--------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> call db2.proc1();
+---+
| a |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> show create procedure db2.proc1\G
*************************** 1. row ***************************
           Procedure: proc1
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
    Create Procedure: NULL
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
  Database Collation: utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

Now find any text in MySQL 8.0 manual explaining why the value in "Create Procedure" is NULL.

Suggested fix:
Please, make this manual page correct and complete based on current MySQL 8.0.x implementation.

Hello Valeriy,

Thank you for the report and feedback.

regards,
Umesh

-- 8.0.15

mysql> system cat docs/INFO_SRC
commit: 44dae513765dfdb0d861cfc34423f0fb407c2047
date: 2019-01-25 23:31:34 +0100
build-date: 2019-01-25 23:33:22 +0100
short: 44dae51
branch: mysql-8.0.15-release

MySQL source 8.0.15

mysql>
mysql> show grants;
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                                                                                                                                                                                                                                                                                                                   |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `root`@`localhost` WITH GRANT OPTION    |
| GRANT APPLICATION_PASSWORD_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,GROUP_REPLICATION_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SYSTEM_VARIABLES_ADMIN,XA_RECOVER_ADMIN ON *.* TO `root`@`localhost` WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION                                                                                                                                                                                                                                                                                                                                                |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

mysql> create user `u1`@`localhost`;
Query OK, 0 rows affected (0.00 sec)

mysql> create user `u2`@`localhost`;
Query OK, 0 rows affected (0.00 sec)

mysql> create database db2;
Query OK, 1 row affected (0.00 sec)

mysql> create procedure db2.proc3() select 3 as c;
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT EXECUTE ON PROCEDURE `db2`.`proc3` TO `u2`@`localhost`;
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT CREATE ROUTINE ON `db2`.* TO `u1`@`localhost`;
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for `u1`@`localhost`;
+-----------------------------------------------------+
| Grants for u1@localhost                             |
+-----------------------------------------------------+
| GRANT USAGE ON *.* TO `u1`@`localhost`              |
| GRANT CREATE ROUTINE ON `db2`.* TO `u1`@`localhost` |
+-----------------------------------------------------+
2 rows in set (0.00 sec)

mysql> show grants for `u2`@`localhost`;
+--------------------------------------------------------------+
| Grants for u2@localhost                                      |
+--------------------------------------------------------------+
| GRANT USAGE ON *.* TO `u2`@`localhost`                       |
| GRANT EXECUTE ON PROCEDURE `db2`.`proc3` TO `u2`@`localhost` |
+--------------------------------------------------------------+
2 rows in set (0.00 sec)

######
 bin/mysql -uu1 -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.15 MySQL Community Server - GPL

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show grants;
+-----------------------------------------------------+
| Grants for u1@localhost                             |
+-----------------------------------------------------+
| GRANT USAGE ON *.* TO `u1`@`localhost`              |
| GRANT CREATE ROUTINE ON `db2`.* TO `u1`@`localhost` |
+-----------------------------------------------------+
2 rows in set (0.00 sec)

mysql> use db2
Database changed
mysql> show create procedure db2.proc3\G
*************************** 1. row ***************************
           Procedure: proc3
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
    Create Procedure: NULL
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
  Database Collation: utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

mysql> create procedure db2.proc1() select 3 as c;
Query OK, 0 rows affected (0.00 sec)

mysql> show create procedure db2.proc1\G
*************************** 1. row ***************************
           Procedure: proc1
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
    Create Procedure: CREATE DEFINER=`u1`@`localhost` PROCEDURE `proc1`()
select 3 as c
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
  Database Collation: utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

###########
 bin/mysql -uu2 -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.15 MySQL Community Server - GPL

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show grants;
+--------------------------------------------------------------+
| Grants for u2@localhost                                      |
+--------------------------------------------------------------+
| GRANT USAGE ON *.* TO `u2`@`localhost`                       |
| GRANT EXECUTE ON PROCEDURE `db2`.`proc3` TO `u2`@`localhost` |
+--------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> use db2
Database changed
mysql> show create procedure db2.proc3\G
*************************** 1. row ***************************
           Procedure: proc3
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
    Create Procedure: NULL
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
  Database Collation: utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

mysql>