MariaDB’s default password validation is alarmingly weak, allowing trivial passwords like "123456" and even empty strings. This isn’t just a minor oversight; it’s a critical security vulnerability that leaves your databases wide open to brute-force attacks and unauthorized access. If you’re not actively enforcing strong passwords, you’re essentially leaving the door unlocked for attackers.
Let’s see what a weak password policy looks like in practice. Imagine a user testuser with a password password.
-- Connect as a privileged user (e.g., root)
mysql -u root -p
-- Create a new user with a weak password
CREATE USER 'testuser'@'localhost' IDENTIFIED BY 'password';
-- Attempt to log in with the weak password
mysql -u testuser -p
-- Enter 'password' when prompted
As you can see, this works without any issues. Now, let’s fix this.
The core of MariaDB’s password validation lies in the validate_password plugin. This plugin provides a flexible way to define password strength requirements. To enforce strong passwords, you need to install and configure this plugin.
First, ensure the plugin is available. Connect to your MariaDB server as a privileged user:
mysql -u root -p
Then, check if the validate_password plugin is installed:
SHOW PLUGINS LIKE 'validate_password';
If it’s not installed, you’ll need to install it. The exact command might vary slightly depending on your MariaDB version and distribution, but it generally looks like this:
INSTALL PLUGIN validate_password SONAME 'validate_password.so';
Once installed, you can configure its parameters. These parameters control the password strength rules. The most important ones are:
validate_password_length: The minimum length of the password.validate_password_mixed_case_count: The minimum number of uppercase and lowercase letters required.validate_password_number_count: The minimum number of digits required.validate_password_special_char_count: The minimum number of special characters required.
You can set these parameters globally for all users or per-user. For a strong, system-wide policy, set them globally.
-- Set global validation policy
SET GLOBAL validate_password_length = 12;
SET GLOBAL validate_password_mixed_case_count = 2;
SET GLOBAL validate_password_number_count = 2;
SET GLOBAL validate_password_special_char_count = 2;
These settings enforce a minimum password length of 12 characters, requiring at least two uppercase letters, two lowercase letters, two digits, and two special characters.
Now, let’s try to create a user with a password that doesn’t meet these criteria.
-- Attempt to create a user with a weak password (too short)
CREATE USER 'weakuser'@'localhost' IDENTIFIED BY 'short';
This will result in an error, typically:
ERROR 1801 (HY000): Password validation failed for user 'weakuser'@'localhost'
The error message itself is the validation failing. The plugin intercepts the CREATE USER or ALTER USER statement and checks the provided password against the configured policy. If it fails, the operation is aborted before the user account is created or modified.
To successfully create a user with a strong password, it must meet all the configured criteria. For example, a password like P@sswOrd123! would satisfy our current global policy.
-- Create a user with a strong password
CREATE USER 'stronguser'@'localhost' IDENTIFIED BY 'P@sswOrd123!';
This command should now succeed.
You can also check the current policy settings:
SHOW VARIABLES LIKE 'validate_password%';
This will list all the active validate_password variables and their current values.
The validate_password plugin also supports different policy levels (LOW, MEDIUM, STRONG, CUSTOM). You can set a predefined level to quickly apply a set of rules:
-- Set to MEDIUM policy
SET GLOBAL validate_password_policy = MEDIUM;
The MEDIUM policy typically enforces a minimum length of 8 characters, with at least one digit and one special character. You can find the exact rules for each level in the MariaDB documentation for your specific version.
It’s crucial to remember that changing these global settings only affects newly created or altered user accounts. Existing user accounts with weak passwords will remain vulnerable until their passwords are changed. You can use a query like this to identify users with potentially weak passwords (though this is a heuristic and not a definitive check of the validate_password plugin’s rules):
SELECT user, host FROM mysql.user WHERE LENGTH(authentication_string) < 16; -- This is a very rough estimate
For existing users, you’ll need to explicitly alter their accounts to enforce the new policy:
ALTER USER 'olduser'@'localhost' IDENTIFIED BY 'NewStrongP@ssw0rd!';
This ALTER USER statement will trigger the validate_password plugin, ensuring the new password meets the defined strength requirements.
The most counterintuitive aspect of password validation is that even with the plugin enabled and configured, a user can still set a password that looks strong to a human but is easily guessable by a machine if it doesn’t meet the specific character count requirements. For instance, SuperLongPassword123 might seem robust, but if your policy requires special characters and this password lacks one, it will be rejected. The plugin is strictly programmatic; it doesn’t understand human perception of "strength."
The next step after enforcing strong passwords is to implement regular password rotation and consider multi-factor authentication for critical accounts.