# Stored Routine Privileges

It's important to give careful thought to the privileges associated with [stored functions](https://mariadb.com/docs/server/server-usage/stored-routines/stored-functions) and [stored procedures](https://mariadb.com/docs/server/server-usage/stored-routines/stored-procedures). The following is an explanation of how they work.

## Creating Stored Routines

* To create a stored routine, the [CREATE ROUTINE](https://mariadb.com/docs/server/reference/sql-statements/account-management-sql-statements/grant#database-privileges) privilege is needed. The [SUPER](https://mariadb.com/docs/server/reference/sql-statements/account-management-sql-statements/grant#super) privilege is required if a `DEFINER` is declared that's not the creator's account (see [DEFINER clause](#definer-clause) below). The `SUPER` privilege is also required if statement-based binary logging is used. See [Binary Logging of Stored Routines](https://mariadb.com/docs/server/server-usage/stored-routines/binary-logging-of-stored-routines) for more details.

## Altering Stored Routines

* To make changes to, or drop, a stored routine, the [ALTER ROUTINE](https://mariadb.com/docs/server/reference/sql-statements/account-management-sql-statements/grant#function-privileges) 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](https://mariadb.com/docs/server/server-management/variables-and-modes/server-system-variables#automatic_sp_privileges) variable is set to `0` (it defaults to 1).
* The `SUPER` privilege is also required if statement-based binary logging is used. See [Binary Logging of Stored Routines](https://mariadb.com/docs/server/server-usage/stored-routines/binary-logging-of-stored-routines) for more details.

## Running Stored Routines

* To run a stored routine, the [EXECUTE](https://mariadb.com/docs/server/reference/sql-statements/account-management-sql-statements/grant#function-privileges) privilege is needed. This is also temporarily granted to the creator if they attempt to run their routine unless the [automatic\_sp\_privileges](https://mariadb.com/docs/server/server-management/variables-and-modes/server-system-variables#automatic_sp_privileges) variable is set to `0`.
* The [SQL SECURITY clause](#sql-security-clause) (by default `DEFINER`) specifies what privileges are used when a routine is called. If `SQL SECURITY` is `INVOKER`, the function body are 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.
* The creator of a routine is the account that ran the [CREATE FUNCTION](https://mariadb.com/docs/server/reference/sql-statements/data-definition/create/create-function) or [CREATE PROCEDURE](https://mariadb.com/docs/server/server-usage/stored-routines/stored-procedures/create-procedure) statement, regardless of whether a `DEFINER` is provided. The definer is by default the creator unless otherwise specified.
* The server automatically changes the privileges in the [mysql.proc](https://mariadb.com/docs/server/reference/system-tables/the-mysql-database-tables/mysql-proc-table) table as required, but will not look out for manual changes.

### DEFINER Clause

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`.

### SQL SECURITY Clause

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, are 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.

## Dropping Stored Routines

All privileges that are specific to a stored routine are dropped when a [DROP FUNCTION](https://mariadb.com/docs/server/server-usage/stored-routines/stored-functions/drop-function) or DROP ROUTINE is run. However, if a [CREATE OR REPLACE FUNCTION](https://mariadb.com/docs/server/reference/sql-statements/data-definition/create/create-function) or [CREATE OR REPLACE PROCEDURE](https://mariadb.com/docs/server/server-usage/stored-routines/stored-procedures/create-procedure) is used to drop and replace and the routine, any privileges specific to that routine will not be dropped.

## See Also

* [Changing the DEFINER of MySQL stored routines etc.](https://mariadb.com/blog/changing-definer-mysql-stored-routines-etc) - maria.com post on what to do after you've dropped a user, and now want to change the DEFINER on all database objects that currently have it set to this dropped user.

<sub>*This page is licensed: CC BY-SA / Gnu FDL*</sub>

{% @marketo/form formId="4316" %}


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://mariadb.com/docs/server/server-usage/stored-routines/stored-functions/stored-routine-privileges.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.