mariadb操作审计

mariadb可以启用审计插件来对数据库的各种操作进行审计，以防运维背锅！

启用MariaDB的审计插件，并调整相关参数

1 2	MariaDB [(none)]> show variables like  '%audit%' ; Empty  set  (0.02 sec)

安装MariaDB审计插件

1 2	MariaDB [(none)]> INSTALL PLUGIN server_audit SONAME  'server_audit' ; Query OK, 0 rows affected (0.41 sec)

查看安装审计插件后的变量

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

MariaDB [(none)]> show variables like  '%audit%' ; +-------------------------------+-----------------------+ | Variable_name                 | Value                 | +-------------------------------+-----------------------+ | server_audit_events           |                       | | server_audit_excl_users       |                       | | server_audit_file_path        | server_audit.log      | | server_audit_file_rotate_now  | OFF                   | | server_audit_file_rotate_size | 1000000               | | server_audit_file_rotations   | 9                     | | server_audit_incl_users       |                       | | server_audit_loc_info         |                       | | server_audit_logging          | OFF                   | | server_audit_mode             | 0                     | | server_audit_output_type      |  file                   | | server_audit_query_log_limit  | 1024                  | | server_audit_syslog_facility  | LOG_USER              | | server_audit_syslog_ident     | mysql-server_auditing | | server_audit_syslog_info      |                       | | server_audit_syslog_priority  | LOG_INFO              |

设置相关参数及说明：

1.设置记录的事件

1 2	MariaDB [(none)]>  set  global server_audit_events= 'connect,query,table' ; Query OK, 0 rows affected (0.00 sec)

 注：connect,query,table可满足我们所有审计需求

 相关事件说明可参考：https://mariadb.com/kb/en/mariadb/about-the-mariadb-audit-plugin/

2.指定不审计某些用户的操作

1 2	MariaDB [(none)]>  set  global server_audit_excl_users= 'liuwei' ; Query OK, 0 rows affected (0.01 sec)

3.审计日志存放的位置

1	server_audit_file_path        | server_audit.log

4.设置日志轮转

1 2	MariaDB [(none)]>  set  global server_audit_file_rotate_now=on; Query OK, 0 rows affected (0.00 sec)

5.设置审计日志的大小

1 2	MariaDB [(none)]>  set  global server_audit_file_rotate_size=1024*1024*1024; Query OK, 0 rows affected (0.03 sec)

6.开启审计日志

1 2	MariaDB [(none)]>  set  global server_audit_logging=on; Query OK, 0 rows affected (0.00 sec)

7.设置日志可以轮转的个数

1	server_audit_file_rotations   | 9

查看设置之后的参数：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

MariaDB [(none)]> show variables like  '%audit%' ; +-------------------------------+-----------------------+ | Variable_name                 | Value                 | +-------------------------------+-----------------------+ | server_audit_events           | QUERY,TABLE           | | server_audit_excl_users       | liuwei                | | server_audit_file_path        | server_audit.log      | | server_audit_file_rotate_now  | OFF                   | | server_audit_file_rotate_size | 1073741824            | | server_audit_file_rotations   | 9                     | | server_audit_incl_users       |                       | | server_audit_loc_info         |                       | | server_audit_logging          | ON                    | | server_audit_mode             | 0                     | | server_audit_output_type      |  file                   | | server_audit_query_log_limit  | 1024                  | | server_audit_syslog_facility  | LOG_USER              | | server_audit_syslog_ident     | mysql-server_auditing | | server_audit_syslog_info      |                       | | server_audit_syslog_priority  | LOG_INFO              | +-------------------------------+-----------------------+

查看审计日志是否进行记录：可以记录增删改查所有操作

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

[root@test2 mysql] # cat server_audit.log  20160705 16:25:24,test2,root,localhost,9,2544,QUERY,, 'set global server_audit_logging=on' ,0 20160705 16:25:38,test2,root,localhost,9,2545,QUERY,, 'show variables like \'%audit%\'' ,0 20160705 16:28:06,test2,root,localhost,9,2546,QUERY,, 'SELECT DATABASE()' ,0 20160705 16:28:10,test2,root,localhost,9,2548,QUERY,, 'show databases' ,0 20160705 16:28:44,test2,root,localhost,9,2549,QUERY,, 'create database v1' ,0 [root@test2 mysql] # tailf server_audit.log 20160705 16:25:24,test2,root,localhost,9,2544,QUERY,, 'set global server_audit_logging=on' ,0 20160705 16:25:38,test2,root,localhost,9,2545,QUERY,, 'show variables like \'%audit%\'' ,0 20160705 16:28:06,test2,root,localhost,9,2546,QUERY,, 'SELECT DATABASE()' ,0 20160705 16:28:10,test2,root,localhost,9,2548,QUERY,, 'show databases' ,0 20160705 16:28:44,test2,root,localhost,9,2549,QUERY,, 'create database v1' ,0 20160705 16:31:20,test2,root,localhost,9,2552,QUERY,, 'set global server_audit_events=\'connect,query,table\'' ,0 20160705 16:31:30,test2,root,localhost,9,2553,QUERY,, 'show variables like \'%audit%\'' ,0 20160705 16:31:35,test2,root,localhost,9,0,DISCONNECT,,,0 20160705 16:31:51,test2,root,localhost,24,0,CONNECT,,,0 20160705 16:31:51,test2,root,localhost,24,2555,QUERY,, 'select @@version_comment limit 1' ,0 20160705 16:33:44,test2,root,192.168.10.215,25,0,FAILED_CONNECT,,,1045 20160705 16:33:44,test2,root,192.168.10.215,25,0,DISCONNECT,,,0 20160705 16:33:53,test2,liuwei,192.168.10.215,26,0,CONNECT,,,0 20160705 16:35:21,test2,root,localhost,24,2561,QUERY,, 'show variables like \'%audit%\'' ,0 20160705 16:40:22,test2,root,localhost,24,2563,WRITE,mysql,user, 20160705 16:40:22,test2,root,localhost,24,2563,WRITE,mysql,db, 20160705 16:40:22,test2,root,localhost,24,2563,QUERY,mysql, 'grant all on *.* to hb@\'%\' identified by *****' ,0

在配置文件中配置审计参数，因为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