4

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?

4 Answers 4

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,

0
3

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 ....
1
  • This will allow the user to view ALL DATA
    – Kelly Bang
    Oct 20, 2021 at 19:17
2

From MySQL 8.0.20, instead of adding the global SELECT permissions (too broad), add the new SHOW_ROUTINE permissions:

GRANT SHOW_ROUTINE ON *.* TO <USER>@<LOCATION>;
2
  • 1
    I've just upgraded from 8.0.12 to 8.0.27, but I'm still seeing "ERROR 3619 (HY000): Illegal privilege level specified for SHOW_ROUTINE" when trying to GRANT SHOW_ROUTINE ON database.* TO ... - any ideas? It works for *.* - can we not limit this to one database?
    – Attie
    Dec 6, 2021 at 22:33
  • 1
    AFAIK it's an all or nothing. Much better than SELECT on *.* but still not ideal.
    – Kelly Bang
    Dec 7, 2021 at 23:41
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.