I have users who should be able to see the definition of a procedure without the permission to execute it. However, running SHOW CREATE PROCEDURE on v 6.8.9 currently returns an error that “SHOW CREATE FUNC” is denied. When attempting to grant “SHOW CREATE FUNC” I get a syntax error. I’ve found MySQL documentation that says the user should have SELECT granted on the mysql.proc table, but that table doesn’t appear to exist in MemSQL. How can I give my users the access they need?
I think currently users need to have CREATE ROUTINE or ALTER ROUTINE permission on the said procedure to view the body of that procedure.
We have an internal feature request for adding a separate more granular permission that just allows to view the body of the procedure. Probably will be available in memsql release after 7.0
You see list of all permissions here:SingleStoreDB Cloud · SingleStore Documentation
I am running the below command and its throwing up error:
GRANT SHOW ROUTINE ON *.* TO 'db_swateek'@'%';
The error below:
GRANT SHOW ROUTINE ON *.* TO 'db_swateek'@'%' ERROR 1064 ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ROUTINE ON *.* TO 'db_swateek'@'%'' at line 1
Hi swateek!
Sorry to hear you are experiencing this error. Are you still using 7.3 version ?.
Hello Maria,
Yes this is 7.3.x
Hi Swateek!
It looks like the GRANT SHOW ROUTINE command was introduced in v7.5.
Here are the The GRANT DOCS for 7.3 but they do not include the SHOW ROUTINE command.
The following command works fine on v7.8 if you want to upgreade:
GRANT SHOW ROUTINE ON . TO ‘db_swateek’@‘%’;
Hope this is helpful. Let us know how it goes! 
1 Like