MySQL 5.0 Reference Manual  /  Stored Programs and Views  /  Access Control for Stored Programs and Views

18.5 Access Control for Stored Programs and Views

Stored programs 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, if there is one, their SQL SECURITY characteristic.

All stored programs (procedures, functions, and triggers) and views can have a DEFINER attribute that names a MySQL account. If the DEFINER attribute is omitted from a stored program or view definition, the default account is the user who creates the object.

In addition, stored routines (procedures and functions) and views can have a SQL SECURITY characteristic with a value of DEFINER or INVOKER to specify whether the object executes in definer or invoker context. If the SQL SECURITY characteristic is omitted, the default is definer context.

Triggers 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 program or view 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 when the object executes, the invoker's privileges are ignored and only the DEFINER account privileges matter. If this 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 can be specified but has no effect for objects that execute in invoker context.

Consider the following stored procedure:

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

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. 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()
  UPDATE t1 SET counter = counter + 1;

p2, unlike p1, executes in INVOKER security context. The DEFINER attribute is irrelevant and p2 executes with the privileges of the invoking user. p2 fails if the invoker lacks the EXECUTE privilege for p2 or the UPDATE privilege for the table t1.

MySQL uses the following rules to control which accounts a user can specify in an object DEFINER attribute:

  • You can specify a DEFINER value other than your own account only if you have the SUPER privilege.

  • If you do not have the SUPER privilege, the only legal user value is your own account, either specified literally or by using CURRENT_USER. You cannot set the definer to some other account.

To minimize the risk potential for stored program and view 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 programs or views while using an account that has the 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 specifying highly privileged DEFINER accounts by not granting them the 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 reference to these objects by not granting unauthorized users particular privileges:

    • A stored procedure or function 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 because users do not reference them directly. A trigger always executes in DEFINER context and is activated by access to the table with which it is associated, even ordinary table accesses by users with no special privileges. If the DEFINER account is highly privileged, the trigger can perform sensitive or dangerous operations. This remains true if the SUPER privilege needed to create the trigger is revoked from the account of the user who created it. Administrators should be especially careful about granting users that privilege.

Download this Manual
User Comments
  Posted by Howard Walker on November 23, 2013
If you ever create a view with the default root user, you will find that it will not be possible to upload it to a shared hosting account. Use the username that your web site will use to log on to the site to define your view.
If you need to re-create your view with a new definer,or just with a definer if, for instance, you are changing from 5.1 to 5.5, make sure that you have the original view creation script to hand. So far after weeks of searching this site, I have not as yet found a way to change the definer of a view.
Howard Walker
Sign Up Login You must be logged in to post a comment.