Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-15480

Audit plugin does not respect QUERY_DML for audit plugin

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.1.29
    • 10.1.34
    • Plugin - Audit
    • None
    • 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.

      Attachments

        Activity

          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.

          elenst Elena Stepanova added a comment - 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.
          Tasso85 Matteo Tassinari added a comment - - edited

          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.

          Tasso85 Matteo Tassinari added a comment - - edited 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.
          soerenrobe Sören Robe added a comment -

          Same behavior in 10.2.13.

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

          Best Regards

          soerenrobe Sören Robe added a comment - Same behavior in 10.2.13. In my opinion SELECTs has to filter out, because they do no data Manipulation. Best Regards
          karll Karl Levik added a comment - - edited

          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.

          karll Karl Levik added a comment - - edited 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.

          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/

          holyfoot Alexey Botchkov added a comment - 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/
          gecon Giannis E added a comment -

          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

          gecon Giannis E added a comment - 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
          nextgentech Davison Long added a comment -

          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?

          nextgentech Davison Long added a comment - 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?

          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

          holyfoot Alexey Botchkov added a comment - 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

          People

            holyfoot Alexey Botchkov
            Tasso85 Matteo Tassinari
            Votes:
            2 Vote for this issue
            Watchers:
            9 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.