9

I have created some stored procedure on a specific schema.

In this stored procedure, I want to grant execute privilege.

So I wrote that :

GRANT EXECUTE ON PROCEDURE schema_name.proc_name TO 'user_name';
GRANT SELECT ON mysql.proc to 'user_name';

The problem is : My user can see every stored procedure. I wish he could only see the procedure where he has the EXECUTE privilege.

Is there a way to achieve that ?

Thanks in advance.

TriGerZ
  • 91
  • 1
  • 1
  • 2

2 Answers2

6

Yes... this works as expected if you don't grant the user the SELECT privilege on the mysql.proc table, either directly or indirectly, such as with GRANT SELECT ON *.* TO ...

Without SELECT permission on this table, a user can only see the existence of stored procedures and stored functions where they have other permissions, like EXECUTE.

Under the hood, the lack of SELECT on mysql.proc also prevents the user from seeing the procedures they don't have access to via the information_schema.routines pseudo-table.

You shouldn't need to GRANT SELECT ON mysql.proc to enable the user to execute procedures or functions... and if you do, then that seems like the question.

Michael - sqlbot
  • 169,571
  • 25
  • 353
  • 427
  • In fact, it seems that it's my plugin which caused the issue. I'm using MySQLForExcel to allow users to interact with the DB. You're right, without SELECT ON mysql.proc, I can call my stored procedures without problems. But MySQLForExcel doesn't allow it for some obscurs reason... **Unable to retrieve stored procedure metadata for routine 'truncate_t_qcms_lead_engineer'. Either grant SELECT privilege to mysql.proc for this user or use "check parameters=false" with your connection string.** – TriGerZ Jul 01 '14 at 07:21
-1

Problem solved.

In fact, To be able to execute stored procedure within MySQLForExcel, we need to SET the DEFINER of each stored procedure that we want to be called by a MySQLForExcel user.

DELIMITER $$

DROP PROCEDURE IF EXISTS `procedure_name` $$
CREATE DEFINER=`user_mysqlforexcel` PROCEDURE `procedure_name`(param)
BEGIN
     Do smth as usual
END $$

I found that here

Thank for the help.

TriGerZ
  • 91
  • 1
  • 1
  • 2
  • Actually, that works for the wrong reasons and may have security implications. The correct solution was probably in the original error message: you need CheckParameters=false in the ODBC connection string to disable the brokenness in ODBC that was causing the error. – Michael - sqlbot Jul 01 '14 at 11:58