24.6 Stored Object Access Control

Table des matières

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 or SUPER privilege, you can specify any account as the DEFINER value, although a warning is generated if the account does not exist. Additionally, as of MySQL 8.0.16, to set the DEFINER attribute for a stored object to an account that has the SYSTEM_USER privilege, you must have the SYSTEM_USER privilege.
Otherwise, the only permitted account is your own, either specified literally or as CURRENT_USER or CURRENT_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 is DEFINER 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 is DEFINER but the definer account does not exist.

Table des matières Haut

The SQL SECURITY Characteristic

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 or SELECT to select from a view), but during object execution, the invoker's privileges are ignored and only the DEFINER account privileges matter. If the DEFINER 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 has no effect during object execution.

Table des matières Haut

Examples

Consider the following stored procedure, which is declared with SQL SECURITY DEFINER to execute in definer security context:

CREATE DEFINER = 'admin'@'localhost' PROCEDURE p1()
SQL SECURITY DEFINER
BEGIN
UPDATE t1 SET counter = counter + 1;
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:

CREATE DEFINER = 'admin'@'localhost' PROCEDURE p2()
SQL SECURITY INVOKER
BEGIN
UPDATE t1 SET counter = counter + 1;
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.

Table des matières Haut

Risk-Minimization Guidelines

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 or 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 creating stored objects that specify highly privileged DEFINER accounts by not granting them the SET_USER_ID or 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 references to these objects by not granting unauthorized users particular privileges:
- A stored routine 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 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.

Rechercher dans le manuel MySQL

Traduction non disponible

Le manuel MySQL n'est pas encore traduit en français sur l'infobrol. Seule la version anglaise est disponible pour l'instant.

Document créé le 26/06/2006, dernière modification le 26/10/2018
Source du document imprimé : https://www.gaudry.be/mysql-rf-stored-objects-security.html

L'infobrol est un site personnel dont le contenu n'engage que moi. Le texte est mis à disposition sous licence CreativeCommons(BY-NC-SA). Plus d'info sur les conditions d'utilisation et sur l'auteur.

Références

Manuel MySQL : https://dev.mysql.com/

Ces références et liens indiquent des documents consultés lors de la rédaction de cette page, ou qui peuvent apporter un complément d'information, mais les auteurs de ces sources ne peuvent être tenus responsables du contenu de cette page.
L'auteur de ce site est seul responsable de la manière dont sont présentés ici les différents concepts, et des libertés qui sont prises avec les ouvrages de référence. N'oubliez pas que vous devez croiser les informations de sources multiples afin de diminuer les risques d'erreurs.

Table des matières Haut