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

The Geek Diary

CONCEPTS | BASICS | HOWTO

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • Linux Services
    • VCS
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
    • Data Guard
  • DevOps
    • Docker
    • Shell Scripting
  • Interview Questions
  • 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. How to use foreign keys to attain referential integrity in MySQL
  2. Understanding MySQL Privileges
  3. “Access denied for user ‘username’@’hostname’ (using password: YES)” – Error while connecting MySQL with PHP
  4. How to Disable Client Access Control in MySQL
  5. MySQL : How To Find the Slowest Queries
  6. MySQL : how to set (change) user password
  7. System Tuning for MySQL Server
  8. Beginners Guide to MySQL User Management
  9. Understanding MySQL Storage Engines – MyISAM, MEMORY, BLACKHOLE and ARCHIVE
  10. MySQL – How to Set Maximum Rates for Connections and Queries

You May Also Like

Primary Sidebar

Recent Posts

  • How to disable ICMP redirects on CentOS/RHEL
  • What are Oracle Key Vault Roles
  • What Is Oracle Key Vault
  • Auditing with Oracle Database Vault Reports
  • Archives
  • Contact Us
  • Copyright

© 2021 · The Geek Diary