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.
|
1
2
3
4
5
6
7
|
CREATE SCHEMA test_datachange_audit; CREATE TABLE `test_datachange_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_datachange_audit`.`info_cat_test` (`id`, `name`, `desc`, `sec_level`) VALUES ('1', 'fred', 'engineer', 'H'); INSERT INTO `test_datachange_audit`.`info_cat_test` (`id`, `name`, `desc`, `sec_level`) VALUES ('2', 'jill', 'program manager', 'M'); INSERT INTO `test_datachange_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. – you’ll need MySQL EE 8.0.17 or higher – as of this writing 8.0.22 is the latest)
> mysqlsh
|
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.
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.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
DELIMITER $$ CREATE PROCEDURE test_datachange_audit.audit_api_message_emit_sp(name CHAR(20), ttype CHAR(3)) BEGIN DECLARE aud_msg VARCHAR(255); select audit_api_message_emit_udf('sec_level_trigger', 'TRIGGER audit_change_attempt', 'Change H level sec data', 'FOR ', name, 'ACTION',ttype ) into aud_msg; END$$ DELIMITER ; |
Next we need to create trigger on our table
|
1
2
3
4
5
6
7
8
9
10
11
|
DELIMITER $$ CREATE TRIGGER test_datachange_audit.audit_delete BEFORE DELETE ON `test_datachange_audit`.`info_cat_test` FOR EACH ROW BEGIN IF OLD.sec_level = 'H' THEN CALL audit_api_message_emit_sp(OLD.name,'DEL' ); END IF; END$$ DELIMITER ; |
|
1
2
3
4
5
6
7
8
9
10
|
DELIMITER $$ CREATE TRIGGER test_datachange_audit.audit_insert BEFORE INSERT ON `test_datachange_audit`.`info_cat_test` FOR EACH ROW BEGIN IF NEW.sec_level = 'H' THEN CALL audit_api_message_emit_sp(NEW.name,'INS'); END IF; END$$ DELIMITER ; |
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
DELIMITER $$ CREATE TRIGGER test_datachange_audit.audit_update BEFORE UPDATE ON `test_datachange_audit`.`info_cat_test` FOR EACH ROW BEGIN IF OLD.sec_level = 'H' THEN CALL audit_api_message_emit_sp(OLD.name,'UPO'); END IF; IF NEW.sec_level = 'H' THEN CALL audit_api_message_emit_sp(NEW.name, 'UPN'); END IF; END$$ DELIMITER ; |
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.
|
1
2
3
4
5
6
7
8
9
|
DELETE from `test_datachange_audit`.`info_cat_test` where id=1; INSERT INTO `test_datachange_audit`.`info_cat_test` (`id`, `name`, `desc`, `sec_level`) VALUES ('5', 'joey', 'spy', 'H'); INSERT INTO `test_datachange_audit`.`info_cat_test` (`id`, `name`, `desc`, `sec_level`) VALUES ('8', 'jessie', 'engineer', 'L'); UPDATE`test_datachange_audit`.`info_cat_test` set sec_level='H' where id=2; UPDATE`test_datachange_audit`.`info_cat_test` set sec_level='M' where id=2; |
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.
|
1 |
>sudo cat /usr/local/mysql/data/audit.log | grep sec_level_trigger |
|
1
2
3
4
5
6
7
|
{ "timestamp": "2020-11-17 20:04:32", "id": 13, "class": "message", "event": "user", "connection_id": 9, "account": { "user": "root", "host": "localhost" }, "login": { "user": "root", "os": "", "ip": "127.0.0.1", "proxy": "" }, "message_data": { "component": "sec_level_trigger", "producer": "TRIGGER audit_change_attempt", "message": "Change H level sec data", "map": { "Action": "DEL", "FOR ": "fred" } } }, { "timestamp": "2020-11-17 20:04:32", "id": 14, "class": "message", "event": "user", "connection_id": 9, "account": { "user": "root", "host": "localhost" }, "login": { "user": "root", "os": "", "ip": "127.0.0.1", "proxy": "" }, "message_data": { "component": "sec_level_trigger", "producer": "TRIGGER audit_change_attempt", "message": "Change H level sec data", "map": { "Action": "DEL", "FOR ": "joey" } } }, { "timestamp": "2020-11-17 20:04:35", "id": 2, "class": "message", "event": "user", "connection_id": 9, "account": { "user": "root", "host": "localhost" }, "login": { "user": "root", "os": "", "ip": "127.0.0.1", "proxy": "" }, "message_data": { "component": "sec_level_trigger", "producer": "TRIGGER audit_change_attempt", "message": "Change H level sec data", "map": { "Action": "INS", "FOR ": "fred" } } }, { "timestamp": "2020-11-17 20:04:45", "id": 2, "class": "message", "event": "user", "connection_id": 9, "account": { "user": "root", "host": "localhost" }, "login": { "user": "root", "os": "", "ip": "127.0.0.1", "proxy": "" }, "message_data": { "component": "sec_level_trigger", "producer": "TRIGGER audit_change_attempt", "message": "Change H level sec data", "map": { "Action": "DEL", "FOR ": "fred" } } }, { "timestamp": "2020-11-17 20:04:47", "id": 2, "class": "message", "event": "user", "connection_id": 9, "account": { "user": "root", "host": "localhost" }, "login": { "user": "root", "os": "", "ip": "127.0.0.1", "proxy": "" }, "message_data": { "component": "sec_level_trigger", "producer": "TRIGGER audit_change_attempt", "message": "Change H level sec data", "map": { "Action": "INS", "FOR ": "joey" } } }, { "timestamp": "2020-11-17 20:04:51", "id": 2, "class": "message", "event": "user", "connection_id": 9, "account": { "user": "root", "host": "localhost" }, "login": { "user": "root", "os": "", "ip": "127.0.0.1", "proxy": "" }, "message_data": { "component": "sec_level_trigger", "producer": "TRIGGER audit_change_attempt", "message": "Change H level sec data", "map": { "Action": "UPN", "FOR ": "jill" } } }, { "timestamp": "2020-11-17 20:04:54", "id": 2, "class": "message", "event": "user", "connection_id": 9, "account": { "user": "root", "host": "localhost" }, "login": { "user": "root", "os": "", "ip": "127.0.0.1", "proxy": "" }, "message_data": { "component": "sec_level_trigger", "producer": "TRIGGER audit_change_attempt", "message": "Change H level sec data", "map": { "Action": "UPO", "FOR ": "jill" } } }, |
Conclusion
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.