MySQL : Stored Procedure For Rotating the Audit Log

This is a short note on allowing a user without SUPER to rotate the audit log. As the audit_log_flush variable only exists in the GLOBAL scope, it requires the SUPER privilege to set it.

If you don’t want to grant that user the SUPER privilege (this privilege should be given sparingly) an option is to create a simple procedure that flushes the audit log and have that procedure executed with SQL SECURITY DEFINER. This way you can create the procedure with a user that has SUPER, and then grant EXECUTE on the procedure to the users who should be allowed to execute it.

Requirements

The MySQL Enterprise Audit Log Plugin must be installed with the following requirements:

• The MySQL Enterprise Audit Log Plugin is included in MySQL Enterprise Edition: 5.5.28 and later and 5.6 and later: GA releases
• The MySQL Enterprise Audit Log Plugin must be installed and be enabled.
• The MySQL Enterprise Audit Log Plugin must be configured with audit_log_rotate_on_size = 0

Configuration

The following privileges are required:

• The creator of the stored procedure must have SUPER and CREATE ROUTINE on the database where the routine is created.
• The executor of the stored procedure must have EXECUTE on the stored procedure.

1. Rename the audit log file, for example:

$ mv audit.log audit.log.1

2. Execute the procedure:

mysql> CALL flush_audit_log();

SAMPLE CODE

The Sample Code:

DROP PROCEDURE IF EXISTS mysql.flush_audit_log;
DELIMITER //
CREATE PROCEDURE mysql.flush_audit_log()
   SQL SECURITY DEFINER
BEGIN
   IF (@@global.audit_log_rotate_on_size = 0) THEN
       SET GLOBAL audit_log_flush = 1;
   ELSE
       SIGNAL SQLSTATE '01000'
          SET MYSQL_ERRNO  = 1642,
              MESSAGE_TEXT = 'Setting audit_log_flush = 1 only has an effect when audit_log_rotate_on_size = 0';
   END IF;
END//
DELIMITER ;

Sample Output

Sample output from the above sample code:

mysql> CALL flush_audit_log();
Query OK, 0 rows affected (0.01 sec)