Movatterモバイル変換

MySQL 9.2 Reference Manual

27.7 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. The privileges applicable to execution of a stored object are controlled by itsDEFINER attribute andSQL SECURITY characteristic.

The DEFINER Attribute

A stored object definition can include aDEFINER attribute that names a MySQL account. If a definition omits theDEFINER attribute, the default object definer is the user who creates it.

The following rules determine which accounts you can specify as theDEFINER attribute for a stored object:

If you have theSET_ANY_DEFINER privilege, you can specify any account as theDEFINER attribute. If the account does not exist, a warning is generated. Additionally, to set a stored objectDEFINER attribute to an account that has theSYSTEM_USER privilege, you must have theSYSTEM_USER privilege.
Otherwise, the only permitted account is your own, specified either literally or asCURRENT_USER orCURRENT_USER(). You cannot set the definer to any other account.

Creating a stored object with a nonexistentDEFINER account creates an orphan object, which may have negative consequences; seeOrphan Stored Objects.

The SQL SECURITY Characteristic

For stored routines (procedures and functions) and views, the object definition can include anSQL SECURITY characteristic with a value ofDEFINER orINVOKER to specify whether the object executes in definer or invoker context. If the definition omits theSQL SECURITY characteristic, the default is definer context.

Triggers and events have noSQL 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 itsDEFINER 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 an administrative account), the object can perform powerful operationsno 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. TheDEFINER attribute has no effect on object execution.

Examples

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

CREATE DEFINER = 'admin'@'localhost' PROCEDURE p1()SQL SECURITY DEFINERBEGIN  UPDATE t1 SET counter = counter + 1;END;

Any user who has theEXECUTE privilege forp1 can invoke it with aCALL statement. However, whenp1 executes, it does so in definer security context and thus executes with the privileges of'admin'@'localhost', the account named as itsDEFINER attribute. This account must have theEXECUTE privilege forp1 as well as theUPDATE privilege for the tablet1 referenced within the object body. Otherwise, the procedure fails.

Now consider this stored procedure, which is identical top1 except that itsSQL SECURITY characteristic isINVOKER:

CREATE DEFINER = 'admin'@'localhost' PROCEDURE p2()SQL SECURITY INVOKERBEGIN  UPDATE t1 SET counter = counter + 1;END;

Unlikep1,p2 executes in invoker security context and thus with the privileges of the invoking user regardless of theDEFINER attribute value.p2 fails if the invoker lacks theEXECUTE privilege forp2 or theUPDATE privilege for the tablet1.

Orphan Stored Objects

An orphan stored object is one for which itsDEFINER attribute names a nonexistent account:

An orphan stored object can be created by specifying a nonexistentDEFINER account at object-creation time.
An existing stored object can become orphaned through execution of aDROP USER statement that drops the objectDEFINER account, or aRENAME USER statement that renames the objectDEFINER account.

An orphan stored object may be problematic in these ways:

Because theDEFINER account does not exist, the object may not work as expected if it executes in definer security context:
- For a stored routine, an error occurs at routine execution time if theSQL 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 theSQL SECURITY value isDEFINER but the definer account does not exist.
The object may present a security risk if the nonexistentDEFINER account is subsequently re-created for a purpose unrelated to the object. In this case, the account“adopts” the object and, with the appropriate privileges, is able to execute it even if that is not intended.

The server imposes the following account-management security checks designed to prevent operations that (perhaps inadvertently) cause stored objects to become orphaned or that cause adoption of stored objects that are currently orphaned:

DROP USER fails with an error if any account to be dropped is named as theDEFINER attribute for any stored object. (That is, the statement fails if dropping an account would cause a stored object to become orphaned.)
RENAME USER fails with an error if any account to be renamed is named as theDEFINER attribute for any stored object. (That is, the statement fails if renaming an account would cause a stored object to become orphaned.)
CREATE USER fails with an error if any account to be created is named as theDEFINER attribute for any stored object. (That is, the statement fails if creating an account would cause the account to adopt a currently orphaned stored object.)

