MySQL Stored Procedure security best practises

Question

Sorry if this has been answered before but what is the "correct" way to set up security for a stored procedure?

Scenario is I want execute permissions only for the logged on web user and select permission for the table being queried so I set the web_user account to execute only and have another user with select permissions for the definer(?) with the SQL SECURITY set to DEFINER (?)

• nbk: why run the queries for not logged in users, exclude them in your html/Javascript/php, python code

Answer 1

SQL SECURITY INVOKER

says that anyone can use the routine. And it will act like a shorthand way of performing the the code in the routine. Note that includes being limited to their privilege constraints.

SQL SECURITY DEFINER

means that the user takes on the definer's privileges for the duration of the CALL. A common case is for some privileged person (eg, 'root') to define the routine as a way of letting a less-privileged user do something that they would not have privileges to do. It would be wise to include code in the routine to verify that the caller's arguments are 'valid' for the security desired.

This simple 2-option mechanism covers virtually all cases I have needed.

Meanwhile, here is a guideline for a web site:

• Each "application" should have its own login. (For a simple web site, only one "user" is needed.)
• That login should be GRANTed most or all privileges to one database.
• Extra grants and/or routines can be used for providing limited access to stuff outside that one database. (wg: a common database for counting logins -- to be used by multiple apps.)