Stored programs and views are defined prior to use and, when
referenced, execute within a security context that determines
their privileges. These privileges are controlled by their
DEFINER attribute, and, if there is one, their
SQL SECURITY characteristic.
All stored programs (procedures, functions, and triggers) and
views can have a
DEFINER attribute that names a
MySQL account. If the
DEFINER attribute is
omitted from a stored program or view definition, the default
account is the user who creates the object.
In addition, stored routines (procedures and functions) and views
can have a
SQL SECURITY characteristic with a
to specify whether the object executes in definer or invoker
context. If the
SQL SECURITY characteristic is
omitted, the default is definer context.
Triggers have no
SQL SECURITY characteristic
and always execute in definer context. The server invokes these
objects automatically as necessary, so there is no invoking user.
Definer and invoker security contexts differ as follows:
A stored program or view that executes in definer security context executes with the privileges of the account named by its
DEFINERattribute. These privileges may be entirely different from those of the invoking user. The invoker must have appropriate privileges to reference the object (for example,
EXECUTEto call a stored procedure or
SELECTto select from a view), but when the object executes, the invoker's privileges are ignored and only the
DEFINERaccount privileges matter. If this account has few privileges, the object is correspondingly limited in the operations it can perform. If the
DEFINERaccount is highly privileged (such as a
rootaccount), the object can perform powerful operations no matter who invokes it.
A stored routine or view that executes in invoker security context can perform only operations for which the invoker has privileges. The
DEFINERattribute can be specified but has no effect for objects that execute in invoker context.
Consider the following stored procedure:
CREATE DEFINER = 'admin'@'localhost' PROCEDURE p1() SQL SECURITY DEFINER BEGIN UPDATE t1 SET counter = counter + 1; END;
Any user who has the
p1 can invoke it with a
CALL statement. However, when
p1 executes, it does so in
DEFINER security context and thus executes with
the privileges of
account named in the
DEFINER attribute. This
account must have the
p1 as well as the
UPDATE privilege for the table
t1. Otherwise, the procedure fails.
Now consider this stored procedure, which is identical to
p1 except that its
SECURITY characteristic is
CREATE DEFINER = 'admin'@'localhost' PROCEDURE p2() SQL SECURITY INVOKER BEGIN UPDATE t1 SET counter = counter + 1; END;
p1, executes in
INVOKER security context. The
DEFINER attribute is irrelevant and
p2 executes with the privileges of the invoking
p2 fails if the invoker lacks the
EXECUTE privilege for
p2 or the
privilege for the table
MySQL uses the following rules to control which accounts a user
can specify in an object
To minimize the risk potential for stored program and view creation and use, follow these guidelines:
For a stored routine or view, use
SQL SECURITY INVOKERin the object definition when possible so that it can be used only by users with permissions appropriate for the operations performed by the object.
If you create definer-context stored programs or views while using an account that has the
SUPERprivilege, specify an explicit
DEFINERattribute that names an account possessing only the privileges required for the operations performed by the object. Specify a highly privileged
DEFINERaccount only when absolutely necessary.
Administrators can prevent users from specifying highly privileged
DEFINERaccounts by not granting them the
Definer-context objects should be written keeping in mind that they may be able to access data for which the invoking user has no privileges. In some cases, you can prevent reference to these objects by not granting unauthorized users particular privileges:
However, no such control exists for triggers because users do not reference them directly. A trigger always executes in
DEFINERcontext and is activated by access to the table with which it is associated, even ordinary table accesses by users with no special privileges. If the
DEFINERaccount is highly privileged, the trigger can perform sensitive or dangerous operations. This remains true if the
SUPERprivilege needed to create the trigger is revoked from the account of the user who created it. Administrators should be especially careful about granting users that privilege.