Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Cannot Reproduce
-
10.0.24
-
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 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
|
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?