mysqldatabaseamazon-rdsdbforge

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

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.

Solution

  • 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