Skip to content

Insight and analysis of technology and business strategy

How to use procedures to increase security in MySQL

Pep Pla date icon
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: 
DELIMITER //
 CREATE PROCEDURE show_processlist()
 BEGIN
 show processlist;
 END //
 DELIMITER ;
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
 owners.
 
 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>
 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'@'%'
 mysql>
 

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: 
DELIMITER //
 CREATE PROCEDURE pythian.change_master(newmaster varchar(256))
 BEGIN
 SET @change_master=CONCAT('CHANGE MASTER TO MASTER_HOST=\'',newmaster,'\'');
 PREPARE cmtm FROM @change_master;
 SET sql_log_bin=FALSE;
 EXECUTE cmtm;
 DEALLOCATE PREPARE cmtm;
 SET sql_log_bin=TRUE;
 END //
 CREATE PROCEDURE show_slave_status()
 BEGIN
 show slave status;
 END //
 DELIMITER ;
 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 ***************************
  Slave_IO_State:
  Master_Host: master2
  Master_User:
  Master_Port: 3306
  Connect_Retry: 60
  Master_Log_File:
  Read_Master_Log_Pos: 4
  Relay_Log_File: iMac-de-Pep-relay-bin.000001
  Relay_Log_Pos: 4
  Relay_Master_Log_File:
  Slave_IO_Running: No
  Slave_SQL_Running: No
  Replicate_Do_DB:
  Replicate_Ignore_DB:
  Replicate_Do_Table:
  Replicate_Ignore_Table:
  Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
  Last_Errno: 0
  Last_Error:
  Skip_Counter: 0
  Exec_Master_Log_Pos: 0
  Relay_Log_Space: 154
  Until_Condition: None
  Until_Log_File:
  Until_Log_Pos: 0
  Master_SSL_Allowed: No
  Master_SSL_CA_File:
  Master_SSL_CA_Path:
  Master_SSL_Cert:
  Master_SSL_Cipher:
  Master_SSL_Key:
  Seconds_Behind_Master: NULL
 Master_SSL_Verify_Server_Cert: No
  Last_IO_Errno: 0
  Last_IO_Error:
  Last_SQL_Errno: 0
  Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
  Master_Server_Id: 0
  Master_UUID:
  Master_Info_File: /opt/mysql/data/master.info
  SQL_Delay: 0
  SQL_Remaining_Delay: NULL
  Slave_SQL_Running_State:
  Master_Retry_Count: 86400
  Master_Bind:
  Last_IO_Error_Timestamp:
  Last_SQL_Error_Timestamp:
  Master_SSL_Crl:
  Master_SSL_Crlpath:
  Retrieved_Gtid_Set:
  Executed_Gtid_Set:
  Auto_Position: 0
  Replicate_Rewrite_DB:
  Channel_Name:
  Master_TLS_Version:
 

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.  
Pep Pla

Top Categories

  • There are no suggestions because the search field is empty.

Tell us how we can help!
dba-cloud-services
Upcoming-Events-banner