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
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.