Audit Log events do not report default DB

[eroomydna]

The DML events logged via PS audit log plugin do not identify the schema that the event occurred against.

JSON
{"audit_record":{"name":"Query","record":"5898439721_2015-03-18T13:56:17","timestamp":"2015-03-22T23:30:42 UTC","command_class":"update","connection_id":"11373","status":0,"sqltext":"update ai_test set id = 10 where id =1","user":"root[root] @ localhost []","host":"localhost","os_user":"","ip":""}}

OLD
<AUDIT_RECORD
  NAME="Query"
  RECORD="32975429_2015-03-22T23:47:13"
  TIMESTAMP="2015-03-23T00:11:20 UTC"
  COMMAND_CLASS="update"
  CONNECTION_ID="2"
  STATUS="0"
  SQLTEXT="update ai_test set id = 10 where id =1"
  USER="root[root] @ localhost []"
  HOST="localhost"
  OS_USER=""
  IP=""
/>

Whilst this is possibly a hangover from upstream compatibility, there could be confusion where multiple schemas have the same tables names. Resolution of where events were executed may become difficult and the audit trail untrusted.

Proposal to add "DB" field to the event.

[eroomydna (andrew-2-moore)]

Also note that both McAfee and MariaDB include the default schema that the event was executed against;

MariaDB (CSV Format)
20150323 00:22:11,ricky.mysqlboy.com,root,localhost,131,22226336,QUERY,test,'select count(*) from rooms',0
                                                                                                                                                         ^
McAfee (JSON Format)
{"msg-type":"activity",
 "date":"1425967153721",
 "thread-id":"2",
 "query-id":"17",
 "user":"root",
 "priv_user":"root",
 "host":"localhost",
 "ip":"",
 "cmd":"select",
 "objects":[{"db":"test","name":"people","obj_type":"TABLE"}],
     "query":"select * from people"}

[Przemek (pmalkowski)]

Indeed the Percona Audit Log Plugin misses the database information, and McAfee solution does it very well. An example comparison:

* McAfee:
mysql [localhost] {msandbox} ((none)) > show plugins;
...
| AUDIT | ACTIVE | AUDIT | libaudit_plugin.so | GPL |

mysql [localhost] {msandbox} ((none)) > show status like 'aud%';
+------------------------+-----------+
| Variable_name | Value |
+------------------------+-----------+
| Audit_protocol_version | 1.0 |
| Audit_version | 1.0.8-527 |
+------------------------+-----------+
2 rows in set (0.00 sec)

Example log entry:

{"msg-type":"activity","date":"1455614754490","thread-id":"1","query-id":"33","user":"msandbox","priv_user":"msandbox","host":"localhost","ip":"","cmd":"select","objects":[{"db":"test","name":"t1","obj_type":"TABLE"},{"db":"db1","name":"t1","obj_type":"TABLE"}],"query":"select a from t1 join db1.t1 using(id)"}

* Percona:

mysql [localhost] {msandbox} ((none)) > show plugins;
...
| audit_log | ACTIVE | AUDIT | audit_log.so | GPL |

mysql [localhost] {msandbox} ((none)) > select @@version,@@version_comment;
+-------------+------------------------------------------------------+
| @@version | @@version_comment |
+-------------+------------------------------------------------------+
| 5.6.28-76.1 | Percona Server (GPL), Release 76.1, Revision 5759e76 |
+-------------+------------------------------------------------------+
1 row in set (0.00 sec)

Example log entry:

{"audit_record":{"name":"Query","record":"3629_2016-02-16T14:09:14","timestamp":"2016-02-16T14:12:40 UTC","command_class":"select","connection_id":"1","status":0,"sqltext":"select a from test.t1 join db1.t1 using(id)","user":"msandbox[msandbox] @ localhost []","host":"localhost","os_user":"","ip":""}}

So, McAfee is able to log all involved tables and databases even if they are uses in the same statement, while Percona implementation does not provide that info.

[Sergei Glushchenko (sergei.glushchenko)]

https://github.com/percona/percona-server/pull/649
https://github.com/percona/percona-server/pull/650

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-2461