Locking database user account in SingleStore

{question}

How to lock database user account in SingleStore?

{question}

{answer}

We don't have a single built-in feature to achieve this directly, but below we can suggest a two-step process to achieve this result:

Step 1. Change the password of the user using the SET PASSWORD command:

SET PASSWORD FOR 'username'@'host' = PASSWORD('password')

Note: You can change another user's password with this command if you have SUPER privileges. Click here to learn more about it.

Step 2. Alter the user account with FAILED_LOGON_ATTEMPTS and PASSWORD_LOCK_TIME,

ALTER USER 'username'@'host' SET FAILED_LOGIN_ATTEMPTS = 1 PASSWORD_LOCK_TIME = 2592000;

When the user tries to log in with the old password, it will fail with an access denied error as we have changed the password. A couple of more attempts will result in the below error:

ERROR 2449 (HY000): This user account has been locked due to too many failed login attempts. It will be automatically unlocked in 2591996 seconds. Please contact your MemSQL administrator to unlock it manually via ALTER USER.

Note:

• FAILED_LOGIN_ATTEMPTS: Together with PASSWORD_LOCK_TIME, specifies the failed login attempt lockout behavior. FAILED_LOGIN_ATTEMPTS is the number of failed attempts allowed before the account is locked out. Default is 0 which means there is no restriction. When set to a value >=1, PASSWORD_LOCK_TIME must also be specified.

• PASSWORD_LOCK_TIME: Together with FAILED_LOGIN_ATTEMPTS, specifies the failed login attempt lockout behavior. PASSWORD_LOCK_TIME is the number of seconds a locked out account must wait before reattempting to log in. Click here to learn more about alter user command.

Example: We have created a user called db_user. Changed the password and set the FAILED_LOGIN_ATTEMPTS to 1 and PASSWORD_LOCK_TIME to 2592000 (30 days) like below:

mysql> SET PASSWORD FOR 'db_user'@'%' = PASSWORD('password');
Query OK, 0 rows affected (0.30 sec)

mysql> ALTER USER 'db_user'@'%' SET FAILED_LOGIN_ATTEMPTS = 1 PASSWORD_LOCK_TIME = 2592000;
Query OK, 0 rows affected (0.28 sec)

When the user tries to log in with the old password, It will be locked:

% mysql -h ec2-34-201-4-179.compute-1.amazonaws.com -u db_user -P 3306 -pPassword@123
ERROR 1045 (28000): Access denied for user 'db_user'@'106.208.130.11' (using password: YES)

% mysql -h ec2-34-201-4-179.compute-1.amazonaws.com -u db_user -P 3306 -pPassword@123
ERROR 1045 (28000): Access denied for user 'db_user'@'106.208.130.11' (using password: YES)

% mysql -h ec2-34-201-4-179.compute-1.amazonaws.com -u db_user -P 3306 -pPassword@123
ERROR 2449 (HY000): This user account has been locked due to too many failed login attempts. It will be automatically unlocked in 2591996 seconds. Please contact your MemSQL administrator to unlock it manually via ALTER USER.

Database Administrator can unlock the account using the below command:

ALTER USER 'username'@'%' ACCOUNT UNLOCK;

{answer}