Maxscale-SQL防火墙(3)

 

Maxscale的SQL防火墙需要有：

1. 防火墙规则文件
2. 将规则文件配置进 /etc/maxscale.cnf

实现

1. 防火墙规则文件

1 2 3 4 5 6 7 8 9 10 11 12

vim / usr / local / maxscale / etc / blacklists . rule # limit Query times in Table t1 限制 20 秒内 某语句只能查询 5 次 超过则冻结 10 秒 # rule limit_rate_of_queries deny limit_queries 5 20 10 # rule query_regex deny regex ‘.*select.*from.*t1.*’ # # users %@% match all rules limit_rate_of_queries query_regex # Deny delete table t1 no WHERE clause 在某一时间段不允许删除语句没有WHERE rule safe_delete deny no_where_clause at _times 14 : 40 : 00 – 14 : 47 : 00 on_queries delete rule managers_table deny regex ‘.*[fF][rR][oO][mM].*[tT]1.*’ users % @ % match all rules safe_delete managers_table

1. /etc/maxscale.cnf 配置文件主要配置

这边使用Maxscale只读服务来说明配置现象

1 2 3 4 5 6 7 8 9 10 11

# 配置使用 规则的服务 [ Read – Only Service ] . . . filters = dbfw – blacklist # 配置规则 [ dbfw – blacklist ] type = filter module = dbfwfilter action = block rules = / usr / local / maxscale / etc / blacklists . rule

1.  /etc/maxscale.cnf 完整配置

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139

[ root @ normal_11 tmp ] # cat /etc/maxscale.cnf ################################################### # CREATE USER maxscale@’%’ IDENTIFIED BY “123456”; # GRANT replication slave, replication client ON *.* TO maxscale@’%’; # GRANT SELECT ON mysql.* TO maxscale@’%’; # GRANT ALL ON maxscale_schema.* TO maxscale@’%’; # GRANT SHOW DATABASES ON *.* TO maxscale@’%’; # groupadd maxscale # useradd -g maxscale maxscale # cd /opt # tar -zxf maxscale-2.0.1.rhel.7.tar.gz # ln -s /opt/maxscale-2.0.1.rhel.7 /usr/local/maxscale # chown -R maxscale:maxscale /usr/local/maxscale # mkdir -p /u01/maxscale/{data,cache,logs,tmp} # mkdir -p /u01/maxscale/logs/{binlog,trace} # chown -R maxscale:maxscale /u01/maxscale # /usr/local/maxscale/bin/maxkeys /u01/maxscale/data/ # /usr/local/maxscale/bin/maxpasswd /u01/maxscale/data/.secrets 123456 ################################################### [ maxscale ] # 开启线程个数，默认为1.设置为auto会同cpu核数相同 threads = auto # timestamp精度 ms_timestamp = 1 # 将日志写入到syslog中 syslog = 1 # 将日志写入到maxscale的日志文件中 maxlog = 1 # 不将日志写入到共享缓存中，开启debug模式时可打开加快速度 log_to_shm = 0 # 记录告警信息 log_warning = 1 # 记录notice log_notice = 1 # 记录info log_info = 1 # 不打开debug模式 log_debug = 0 # 日志递增 log_augmentation = 1 # 相关目录设置 basedir = / usr / local / maxscale / logdir = / u01 / maxscale / logs / trace / datadir = / u01 / maxscale / data / cachedir = / u01 / maxscale / cache / piddir = / u01 / maxscale / tmp / [ server1 ] type = server address = 192.168.137.21 port = 3306 protocol = MySQLBackend serv_weight = 1 [ server2 ] type = server address = 192.168.137.22 port = 3306 protocol = MySQLBackend serv_weight = 3 [ server3 ] type = server address = 192.168.137.23 port = 3306 protocol = MySQLBackend serv_weight = 3 [ MySQL Monitor ] type = monitor module = mysqlmon servers = server1 , server2 , server3 user = maxscale passwd = 1D30C1E689410756D7B82C233FCBF8D9 # 监控心态为 10s monitor_interval = 10000 # 当复制slave全部断掉时，maxscale仍然可用，将所有的访问指向master节点 detect_stale_master = true # 监控主从复制延迟，可用后续指定router service的(配置此参数请求会永远落在 master) # detect_replication_lag=true [ Read – Only Service ] type = service router = readconnroute servers = server1 , server2 , server3 user = maxscale passwd = 1D30C1E689410756D7B82C233FCBF8D9 router_options = slave # 允许root用户登录执行 enable_root_user = 1 # 查询权重 weightby = serv_weight filters = dbfw – blacklist [ Read – Write Service ] type = service router = readwritesplit servers = server1 , server2 , server3 user = maxscale passwd = 1D30C1E689410756D7B82C233FCBF8D9 max_slave_connections = 100 % # sql语句中的存在变量只指向master中执行 use_sql_variables_in = master # 允许root用户登录执行 enable_root_user = 1 # 允许主从最大间隔(s) max_slave_replication_lag = 3600 filters = Hint [ MaxAdmin Service ] type = service router = cli [ Read – Only Listener ] type = listener service = Read – Only Service protocol = MySQLClient port = 4008 [ Read – Write Listener ] type = listener service = Read – Write Service protocol = MySQLClient port = 4006 [ MaxAdmin Listener ] type = listener service = MaxAdmin Service protocol = maxscaled socket = / u01 / maxscale / tmp / maxadmin . sock port = 6603 [ dbfw – blacklist ] type = filter module = dbfwfilter action = block rules = / usr / local / maxscale / etc / blacklists . rule

1. 查询结果

1 2 3 4 5

HH @ 192.168.137.11 03 : 08 : 26 [ ( none ) ] > SELECT * FROM test . t1 ; ERROR 1141 ( HY000 ) : Access denied for user ‘HH’ @ ‘192.168.137.11’ : Permission denied , query matched regular expression . # 日志输出 2016 – 11 – 05 15 : 09 : 46.045    [ 5 ]    info    : ( rule_matches ) : dbfwfilter : rule ‘managers_table’ : regex matched on query

提示：虽然上面的设置以及达到了无WHERE不能执行的目的，当时其实这样跟现实的程序的编写还是有出入了。往往程序员基本都知道为了让查询不报错都会写成 WHERE 1=1，这是一个小技巧啦。当是这样的语句就没办法通过Maxscale来过滤了。

文章转载来自：trustauth.cn