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.
|
1
2
3
4
5
6
7
|
CREATE SCHEMA test_sel_audit; CREATE TABLE `test_sel_audit`.`info_cat_test` ( `id` INT NOT NULL, `name` VARCHAR(20) NULL, `desc` VARCHAR(20) NULL, `sec_level` CHAR(1) NULL, PRIMARY KEY (`id`)); |
Lets add a few rows of data.
|
1
2
3
|
INSERT INTO `test_sel_audit`.`info_cat_test` (`id`, `name`, `desc`, `sec_level`) VALUES ('1', 'fred', 'engineer', 'H'); INSERT INTO `test_sel_audit`.`info_cat_test` (`id`, `name`, `desc`, `sec_level`) VALUES ('2', 'jill', 'program manager', 'M'); INSERT INTO `test_sel_audit`.`info_cat_test` (`id`, `name`, `desc`, `sec_level`) VALUES ('3', 'joe', 'maintenance', 'L'); |
Enable EE audit (requires EE – connecting with shell shows my version.
>mysqlsh
|
1 |
mysqlsh> \connect newuser@localhost |
Server version: 8.0.21-commercial MySQL Enterprise Server – Commercial
Or
|
1 |
mysql> select @@version; |
> bin/mysql -u root -p
|
1 |
INSTALL COMPONENT "file://component_audit_api_message_emit"; |
In the [mysqld] enable auditing at startup and set your options. For example:
> vi /etc/my.cnf
|
1
2
3
4
|
plugin-load-add=audit_log.so audit-log=FORCE_PLUS_PERMANENT audit-log-format=JSON audit-log-strategy=SYNCHRONOUS |
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
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
DELIMITER $$ CREATE FUNCTION audit_api_message_emit_FN(name CHAR(20)) RETURNS VARCHAR(2) DETERMINISTIC BEGIN DECLARE aud_msg VARCHAR(255); select audit_api_message_emit_udf('sec_level_selected', 'audit_selected', 'Sensitive Data Selected', 'FOR ', name ) into aud_msg; RETURN('OK'); END$$ DELIMITER ; |
Ok – lets run a select to demonstrate the premise how to build select auditing.
|
1
2
3
4
5
6
7
8
9
10
|
SELECT `info_cat_test`.`id`, `info_cat_test`.`name`, `info_cat_test`.`desc`, `info_cat_test`.`sec_level`, IF(`info_cat_test`.`sec_level` = 'H', AUDIT_API_MESSAGE_EMIT_FN(name), CAST('NA' AS CHAR)) FROM `test_trigger`.`info_cat_test`; |
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.
|
1 |
>sudo cat /usr/local/mysql/data/audit.log | egrep "H level sec data retrieved" | egrep fred |
|
1
2
3
4
5
6
7
|
{ "timestamp": "2020-08-24 18:42:46", "id": 10, "class": "message", "event": "user", "connection_id": 10, "account": { "user": "root", "host": "localhost" }, "login": { "user": "root", "os": "", "ip": "::1", "proxy": "" }, "message_data": { "component": "sec_level_selected", "producer": "SELECT Audit", "message": " H level sec data retrieved", "map": { "FOR ": "fred" } } }, |
If I just ran
|
1 |
select * from FROM `test_sel_audit`.`info_cat_test`; |
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.
|
1
2
3
4
5
6
|
{ "timestamp": "2020-08-24 18:47:07", "id": 2, "class": "general", "event": "status", "connection_id": 10, "account": { "user": "root", "host": "localhost" }, "login": { "user": "root", "os": "", "ip": "::1", "proxy": "" }, "general_data": { "command": "Query", "sql_command": "select", "query": "select * FROM `test_sel_audit`.`info_cat_test`\nLIMIT 0, 1000", "status": 0 } }, |
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.
|
1
2
3
4
5
6
7
8
9
|
create view audit_cat_test as SELECT `info_cat_test`.`id`, `info_cat_test`.`name`, `info_cat_test`.`desc`, `info_cat_test`.`sec_level` FROM `test_trigger`.`info_cat_test` where length(IF(`info_cat_test`.`sec_level`= 'H', audit_api_message_emit_FN(name), CAST('NA' as CHAR))) = 2; |
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
|
1 |
select * from audit_cat_test; |
A quick look into the audit log and I see that Fred was accessed.
|
1
2
3
4
5
6
|
{ "timestamp": "2020-08-25 13:58:11", "id": 2, "class": "message", "event": "user", "connection_id": 10, "account": { "user": "root", "host": "localhost" }, "login": { "user": "root", "os": "", "ip": "localhost", "proxy": "" }, "message_data": { "component": "sec_level", "producer": "audit_selected", "message": "Sensitive Data Selected", "map": { "FOR ": "fred" } } }, |
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.