MySQL Stored Object Access Control

 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.