19.6 Access Control for Stored Programs and Views

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, triggers, and events)
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
value of DEFINER or INVOKER
to specify whether the object executes in definer or invoker
context. If the SQL SECURITY characteristic is
omitted, the default is definer context.

Triggers and events 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 DEFINER attribute. These privileges may
be entirely different from those of the invoking user. The
invoker must have appropriate privileges to reference the
object (for example, EXECUTE to
call a stored procedure or
SELECT to select from a view),
but when the object executes, the invoker's privileges are
ignored and only the DEFINER account
privileges matter. If this account has few privileges, the
object is correspondingly limited in the operations it can
perform. If the DEFINER account is highly
privileged (such as a root account), 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 DEFINER attribute can be
specified but has no effect for objects that execute in
invoker context.

Any user who has the EXECUTE
privilege for 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 'admin'@'localhost', the
account named in the DEFINER attribute. This
account must have the EXECUTE
privilege for 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 SQL
SECURITY characteristic is INVOKER:

p2, unlike p1, executes in
INVOKER security context. The
DEFINER attribute is irrelevant and
p2 executes with the privileges of the invoking
user. p2 fails if the invoker lacks the
EXECUTE privilege for
p2 or the UPDATE
privilege for the table t1.

MySQL uses the following rules to control which accounts a user
can specify in an object DEFINER attribute:

You can specify a DEFINER value other than
your own account only if you have the
SUPER privilege.

If you do not have the SUPER
privilege, the only legal user value is your own account,
either specified literally or by using
CURRENT_USER. You cannot set
the definer to some other account.

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
INVOKER in 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
SUPER privilege, specify an
explicit DEFINER attribute that names an
account possessing only the privileges required for the
operations performed by the object. Specify a highly
privileged DEFINER account only when
absolutely necessary.

Administrators can prevent users from specifying highly
privileged DEFINER accounts by not granting
them the SUPER privilege.

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:

A stored procedure or function cannot be referenced by a
user who does not have the
EXECUTE privilege for it.

A view cannot be referenced by a user who does not have
the appropriate privilege for it
(SELECT to select from it,
INSERT to insert into it,
and so forth).

However, no such control exists for triggers because users do
not reference them directly. A trigger always executes in
DEFINER context 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
DEFINER account is highly privileged, the
trigger can perform sensitive or dangerous operations. This
remains true if the SUPER and
TRIGGER privileges needed to
create the trigger are revoked from the account of the user
who created it. Administrators should be especially careful
about granting users that combination of privileges.