27.6 Stored Object Access Control (original) (raw)

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 its DEFINER attribute andSQL SECURITY characteristic.

The DEFINER Attribute

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

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

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 an SQL SECURITY characteristic with a value of DEFINER orINVOKER to specify whether the object executes in definer or invoker context. If the 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:

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 DEFINER
BEGIN
  UPDATE t1 SET counter = counter + 1;
END;

Any user who has the EXECUTE privilege for p1 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 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 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 may be problematic in these ways:

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:

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 a DEFINER 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 object DEFINER values exist and which objects have a particular DEFINER value:

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: 

SELECT EVENT_SCHEMA, EVENT_NAME FROM INFORMATION_SCHEMA.EVENTS  
WHERE DEFINER = 'user_name@host_name';  
SELECT ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE  
FROM INFORMATION_SCHEMA.ROUTINES  
WHERE DEFINER = 'user_name@host_name';  
SELECT TRIGGER_SCHEMA, TRIGGER_NAME FROM INFORMATION_SCHEMA.TRIGGERS  
WHERE DEFINER = 'user_name@host_name';  
SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS  
WHERE DEFINER = 'user_name@host_name';

For the ROUTINES table, the query includes the ROUTINE_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: