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

database field is empty while using audit plugin for MySQL 5.6.21 community editoin

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Cannot Reproduce
    • 10.0.24
    • 10.0.30
    • Plugin - Audit
    • MySQL Server version: 5.6.21-log MySQL Community Server (GPL)
    • 5.5.55, 10.0.30

    Description

      I am using server_audit.so for auditing MySQL data into audit file everything is working as expected however database field is coming as empty in the audit log. Here is the sample information

      Here is the sequence of events I fired on mySQL server
      1. connected to server
      2. executed show databases
      3. change the database context using USE command, it is not captured

      20170120 01:12:26,mariadbserver1,root,localhost,1,0,CONNECT,,,0
      20170120 01:34:24,mariadbserver1,root,localhost,1,9,QUERY,,'show databases',0
      20170120 01:34:27,mariadbserver1,root,localhost,1,10,QUERY,,'SELECT DATABASE()',0
      20170120 01:34:30,mariadbserver1,root,localhost,1,11,QUERY,,'show tables',0
      20170120 01:34:39,mariadbserver1,root,localhost,1,12,QUERY,,'delete from user where id=2',0
      20170120 01:34:45,mariadbserver1,root,localhost,1,13,QUERY,,'delete from user where id=3',0

      Attachments

        Activity

          I wasn't able to reproduce that. Meaning the plugin shows the database name properly for me.
          Do you use 'mysql' client to run queries, or it does another application?

          holyfoot Alexey Botchkov added a comment - I wasn't able to reproduce that. Meaning the plugin shows the database name properly for me. Do you use 'mysql' client to run queries, or it does another application?
          fmyoen Fmy Oen added a comment - - edited

          I have the same issue:

          Shell command:

          mysql -u root -p

          Queries:

          use testdb;
          show tables;
          drop tables t2;
          create table t2 (c1 int, c2 int);
          insert into t2 (c1, c2) values (1000,2000);
          insert into t2 (c1, c2) values (1100,2100);
          use mysql;
          select * from user;
          \q
          

          Log:

          20170428 12:52:12,vm-tmisc-mysqlaud,root,localhost,5,0,CONNECT,,,0
          20170428 12:52:12,vm-tmisc-mysqlaud,root,localhost,5,19,QUERY,,'select @@version_comment limit 1',0
          20170428 12:52:17,vm-tmisc-mysqlaud,root,localhost,5,20,QUERY,,'SELECT DATABASE()',0
          20170428 12:52:17,vm-tmisc-mysqlaud,root,localhost,5,21,QUERY,,'show databases',0
          20170428 12:52:17,vm-tmisc-mysqlaud,root,localhost,5,22,QUERY,,'show tables',0
          20170428 12:52:24,vm-tmisc-mysqlaud,root,localhost,5,23,QUERY,,'show tables',0
          20170428 12:52:39,vm-tmisc-mysqlaud,root,localhost,5,24,QUERY,,'drop tables t2',0
          20170428 12:52:47,vm-tmisc-mysqlaud,root,localhost,5,25,QUERY,,'create table t2 (c1 int, c2 int)',0
          20170428 12:52:54,vm-tmisc-mysqlaud,root,localhost,5,26,QUERY,,'insert into t2 (c1, c2) values (1000,2000)',0
          20170428 12:53:02,vm-tmisc-mysqlaud,root,localhost,5,27,QUERY,,'insert into t2 (c1, c2) values (1100,2100)',0
          20170428 12:53:08,vm-tmisc-mysqlaud,root,localhost,5,28,QUERY,,'SELECT DATABASE()',0
          20170428 12:53:08,vm-tmisc-mysqlaud,root,localhost,5,29,QUERY,,'show databases',0
          20170428 12:53:08,vm-tmisc-mysqlaud,root,localhost,5,30,QUERY,,'show tables',0
          20170428 12:53:14,vm-tmisc-mysqlaud,root,localhost,5,31,QUERY,,'select * from user',0

          Here is another try:

          Shell command:

          mysql -u root -p testdb

          Queries:

          select * from t2;
          use mysql;
          select * from user;
          \q

          Log:

          20170428 12:58:05,vm-tmisc-mysqlaud,root,localhost,7,0,CONNECT,testdb,,0
          20170428 12:58:05,vm-tmisc-mysqlaud,root,localhost,7,34,QUERY,testdb,'show databases',0
          20170428 12:58:05,vm-tmisc-mysqlaud,root,localhost,7,35,QUERY,testdb,'show tables',0
          20170428 12:58:05,vm-tmisc-mysqlaud,root,localhost,7,36,QUERY,testdb,'select @@version_comment limit 1',0
          20170428 12:58:14,vm-tmisc-mysqlaud,root,localhost,7,37,QUERY,testdb,'select * from t2',0
          20170428 12:58:17,vm-tmisc-mysqlaud,root,localhost,7,38,QUERY,testdb,'SELECT DATABASE()',0
          20170428 12:58:17,vm-tmisc-mysqlaud,root,localhost,7,39,QUERY,testdb,'show databases',0
          20170428 12:58:17,vm-tmisc-mysqlaud,root,localhost,7,40,QUERY,testdb,'show tables',0
          20170428 12:58:23,vm-tmisc-mysqlaud,root,localhost,7,41,QUERY,testdb,'select * from user',0
          20170428 12:58:24,vm-tmisc-mysqlaud,,,7,0,DISCONNECT,,,0
          

          My environment:

          $ cat /etc/centos-release 
          CentOS release 6.9 (Final)
           
          $ uname -a
          Linux vm-tmisc-mysqlaud 2.6.32-696.1.1.el6.x86_64 #1 SMP Tue Apr 11 17:13:24 UTC 2017 x86_64 x86_64 x86_64 GNU/Linux
           
          $ getenforce
          Enforcing
           
          $ yum list installed | grep mysql
          compat-mysql51.x86_64 5.1.73-1.el6.remi @remi                                   
          mysql.x86_64          5.5.55-1.el6.remi @remi                                   
          mysql-libs.x86_64     5.5.55-1.el6.remi @remi                                   
          mysql-server.x86_64   5.5.55-1.el6.remi @remi      
           
          $ yum repolist
          ...
          repo id                                                         repo name                                                                                                         status
          base                                                            CentOS-6 - Base                                                                                                    6 706
          epel                                                            Extra Packages for Enterprise Linux 6 - x86_64                                                                    12 317
          extras                                                          CentOS-6 - Extras                                                                                                     64
          remi                                                            Remi's RPM repository for Enterprise Linux 6 - x86_64                                                              4 059
          remi-safe                                                       Safe Remi's RPM repository for Enterprise Linux 6 - x86_64                                                         1 769
          updates                                                         CentOS-6 - Updates                                                                                                   252
          repolist: 25 167
           
          $ cat /var/log/mysqld.log
          ...
          170428 12:06:19 [Note] Plugin 'FEDERATED' is disabled.
          170428 12:06:19 InnoDB: The InnoDB memory heap is disabled
          170428 12:06:19 InnoDB: Mutexes and rw_locks use GCC atomic builtins
          170428 12:06:19 InnoDB: Compressed tables use zlib 1.2.3
          170428 12:06:19 InnoDB: Using Linux native AIO
          170428 12:06:19 InnoDB: Initializing buffer pool, size = 128.0M
          170428 12:06:19 InnoDB: Completed initialization of buffer pool
          170428 12:06:19 InnoDB: highest supported file format is Barracuda.
          170428 12:06:19  InnoDB: Waiting for the background threads to start
          170428 12:06:20 InnoDB: 5.5.55 started; log sequence number 1603876
          170428 12:06:20 server_audit: MariaDB Audit Plugin version 1.4.1 STARTED.
          170428 12:06:20 server_audit: logging started to the file /var/log/mysql/audit.log.
          170428 12:06:20 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
          170428 12:06:20 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
          170428 12:06:20 [Note] Server socket created on IP: '0.0.0.0'.
          170428 12:06:20 [Note] Event Scheduler: Loaded 0 events
          170428 12:06:20 [Note] /usr/libexec/mysqld: ready for connections.
          Version: '5.5.55'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server (GPL) by Remi
           
          $ cat /etc/my.cnf
          [mysqld]
          datadir=/var/lib/mysql
          socket=/var/lib/mysql/mysql.sock  
           
          # Disabling symbolic-links is recommended to prevent assorted security risks
          symbolic-links=0
           
          # Settings user and group are ignored when systemd is used (fedora >= 15).
          # If you need to run mysqld under a different user or group,
          # customize your systemd unit file for mysqld according to the
          # instructions in http://fedoraproject.org/wiki/Systemd
          user=mysql
           
          # Semisynchronous Replication
          # http://dev.mysql.com/doc/refman/5.5/en/replication-semisync.html
          # uncomment next line on MASTER   
          ;plugin-load=rpl_semi_sync_master=semisync_master.so
          # uncomment next line on SLAVE
          ;plugin-load=rpl_semi_sync_slave=semisync_slave.so
           
          # Others options for Semisynchronous Replication
          ;rpl_semi_sync_master_enabled=1
          ;rpl_semi_sync_master_timeout=10
          ;rpl_semi_sync_slave_enabled=1
           
          # http://dev.mysql.com/doc/refman/5.5/en/performance-schema.html
          ;performance_schema
           
          ### Server audit plugin settings
          # https://mariadb.com/resources/blog/activating-auditing-mariadb-and-mysql-5-minutes
          # https://mariadb.com/kb/en/mariadb/about-the-mariadb-audit-plugin/
          # load plugin
          plugin-load=server_audit=server_audit.so
          # do not allow users to uninstall plugin
          server_audit=FORCE_PLUS_PERMANENT
          # events to audit
          #       CONNECT         Logs connects, disconnects and failed connects (including the error code).
          #       QUERY           Queries issued and their results (in plain text), including failed queries due to syntax or permission errors.
          #       TABLE           Which tables were affected by query execution.
          #       QUERY_DDL       Works as the 'QUERY' value, but filters only DDL-type queries (CREATE, ALTER, etc).
          #       QUERY_DML       Works as the 'QUERY' value, but filters only DML-type queries (INSERT, UPDATE, etc).
          #       QUERY_DCL       Works as the 'QUERY' value, but filters only DCL-type queries (GRANT, REVOKE, etc.)
          server_audit_events=CONNECT,QUERY,TABLE
          # enable logging
          server_audit_logging=ON
          # any users who don't need auditing (csv)
          ;server_audit_excl_users='root'   
          # or can use
          ;server_audit_incl_users='jayj'   
           
          # flat file
          server_audit_output_type=FILE
          server_audit_file_path=/var/log/mysql/audit.log
          server_audit_file_rotate_size=1000000
          server_audit_file_rotations=9
           
          # syslog
          ;server_audit_output_type=SYSLOG  
          ;server_audit_syslog_facility=LOG_LOCAL6
          ;server_audit_syslog_ident=mysql_audit
          ;server_audit_syslog_info=this-host.name
          ;server_audit_syslog_priority=LOG_INFO
           
          # limit on the length of the query string in a record
          ;server_audit_query_log_limit=1024
           
          [mysqld_safe]
          log-error=/var/log/mysqld.log
          pid-file=/var/run/mysqld/mysqld.pid
           
          #
          # include all files from the config directory
          #
          !includedir /etc/my.cnf.d
          

          I've got server_audit.so from MariaDB server from this repository:

          $ cat /etc/yum.repos.d/MariaDB.repo 
          [mariadb]
          name = MariaDB
          baseurl = http://yum.mariadb.org/5.5/centos6-amd64
          gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
          gpgcheck=1

          fmyoen Fmy Oen added a comment - - edited I have the same issue: Shell command: mysql -u root -p Queries: use testdb; show tables; drop tables t2; create table t2 (c1 int, c2 int); insert into t2 (c1, c2) values (1000,2000); insert into t2 (c1, c2) values (1100,2100); use mysql; select * from user; \q Log: 20170428 12:52:12,vm-tmisc-mysqlaud,root,localhost,5,0,CONNECT,,,0 20170428 12:52:12,vm-tmisc-mysqlaud,root,localhost,5,19,QUERY,,'select @@version_comment limit 1',0 20170428 12:52:17,vm-tmisc-mysqlaud,root,localhost,5,20,QUERY,,'SELECT DATABASE()',0 20170428 12:52:17,vm-tmisc-mysqlaud,root,localhost,5,21,QUERY,,'show databases',0 20170428 12:52:17,vm-tmisc-mysqlaud,root,localhost,5,22,QUERY,,'show tables',0 20170428 12:52:24,vm-tmisc-mysqlaud,root,localhost,5,23,QUERY,,'show tables',0 20170428 12:52:39,vm-tmisc-mysqlaud,root,localhost,5,24,QUERY,,'drop tables t2',0 20170428 12:52:47,vm-tmisc-mysqlaud,root,localhost,5,25,QUERY,,'create table t2 (c1 int, c2 int)',0 20170428 12:52:54,vm-tmisc-mysqlaud,root,localhost,5,26,QUERY,,'insert into t2 (c1, c2) values (1000,2000)',0 20170428 12:53:02,vm-tmisc-mysqlaud,root,localhost,5,27,QUERY,,'insert into t2 (c1, c2) values (1100,2100)',0 20170428 12:53:08,vm-tmisc-mysqlaud,root,localhost,5,28,QUERY,,'SELECT DATABASE()',0 20170428 12:53:08,vm-tmisc-mysqlaud,root,localhost,5,29,QUERY,,'show databases',0 20170428 12:53:08,vm-tmisc-mysqlaud,root,localhost,5,30,QUERY,,'show tables',0 20170428 12:53:14,vm-tmisc-mysqlaud,root,localhost,5,31,QUERY,,'select * from user',0 Here is another try: Shell command: mysql -u root -p testdb Queries: select * from t2; use mysql; select * from user; \q Log: 20170428 12:58:05,vm-tmisc-mysqlaud,root,localhost,7,0,CONNECT,testdb,,0 20170428 12:58:05,vm-tmisc-mysqlaud,root,localhost,7,34,QUERY,testdb,'show databases',0 20170428 12:58:05,vm-tmisc-mysqlaud,root,localhost,7,35,QUERY,testdb,'show tables',0 20170428 12:58:05,vm-tmisc-mysqlaud,root,localhost,7,36,QUERY,testdb,'select @@version_comment limit 1',0 20170428 12:58:14,vm-tmisc-mysqlaud,root,localhost,7,37,QUERY,testdb,'select * from t2',0 20170428 12:58:17,vm-tmisc-mysqlaud,root,localhost,7,38,QUERY,testdb,'SELECT DATABASE()',0 20170428 12:58:17,vm-tmisc-mysqlaud,root,localhost,7,39,QUERY,testdb,'show databases',0 20170428 12:58:17,vm-tmisc-mysqlaud,root,localhost,7,40,QUERY,testdb,'show tables',0 20170428 12:58:23,vm-tmisc-mysqlaud,root,localhost,7,41,QUERY,testdb,'select * from user',0 20170428 12:58:24,vm-tmisc-mysqlaud,,,7,0,DISCONNECT,,,0 My environment: $ cat /etc/centos-release CentOS release 6.9 (Final)   $ uname -a Linux vm-tmisc-mysqlaud 2.6.32-696.1.1.el6.x86_64 #1 SMP Tue Apr 11 17:13:24 UTC 2017 x86_64 x86_64 x86_64 GNU/Linux   $ getenforce Enforcing   $ yum list installed | grep mysql compat-mysql51.x86_64 5.1.73-1.el6.remi @remi mysql.x86_64 5.5.55-1.el6.remi @remi mysql-libs.x86_64 5.5.55-1.el6.remi @remi mysql-server.x86_64 5.5.55-1.el6.remi @remi   $ yum repolist ... repo id repo name status base CentOS-6 - Base 6 706 epel Extra Packages for Enterprise Linux 6 - x86_64 12 317 extras CentOS-6 - Extras 64 remi Remi's RPM repository for Enterprise Linux 6 - x86_64 4 059 remi-safe Safe Remi's RPM repository for Enterprise Linux 6 - x86_64 1 769 updates CentOS-6 - Updates 252 repolist: 25 167   $ cat /var/log/mysqld.log ... 170428 12:06:19 [Note] Plugin 'FEDERATED' is disabled. 170428 12:06:19 InnoDB: The InnoDB memory heap is disabled 170428 12:06:19 InnoDB: Mutexes and rw_locks use GCC atomic builtins 170428 12:06:19 InnoDB: Compressed tables use zlib 1.2.3 170428 12:06:19 InnoDB: Using Linux native AIO 170428 12:06:19 InnoDB: Initializing buffer pool, size = 128.0M 170428 12:06:19 InnoDB: Completed initialization of buffer pool 170428 12:06:19 InnoDB: highest supported file format is Barracuda. 170428 12:06:19 InnoDB: Waiting for the background threads to start 170428 12:06:20 InnoDB: 5.5.55 started; log sequence number 1603876 170428 12:06:20 server_audit: MariaDB Audit Plugin version 1.4.1 STARTED. 170428 12:06:20 server_audit: logging started to the file /var/log/mysql/audit.log. 170428 12:06:20 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306 170428 12:06:20 [Note] - '0.0.0.0' resolves to '0.0.0.0'; 170428 12:06:20 [Note] Server socket created on IP: '0.0.0.0'. 170428 12:06:20 [Note] Event Scheduler: Loaded 0 events 170428 12:06:20 [Note] /usr/libexec/mysqld: ready for connections. Version: '5.5.55' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL) by Remi   $ cat /etc/my.cnf [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock   # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0   # Settings user and group are ignored when systemd is used (fedora >= 15). # If you need to run mysqld under a different user or group, # customize your systemd unit file for mysqld according to the # instructions in http://fedoraproject.org/wiki/Systemd user=mysql   # Semisynchronous Replication # http://dev.mysql.com/doc/refman/5.5/en/replication-semisync.html # uncomment next line on MASTER ;plugin-load=rpl_semi_sync_master=semisync_master.so # uncomment next line on SLAVE ;plugin-load=rpl_semi_sync_slave=semisync_slave.so   # Others options for Semisynchronous Replication ;rpl_semi_sync_master_enabled=1 ;rpl_semi_sync_master_timeout=10 ;rpl_semi_sync_slave_enabled=1   # http://dev.mysql.com/doc/refman/5.5/en/performance-schema.html ;performance_schema   ### Server audit plugin settings # https://mariadb.com/resources/blog/activating-auditing-mariadb-and-mysql-5-minutes # https://mariadb.com/kb/en/mariadb/about-the-mariadb-audit-plugin/ # load plugin plugin-load=server_audit=server_audit.so # do not allow users to uninstall plugin server_audit=FORCE_PLUS_PERMANENT # events to audit # CONNECT Logs connects, disconnects and failed connects (including the error code). # QUERY Queries issued and their results (in plain text), including failed queries due to syntax or permission errors. # TABLE Which tables were affected by query execution. # QUERY_DDL Works as the 'QUERY' value, but filters only DDL-type queries (CREATE, ALTER, etc). # QUERY_DML Works as the 'QUERY' value, but filters only DML-type queries (INSERT, UPDATE, etc). # QUERY_DCL Works as the 'QUERY' value, but filters only DCL-type queries (GRANT, REVOKE, etc.) server_audit_events=CONNECT,QUERY,TABLE # enable logging server_audit_logging=ON # any users who don't need auditing (csv) ;server_audit_excl_users='root' # or can use ;server_audit_incl_users='jayj'   # flat file server_audit_output_type=FILE server_audit_file_path=/var/log/mysql/audit.log server_audit_file_rotate_size=1000000 server_audit_file_rotations=9   # syslog ;server_audit_output_type=SYSLOG ;server_audit_syslog_facility=LOG_LOCAL6 ;server_audit_syslog_ident=mysql_audit ;server_audit_syslog_info=this-host.name ;server_audit_syslog_priority=LOG_INFO   # limit on the length of the query string in a record ;server_audit_query_log_limit=1024   [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid   # # include all files from the config directory # !includedir /etc/my.cnf.d I've got server_audit.so from MariaDB server from this repository: $ cat /etc/yum.repos.d/MariaDB.repo [mariadb] name = MariaDB baseurl = http://yum.mariadb.org/5.5/centos6-amd64 gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB gpgcheck=1

          People

            holyfoot Alexey Botchkov
            calyankumar Kalyan
            Votes:
            0 Vote for this issue
            Watchers:
            4 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.