1、常规安全
在说审计之前我们先提一点一般我们常用的MySQL的安全注意事项。
- 指定完善的MySQL安全流程
- 用户授权邮件备注
- 每个人对应权限均需留底
- 所有用户非管理员及特殊账户，均精细化授权
2、sql审计
MySQL安全审计，对于小公司来说既没有数据库中间件平台，也没有SQL审计平台，那么如果做一个简单高性能的数据库审计呢？今日浏览global属性时发现 init_connect的属性，这个属性可以执行用户连接后做的操作，官方使用的autocommit 做的实例,而且可以支持动态修改。那我们是不是可以利用这个特性和binlog记录实践ID，审计所有人都做了在什么时间登录做了什么事情。
官方资料: https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html
此实验的难点：
- 用户授权，所有用户及对该表有select，insert权限
- 研究在主从情况模式下如何区分对待（级联）
- 对链接的性能会有多大差距（主要针对非连接池或链接复用的情况下）
2.1 实验
创建存储用户数据的表
(root@localhost:mysql.sock) [(none)]>create database accesslog;
Query OK, 1 row affected (0.08 sec)
(root@localhost:mysql.sock) [(none)]>use accesslog
CREATE TABLE accesslog (`id` int(11) primary key auto_increment,
`thread_id` bigint COMMENT '线程ID',
`time` datetime COMMENT '时间',
`localname` varchar(100) COMMENT '连接的ID用户+地址',
`matchname` varchar(100) COMMENT '匹配到的用户ID');
创建所有需要监控的账号，给予INSERT权限
(root@localhost:mysql.sock) [accesslog]>select connection_id(), now(), user(), current_user();
+-----------------+---------------------+----------------+----------------+
| connection_id() | now() | user() | current_user() |
+-----------------+---------------------+----------------+----------------+
| 5 | 2017-06-20 09:02:12 | root@localhost | root@localhost |
+-----------------+---------------------+----------------+----------------+
1 row in set (0.05 sec)
想必大家看到如上信息，就明白了我们要干什么了，对吧。
那我们配置init_connect
SET GLOBAL init_connect='INSERT INTO accesslog.accesslog(thread_id,time,localname,matchname) values(connection_id(), now(), user(), current_user());';
接下来，见证奇迹的时刻到了
需要注意的就是，官方为了保证安全，有super权限的将not exec，原文如下(所以super。。。。):
For users that have the SUPER privilege, the content of init_connect is not executed. This is done so that an erroneous value for init_connect does not prevent all clients from connecting. For example, the value might contain a statement that has a syntax error, thus causing client connections to fail. Not executing init_connect for users that have the SUPER privilege enables them to open a connection and fix the init_connect value.
创建测试用户:
(root@localhost:mysql.sock) [(none)]>grant create ,drop, select ,insert, update on testdemo.* to evantest@'%' identified by '123';
Query OK, 0 rows affected, 1 warning (0.05 sec)
(root@localhost:mysql.sock) [(none)]>grant insert on accesslog.* to evantest@'%';
Query OK, 0 rows affected (0.00 sec)
测试效果
[root@MySQL-Node-07 mysql_test_data]# mysql -uevantest -p123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 10
Server version: 5.7.18-log MySQL Community Server (GPL)
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
(evantest@localhost:mysql.sock) [(none)]>show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| accesslog |
| testdemo |
+--------------------+
3 rows in set (0.00 sec)
(evantest@localhost:mysql.sock) [(none)]>use testdemo
Database changed
(evantest@localhost:mysql.sock) [testdemo]>create table testtable(id int comment 'test');
Query OK, 0 rows affected (0.02 sec)
查看数据库:
(root@localhost:mysql.sock) [accesslog]>select * from accesslog;
+----+-----------+---------------------+--------------------+----------------+
| id | thread_id | time | localname | matchname |
+----+-----------+---------------------+--------------------+----------------+
| 1 | 6 | 2017-06-20 09:06:32 | root@localhost | root@localhost |
| 2 | 10 | 2017-06-20 09:17:35 | evantest@localhost | evantest@% |
+----+-----------+---------------------+--------------------+----------------+
2 rows in set (0.00 sec)
查看执行了那些操作:
解析binlog
# at 2895
#170620 9:17:35 server id 1 end_log_pos 2926 CRC32 0x45ce5d15 Xid = 67
COMMIT/*!*/;
# at 2926
#170620 9:18:02 server id 1 end_log_pos 2991 CRC32 0x86520809 Anonymous_GTID last_committed=11 sequence_number=12
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 2991
#170620 9:18:02 server id 1 end_log_pos 3118 CRC32 0xf03899c0 Query thread_id=10 exec_time=0 error_code=0
use `testdemo`/*!*/;
SET TIMESTAMP=1497921482/*!*/;
create table testtable(id int comment 'test')
/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
这样我们就可以通过线程ID + 时间锁定对应的人在对应的时间做了那些事情。