When you create a procedure or function you can specify whether you want the procedure or function to run with the privileges of its owner, or with the privileges of the person or procedure that calls it (the invoker). The identification of the invoker is not always obvious. While a user can invoke a procedure, that procedure can invoke another procedure. In these cases, a distinction is made between the logged in user (the user who makes the initial call to the top level procedure) and the effective user, which may be the owner of a procedure that is called by the initial procedure. When a procedure runs with invoker privileges, the privileges of the effective user are enforced.

When creating a procedure or function, the SQL SECURITY clause of the CREATE PROCEDURE statement or CREATE FUNCTION statement sets which privileges apply when the procedure or function is executed, as well as the ownership of unqualified objects. The choice for this clause is INVOKER or DEFINER. However, a user can create a procedure or function that is owned by another user. In this case, it is actually the privileges of the owner, not the definer.

When creating procedures or function care should be taken to qualify all object names (tables, procedures, and so on) with their appropriate owner. If the objects in the procedure are not qualified as to ownership, ownership is different depending on whether it is running as owner or invoker. For example, suppose user1 creates the following procedure:

CREATE PROCEDURE user1.myProcedure()
   RESULT( columnA INT )
   SQL SECURITY INVOKER
   BEGIN
     SELECT columnA FROM table1;
   END;

If another user, user2, attempts to run this procedure and a table user2.table1 does not exist, an error is returned. If a user2.table1 exists, that table is used, not user1.table1.

When procedures or functions run using the privileges of the invoker, the invoker must have EXECUTE privilege for the procedure, as well as the privileges required for the database objects that the procedure, function, or system procedure operates on.

If you are not sure whether any procedures or functions execute as invoker or definer, you can look at the SQL SECURITY clause in their SQL definitions.

To determine the privileges required to execute a procedure or function that performs privileged operations on the database, you can use the sp_proc_priv system procedure. See sp_proc_priv system procedure.

 See also