How to use procedures to increase security in MySQL

Posted in: MySQL, Open Source

MySQL privilege system is small, almost all administrative tasks can be completed using a handful of privileges. If we exclude generic ones as ALL, SHOW DATABASES and USAGE, create and drop permissions as CREATE USER, DROP ROLE or CREATE TABLESPACE, the number of privileges remaining is really limited: PROCESS, PROXY, RELOAD, REPLICATION_CLIENT, REPLICATION_SLAVE, SHUTDOWN, and SUPER.

Having such a reduced list of privileges means that it is very difficult to control what a connected session can do. For example, if a user has privileges to stop replication, it also has privileges to start it, and also to configure it. Actually, it has rights to do almost everything as the privilege required to stop replication is SUPER.

MySQL 8 improves this by introducing Dynamic Privileges. There are 18 Dynamic Privileges. But again the granularity of these privileges is really reduced. For example, REPLICATION_SLAVE_ADMIN allows the user to start, stop, change master and change replication filters. Again, if we need to grant an account only the ability to start and stop replication, this is not possible without providing additional permissions.

But how could we avoid granting too much power?


What happens in Procedures stays in Procedures

One interesting feature of procedures, functions, and views is SECURITY CONTEXT. There are two security contexts: INVOKER and DEFINER. A procedure created with the invoker security context will be executed using the privileges of the invoker account. But the default security context is definer. A procedure created with the definer security context will be executed with the privileges of the definer at execution time.

Actually, during the execution of a procedure created using the definer security context, the processlist table and show processlist command will display the definer in the user column instead of the connected user.

This means that using procedures is really a great way to raise the permissions and execute privileged code. The privileges remain restricted to the code within the procedure.


Impossible is nothing

But what can procedures do? What are the limitations of code executed within a procedure? Well, it is possible to run almost any MySQL statement in a procedure. You can start and stop replication, change master, change both local and global variables, and more…

The list of statements that are not permitted is: LOCK TABLES/UNLOCK TABLES, ALTER VIEW, LOAD DATA and LOAD TABLE.

Let’s see one example of a valid procedure:

CREATE DEFINER=`root`@`localhost` PROCEDURE show_processlist()
show processlist;
END //

The only small inconvenience is that procedures must belong to a database schema. Let’s see the results of this procedure:

$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 112
Server version: 5.7.17 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> CREATE DATABASE pythian;
mysql> USE pythian;
mysql> DELIMITER //
mysql> CREATE PROCEDURE show_processlist()
    -> BEGIN
    -> show processlist;
    -> END //
Query OK, 0 rows affected (0,00 sec)

mysql> DELIMITER ;
mysql> CREATE USER test_user@'%' identified by 'test';
Query OK, 0 rows affected (0,01 sec)
mysql> GRANT EXECUTE ON PROCEDURE pythian.show_processlist TO test_user;
Query OK, 0 rows affected (0,00 sec)

mysql> exit

And now let’s call the procedure with our unprivileged user:

$ mysql -s -u test_user -ptest pythian
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql> call show_processlist;
Id User Host db Command Time State Info
112 root localhost pythian Sleep 3 NULL
116 root localhost pythian Query 0 checking permissions show processlist
mysql> show grants for current_user();
Grants for test_user@%
GRANT USAGE ON *.* TO 'test_user'@'%'
GRANT EXECUTE ON PROCEDURE `pythian`.`show_processlist` TO 'test_user'@'%'


Preparation is the key to success

We’ve seen that it is possible to execute simple administrative statements from a procedure, but what if we need to execute more complex statements? The answer is a quote from Alexander Graham Bell: “Before anything else, preparation is the key to success” or to be more precise, “Prepared statements are the key to success.” By using prepared statements you can craft the command to execute using parameters or data stored in tables.

Let’s see one example code:

Execute as root:

CREATE PROCEDURE pythian.change_master(newmaster varchar(256))
SET @change_master=CONCAT('CHANGE MASTER TO MASTER_HOST=\'',newmaster,'\'');
PREPARE cmtm FROM @change_master;
SET sql_log_bin=FALSE;
SET sql_log_bin=TRUE;
END //
CREATE PROCEDURE show_slave_status()
show slave status;
END //
GRANT EXECUTE ON PROCEDURE pythian.change_master TO test_user;
GRANT EXECUTE ON PROCEDURE pythian.show_slave_status TO test_user;

Then connect to test_user and check:

mysql> call pythian.show_slave_status;
mysql> call pythian.change_master('master2');
mysql> call show_slave_status\G
*************************** 1. row ***************************
                  Master_Host: master2
                  Master_Port: 3306
                Connect_Retry: 60
          Read_Master_Log_Pos: 4
               Relay_Log_File: iMac-de-Pep-relay-bin.000001
                Relay_Log_Pos: 4
             Slave_IO_Running: No
            Slave_SQL_Running: No
                   Last_Errno: 0
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 0
              Relay_Log_Space: 154
              Until_Condition: None
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
               Last_SQL_Errno: 0
             Master_Server_Id: 0
             Master_Info_File: /opt/mysql/data/
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
           Master_Retry_Count: 86400
                Auto_Position: 0


Security First

We have seen how we can use procedures to add more granularity to MySQL privileges, but you must be careful when developing your administrative procedures as they will be executed with higher privileges. Always sanitize and check your inputs to avoid SQL injection. And remember that code will be replicated to slaves and calling a procedure in the replication chain can be replicated to all the slaves. My recommendation is that you explicitly disable binary logging for the execution of this type of procedures.



Interested in working with Pep? Schedule a tech call.

About the Author

Pep has a broad experience in several database platforms, but in recent years he has focused on MySQL. His work abides by the motto of Mission Control at NASA: "Tough and competent". Tough means you are accountable for what you do or fail to do, it means compromise and responsibility. Competent means that you take nothing for granted and you must never be found short in knowledge and skills. This is how Pep feels and lives database management. He is also interested in applying Lean culture to his job and enjoys living in Barcelona with his kids and his partner.

No comments

Leave a Reply

Your email address will not be published. Required fields are marked *