MySQL Stored Procedure Programming

Execution Mode Options for Stored Programs

Stored program code differs from any other kind of code that might execute against the database in that it can have database privileges that are different from those of the account that executes the stored program. Normally, when we execute some SQL—whether it is inside the MySQL client, a PHP program, or whatever—the activities that the SQL will perform (read table X, update table Y, etc.) will be checked against the privileges that are associated with the database account to which we are connected. If our account lacks privilege to perform the activity, the SQL statement will fail with the appropriate error.

Stored programs can be defined to act in the same way, if the SQL SECURITY INVOKER clause is included in the CREATE PROCEDURE or CREATE FUNCTION statement used to create the program. However, if SQL SECURITY DEFINER (the default) is specified instead, then the stored program executes with the privilege of the account that created the stored program, rather than the account that is executing the stored program. Known as definer rights, this execution mode can be a very powerful way of restricting ad hoc table modifications and avoiding security breaches. Definer rights can also be a problem, however, if you are relying on traditional security privileges to secure your database.

Let’s go through a quick example before we dig in more deeply. A user creates a procedure to execute a simple transaction, as shown in Example 18-1.

Example 18-1. Simple ...