MySQL Stored Object Access Control
Summary: in this tutorial, you will learn about the stored object access control in MySQL.
In MySQL, stored routines (stored procedures and functions), triggers, events, and views execute within a security context which determines their privileges.
MySQL uses DEFINER and SQL SECURITY characteristics to control these privileges.
The DEFINER attribute
When you define a stored routine such as a stored procedure or function, you can optionally specify the DEFINER attribute, which is the name of a MySQL account:
CREATE [DEFINER=user] PROCEDURE spName(parameter_list)
...…
Code language: SQL (Structured Query Language) (sql)
If you skip the DEFINER attribute, the default is the current user account.
You can specify any account in the DEFINER attribute if you have the SUPER or SET_USER_ID privilege. If you specify the user account that does not exist, MySQL will issue a warning.
As of MySQL 8.0.16, you must have the SYSTEM_USER privilege in order to set the DEFINER attribute for a stored object to a user account that has the SYSTEM_USER privilege.
The SQL SECURITY characteristic
Stored routines (stored procedures and functions) and views can include an SQL SECURITY clause with a value of DEFINER or INVOKER:
CREATE [DEFINER=user] PROCEDURE spName(parameter_list)
SQL SECURITY [DEFINER | INVOKER]
...SQL SECURITY [DEFINER | INVOKER]
…
Code language: SQL (Structured Query Language) (sql)
SQL SECURITY DEFINER
When you use the SQL SECURITY DEFINER for a stored object, it will execute in definer security context with the privilege of the user specified in by the DEFINER attribute.
Note that the user that invokes the stored object (or invoker) may not have the same privilege as the definer.
In case the invoker has the least privilege and the definer has most privileges, the invoker can perform operations above its privilege within the stored object.
SQL SECURITY INVOKER
If you use the SQL SECURITY INVOKER for a stored routine or view, it will operate within the privileges of the invoker.
The DEFINER attribute has no effect during object execution.
Stored object access control example
First, create a new database called testdb:
CREATE DATABASE testdb;
Code language: SQL (Structured Query Language) (sql)
Second, select the database testdb to work with:
USE testdb;
Code language: SQL (Structured Query Language) (sql)
Third, create a new table called messages:
CREATE TABLE messages (
id INT AUTO_INCREMENT,
message VARCHAR(100) NOT NULL,
PRIMARY KEY (id)
);
Code language: SQL (Structured Query Language) (sql)
Fourth, create a stored procedure that inserts a new row into the messages table:
DELIMITER $$msg VARCHAR(100)
)
SQL SECURITY DEFINER
BEGIN
INSERT INTO messages(message)
VALUES(msg);
END$$
Code language: SQL (Structured Query Language) (sql)
In this stored procedure, the definer is root@localhost that is the superuser which has all privileges.
The SQL Security is set to the definer. It means that any user account which calls this stored procedure will execute with all privileges of the definer i.e., root@localhost user account.
Fifth, create a new user named dev@localhost:
CREATE USER dev@localhost
IDENTIFIED BY 'Abcd1234';
Code language: SQL (Structured Query Language) (sql)
Sixth, grant the EXECUTE privilege to dev@localhost so that it can execute any stored procedure in the testdb database:
GRANT EXECUTE ON testdb.*
TO dev@localhost;
Code language: SQL (Structured Query Language) (sql)
Seventh, use the dev@localhost to log in to the MySQL Server:
mysql -u dev@localhost -p
Code language: SQL (Structured Query Language) (sql)
Eight, use the SHOW DATABASES to display the database that dev@localhost can access:
mysql> show databases;
Code language: SQL (Structured Query Language) (sql)
Here is the list:
+--------------------+
| Database |
+--------------------+
| information_schema |
| testdb |
+--------------------+
2 rows in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)
Ninth, select the testdb database:
mysql> use testdb;
Code language: SQL (Structured Query Language) (sql)
Tenth, call the InsertMessage procedure to insert a row into the messages table:
mysql> call InsertMessage('Hello World');
Code language: SQL (Structured Query Language) (sql)
This is the output:
Query OK, 1 row affected (0.01 sec)
Code language: SQL (Structured Query Language) (sql)
Even though dev@localhost does not have any privilege on the messages table, it can insert a new row into that table successfully via the stored procedure because the stored procedure executes in the security context of the root@localhost user account.
Eleventh, go to the root’s session and create a stored procedure that updates the messages table:
DELIMITER $$PROCEDURE UpdateMessage(
msgId INT,
msg VARCHAR(100)
)
SQL SECURITY INVOKER
BEGIN
UPDATE messages
SET message = msg
WHERE id = msgId;
END$$
Code language: SQL (Structured Query Language) (sql)
The UpdateMessage has the security context of INVOKER who will call this stored procedure.
Twelfth, go to the dev@localhost‘s session and call the UpdateMessage() stored procedure:
mysql> call UpdateMessage(1,'Good Bye');
Code language: SQL (Structured Query Language) (sql)
This time the UpdateMessage() stored procedure executes with the privileges of the caller which is dev@localhost.
Because dev@localhost does not have any privileges on the messages table, MySQL issues an error and rejects the update:
ERROR 1142 (42000): UPDATE command denied to user 'dev'@'localhost' for table 'messages'
Code language: SQL (Structured Query Language) (sql)
In this tutorial, you have learned about the MySQL stored object access control.