[MDEV-15480] Audit plugin does not respect QUERY_DML for audit plugin Created: 2018-03-06  Updated: 2018-08-30  Resolved: 2018-08-30

Status: Closed
Project: MariaDB Server
Component/s: Plugin - Audit
Affects Version/s: 10.1.29
Fix Version/s: 10.1.34

Type: Bug Priority: Major
Reporter: Matteo Tassinari Assignee: Alexey Botchkov
Resolution: Fixed Votes: 2
Labels: None
Environment:

Centos 7 64bit


 Description   

I have configured the Audit plugin as:

plugin-load-add = server_audit
server_audit_logging = ON
server_audit_events = QUERY_DML
server_audit_output_type = FILE
server_audit_file_path = /path/to/audit.log
server_audit_query_log_limit = 1048576
server_audit_file_rotate_size = 1073741824
server_audit_file_rotations = 1

Based on the docs, "QUERY_DML" should mean:

Same as QUERY, but filters only DML-type queries (DO, CALL, LOAD DATA/XML, DELETE, INSERT, UPDATE, HANDLER and REPLACE statements)

However, in the created log file, together with expected INSERTs, UPDATEs and DELETEs, I also find all SELECTs are logged.

Based on the description, SELECTs should not appear in the log with this config, or perhaps the description is wrong.

 Comments   
Comment by Elena Stepanova [ 2018-03-06 ]

My guess is that it's documentation omission, traditionally SELECT is included into "DML" queries, even though technically it doesn't modify anything.

I'll assign it to holyfoot to confirm – if it's indeed as designed, please reassign to Ian for documentation fix, or just fix it yourself.

Comment by Matteo Tassinari [ 2018-03-06 ]

Thanks for your feedback, I must admit I find it counter-intuitive to think of a SELECT as "manipulating", and so I expected it to be filtered out, and that is why I opened this bug report.

It'd be nice to have a way to include only query which actually change some data, and not just read it.

Comment by Sören Robe [ 2018-03-09 ]

Same behavior in 10.2.13.

In my opinion SELECTs has to filter out, because they do no data Manipulation.

Best Regards

Comment by Karl Levik [ 2018-03-14 ]

It would seem like a very useful feature to have if you could log all DML statements except SELECTs.

Edited to add: Perhaps there could be an option for server_audit_events called something like QUERY_DML_EXCL_DQL or QUERY_DML_EXCL_SELECT.

Comment by Alexey Botchkov [ 2018-05-10 ]

Well, firstly it was a 'bug' in the docummentation. The SELECT statement is a part of DML.
Secondly, i added the QUERY_DML_NO_SELECT flag - that works as suggested - filters out SELECT statements.
The rest is planned for the plugin v2.0 where filters are going to be more flexible.
https://mariadb.com/kb/en/library/mariadb-audit-plugin-log-settings/

Comment by Giannis E [ 2018-05-23 ]

Documentation on https://mariadb.com/kb/en/library/mariadb-audit-plugin-log-settings/ mentions "QUERY_DML_NO_SELECT" as introduced on MariaDB 10.1.4 (on plugin version 1.4.4).

In my understanding this means that it should be available for example on MariaDB 10.1.33, though it isn't as of today because 10.1.33 has PLUGIN_AUTH_VERSION = 1.4.3

Comment by Davison Long [ 2018-06-13 ]

As Giannis mentioned, there appears to be some incorrect documentation on when the new QUERY_DML_NO_SELECT type became available. It notes that the type was introduced in MariaDB 10.1.4 (via plugin version 1.4.4), but even as of MariaDB 10.1.33 the plugin version is still at 1.4.3. Can anyone shed some light on this? Is there a way to manually update the plugin version?

Comment by Alexey Botchkov [ 2018-08-30 ]

The 10.1.34 release has the 1.4.4 version of the plugin.

It's ok to get the latest plugin source from GIT and build with the 'old' MariaDB tree (say 10.1.33)
https://github.com/MariaDB/server/blob/10.4/plugin/server_audit/server_audit.c

Generated at Thu Feb 08 08:21:38 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.