Password and Secret Management

Two different things are called “passwords” in a MySQL system, and both matter:

1. Database account passwords: how your application and people log in to MySQL.
2. Your users’ passwords: stored in a table by your application.

1. Database account passwords

Enforce strength with validate_password

The validate_password component checks the strength of passwords when accounts are created or changed. Install it once per server:

1
2

INSTALL COMPONENT 'file://component_validate_password';
SELECT @@validate_password.policy AS policy, @@validate_password.length AS min_length;

1
2
3
4
5

+--------+------------+
| policy | min_length |
+--------+------------+
| MEDIUM |          8 |
+--------+------------+

(Checked on the test servers.) The policy levels:

A weak password is then refused:

1
2

CREATE USER 'u'@'localhost' IDENTIFIED BY 'abc';
-> ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

Set the policy: SET PERSIST validate_password.policy = 'STRONG'; SET PERSIST validate_password.length = 14;. Removing it is UNINSTALL COMPONENT 'file://component_validate_password';. (MySQL Server 5.7 used a plugin of the same name, and the variables were named validate_password_policy.)

What makes a good database password

• Long and random: 20 or more characters from a password manager or a generator. Nobody types an application password by hand.
• Unique per account and per environment.
• Not based on words, dates or the project name.

1
2

# generate a random password in a terminal (Linux, macOS)
openssl rand -base64 24

Rotation and history

Use PASSWORD HISTORY, PASSWORD EXPIRE INTERVAL for human accounts, and dual passwords (RETAIN CURRENT PASSWORD) to rotate application passwords without downtime (Creating Users).

Do not put passwords on the command line

1
2

mysql -u root -p'SecretPassword' ...    # BAD: visible in the process list and in shell history
mysql -u root -p ...                    # GOOD: prompts for the password

A better way for scripts is an option file with restricted permissions, or the login path feature, which stores credentials in an encrypted file:

1
2

mysql_config_editor set --login-path=backup --host=localhost --user=shop_backup --password
mysqldump --login-path=backup shop > shop.sql

(The first command asks for the password and stores it obfuscated in ~/.mylogin.cnf, readable only by you. Obfuscated is not the same as safe from a determined local attacker, but it keeps the password out of command lines and scripts.)

2. Your users’ passwords (in your application)

Never store a user’s password. Store a slow, salted hash:

1
2
3
4
5
6
7
8

CREATE TABLE users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
INSERT INTO users (email, password_hash) VALUES ('asha@example.com', '$2y$12$abcdefghijklmnopqrstuuABCDEFGHIJKLMNOPQRSTUVWXYZ01234');
SELECT email, LEFT(password_hash, 7) AS algorithm_and_cost, CHAR_LENGTH(password_hash) AS stored_length FROM users;

1
2
3
4
5
6

+------------------+--------------------+---------------+
| email            | algorithm_and_cost | stored_length |
+------------------+--------------------+---------------+
| asha@example.com | $2y$12$            |            60 |
+------------------+--------------------+---------------+
1 row in set

(The value is a made-up string that only has the shape of a bcrypt hash.) The hash is made by the application, with a password-hashing function:

1
2
3
4
5
6
7
8
9
10
11
12

<?php
// when the user registers or changes the password
$hash = password_hash($plainPassword, PASSWORD_DEFAULT);          // bcrypt or Argon2, salted automatically
// store $hash in users.password_hash

// when the user logs in
if (password_verify($plainPassword, $row['password_hash'])) {
    if (password_needs_rehash($row['password_hash'], PASSWORD_DEFAULT)) {
        // compute a new hash and update the row
    }
    // log the user in
}

A plain fast hash such as SHA-256 can be tested billions of times per second on a graphics card. A password hash such as bcrypt is designed to be slow, so each guess costs the attacker real time (Hash Functions).

Secrets in configuration

• Keep database credentials in environment variables or a secret manager.
• Set file permissions so that only the application user can read the configuration file (chmod 600).
• Do not log SQL with literal passwords. Check the general log and slow query log: they contain statement text.
• CREATE USER ... IDENTIFIED BY 'password' and ALTER USER ... IDENTIFIED BY appear in logs as text, except where the server rewrites them (the general log rewrites passwords by default, log_raw off).

1

SELECT @@log_raw AS general_log_shows_raw_passwords;

1
2
3
4
5
6

+---------------------------------+
| general_log_shows_raw_passwords |
+---------------------------------+
|                               0 |
+---------------------------------+
1 row in set

0 means that passwords in statements are replaced by *** in the general query log. Keep it that way.

Responding to a leaked credential

1. Change the password right away (ALTER USER ... IDENTIFIED BY, with RETAIN CURRENT PASSWORD if you need a short overlap).
2. Find where it leaked (a repository, a log, a backup, a screenshot).
3. Review the logs for unexpected logins and activity.
4. Remove it from history where possible. A deleted Git commit is not safe: treat the secret as public.
5. Fix the root cause so that it cannot happen again.