Access control and account management

Stored programs, including stored procedures, functions, and triggers, are predefined. Their execution privileges are specified by using the DEFINER attribute and the SQL SECURITY characteristic. When the SQL SECURITY clause is set to DEFINER, the DEFINER clause, which is optional, takes effect. When the SQL SECURITY clause is set to INVOKER, the DEFINER clause is meaningless.

DEFINER attribute

The definition of a stored object can contain a DEFINER attribute to specify that the stored object must be executed by a MySQL user. If the DEFINER field is missing in the definition, the user who created the stored object is the definer by default.

If you use a DEFINER account that does not exist to create a stored object, an orphaned object will be created. This may cause a negative impact. For more information, see Orphaned stored objects.

SQL SECURITY characteristic

The definition of a stored routine such as a stored procedure or a function can contain a SQL SECURITY characteristic, whose value can be DEFINER or INVOKER. This characteristic specifies whether the stored object is executed in the security context of its definer or invoker.```` If the SQL SECURITY characteristic is missing in the definition, the stored object is executed in the security context of the definer by default.

A trigger does not have the SQL SECURITY characteristic and is always executed in the security context of the definer. Servers automatically invoke these objects as needed. Therefore, no invoker user is required.

Differences between the security contexts of the definer and invoker:

• A stored object to be executed in the security context of the definer is executed by using the privileges of the account that is named after the DEFINER attribute. These privileges may be totally different from those of the invoker. The invoker must have the privileges to reference objects, such as the privilege to execute the EXECUTE statement to call a stored procedure. However, when the object is executed, the privileges of the invoker will be ignored and only the privileges of the DEFINER account take effect. If the DEFINER account does not have any privilege, the operations that can be performed by the object are limited. If the DEFINER account has high-level privileges, such as account management privileges, the operations executed by this object will not be limited regardless of the invoker.

• A stored routine to be executed in the security context of the invoker can perform only the operations allowed by the privileges of the invoker. The DEFINER attribute does not affect the execution of an object.

In the following example, SQL SECURITY DEFINER is used to declare that the object is to be executed in the security context of the definer.

CREATE DEFINER = 'admin'@'localhost' PROCEDURE proc1()
SQL SECURITY DEFINER
BEGIN
  UPDATE tbl1 SET counter = counter + 1;
END;

In the preceding example, any user with the EXECUTE privilege on proc1 can use the CALL statement to call this procedure, and the proc1 procedure is executed in the security context of the definer. Therefore, the procedure is executed by using the privileges of the 'admin'@'localhost' account, which is named after the DEFINER attribute. This account must have the EXECUTE privilege on proc1 and the UPDATE privilege on the referenced table tbl1. Otherwise, the call to the procedure will fail.

The proc2 procedure in the following example is the same as proc1 in the previous example, except that the SQL SECURITY characteristic is set to INVOKER for the proc2 procedure.

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

Unlike proc1, proc2 is executed in the security context of the invoker. Therefore, it is executed by using the privileges of the invoker regardless of the value of the DEFINER attribute. If the invoker does not have the EXECUTE privilege on proc2 or the UPDATE privilege on tbl1, the call to the proc2 procedure will fail.

Orphaned stored objects

The DEFINER attribute of an orphaned stored object specifies an account that does not exist.

• When you create a stored object, you can specify a DEFINER account that does not exist to create an orphaned stored object.

• You can execute the DROP USER statement to drop the DEFINER account of a stored object or execute the RENAME USER to rename the DEFINER account of the stored object, so that it becomes an orphaned stored object.

An orphaned stored object may have the following issues:

• As the DEFINER account does not exist, the object cannot work as expected if it is executed in the security context of the definer.

  • For a stored routine, if SQL SECURITY is set to DEFINER but the DEFINER account does not exist, an error is returned when the routine is executed.

  • For a trigger, the privilege-related check behavior is undefined. Therefore, we do not recommend that you fire the trigger before the account exists.

• If the DEFINER account that does not exist is re-created later but used for scenarios unrelated to this object, security risks may be caused to this object. In this case, the account will be automatically associated with this object and appropriate execution privileges will be granted to the account.