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)
...
CREATE [DEFINER=user] FUNCTION sfName()
...
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 them 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
the clause with a value of DEFINER
or INVOKER
:
CREATE [DEFINER=user] PROCEDURE spName(parameter_list)
SQL SECURITY [DEFINER | INVOKER]
...
CREATE [DEFINER=user] FUNCTION sfName(parameter_list)
SQL SECURITY [DEFINER | INVOKER]
...
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 the 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
the attribute has no effect during object execution.
Stored object access control example
First, create a new database called testdb
:
CREATE DATABASE testdb;
Second, select the database testdb
to work with:
USE testdb;
Third, create a new table called messages
:
CREATE TABLE messages (
id INT AUTO_INCREMENT,
message VARCHAR(100) NOT NULL,
PRIMARY KEY (id)
);
Fourth, create a stored procedure that inserts a new row into the messages
table:
DELIMITER $$
CREATE DEFINER = root@localhost PROCEDURE InsertMessage(
msg VARCHAR(100)
)
SQL SECURITY DEFINER
BEGIN
INSERT INTO messages(message)
VALUES(msg);
END$$
DELIMITER ;
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';
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;
Seventh, use the dev@localhost
to log in to the MySQL Server:
mysql -u dev@localhost -p
Eight, use the SHOW DATABASES
to display the database that dev@localhost
can access:
mysql> show databases;
Here is the list:
+--------------------+
| Database |
+--------------------+
| information_schema |
| testdb |
+--------------------+
2 rows in set (0.00 sec)
Ninth, select the testdb
database:
mysql> use testdb;
Tenth, call the InsertMessage
procedure to insert a row into the messages
table:
mysql> call InsertMessage('Hello World');
This is the output:
Query OK, 1 row affected (0.01 sec)
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 $$
CREATE DEFINER=root@localhost
PROCEDURE UpdateMessage(
msgId INT,
msg VARCHAR(100)
)
SQL SECURITY INVOKER
BEGIN
UPDATE messages
SET message = msg
WHERE id = msgId;
END$$
DELIMITER ;
The UpdateMessage
has the security context of INVOKER
who will call this stored procedure?
Twelfth, go to the dev@localhost
‘s a session and call the UpdateMessage()
stored procedure:
mysql> call UpdateMessage(1,'Good Bye');
This time the UpdateMessage()
the 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'
In this tutorial, you have learned about the MySQL stored object access control.
0 Comments
CAN FEEDBACK
Emoji