MySQL Audit triggers

-

Introduction

How do you enable auditing in MySQL? There are multiple options including plugins, external tools, custom scripts etc. Among these using triggers is a popular and straight forward method when the audit scope is well defined. Specially when it is required set of application tables for DML this is a suitable option.

Generating audit triggers can be tedious task as it will be repetitive task to create the triggers. Also, over time it would be difficult to track what tables are being audited for which options. Also using ad hoc audit triggers results in ad hoc audit meta data.

Solution

What if we can use built in MySQL procedure to have standard way to enable DML audit for given set of tables and track what tables being audited for which audit options.

https://github.com/stefanhubb4/MySQLAudit_triggers.git

This repo provide MySQL procedures that will help in creating DML (update, insert, delete) audit triggers quickly while managing metadata on audited tables. As of now repo consists of six sql files.

Github Repo Usage guide

1_create_audit_database_user_Proc.sql

Need to be executed first on the MySQl database instance audited. This will,

  • Create “audit” database - Will contain audit meta data table and audit data tables.
  • Create “auditmgr” user – owns the audit database and its objects.
  •  Create “audit_meta_data” table – list audited tables and the enabled audit options (insert, delete, update)

2_create_table_add_Proc.sql

Need to be executed inside audit database created on step 1. Create the procedure “create_audit_table” which used to add audited tables to system. Example in order to audit table product on test database run,

call create_audit_table('audit' , 'test.product');.

This will,

  •  Create audit data table named, “audit_<table_schema>_<table_name>” .e.g audit_test_product as per example.
  • Insert row to audit_meta_data table corresponding to given table. e.g as per example ->

table added to audit_meta_data






3_enable_update_audit_Proc.sql

Need to be executed inside audit database. Create the procedure “proc_create_after_update_trigger” which when run with audited table as input parameter (e.g test.product in step two) will print the required after update trigger to enable Update auditing.

 If the trigger command is executed any update on audited table will insert audit data on relevant audit table.

e.g in test.product table example if after update trigger is created and any update happen it will have audit data on audit.audit_test_product table in below format,

After update audit trigger generate audits






4_enable_insert_audit_Proc.sql

Need to be executed inside audit database. Simler to above but this is for insert. Create the procedure “proc_create_after_insert_trigger” which when run with audited table as input parameter (e.g test.product in step two) will print the required after insert trigger to enable Update auditing.

5_enable_delete_audit_Proc.sql

Need to be executed inside audit database. Simler to above but this is for delete. Create the procedure “proc_create_after_delete_trigger” which when run with audited table as input parameter (e.g test.product in step two) will print the required after delete trigger to enable Update auditing.

Once a table is added to audit system and all three (insert, delete, updated) trigger types are generated and created using above procedures the audit data generation will continue. Below is sample audit data generated for test.product table we discussed as the example.

trigger based mysql audit data





The generated audit table will contain all the columns of original table to include the actual data that got updated (OLD and NEW both), deleted or inserted. Then there are five audit meta data columns,

  • id_trigtrack – audit record id
  • datetime_trigtrack – data changed time
  • user_trigtrack – user who did the change
  • host_trigtrack – connected hostname of user who made the change
  • audit_type_trigtrack – UPDATE_NEW&OLD in case of update, INSERT or DELETE

Conclusion

Overall, this can be a nice entry point for managed trigger based MySQL DML audit system which enable uniform audit data management within MySQL without any plugins or external tools. It do has some clear improvement point including wrapper function for each procedure, simplified input parameter management, ability to disable any single audit option etc.

Comments

Post a Comment

Popular posts from this blog

ORA-16433: The database or pluggable database must be opened in read/write

-
Image
ORA-16433 is an error returned when a database cannot be opened. This can be a result of multiple conditions including, The inconsistency of the database Physical corruption Loss of dependent files or access issues etc.. More often than not the solution is to recover from available backups, but there can be situations where backups are not available, specially on UAT or Test setups. Although not the ideal scenario, in this type of situation you might want to open the databases even with inconsistency.  In Comes --> _ALLOW_RESETLOGS_CORRUPTION First, if you are in a position to use _ALLOW_RESETLOGS_CORRUPTION=TRUE, that means it has to be a critical moment, and when you come across more ora- errors while you are trying to reopen the databsae, that can be annoying. Note - _ALLOW_RESETLOGS_CORRUPTION=TRUE must only be used after Oracle support confirmation as a last resort. Also make sure to take cold backup before proceeding. Basic steps to be followed to resolve ORA 16433 af...
Read more

Oracle Multitenant - Create new service for PDB using DBMS_SERVICE

-
Image
When an Oracle PDB is created it starts up a default service with the PDB name. For direct connections to    PDB this service can be used. But there are situations where new service name is required in PDB level, common example is after a PDB restoration/clone, in order to enable application connections without changes in connection string. Setting service_names parameter in PDB level is not possible hence it is not an option in multitenant setup. How to create a new PDB service on Oracle 12C Check the current services, here only the default service is running, SQL> show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 3 NFACTORPDB READ WRITE NO SQL> select service_id, name, pdb from dba_Services; SERVICE_ID NAME PDB ---------- ---------------------------------------- ---------- 7 nfactorpdb NFACTORPDB SQL> ...
Read more

Wait for unread message on broadcast channel - Blocking Sessions

-
Image
What is Wait for unread message on broadcast channel wait event? Wait for unread message on broadcast channel is a wait event where source destination communication is involved and the database is waiting for a reply from remote party. Mostly this wait is seen related to data pump export (expdp) or import (impdp) operations. Although categorized in idle wait class "Wait for unread message on broadcast channel" can become the blocking session for some other sessions. Common causes for stuck sessions waiting on this wait event includes, Broken databases links, where remote database is not responding. Abnormally killed remote processes causing the sessions to wait. When a session is waiting on "Wait for unread message on broadcast channel" it can become a blocking session for dependent sessions. Most probably these sessions will be waiting on "library cache pin" or related concurrency wait events. The blocking_session column of (g)v$session view...
Read more