SQL SECURITY

What is SQL SECURITY in MySQL?

SQL SECURITY specifies whether a view, stored program, or trigger executes with the privileges of its DEFINER or of the INVOKER who calls it.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL SECURITY: Supported: MySQL, MariaDB, Percona Server. Not supported or uses different syntax: PostgreSQL, SQL Server, Oracle, SQLite, Snowflake.

SQL SECURITY Full Explanation

SQL SECURITY is a clause supported by MySQL and MariaDB that controls the privilege context used when a view, stored procedure, stored function, trigger, or event executes. When set to DEFINER, the object runs with the rights of the account named in its DEFINER clause, regardless of who invokes it. When set to INVOKER, the object runs with the rights of the current session user. The clause strengthens security by limiting or extending access in a controlled way and prevents accidental privilege escalation. If SQL SECURITY is omitted, MySQL defaults to DEFINER for routines, triggers, and events, and to DEFINER for views unless explicit default settings have been changed. Only users with the SUPER or SET_USER_ID privilege can create objects with a DEFINER other than themselves. Changes to the DEFINER account (for example, dropping it) can make objects that use SQL SECURITY DEFINER become unusable.

SQL SECURITY Syntax

-- Views
CREATE [OR REPLACE] [ALGORITHM = MERGE|TEMPTABLE|UNDEFINED]
VIEW view_name
AS select_statement
SQL SECURITY { DEFINER | INVOKER };

-- Stored procedures and functions
CREATE PROCEDURE proc_name ( ... )
    SQL SECURITY { DEFINER | INVOKER }
    routine_body;

CREATE FUNCTION func_name ( ... )
    RETURNS data_type
    SQL SECURITY { DEFINER | INVOKER }
    routine_body;

-- Triggers
CREATE TRIGGER trg_name
    trigger_time trigger_event
    ON tbl_name FOR EACH ROW
    SQL SECURITY { DEFINER | INVOKER }
    trigger_body;

-- Events
CREATE EVENT evt_name
    ON SCHEDULE schedule
    SQL SECURITY { DEFINER | INVOKER }
    DO event_body;

SQL SECURITY Parameters

DEFINER (keyword) - Executes the object with the privileges of the account named in the DEFINER clause.
INVOKER (keyword) - Executes the object with the privileges of the session user who calls or fires the object.

Example Queries Using SQL SECURITY

-- Create a view that always runs with the DBA account
CREATE ALGORITHM = MERGE
VIEW sales_summary AS
SELECT salesperson_id, SUM(amount) AS total_sales
FROM sales
GROUP BY salesperson_id
SQL SECURITY DEFINER
DEFINER = 'dba'@'localhost';

-- Create a procedure that runs with the caller's privileges
CREATE PROCEDURE add_order(p_cust INT, p_amt DECIMAL(10,2))
SQL SECURITY INVOKER
BEGIN
    INSERT INTO orders(customer_id, amount)
    VALUES (p_cust, p_amt);
END;

-- Trigger executed with invoker rights
CREATE TRIGGER trg_before_update
BEFORE UPDATE ON accounts
FOR EACH ROW
SQL SECURITY INVOKER
SET NEW.updated_at = NOW();

Expected Output Using SQL SECURITY

The objects are created successfully
When invoked, sales_summary will always run as user dba regardless of the caller, while add_order and trg_before_update will check the caller's rights
If the caller lacks INSERT on orders, add_order will fail with an error

Use Cases with SQL SECURITY

Provide readonly access to sensitive tables via a view that runs with DBA privileges.
Allow non-privileged users to execute maintenance procedures without granting them direct table rights.
Prevent privilege escalation by forcing triggers to run with the calling user's permissions.
Simplify permission management by centralizing rights in the DEFINER account instead of every individual user.

Common Mistakes with SQL SECURITY

Forgetting to include a DEFINER clause, causing the object to inherit the creator's account implicitly.
Dropping or renaming the DEFINER account, which makes SQL SECURITY DEFINER objects invalid.
Assuming INVOKER bypasses all privilege checks; the invoker still needs required permissions.
Mixing up MySQL's SQL SECURITY with PostgreSQL's SECURITY DEFINER keyword, which uses different syntax.

First Introduced In

MySQL 5.0 (views) and 5.0.13 (routines)

Frequently Asked Questions

What is the default if I omit SQL SECURITY?

MySQL defaults to DEFINER for views, routines, triggers, and events. The object runs with the creator's rights unless a different DEFINER is specified.

Can I change SQL SECURITY after creation?

Yes. Use ALTER VIEW, ALTER PROCEDURE, or ALTER EVENT with a new SQL SECURITY clause to switch between DEFINER and INVOKER.

Who can specify a different DEFINER account?

Only users with the SUPER or SET_USER_ID privilege can create or alter an object with a DEFINER that is not themselves.

What happens if the DEFINER account is deleted?

The object becomes invalid. Attempts to invoke it will raise error 1449: The user specified as a DEFINER does not exist.