Rechercher dans le manuel MySQL
24.6 Stored Object Access Control
Stored programs (procedures, functions, triggers, and events) 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 SQL SECURITY
characteristic.
The DEFINER Attribute
All stored object definitions can include a
DEFINER
attribute that names a MySQL account.
If a definition omits the DEFINER
attribute,
the default definer is the user who creates the object.
MySQL uses the following rules to control which accounts a user
can specify in an object DEFINER
attribute:
If you have the
SET_USER_ID
orSUPER
privilege, you can specify any account as theDEFINER
value, although a warning is generated if the account does not exist. Additionally, as of MySQL 8.0.16, to set theDEFINER
attribute for a stored object to an account that has theSYSTEM_USER
privilege, you must have theSYSTEM_USER
privilege.Otherwise, the only permitted account is your own, either specified literally or as
CURRENT_USER
orCURRENT_USER()
. You cannot set the definer to some other account.
Creating a stored object with a nonexistent
DEFINER
account may have negative
consequences:
For a stored routine, an error occurs at routine execution time if the
SQL SECURITY
value isDEFINER
but the definer account does not exist.For a trigger, it is not a good idea for trigger activation to occur until the account actually does exist. Otherwise, the behavior with respect to privilege checking is undefined.
For an event, an error occurs at event execution time if the account does not exist.
For a view, an error occurs when the view is referenced if the
SQL SECURITY
value isDEFINER
but the definer account does not exist.
Definitions for stored routines (procedures and functions) and
views can include an SQL SECURITY
characteristic with a value of DEFINER
or
INVOKER
to specify whether the object
executes in definer or invoker context. If a definition omits
the SQL SECURITY
characteristic, 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 object 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 orSELECT
to select from a view), but during object execution, the invoker's privileges are ignored and only theDEFINER
account privileges matter. If theDEFINER
account has few privileges, the object is correspondingly limited in the operations it can perform. If theDEFINER
account is highly privileged (such as aroot
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 has no effect during object execution.
Consider the following stored procedure, which is declared with
SQL SECURITY DEFINER
to execute in definer
security context:
- END;
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
referenced within the object body.
Otherwise, the procedure fails.
Now consider this stored procedure, which is identical to
p1
except that its SQL
SECURITY
characteristic is INVOKER
:
- END;
Unlike p1
, p2
executes in
invoker security context and thus with the privileges of the
invoking user regardless of the DEFINER
attribute value. p2
fails if the invoker
lacks the EXECUTE
privilege for
p2
or the
UPDATE
privilege for the table
t1
.
To minimize the risk potential for stored object 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 objects while using an account that has the
SET_USER_ID
orSUPER
privilege, specify an explicitDEFINER
attribute that names an account possessing only the privileges required for the operations performed by the object. Specify a highly privilegedDEFINER
account only when absolutely necessary.Administrators can prevent users from creating stored objects that specify highly privileged
DEFINER
accounts by not granting them theSET_USER_ID
orSUPER
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 references to these objects by not granting unauthorized users particular privileges:
However, no such control exists for triggers and events because they always execute in definer context. The server invokes these objects automatically as necessary; users do not reference them directly:
A trigger is activated by access to the table with which it is associated, even ordinary table accesses by users with no special privileges.
An event is executed by the server on a scheduled basis.
In both cases, if the
DEFINER
account is highly privileged, the object may be able to perform sensitive or dangerous operations. This remains true if the privileges needed to create the object are revoked from the account of the user who created it. Administrators should be especially careful about granting users object-creation privileges.
Document created the 26/06/2006, last modified the 26/10/2018
Source of the printed document:https://www.gaudry.be/en/mysql-rf-stored-objects-security.html
The infobrol is a personal site whose content is my sole responsibility. The text is available under CreativeCommons license (BY-NC-SA). More info on the terms of use and the author.
References
These references and links indicate documents consulted during the writing of this page, or which may provide additional information, but the authors of these sources can not be held responsible for the content of this page.
The author This site is solely responsible for the way in which the various concepts, and the freedoms that are taken with the reference works, are presented here. Remember that you must cross multiple source information to reduce the risk of errors.