KingbaseES V8R6 account abnormal login locking case

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).

Posted by deadlyp99 on Fri, 13 May 2022 07:28:13 +0300