mariadb可以启用审计插件来对数据库的各种操作进行审计,以防运维背锅!
启用MariaDB的审计插件,并调整相关参数
<code>MariaDB [(none)]> show variables like </code><code>'%audit%'</code><code>;</code>
<code>Empty </code><code>set</code> <code>(0.02 sec)</code>
安装MariaDB审计插件
<code>MariaDB [(none)]> INSTALL PLUGIN server_audit SONAME </code><code>'server_audit'</code><code>;</code>
<code>Query OK, 0 rows affected (0.41 sec)</code>
查看安装审计插件后的变量
<code>+-------------------------------+-----------------------+</code>
<code>| Variable_name | Value |</code>
<code>| server_audit_events | |</code>
<code>| server_audit_excl_users | |</code>
<code>| server_audit_file_path | server_audit.log |</code>
<code>| server_audit_file_rotate_now | OFF |</code>
<code>| server_audit_file_rotate_size | 1000000 |</code>
<code>| server_audit_file_rotations | 9 |</code>
<code>| server_audit_incl_users | |</code>
<code>| server_audit_loc_info | |</code>
<code>| server_audit_logging | OFF |</code>
<code>| server_audit_mode | 0 |</code>
<code>| server_audit_output_type | </code><code>file</code> <code>|</code>
<code>| server_audit_query_log_limit | 1024 |</code>
<code>| server_audit_syslog_facility | LOG_USER |</code>
<code>| server_audit_syslog_ident | mysql-server_auditing |</code>
<code>| server_audit_syslog_info | |</code>
<code>| server_audit_syslog_priority | LOG_INFO |</code>
设置相关参数及说明:
1.设置记录的事件
<code>MariaDB [(none)]> </code><code>set</code> <code>global server_audit_events=</code><code>'connect,query,table'</code><code>;</code>
<code>Query OK, 0 rows affected (0.00 sec)</code>
注:connect,query,table可满足我们所有审计需求
相关事件说明可参考:https://mariadb.com/kb/en/mariadb/about-the-mariadb-audit-plugin/
2.指定不审计某些用户的操作
<code>MariaDB [(none)]> </code><code>set</code> <code>global server_audit_excl_users=</code><code>'liuwei'</code><code>;</code>
<code>Query OK, 0 rows affected (0.01 sec)</code>
3.审计日志存放的位置
<code>server_audit_file_path | server_audit.log</code>
4.设置日志轮转
<code>MariaDB [(none)]> </code><code>set</code> <code>global server_audit_file_rotate_now=on;</code>
5.设置审计日志的大小
<code>MariaDB [(none)]> </code><code>set</code> <code>global server_audit_file_rotate_size=1024*1024*1024;</code>
<code>Query OK, 0 rows affected (0.03 sec)</code>
6.开启审计日志
<code>MariaDB [(none)]> </code><code>set</code> <code>global server_audit_logging=on;</code>
7.设置日志可以轮转的个数
<code>server_audit_file_rotations | 9</code>
查看设置之后的参数:
<code>| server_audit_events | QUERY,TABLE |</code>
<code>| server_audit_excl_users | liuwei |</code>
<code>| server_audit_file_rotate_size | 1073741824 |</code>
<code>| server_audit_logging | ON |</code>
查看审计日志是否进行记录:可以记录增删改查所有操作
<code>[root@test2 mysql]</code><code># cat server_audit.log </code>
<code>20160705 16:25:24,test2,root,localhost,9,2544,QUERY,,</code><code>'set global server_audit_logging=on'</code><code>,0</code>
<code>20160705 16:25:38,test2,root,localhost,9,2545,QUERY,,</code><code>'show variables like \'%audit%\''</code><code>,0</code>
<code>20160705 16:28:06,test2,root,localhost,9,2546,QUERY,,</code><code>'SELECT DATABASE()'</code><code>,0</code>
<code>20160705 16:28:10,test2,root,localhost,9,2548,QUERY,,</code><code>'show databases'</code><code>,0</code>
<code>20160705 16:28:44,test2,root,localhost,9,2549,QUERY,,</code><code>'create database v1'</code><code>,0</code>
<code>[root@test2 mysql]</code><code># tailf server_audit.log</code>
<code>20160705 16:31:20,test2,root,localhost,9,2552,QUERY,,</code><code>'set global server_audit_events=\'connect,query,table\''</code><code>,0</code>
<code>20160705 16:31:30,test2,root,localhost,9,2553,QUERY,,</code><code>'show variables like \'%audit%\''</code><code>,0</code>
<code>20160705 16:31:35,test2,root,localhost,9,0,DISCONNECT,,,0</code>
<code>20160705 16:31:51,test2,root,localhost,24,0,CONNECT,,,0</code>
<code>20160705 16:31:51,test2,root,localhost,24,2555,QUERY,,</code><code>'select @@version_comment limit 1'</code><code>,0</code>
<code>20160705 16:33:44,test2,root,192.168.10.215,25,0,FAILED_CONNECT,,,1045</code>
<code>20160705 16:33:44,test2,root,192.168.10.215,25,0,DISCONNECT,,,0</code>
<code>20160705 16:33:53,test2,liuwei,192.168.10.215,26,0,CONNECT,,,0</code>
<code>20160705 16:35:21,test2,root,localhost,24,2561,QUERY,,</code><code>'show variables like \'%audit%\''</code><code>,0</code>
<code>20160705 16:40:22,test2,root,localhost,24,2563,WRITE,mysql,user,</code>
<code>20160705 16:40:22,test2,root,localhost,24,2563,WRITE,mysql,db,</code>
<code>20160705 16:40:22,test2,root,localhost,24,2563,QUERY,mysql,</code><code>'grant all on *.* to hb@\'%\' identified by *****'</code><code>,0</code>
在配置文件中配置审计参数,因为global重启后就失效:
[mysqld]
server_audit_events=‘CONNECT,QUERY,TABLE‘
server_audit_logging=on
server_audit_file_rotate_size=200000
server_audit_file_rotations=10
server_audit_excl_users='liuwei'
重启生效
本文转自激情燃烧的岁月博客51CTO博客,原文链接http://blog.51cto.com/liuzhengwei521/1882767如需转载请自行联系原作者
weilovepan520