In certain situations, it may be necessary to deliberately execute those account-management statements even when they would otherwise fail. To make this possible, if a user has theALLOW_NONEXISTENT_DEFINER privilege, that privilege overrides the orphan object security checks and the statements succeed with a warning rather than failing with an error.

To obtain information about the accounts used as stored object definers in a MySQL installation, query theINFORMATION_SCHEMA.

This query identifies whichINFORMATION_SCHEMA tables describe objects that have aDEFINER attribute:

mysql> SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS       WHERE COLUMN_NAME = 'DEFINER';+--------------------+------------+| TABLE_SCHEMA       | TABLE_NAME |+--------------------+------------+| information_schema | EVENTS     || information_schema | ROUTINES   || information_schema | TRIGGERS   || information_schema | VIEWS      |+--------------------+------------+

The result tells you which tables to query to discover which stored objectDEFINER values exist and which objects have a particularDEFINER value:

To identify whichDEFINER values exist in each table, use these queries:
```
SELECT DISTINCT DEFINER FROM INFORMATION_SCHEMA.EVENTS;SELECT DISTINCT DEFINER FROM INFORMATION_SCHEMA.ROUTINES;SELECT DISTINCT DEFINER FROM INFORMATION_SCHEMA.TRIGGERS;SELECT DISTINCT DEFINER FROM INFORMATION_SCHEMA.VIEWS;
```
The query results are significant for any account displayed as follows:
- If the account exists, dropping or renaming it causes stored objects to become orphaned. If you plan to drop or rename the account, consider first dropping its associated stored objects or redefining them to have a different definer.
- If the account does not exist, creating it causes it to adopt currently orphaned stored objects. If you plan to create the account, consider whether the orphaned objects should be associated with it. If not, redefine them to have a different definer.
To redefine an object with a different definer, you can useALTER EVENT orALTER VIEW to directly modify theDEFINER account of events and views. For stored procedures and functions and for triggers, you must drop the object and re-create it to assign a differentDEFINER account
To identify which objects have a givenDEFINER account, use these queries, substituting the account of interest foruser_name@host_name:
```
SELECT EVENT_SCHEMA, EVENT_NAME FROM INFORMATION_SCHEMA.EVENTSWHERE DEFINER = 'user_name@host_name';SELECT ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPEFROM INFORMATION_SCHEMA.ROUTINESWHERE DEFINER = 'user_name@host_name';SELECT TRIGGER_SCHEMA, TRIGGER_NAME FROM INFORMATION_SCHEMA.TRIGGERSWHERE DEFINER = 'user_name@host_name';SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.VIEWSWHERE DEFINER = 'user_name@host_name';
```
For theROUTINES table, the query includes theROUTINE_TYPE column so that output rows distinguish whether theDEFINER is for a stored procedure or stored function.
If the account you are searching for does not exist, any objects displayed by those queries are orphan objects.

Risk-Minimization Guidelines

To minimize the risk potential for stored object creation and use, follow these guidelines:

Do not create orphan stored objects; that is, objects for which theDEFINER attribute names a nonexistent account. Do not cause stored objects to become orphaned by dropping or renaming an account named by theDEFINER attribute of any existing object.
For a stored routine or view, useSQL 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 theSET_ANY_DEFINER 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 privilegedDEFINER accounts by not granting them theSET_ANY_DEFINER 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 theEXECUTE 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, and 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 theDEFINER 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.
By default, when a routine with theSQL SECURITY DEFINER characteristic is executed, MySQL Server does not set any active roles for the MySQL account named in theDEFINER clause, only the default roles. The exception is if theactivate_all_roles_on_login system variable is enabled, in which case MySQL Server sets all roles granted to theDEFINER user, including mandatory roles. Any privileges granted through roles are therefore not checked by default when theCREATE PROCEDURE orCREATE FUNCTION statement is issued. For stored programs, if execution should occur with roles different from the default, the program body can executeSET ROLE to activate the required roles. This must be done with caution since the privileges assigned to roles can be changed.

PREV HOME UP NEXT