« Where's the MySQL Podcast? | Main | Catching up on MySQL »

August 24, 2005

MySQL Changes to Procedure Logging

Perusing the MySQL docs tonight and wanted to note this change. In version 5.0.6 MySQL added writing (correctly) stored routine syntax to the binary log (which is used for replication and data recovery). Along with the CALL statement, CREATE PROCEDURE, ALTER PROCEDURE, DROP PROCEDURE, CREATE FUNCTION, ALTER FUNCTION, and DROP FUNCTION all have been added to the items logged in the binary logs.

This is excellent news. However, this opens up at least one security issue, namely that these statements are replicated and run on slave machines with full privileges (not using the user's permissions) and can lead to unauthorized changes in the slaves.

To secure your routine statements on servers with binary logging enabled, 5.0.6 requires that to CREATE or ALTER procedures or functions one of two conditions must be satisfied:

  1. The user must have the SUPER privilege in addition to having a CREATE/ALTER ROUTINE or CREATE/ALTER FUNCTION privilege.
  2. The log_bin_trust_routine_creators option must be set

Without one of those conditions, a server with binary logging enabled will now give an error stating that either you need the SUPER privilege or you must enable the trust option.

There are a few other things to note about procedure logging.

Posted by mike at August 24, 2005 10:07 PM