测试用户登录失败自动锁定
创建用户tee并授权。
TEST=# create user tee;
CREATE ROLE
TEST=# alter user tee with createdb;
ALTER ROLE
TEST=#
TEST=# alter user tee with createrole;
ALTER ROLE
TEST=#
TEST=# alter role tee with replication;
ALTER ROLE
TEST=# alter user tee with password '1234';
ALTER ROLE
TEST=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+------------
sao | No inheritance | {}
sso | No inheritance | {}
system | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
tee | Create role, Create DB, Replication | {}
创建扩展,设置用户触及最大失败登录次数即锁定。
加载插件
修改 kingbase.conf 文件中shared_preload_libraries 参数。
shared_preload_libraries = 'sys_audlog'
create extension sys_audlog;
参数说明:
参数名 | 取值范围 | 默认值 | 描述 |
---|---|---|---|
sys_audlog.max_error_user_connect_times | [0,INT_MAX] | 2147483647 | 用户登录失败次数的最大值界限 |
sys_audlog.error_user_connect_times | [0,INT_MAX] | 0 | 允许用户连续登录失败的最大次数 |
sys_audlog.error_user_connect_interval | [0,INT_MAX] | 0 | 用户被锁定时间 |
TEST=# \c - sso
You are now connected to database "TEST" as user "sso".
TEST=>
TEST=> ALTER SYSTEM SET sys_audlog.error_user_connect_times = 3;
ALTER SYSTEM
test=> CALL sys_reload_conf();
sys_reload_conf
-----------------
t
(1 row)
模拟tee用户登录失败超过最大次数。
[kingbase7@localhost data]$ ksql -UTEE -d test -h 192.168.56.3 -W
Password:
ksql: error: could not connect to server: FATAL: password authentication failed for user "tee"
NOTICE: This is the 1 login failed. There are 2 left.
[kingbase7@localhost data]$ ksql -UTEE -d test -h 192.168.56.3 -W
Password:
ksql: error: could not connect to server: FATAL: password authentication failed for user "tee"
NOTICE: This is the 2 login failed. There are 1 left.
[kingbase7@localhost data]$ ksql -UTEE -d test -h 192.168.56.3 -W
Password:
ksql: error: could not connect to server: FATAL: The user "tee" is locked.
登录失败次数达到3次后,用户tee被锁定,此时,即使密码正确也无法正常登录,需要解锁该用户。
解锁用户两种方法:
1.超过时间间隔参数自动解除用户封锁 sys_audlog.error_user_connect_interval (此方法略过)
2.用户可由具有 ALTER USER 权限的用户通过 SQL 语句进行手动解锁,解锁后用户登录的信息自动删除。
[kingbase7@localhost data]$ ksql -USSO -d test -h 192.168.56.3 -c 'alter user tee with login;'
Password for user SSO:
ERROR: must be superuser to alter replication users
报错原因是tee用户具有replication权限,所以sso用户不能为具有此权限的用户解锁。
根据官方文档描述,此命令执行用户需要sso安全用户执行。
如果用system用户执行会报以下错误:
[kingbase7@localhost data]$ ksql -USYSTEM -d test -c 'alter user tee with login;'
ERROR: permission denied, only sso can enable user
解决方法:
用system登录,取消replication权限,这是集群复制相关的权限,普通用户没有用处。
test=# alter user tee with noreplication;
ALTER ROLE
test=# \c - sso
You are now connected to database "test" as user "sso".
test=>
test=> alter user tee with login;
ALTER ROLE
test=>
此时可以正常解决,解锁后,tee用户可以正常登录。
[kingbase7@localhost data]$ ksql -UTEE -d test -h 192.168.56.3 -W
Password:
ksql (V8.0)
Type "help" for help.
总结
虽然数据库提供了sso用户解锁因为异常登录被锁定的用户,但是遇到拥有特殊权限的用户就会解锁失败,这时需要把该权限回收后再次解锁。解锁失败的原因是由于三权分立特性,安全用户不能干预系统管理员的授权功能。
- KingbaseESV8R6 KingbaseESV8 KingbaseESV 权限 用户kingbaseesv8r6 kingbaseesv8 kingbaseesv权限 kingbaseesv8 kingbaseesv8r6 kingbaseesv8r6 kingbaseesv8 kingbaseesv kbbench kingbaseesv8r6 kingbaseesv8 kingbaseesv pageinspect kingbaseesv8r6 kingbaseesv8 kingbaseesv字母 kingbaseesv8r6全局kingbaseesv8 kingbaseesv kingbaseesv8r6 kingbaseesv8 kingbaseesv参数 kingbaseesv8r6 kingbaseesv8 kingbaseesv索引 末端kingbaseesv8r6 kingbaseesv8 kingbaseesv