Auditing Selection of Classified Data Stored in MySQL 8.0

The Challenge

Often with sensitive information, you need to have an audit log. Not just that a table had a select run, but that specific cells within the table were accessed.  Frequently data such as this will contain a classification level as part of the row, defining policies for how it is handled, audited, etc.
Sensitive Data such as that 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 select/read audits in MySQL Audit.  But in that case you can not tell if sensitive data was selected or not, just that a select was run on the table and the sql statement used to run that select.

A Solution

Although not entirely obvious, there is more than one way to accomplish auditing data based on the data was selected.  Here’s one example that solves the challenge.
Our example table is simple, id, name, desc, and has an additional column for sec_level.  We want to audit  selects to a row where the sec_level is high – H.
Lets add a few rows of data.
Enable EE audit  (requires EE – connecting with shell shows my version.
>mysqlsh
Server version: 8.0.21-commercial MySQL Enterprise Server – Commercial
Or
> 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.
First I am going to write a simple function that contains the audit meta data I want to have in my audit trail.
I’ll create a simple wrapper function
Ok – lets run a select to demonstrate the premise how to build select auditing.
As you can see, there is an IF with a call to udit_api_message_emit_ud when the sec_level is ‘H’. OK shows the H level selection.
We can now see its in the audit log.
If I just ran
I could see the table data was accessed if I had an audit filter in place for this type of SQL event (read) – but as you can see I wouldn’t know if fred was selected.
Now of course I don’t really want the user to see the auditing calls. So instead let’s create a simple view and let’s move the emit audit function into the where part of the select thus making it transparent.
Keep the view simple to ensure the WHERE clause will be sure to execute  the function audit_api_message_emit_FN.
Now I can run the view
A quick look into the audit log and I see that Fred was accessed.

Conclusion

There are other methods for pushing selected data into the MySQL Audit Stream from audit_api_message_emit_udf().  This is just one possible and simple approach.
In a followup blog I’ll provide a simple example for auditing changed – updates, inserts, and deletes – of sensitive classified data on a table.
As always, thanks for using MySQL.

Leave a Reply