• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer navigation

The Geek Diary

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • VCS
  • Interview Questions
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
  • DevOps
    • Docker
    • Shell Scripting
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

MySQL : Stored Procedure For Rotating the Audit Log

by admin

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)
Important: The above sample code is meant as an example. Please change to meet your needs and test carefully before deploying to production.

Filed Under: mysql

Some more articles you might also be interested in …

  1. Beginners Guide to Storage Engines in MySQL
  2. How to Restore a Specific Database or Table (MySQL)
  3. Understanding MySQL Query Cache
  4. MySQL Fails to Start Using systemctl On systemd Linux Distributions
  5. How to Rollback RPM Upgrade of the MySQL Server
  6. Understanding the REVOKE statement in MySQL
  7. How to set the default character set in MySQL and how to propagate it in a master-master replication scenario
  8. MySQL : What is the Slow Query Log and How to Control Where the Log is Stored and What is Logged
  9. What are Reserved User Accounts in MySQL
  10. How to List and Set SELinux Context for MySQL Server

You May Also Like

Primary Sidebar

Recent Posts

  • Chezmoi: A multi-machine dotfile manager, written in Go
  • cheat: Create and view interactive cheat sheets on the command-line
  • chars: Display names and codes for various ASCII and Unicode characters and code points
  • chafa: Image printing in the terminal

© 2023 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright