This topic does not apply to MemSQL Helios.
You can configure a robust password policy, including rules for password expiration, reuse, and complexity, by configuring the following engine variables. See the engine variables overview for information on working with engine variables.
password_expiration_seconds: The time in seconds before a password expires. The default value is
0, which indicates that passwords will never expire.
expire_root_password: Specifies whether the root password can expire. The default value is
OFF. When set to
password_expiration_seconds duration does not apply to the root password. If set to
ON, the root password will expire after the
password_expiration_seconds duration is reached.
password_history_count: Restricts the reuse of previous user passwords. This variable is the number of previous passwords per user that MemSQL will store and disallow from reuse.
MemSQL will disallow 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
These variables control password complexity requirements. For each variable, the default value is
0 (disabled) and the accepted values are integers from
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 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.
Example Password Complexity Usage
The following stored procedure (you can also use individual SET statements in the command line) will create a password complexity policy where passwords must:
- be at least 12 characters long
- include at least one uppercase character
- include at least one lowercase character
- include at least one numeric character
- include at least one special character
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();
Password Complexity and Existing Passwords
When changes are made to the password complexity policy, existing passwords are not checked. The policy will only be applied to new or changed passwords.
Password Complexity in Previous Versions
strict_passwords is deprecated as of version 7.1 of MemSQL. If you use both
strict_passwords and any of the password complexity variables described in this section, then both sets of rules will be enforced. MemSQL recommends leaving
strict_passwords set to
OFF (the default).
strict_passwords: When set to
ON, MemSQL will require that all passwords be at least 6 characters in length, contain at least one letter, and contain at least one number. The default value is