MySQL Bugs: #96601: Replacement of mysql.proc table

Bug #96601	Replacement of mysql.proc table
Submitted:	21 Aug 2019 11:27	Modified:	4 Sep 2019 11:42
Reporter:	DBAJJ J	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: General	Severity:	S4 (Feature request)
Version:	MySQL 8.0	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	MySQL

Description:
Till version 5.7, if we need to provide access for users to view routines which was not created by them(definers), we can use this query

Grant select on mysql.proc to 'user'@'host';

Also, to change the definers in one shot (for the deleted users), we can use this query

update mysql.proc set definer = 'user'@'host' where db='dbname' 

or

update mysql.proc set definer = 'user1'@'host' where definer = 'user'@'host'

What replacement do we have for the above in MySQL 8 

How to repeat:
update mysql.proc set definer = 'user1'@'host' where definer = 'user'@'host'

--- Table mysql.proc not found

Hi, 

To view the procedures, you can use the information_schema.routines table like this : 

mysql> select routine_schema, routine_name, routine_type, definer from information_Schema.routines where routine_schema not in ('sys');
+----------------+--------------+--------------+----------------+
| ROUTINE_SCHEMA | ROUTINE_NAME | ROUTINE_TYPE | DEFINER        |
+----------------+--------------+--------------+----------------+
| test           | hello        | PROCEDURE    | root@localhost |
| test           | hello2       | PROCEDURE    | root@localhost |
+----------------+--------------+--------------+----------------+
2 rows in set (0.00 sec)

Instead of changing definer, you can also check permissions for the "INVOKER", check https://dev.mysql.com/doc/refman/8.0/en/create-procedure.html

Regards,

Many thanks for the response.

Yes, I do agree that information_schema tables are there to view routines, tables, views etc... But my concern is, we cannot edit anything in information_schema tables just like that we do in mysql.proc table. And my question is, is there any alternative in version 8, so that I can do the same here. 

Also when I migrated my stored procedures from version 5.6 to 5.7, I came across an error i.e., Incorrect usage of UNION and LIMIT.
I just updated the body utf8 column in mysql.proc using replace function. All those stuffs are not available in version 8, or if I am wrong please do correct me.

Thanks in advance

Hi Mr. J,

Thank you for your bug report.

However, what you are asking for is not available in 8.0. You can use ALTER PROCEDURE or DROP / CREATE PROCEDURE to accomplish the same task.

Not a bug.