Stored procedures are also used to enhance the security. The user needs the EXECUTE privilege on the stored procedure to execute it. By the definition of a stored routine:
- The DEFINER clause specifies the creator of the stored routine. If nothing is specified, the current user is taken.
- The SQL SECURITY clause specifies the execution context of the stored routine. It can be either DEFINER or INVOKER.
DEFINER: A user even with only the EXECUTE permission for routine can call and get the output of the stored routine, regardless of whether that user has permission on the underlying tables or not. It is enough if DEFINER has privileges.
INVOKER: The security context is switched to the user who invokes the stored routine. In ...