Skip to content

Using trigger based stored procedure to create audit table. It follows the wordpress meta data approach to store the changes, so all the data is store in just two centalized tables.

License

Notifications You must be signed in to change notification settings

hotmit/mysql-sp-audit

Folders and files

NameName
Last commit message
Last commit date

Latest commit

 

History

37 Commits
 
 
 
 
 
 
 
 

Repository files navigation

MySQL Audit (mysql-sp-audit)

Using trigger based stored procedure to create audit table. It follows the word press meta data approach to store the changes, so all the data is stores in just two centralized tables.


MySQL Component Requirements

I put the requirement here so in case you want to run this in a lower version of mysql, you'll know where to change.

  • v5.x Trigger support
  • v5.0.10 INFORMATION_SCHEMA.TRIGGERS
  • v5.0.32 DROP ... IF EXISTS
  • v5.6.1 Base64 (not yet implemented)

Usage

  1. Run 'mysql_sp_audit_setup.sql' script.
    • This will create:
      1. zaudit and zaudit_meta tables (the tables that hold the data for all the audits)
      2. Stored procedures:
        1. zsp_generate_audit: generates audit script for one table
        2. zsp_generate_batch_audit: generates a script for multiple table at a time
        3. zsp_generate_remove_audit: generates the script to remove audit from one table
        4. zsp_generate_batch_remove_audit: generates a script for multiple table at a time
  2. To Enable the audit on the table you want.
    • zsp_generate_audit( @audit_schema_name, @audit_table_name, OUT @script, OUT @errors )
       CALL zsp_generate_audit( 'mydb_name', 'my_table_name', @output_script, @output_errors);
       SELECT @output_script, @output_errors;
       
       -- now, copy the output_script column value and save to sql
       -- once you run that newly create sql file, the table you specified  
       -- above will audit all create/delete/update transactions
    • Copy the value from @output_script and run it
      • Now you should see three triggers and 2 new views on the contact table
        • Triggers: zcontact_AINS, zcontact_AUPD, and zcontact_ADEL
        • Views: zvw_audit_contact and zvw_audit_contact_meta

Features

  • Using stored procedures to generate the audit setup and remove scripts
  • The script will includes pre-generated views for easy access to the data
  • Centralized audit data, everything is stored in two table (similar to wordpress meta)
  • Allow the table's schemas to change, just need to rerun the stored procedure * Keep deleted columns data
  • All values are stored as LONGTEXT therefore no blob support (as of now)
  • Allow audit table up to 2 primary keys

Stored Procedures

  • zsp_generate_audit( @audit_schema_name, @audit_table_name, OUT @script, OUT @errors ) * Generate the audit script for one table
  • zsp_generate_batch_audit ( @audit_schema_name, @audit_tables, OUT @script, OUT @errors ) * Put the comma separated list of table names to generate a batch of audit scripts
  • zsp_generate_remove_audit( @audit_schema_name, @audit_table_name, OUT @script ) * Generate the script to remove the triggers and views
  • zsp_generate_batch_remove_audit ( @audit_schema_name, @audit_tables, OUT @script) * Put the comma separated list of table names to generate a batch script that remove all the tables specified

Conflict

  • Since MySQL 5 only allow one trigger per action, you have to merge your existing triggers with our audit trigger.
  • If you already have a trigger on your table, this is how you resolve it:
    • Copy the code for your trigger, then remove it
    • Run zsp_generate_audit()
    • Edit the trigger and add the code you copied to the appropriate trigger

Conventions

All names are prefixed with "z" to stay out of the way of your important stuff


Tables

Audit Table: zaudit
audit_id user table_name pk1 pk2 action time-stamp
Auto-increment, one number for each change User that made the change The table name First primary key Second primary key Insert, update or delete Time the changed occurred
Meta Table: zaudit_meta
audit_meta_id audit_id col_name old_value new_value
Auto-increment, one row for one value Id from audit table Name of the column Old value New value

Generated Views

View: zvw_audit_<table_name>_meta
audit_id audit_meta_id user pk1 pk2 action col_name old_value new_value time-stamp
Audit id Meta id User name/ user id pk1 pk2 Insert, update or delete Column name Old value New value Date time
View: zvw_audit_<table_name>
audit_id user pk1 pk2 action time-stamp col1_old col1_new col2_old col2_new
Audit id User name/id pk1 pk2 Insert, update, delete Date time Col1 old value Col1 new value Col2 old value Col2 new value

About

Using trigger based stored procedure to create audit table. It follows the wordpress meta data approach to store the changes, so all the data is store in just two centalized tables.

Topics

Resources

License

Stars

Watchers

Forks

Packages

No packages published