GBase 8a在创建用户以及授权时,可以指定该用户的主机IP,只有该IP才可以通过该用户进行连接和操作,从而实现了主机白名单功能。
注意本机是localhost,如果不授权的话,默认就必须指定IP来连接本机。 其中主机名%表示所有的IP,支持匹配。
白名单,是指在现有用户名【允许连接】的范围与白名单的【交集】。比如默认允许%所有IP,加白名单,则将只允许白名单内的IP可以连接。
如果用户名默认允许@'X.X.X.12%', 如果白名单是'X.X.X.13X', 那么交集为空,造成任何IP都无法登录的结果。
目录导航
参考
通过create user创建用户
gbase> create user testdb2@10.0.2.101 identified by 'testdb2';
Query OK, 0 rows affected (Elapsed: 00:00:00.05)
支持模糊匹配
gbase> create user testdb@'10.0.2.%' identified by 'testdb';
Query OK, 0 rows affected (Elapsed: 00:00:00.03)
gbase> select trim(user),trim(host) from gbase.user;
+------------+------------+
| trim(user) | trim(host) |
+------------+------------+
| ab | % |
| gbase | % |
| root | % |
| testdb | 10.0.2.% |
+------------+------------+
4 rows in set (Elapsed: 00:00:00.00)
通过grant创建用户
gbase> select trim(user),trim(host) from gbase.user;
+------------+------------+
| trim(user) | trim(host) |
+------------+------------+
| ab | % |
| gbase | % |
| root | % |
+------------+------------+
3 rows in set (Elapsed: 00:00:00.00)
创建用户
gbase> grant all on testdb.* to testdb@10.0.2.115 identified by 'testdb';
Query OK, 0 rows affected (Elapsed: 00:00:00.04)
gbase> select trim(user),trim(host) from gbase.user;
+------------+------------+
| trim(user) | trim(host) |
+------------+------------+
| ab | % |
| gbase | % |
| root | % |
| testdb | 10.0.2.115 |
+------------+------------+
4 rows in set (Elapsed: 00:00:00.00)
通过user的hosts功能
数据库用户有个hosts参数,可以通过create /alter user时指定。 可以参考 GBase 8a集群创建用户create user完整语法
如下是给默认%的用户设置白名单
gbase> alter user user1 hosts '10.0.2.18%';
Query OK, 0 rows affected (Elapsed: 00:00:00.02)
- 默认hosts为空,不限制
- 多个host用空格分割
- host的IP列表可以用%和_做通配符,与like相同
如果是给已经存在IP限制的用户(user1@'10.0.2.18%')设置白名单,需要注意IP的交集。
该信息可有从user_check表的hostlist里查询到,默认长度5000字符。 GBase 8a用户安全策略元数据表user_check介绍
连接测试
白名单内的IP连接
[gbase@gbase_rh7_015 ~]$ ip addr
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN qlen 1
link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
inet 127.0.0.1/8 scope host lo
valid_lft forever preferred_lft forever
inet6 ::1/128 scope host
valid_lft forever preferred_lft forever
2: enp0s3: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
link/ether 08:00:27:db:02:33 brd ff:ff:ff:ff:ff:ff
inet 10.0.2.115/24 brd 10.0.2.255 scope global enp0s3
valid_lft forever preferred_lft forever
inet6 fe80::8b26:63ff:c505:191c/64 scope link
valid_lft forever preferred_lft forever
3: virbr0: <NO-CARRIER,BROADCAST,MULTICAST,UP> mtu 1500 qdisc noqueue state DOWN qlen 1000
link/ether 52:54:00:4a:d6:8a brd ff:ff:ff:ff:ff:ff
inet 192.168.122.1/24 brd 192.168.122.255 scope global virbr0
valid_lft forever preferred_lft forever
4: virbr0-nic: <BROADCAST,MULTICAST> mtu 1500 qdisc pfifo_fast master virbr0 state DOWN qlen 1000
link/ether 52:54:00:4a:d6:8a brd ff:ff:ff:ff:ff:ff
[gbase@gbase_rh7_015 ~]$ gccli -utestdb -ptestdb -h10.0.2.101
GBase client 9.5.2.43.5f8fd4b2. Copyright (c) 2004-2022, GBase. All Rights Reserved.
gbase> ^CAborted
[gbase@gbase_rh7_015 ~]$
其它IP连接
报找不到对应用户的错误
[gbase@gbase_rh7_001 ~]$ gccli -utestdb -ptestdb
ERROR 1133 (42000): Can't find any matching row in the user table
[gbase@gbase_rh7_001 ~]$ gccli -utestdb -ptestdb -h10.0.2.101
ERROR 1133 (42000): Can't find any matching row in the user table
[gbase@gbase_rh7_001 ~]$
IP被限制
[gbase@gbase_rh7_015 ~]$ gccli -uuser1 -pp2resu
ERROR 1130 (HY000): Host 'localhost' is not allowed to connect to this GBase server
[gbase@gbase_rh7_015 ~]$ gccli -uuser1 -pp2resu -h10.0.2.115
ERROR 1130 (HY000): Host '10.0.2.115' is not allowed to connect to this GBase server