I have a series of complicated SELECTS with JOINS and UNIONS which I use frequently that I have placed in stored procedures. I wanted a more secure user set up that could only call these SELECT statements within these stored procedures. It seem the EXECUTE permission alone does not allow me to execute stored procedures which contain SELECT statements, but it also seems that I cannot grant SELECT permissions within the stored procedures without granting them for the whole table/tables. Is there a way to accomplish this in MYSQL, it appears it is possible in SQL Server, but I can't find anything on it in MYSQL. I want to grant full permission to execute everything within my stored procedures, but only when called through my stored procedures.

CodePudding user response：

Grant SELECT permission on the tables only to a special username. Then make that user the definer of the procedure and use the SQL SECURITY DEFINER option in the CREATE PROCEDURE statement.

Give the ordinary users EXECUTE permission on the procedures (see https://dba.stackexchange.com/questions/97719/how-to-grant-execute-on-mysql).

With these procedure settings, when the procedure is running it has permissions based on the definer, allowing it to access tables that the user can't access directly.

CodePudding user response：

GRANT EXECUTE ON PROCEDURE mydb.myproc TO 'someuser'@'somehost';