Serious replication problem with stored routines in MySQL 5.0: DEFINER not written to binary log

CREATE PROCEDURE and CREATE FUNCTION in MySQL currently (as of 5.0.18) don't allow for an explicit DEFINER= clause. This has dangerous implications for any setup in which stored routines and replication are used together.

The DEFINER of a stored routine currently is always set to the CURRENT_USER at the time of the CREATE statement, there is no possibility to override this with an explicit setting. If you issue a CREATE command as root on the MySQL server, that's what you get:

mysql-master [root]> CREATE PROCEDURE test() DETERMINISTIC SELECT 'test';
Query OK, 0 rows affected (0.00 sec)

mysql-master [root]> SELECT * FROM information_schema.routines
    WHERE ROUTINE_NAME='test'\G
*************************** 1. row ***************************
     SPECIFIC_NAME: test
               ...
           DEFINER: root@localhost
1 row in set (0.00 sec)

The DEFINER is correctly set to root@localhost. However, as there is no possibility to set a DEFINER explicitly, the information on the DEFINER gets lost in the binary log, which looks something like this:

#060208 22:21:24 server id 101  end_log_pos 775552082  Query  thread_id=446199  exec_time=0  error_code=0
SET TIMESTAMP=1139437284;
CREATE PROCEDURE test() DETERMINISTIC SELECT 'test';

On a slave all the replicated commands from the binary log are executed under the privileges of a special user with any rights. The slave has no possibility to determine the original DEFINER and that's thus what you get:

mysql-slave [root]> SELECT * FROM information_schema.routines
    WHERE ROUTINE_NAME = 'test'\G
*************************** 1. row ***************************
     SPECIFIC_NAME: test
               ...
           DEFINER: @
1 row in set (0.02 sec)

This current situation of DEFINERs not being replicated actually results in an impossibility to use replication and stored procedures together in almost all cases. Just think of what the two features are typically used for:

Typical use of replication
redundancy (backup)
scalability (load balancing for read requests)
Typical use of stored procedures
abstraction
security

To use stored procedures for abstraction and security we need to define them with SQL SECURITY DEFINER (we usually don't want the user who's up to use the procedures to have direct access to the base tables as well). But on the slave such procedures will now be executed in the security context of the INVOKER, as no DEFINER is available, and thus stop to work.

This makes load balancing with procedures of SQL SECURITY DEFINER completely impossible (which is bad enough), but much worse in a redundancy/backup scenario: Whenever you'll have to switch to a slave due to failure of the master in an emergency situation, it will certainly be a very bad surprise that suddenly all your applications stop to work.

Let's just have a look at how procedures with SQL SECURITY DEFINER will fail in a replication setup.

mysql-master [root]> use test
Database changed

mysql-master [root]> CREATE TABLE t (i INT);
Query OK, 0 rows affected (0.03 sec)

mysql-master [root]> INSERT INTO t (i) VALUES (1);
Query OK, 1 row affected (0.00 sec)

We just created a simple table with one row. Now let's create a stored procedure to access the data within it (we do this as user root):

mysql-master [root]> CREATE PROCEDURE tp ()
    -> READS SQL DATA
    -> DETERMINISTIC
    -> SQL SECURITY DEFINER
    -> SELECT * FROM t;
Query OK, 0 rows affected (0.02 sec)

Now we grant execution rights for this procedure to a user testuser, without granting it access to the table itself.

mysql-master [root]> GRANT EXECUTE ON test.* TO testuser@'%' IDENTIFIED BY 'test';
Query OK, 0 rows affected (0.00 sec)

As the procedure was defined with SQL SECURITY DEFINER, it gets executed with the privileges of root even if run by testuser, who can now access the data in the table only through use of the procedure:

mysql-master [testuser]> CALL tp();
+------+
| i    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

But on the slave, where the DEFINER is empty, execution fails:

mysql-slave [testuser]> CALL tp();
ERROR 1142 (42000): SELECT command denied to user 'testuser'@'localhost' for table 't'

This behaviour has been reported as bugs #15963 and #17247 and will hopefully be corrected soon. In the meantime you'll have to pay great care when relying on a replication server as a backup and using stored procedures.

The probable solution will look something like this:

  1. CREATE PROCEDURE and CREATE FUNCTION should allow for an explicit DEFINER= clause, in exactly the same way as CREATE TRIGGER and CREATE VIEW already do. Security considerations for the definer clause (taken from the manual on TRIGGERs and VIEWs):
    • If you do not have the SUPER privilege, the only legal user value is your own account, either specified literally or by using CURRENT_USER. You cannot set the definer to some other account.
    • If you have the SUPER privilege, you can specify any syntactically legal account name. If the account does not actually exist, a warning is generated.
  2. The binary log should always contain an explicit DEFINER= clause, even if no definer was explicitly set in the original statement.
  3. If an according user for a definer of a stored routine doesn't exist and the routine is set to SQL SECURITY DEFINER, the execution of the stored routine should be denied with an error message.