It's important to give careful thought to the privileges associated with stored functions and stored procedures. The following is an explanation of how they work.
CREATE ROUTINE
privilege is needed. The SUPER
privilege is required if a DEFINER
is declared that's not the creator's account (see DEFINER clause below). The SUPER
privilege is also required if statement-based binary logging is used. See Binary Logging of Stored Routines for more details. ALTER ROUTINE
privilege is needed. The creator of a routine is temporarily granted this privilege if they attempt to change or drop a routine they created, unless the automatic_sp_privileges variable is set to 0
(it defaults to 1). SUPER
privilege is also required if statement-based binary logging is used. See Binary Logging of Stored Routines for more details. EXECUTE
privilege is needed. This is also temporarily granted to the creator if they attempt to run their routine unless the automatic_sp_privileges variable is set to 0
. SQL SECURITY clause
(by default DEFINER
) specifies what privileges are used when a routine is called. If SQL SECURITY
is INVOKER
, the function body will be evaluated using the privileges of the user calling the function. If SQL SECURITY
is DEFINER
, the function body is always evaluated using the privileges of the definer account. DEFINER
is the default. Thus, by default, users who can access the database associated with the stored routine can also run the routine, and potentially perform operations they wouldn't normally have permissions for. CREATE FUNCTION
or CREATE PROCEDURE
statement, regardless of whether a DEFINER
is provided. The definer is by default the creator unless otherwise specified. If left out, the DEFINER
is treated as the account that created the stored routine or view. If the account creating the routine has the SUPER
privilege, another account can be specified as the DEFINER
.
This clause specifies the context the stored routine or view will run as. It can take two values - DEFINER
or INVOKER
. DEFINER
is the account specified as the DEFINER
when the stored routine or view was created (see the section above). INVOKER
is the account invoking the routine or view.
As an example, let's assume a routine, created by a superuser who's specified as the DEFINER
, deletes all records from a table. If SQL SECURITY=DEFINER
, anyone running the routine, regardless of whether they have delete privileges, will be able to delete the records. If SQL SECURITY = INVOKER
, the routine will only delete the records if the account invoking the routine has permission to do so.
INVOKER
is usually less risky, as a user cannot perform any operations they're normally unable to. However, it's not uncommon for accounts to have relatively limited permissions, but be specifically granted access to routines, which are then invoked in the DEFINER
context.
All privileges that are specific to a stored routine will be dropped when a DROP FUNCTION or DROP ROUTINE is run. However, if a CREATE OR REPLACE FUNCTION or CREATE OR REPLACE PROCEDURE is used to drop and replace and the routine, any privileges specific to that routine will not be dropped.
© 2019 MariaDB
Licensed under the Creative Commons Attribution 3.0 Unported License and the GNU Free Documentation License.
https://mariadb.com/kb/en/stored-routine-privileges/