How to use procedures to increase security in MySQL
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 proceduresOne 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 nothingBut 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> exitAnd 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 successWe'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: