Enforcing strong passwords for MariaDB users

MariaDB users normally connect using a password. Weak passwords are a common security problem, especially when passwords are generated by humans. However, MariaDB comes with plugins that help validating passwords to make sure they are strong enough. This article is a practical overview of MariaDB password validation plugins.

Managing password validation plugins

Password validation plugins are just a type of MariaDB plugins. To list all installed or available password validation plugins, use this query:

SELECT * FROM information_schema.ALL_PLUGINS
WHERE PLUGIN_TYPE = 'PASSWORD VALIDATION';

Just like any other MariaDB plugin, they can be installed with an SQL statement:

INSTALL SONAME 'simple_password_check';

If the above returns an error, the share library file is not in the plugin_dir. You’ll need to install it first. For example, to install the Cracklib Password Check plugin on Ubuntu, do the following:

apt-get install mariadb-plugin-cracklib-password-check

Alternatively, we can install a plugin by adding a line in the configuration file, in the [mariadb] or [server] group:

plugin_load_add = simple_password_check

A plugin can add server variables (options) and status variables to MariaDB. The plugin name is normally used as a prefix for these variables. To list them, run:

SHOW GLOBAL VARIABLES LIKE 'simple_password_check%';
SHOW GLOBAL STATUS LIKE 'simple_password_check%';

A plugin can also be uninstalled at runtime by running this SQL statement:

UNINSTALL SONAME 'simple_password_check';

Password validation plugins code is run when we create a user or change a user password:

CREATE USER sally@'100.100.%' IDENTIFIED BY 'victoria.secret';
SET PASSWORD FOR sally@'100.100.%' = PASSWORD('helena.secret');

Or, more importantly, when users change their own passwords:

SET PASSWORD = PASSWORD('NCC1701');

Password can also be set as a MariaDB-generated hash. You might do this because you can read hashed passwords from another MariaDB installation, but you don’t have access to clear passwords. Or you might obtain the hash with the PASSWORD() function.

You can install multiple password check plugins. Each of them will perform different checks on newly set passwords.

Simple Password Check plugin

The Simple Password Check plugin has checks that a password has the required minimum number of characters for each type: digits, lowercase and uppercase letters, and special characters. The following variables can be used to configure this check.

• simple_password_check_minimal_length: password minimum length;
• simple_password_check_digits: minimum number of digits;
• simple_password_check_letters_same_case: minimum number of lowercase letters and uppercase letters;
• simple_password_check_other_characters: minimum number of special characters.

Cracklib Password Check plugin

The Simple Password Check plugin, explained above, is good to make sure that passwords can’t be quickly cracked with a brute force attack, which means an attack where a program generates all combinations of characters. But even a long password might be cracked quickly if it’s based on common words. Examples of such unsafe passwords are 'DarthVader' or 'Mississippi'. Password validation also fails for passwords that are equal to the username, forward or in reverse. The Cracklib Password Check plugin validates passwords against a vast internal dictionary. It is essentially an interface between MariaDB and the CrackLib library.

The variable cracklib_password_check_dictionary contains the dictionary path. It is possible to set it to a different value, to use an alternate dictionary. Or it is possible to edit the default dictionary to add more words. However, when the dictionary is updated, you’ll need to add your words again.

Forcing password changes

MariaDB allows DBAs to force users to change their password at time intervals. The Password Reuse Check plugin can be used to forbid users to reuse past passwords.

Password expiration

To force users to change their password after a time interval, we can use the default_password_lifetime variable:

SET GLOBAL default_password_lifetime = 30; -- 30 days

As the name suggests, this is a default. The actual password lifetime can be set differently for specific users. For example, to set a shorter interval for an external consultant:

ALTER USER sally@'100.100.%' PASSWORD EXPIRE INTERVAL 7 DAY;

For application users, you might want to disable the password change requirement. You can do it in this way:

ALTER USER ms_login@'12.%' PASSWORD EXPIRE NEVER;

Disallowing password reuse

To forbid users to reuse passwords they used in the past, we need to install the Password Reuse Check plugin.

Once it’s installed, the password_reuse_check_interval determines for how many days old passwords (or their hashes) are retained to avoid reuse. Set it to zero to disable this check. Examples:

SET GLOBAL password_reuse_check_interval = 0; -- don't retain old pwds
SET GLOBAL password_reuse_check_interval = 365; -- retains for 1 year

Old password hashes are stored in the password_reuse_check_history table, in the mysql system database. If, for some reason, you need to force the reuse of a password, you can delete its hash from this table.

Failed password validation

When a password check plugin notices a password that doesn’t fulfil the requirements, it returns an error:

MariaDB [(none)]> SET GLOBAL simple_password_check_minimal_length = 10;
Query OK, 0 rows affected (0.003 sec)

MariaDB [(none)]> CREATE USER sally@'100.100.%' IDENTIFIED BY 'short';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements (simple_password_check)

We don’t have specific information about why the password is not valid, but we can see the plugin name.

Suspend or disallow login

Sometimes, you might want to disable a user’s ability to login for a period of time. For example, because a human user is on leave, or because it’s used by a job that has been disabled. Also, some users should never be able to login. That is the case for users that are definers of stored procedures or views, but should never be used in other contexts. In this way, the necessary permissions can be assigned exclusively to the user that created the stored procedures or views. See MariaDB/MySQL: Using views to grant or deny row-level privileges.

To disallow a user login, do the following:

ALTER USER sally@'100.100.%' ACCOUNT LOCK;

This operation can be reversed in this way:

ALTER USER sally@'100.100.%' ACCOUNT UNLOCK;

Conclusions

MariaDB has several plugins and features that can be used to validate user passwords:

• simple_password_check validates that passwords have enough characters for each type;
• cracklib_password_check validates passwords against a dictionary;
• password_reuse_check validates new passwords against passwords used in the past by the same user;
• The latter is useful in combination with the PASSWORD EXPIRE syntax or the password_reuse_check_interval variable, that force users to periodically change their passwords;
• Some users’ ability to login can be disabled, temporarily or forever, with the ACCOUNT LOCK syntax.

To make sure that you are using these features wisely and to maximise your database security level, contact us for a MariaDB Health Check.

Federico Razzoli