Password Expiration

password_expiration_seconds: The time in seconds before a password expires. The default value is 0, which indicates that passwords never expire.

expire_root_password: Specifies whether the root password can expire. The default value is OFF. When set to OFF, the password_expiration_seconds duration does not apply to the root password. If set to ON, the root password expires after the password_expiration_seconds duration is reached.

password_expiration_mode: Specifies what happens when a user's password expires. The default value is NO_ACCESS. If set to NO_ACCESS, the user cannot login after the password expires. If set to LIMITED_ACCESS, the user can login after the password expires but can only update their password using ALTER USER or SET PASSWORD commands. Until the password is updated, no other commands can be executed except SET SESSION or SET LOCAL.

Password Reuse

password_history_count: Restricts the reuse of previous user passwords. This variable is the number of previous passwords per user that SingleStore stores and disallows from reuse.

SingleStore does not allow setting a user account’s password to one of the last password_history_count number of passwords for that user. The count includes the current password. For example, if set to 2, setting a user’s password to its current password or the last password before the current password is disallowed. The default value is 0, which indicates that any previous password can be reused. The maximum is 10.

Password Complexity

The following variables control password complexity requirements. For each variable, the default value is 0 (disabled) and the accepted values are integers ranging from 0 to 100.

password_min_length: The minimum number of characters required.

password_min_uppercase_chars: The minimum number of uppercase characters required.

password_min_lowercase_chars: The minimum number of lowercase characters required.

password_min_numeric_chars: The minimum number of numeric digit characters required.

password_min_special_chars: The minimum number of special (non-alphanumeric) characters required.

password_max_consec_sequential_chars: The maximum number of consecutive characters allowed. For example, if set to 3, passwords with a 4-letter sequence or longer (e.g 1234 or abcd) are disallowed.

password_max_consec_repeat_chars: The maximum number of consecutive repeated characters allowed. For example, if set to 3, passwords with 4 or more consecutive repeated characters (e.g., aaaa or 1111) are disallowed.

When a user enters a new password, if the password does not meet the password complexity policy, the following error message is returned:

Error: password does not meet the requirements specified for <variable> in your password complexity policy. Password not changed.

CREATE DATABASE db_security
USE db_security

DELIMITER //
CREATE OR REPLACE PROCEDURE set_password_complexity_policy() AS
BEGIN
    SET GLOBAL password_min_length=12;
    SET GLOBAL password_min_uppercase_chars=1;
    SET GLOBAL password_min_lowercase_chars=1;
    SET GLOBAL password_min_numeric_chars=1;
    SET GLOBAL password_min_special_chars=1;
END
//
DELIMITER ;

CALL set_password_complexity_policy();