Database version:
test=> select version(); version ----------------------------------------------------------------------------------------------------------- KingbaseES V008R006C005B0054 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-46), 64-bit (1 row)
Official documents: https://help.kingbase.com.cn/stage-api/profile/document/kes/v8r6/html/safety/safety-guide/safety-identification.html#id9
Account abnormal login lock
Account abnormal login locking means that if a user fails to log in to the database correctly several times in a row, the user's account will be disabled by the system. The number of consecutive false logins allowed by the system is specified by the database administrator. The disabled account can be made available again by the security officer using SQL commands or automatically unlocked after waiting for a period of time.
KingbaseES uses plug-ins to lock account abnormal login and display account login information. This method is more flexible. When the practical scenario of the database requires account abnormal login locking and account login information display, the plug-in can be loaded. When you do not need this function, you can uninstall the plug-in.
The plug-in name is sys_audlog, the relevant parameters are configured by the database security officer.
3.3.1. Loading plug-ins
Modify Kingbase Shared in conf file_ preload_ Libraries parameter.
shared_preload_libraries = 'sys_audlog' create extension sys_audlog;
3.3.2. Parameter configuration
- sys_audlog.error_user_connect_times
The maximum number of consecutive login failures allowed. If the number of login failures exceeds this value, the user will be locked automatically. The value range is [0,INT_MAX], and the default is 0.
Set the maximum number of consecutive password failures to 10.
\c test sso ALTER SYSTEM SET sys_audlog.max_error_user_connect_times = 10; CALL sys_reload_conf();
- sys_audlog.max_error_user_connect_times
Maximum limit of user login failures, error_ user_ connect_ The maximum value of times. The value range is [0,INT_MAX]. The default value is 2147483647.
Set the maximum number of consecutive password failures to 6.
\c test sso ALTER SYSTEM SET sys_audlog.error_user_connect_times = 6; CALL sys_reload_conf();
- sys_audlog.error_user_connect_interval
The user is locked for a period of time. If the user is locked for more than this parameter, the user can unlock automatically. The unit is minute and the value range is [0, INT_MAX]. When it is 0, the automatic unlocking function is turned off and manual unlocking is required. The default is 0.
Set the automatic unsealing time of blocked users to 1 hour.
\c test sso ALTER SYSTEM SET sys_audlog.error_user_connect_interval = 60; CALL sys_reload_conf();
3.3.3. Unlock
If the time interval is exceeded, the user blockade will be automatically lifted.
The user can be manually unlocked by the user with alter user permission through SQL statement. After unlocking, the login information of the user is automatically deleted.
\c test sso alter user username with login;
Tip
1. After the time interval is exceeded, the user needs to log in successfully. If the user logs in again after reaching the unlocking time and fails again, the user will continue to lock.
2. If you do not add - W during login, you will automatically make a login attempt without password, so you will add one more failure record. If you log in without - W when unlocking the user, you will be locked again. Therefore, when unlocking the user, you should pay attention to adding - W parameter for login attempt.
Test case:
1. Configure Kingbase Conf and sys_hba.conf
Configure Kingbase Conf: (load extension)
[kingbase@node1 data]$ cat kingbase.conf |grep sys_audlog shared_preload_libraries = 'liboracle_parser, synonym, plsql, force_view, kdb_flashback,plugin_debugger, plsql_plugin_debugger, plsql_plprofiler, ora_commands,kdb_ora_expr, sepapower, dblink, sys_kwr, sys_ksh, sys_spacequota, sys_stat_statements, backtrace, kdb_utils_function, auto_bmr, sys_squeeze,sys_audlog'
Configure sys_hba.conf:
=By default, the user logs in local ly, does not authenticate the user's identity, and uses trust=
# Allow replication connections from localhost, by a user with the local all all scram-sha-256
2. After restarting the database, sso user configures relevant parameters
[kingbase@node1 bin]$ ./ksql -U sso test -p 54322 ksql (V8.0) Type "help" for help. test=> show sys_audlog.error_user_connect_interval ; sys_audlog.error_user_connect_interval ---------------------------------------- 0 (1 row) test=> show sys_audlog.error_user_connect_times; sys_audlog.error_user_connect_times ------------------------------------- 0 (1 row) test=> alter system set sys_audlog.error_user_connect_times=5; ALTER SYSTEM test=> alter system set sys_audlog.error_user_connect_interval =3; ALTER SYSTEM test=> select sys_reload_conf(); sys_reload_conf ----------------- t (1 row) test=> show sys_audlog.error_user_connect_times; sys_audlog.error_user_connect_times ------------------------------------- 5 (1 row) test=> show sys_audlog.error_user_connect_interval; sys_audlog.error_user_connect_interval ---------------------------------------- 3 (1 row)
3. Create user test
test=# create user tom with password 'tom'; CREATE 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 | {} tom | | {}
4. User login test
[kingbase@node1 bin]$ ./ksql -U tom -W test -p 54322 Password: ksql: error: could not connect to server: FATAL: password authentication failed for user "tom" NOTICE: This is the 1 login failed. There are 4 left. [kingbase@node1 bin]$ ./ksql -U tom -W test -p 54322 Password: ksql: error: could not connect to server: FATAL: password authentication failed for user "tom" NOTICE: This is the 2 login failed. There are 3 left. [kingbase@node1 bin]$ ./ksql -U tom -W test -p 54322 Password: ksql: error: could not connect to server: FATAL: password authentication failed for user "tom" NOTICE: This is the 3 login failed. There are 2 left. [kingbase@node1 bin]$ ./ksql -U tom -W test -p 54322 Password: ksql: error: could not connect to server: FATAL: password authentication failed for user "tom" NOTICE: This is the 4 login failed. There are 1 left. [kingbase@node1 bin]$ ./ksql -U tom -W test -p 54322 Password: ksql (V8.0) Type "help" for help.
4. Test summary:
In KingbaseES V008R006C005B0054 test, the account locking complies with the established policy of the parameter (sys_audlog.error_user_connect_times).