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
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.
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