Audit Log events do not report default DB

Bug #1435099 reported by eroomydna
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Percona Server moved to
Status tracked in 5.7
Won't Fix
Fix Released
Sergei Glushchenko
Fix Released
Sergei Glushchenko

Bug Description

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

{"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":""}}

  TIMESTAMP="2015-03-23T00:11:20 UTC"
  SQLTEXT="update ai_test set id = 10 where id =1"
  USER="root[root] @ localhost []"

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.

Revision history for this message
eroomydna (andrew-2-moore) wrote :

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

MariaDB (CSV Format)
20150323 00:22:11,,root,localhost,131,22226336,QUERY,test,'select count(*) from rooms',0
McAfee (JSON Format)
     "query":"select * from people"}

tags: added: audit
Przemek (pmalkowski)
tags: added: i65499
Revision history for this message
Przemek (pmalkowski) wrote :

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;

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 | | 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.

Revision history for this message
Sergei Glushchenko (sergei.glushchenko) wrote :
Revision history for this message
Shahriyar Rzayev (rzayev-sehriyar) wrote :

Percona now uses JIRA for bug reports so this bug report is migrated to:

To post a comment you must log in.
This report contains Public information  
Everyone can see this information.

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.