2

I'm working on creating a 'read-only' mysql database. I want the user to be able to see everything, but not be able to modify anything. My user currently has the following grants:

GRANT USAGE ON . TO 'sqlprostudio-ro'@'%' IDENTIFIED BY PASSWORD '*x'
GRANT SELECT, EXECUTE, SHOW VIEW ON northwind.* TO 'sqlprostudio-ro'@'%'

I can see stored procedures, but if I run:

SHOW CREATE PROCEDURE x

The 'Create procedure' column returns null. Is there a way I can allow a read-only user to see the creation procedure without being able to modify it?

5

the second line in man says:

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.

The other option (just a suggestion) you may design a procedure or function (preferable) to show the info in question.

Regards,

1

Under MySQL 8.0 things have changed, and you have to grant global SELECT privs to the user, so you'll need to do:

GRANT SELECT ON *.* to ....
0

For MySQL 8, connect your database via Workbench, go to Administration -> User and Privileges, and select the user account you want to modify, then switch to "Administrative Roles", tick "SELECT" on right panel (Global Privileges), Click 'Apply' and done.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.