Auditing Changes to Classified Data Stored in MySQL 8.0

The Challenge

Often with sensitive information, you need to have an audit log. Frequently data such as this will contain a classification level as part of the row, defining policies for how it is handled, audited, etc. In a prior blog I discussed how to audit the selection of classified data. This blog covers how to audit data changes made to classified data.  As I stated in the prior blog –
Sensitive Data might be labeled as –
  •  Highly Sensitive
  •  Top Secret
  •  Classified
  •  Restricted
  •  Clearance Required
  •  Highly Confidential
  •  Protected
Data that is categorized or labeled in some manner is typically covered by regulations requiring your compliance. Compliance requires auditing of events in the database on that data. Especially with administrators who may have data access but in general should not be viewing certain data.
And sensitive data could be interspersed with data with labels such as
  • Public
  • Unclassified
  • Other
Of course, you can turn on general insert/update/select audits in MySQL Audit.  But in that case you will audit all change. Alternatively maybe you just want to audit if sensitive data was changed. Here’s one way you can do that.

A Solution

This example uses MySQL triggers to audit data changes.
Our example table is simple, if contains an id, name, desc, and has an additional column for sec_level.  We want to audit  when a row where the sec_level is high – H is changed – meaning inserted, updated to H or updated from H, or deleted.
Lets add a few rows of data.
Enable EE audit  (requires EE – connecting with shell shows my version. – you’ll need MySQL EE 8.0.17 or higher – as of this writing 8.0.22 is the latest)
> mysqlsh
> bin/mysql -u root -p
In the [mysqld] enable auditing at startup and set your options.  For example:
> vi /etc/my.cnf
Refer to the audit log reference for more details on audit options and variables.
Restart the MySQL Server.
Note: there are ways to enable auditing without a restart. But you way want to FORCE auditing – so above is how you do that.
The following simple procedure will be used to write the audit meta data I want to have in my audit trail. The FOR and ACTION are meta data tags written to the audit log. In this case FOR will have name whose level data is being changed and ACTION will be if an update (before and after), insert, or delete.
Next we need to create trigger on our table

Next run changes on ‘H’ level or ‘M’ and ‘L’
Remember the triggers will only audit if changes are made to ‘H’ sec_level column.
You will see 4 different tags for the ACTION – INS, DEL, UPN (N for new – meaning someone without ‘H’ was updated to ‘H’), and UPO (O for old – meaning someone with ‘H’ was changed from ‘H’)
We can now see its in the audit log.
Note: use the location – by default its your ‘select @@datadir;’
In my case I’ll run the following OS command and look for sec_level_trigger to filter out these audit events from the log.
This is an approach you might take. This is just an example.  Often with auditing its about specificity versus quantity.  And its about assessing the contents of the audit log – so you can spot any misuse.
As always, thanks for using MySQL.

Leave a Reply