Login Audit SQL;

login_audit.sql

/*************************************************
*
* ADD THE FOLLOWING TO PRODUCTION my.cnf
* init_connect = "CALL <database>.login_audit()";
*
*************************************************/
 
/*CREATE TABLE FOR AUDIT DATA COLLECTION*/
 
CREATE TABLE login_audit (
  audit_id SERIAL,
  connection_id BIGINT,
  user VARCHAR(255),
  created_at TIMESTAMP
) ENGINE = MEMORY CHARSET = latin1;
 
/*CREATE PROCEDURE FOR AUDIT DATA COLLECTION*/
 
DELIMITER $$
 
CREATE PROCEDURE login_audit()
SQL SECURITY DEFINER
 
BEGIN
INSERT INTO login_audit
            (
             audit_id,
             connection_id,
             user,
             created_at
            )
      VALUES
            (
             DEFAULT,
             CONNECTION_ID(),
             USER(),
             CURRENT_TIMESTAMP()
            );
 
END $$
 
DELIMITER ;