MySQL: Securing Access using Stored Procedures

I'm new to using MySQL. I'd like to use stored procedures to help secure my database when accessing it from a web service (i.e., grant only proc execute to the web service account).

What permissions do I need to grant so the web service account does not have table access but the stored procedure the service executes does?

You need to grant the EXECUTE privilege, but there is something else you need to know.

When you create a Stored Procedure (SP), make sure the SQL SECURITY of the SP is DEFINER and not INVOKER. Why?

• When you call a Stored Procedure that has DEFINER for SQL SECURITY, the caller is allowed to have the same grants as the DEFINER for the duration of the call. The GRANT EXECUTE for the specified Stored Procedure is necessary.

• When you call a Stored Procedure that has INVOKER for SQL SECURITY, the caller is expected to have the needed grants. If any of the needed grants are missing, the call will fail at the earliest point where the needed grant was missing.

In plain English, if SP has INVOKER for SQL SECURITY, you have to give the table privileges away to the user permanently before the call is made. When SP has DEFINER for SQL SECURITY, the table privileges are given away temporarily (as long as the SP is running).

I discussed this before

• Sep 11, 2012 : Is it possible to encapsulate a table in MySQL?

• Apr 08, 2013 : Why does this procedure raise a privilege error?

StackExchange Database Administrators Q#44366, answer score: 2