MySQL User Can't View, Edit or Debug Stored Procedures (Amazon RDS)

383 views Asked by At

I had some issues about the creation of new users for my MySQL RDS. The process was the following:

CREATE USER 'james'@'%' IDENTIFIED BY 'AdminTest2022';
GRANT ALL PRIVILEGES ON testdatabase.* TO 'james'@'%';
FLUSH PRIVILEGES;

So, connecting with the previous user and making some privileges testing I found most of them are ok but I found a problem when I tried to access to the stored procedures

Access to SP Error

When I checked the privileges of the user

SHOW GRANTS FOR 'james'@'%';

I got

GRANT USAGE ON *.* TO `james`@`%`
GRANT ALL PRIVILEGES ON `testdatabase`.* TO `james`@`%` WITH GRANT OPTION

Why I can't view, edit or debug any SP if my user has all the privileges over it?

By the way

Engine version: 8.0.28
Tool: dbForge Studio 2020 for MySQL 9.0.597

Also from the beginning of my RDS creation I got

log_bin_trust_function_creators = 1

I'm out of ideas, I'd appreciate any suggestion, I didn't find similar questions about this issue so far and that's why I'm asking this.

1

There are 1 answers

0
jelongpark On BEST ANSWER

Unfortunately, GRANT ALL PRIVILEGES ON testdatabase permission do not give to see the procedure or function code in the database. (I know it sucks!)

You can do Grant show_routine on testdatabase.* to user@% ; then the user can see the code for ALL proc/function even out of the database. it's a SERVER scope privilege. please refer to this https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_show-routine https://dev.mysql.com/doc/refman/8.0/en/show-procedure-code.html