Detections
Analytics
7.8 Ensure 'caching_sha2_password' uses and enforces 'PBKDF2-SHA512' password storage
Information
The caching_sha2_password authentication plugin supports multiple password storage formats. The existing storage format is CRYPT5 with SHA-256. Newer versions add support for PBKDF2_SHA512, which provides a stronger password transformation format.The global dynamic variable caching_sha2_password_storage_format controls the preferred format used when passwords are created or changed through CREATE USER or ALTER USER.
The global dynamic variable caching_sha2_password_enforce_storage_format controls whether accounts using a password stored in a non-preferred format are allowed to continue authenticating normally or are forced to change their password after login.
Password hashes should be stored using the strongest supported format. PBKDF2_SHA512 provides stronger password transformation characteristics than the existing CRYPT5 SHA-256 format and should be preferred for newly created or changed passwords.
Setting caching_sha2_password_storage_format=PBKDF2_SHA512 ensures that new password transformations use the preferred format. Setting caching_sha2_password_enforce_storage_format=ON helps migrate existing accounts by forcing users whose passwords are stored in a different format to change their password after authentication.
The stored password transformation remains the source of truth for verification. Therefore, changing the preferred storage format does not immediately convert existing password hashes. Existing accounts must change their passwords before their stored format is updated.
Solution
Set the preferred password storage format to PBKDF2-SHA512:SET PERSIST caching_sha2_password_storage_format = 'PBKDF2_SHA512';
Enable enforcement of the preferred storage format:
SET PERSIST caching_sha2_password_enforce_storage_format = ON;
Alternatively, configure the settings in the MySQL server option file:
[mysqld]
caching_sha2_password_storage_format=PBKDF2_SHA512
caching_sha2_password_enforce_storage_format=ON
If a site-approved value is required for digest rounds, set it explicitly:
SET PERSIST caching_sha2_password_digest_rounds = <approved_value>;
Existing accounts using the older format must change passwords before their stored password format is updated. For interactive users, expire the password:
ALTER USER '<user_name>'@'<host_name>' PASSWORD EXPIRE;
For application or service accounts, rotate the password in a planned maintenance window:
ALTER USER '<user_name>'@'<host_name>' IDENTIFIED BY '<new_password>';
After password rotation, re-run the audit query to confirm that the account's stored password format is now PBKDF2_SHA512.
In replicated environments, apply the same configuration on all nodes. Upgrade replicas before the primary when introducing the new storage format, and confirm that all nodes use the same values for:
caching_sha2_password_storage_format
caching_sha2_password_enforce_storage_format
Impact:
Enabling PBKDF2_SHA512 as the preferred storage format affects password creation and password changes for accounts that use the caching_sha2_password plugin.
Enabling caching_ sha2_password_enforce_storage_format may cause users with passwords stored in a non-preferred format to be required to change their password immediately after login. Such accounts will not use the fast authentication cache until their password is changed to the preferred format.
Service accounts, application accounts, replication users, and non-interactive users may be disrupted if they are forced to change passwords unexpectedly. These accounts should be identified and remediated through planned password rotation before enforcement is enabled.
In replicated environments, the same values for caching_sha2_password_storage_format and caching_sha2_password_enforce_storage_format should be configured on all primary and replica nodes. Replicas should be upgraded before the primary so that replicated account changes using the newer password storage format can be processed consistently.
See Also
Item Details
Category: IDENTIFICATION AND AUTHENTICATION
References: 800-53|IA-5(1), CSCv7|16.4
Plugin: MySQLDB
Control ID: b46672c695a589606932b7476800ee7d51b73657119ae69c955ecf44115e33a1