Password Policies and Account Security in MySQL

Course: MySQL Training

Topic: Password Policies and Account Security

Course Link: MySQL Training Course – JustAcademy

Demo Link: Register for a Course Demo

1. Introduction to Password Policies and Account Security

Password policies and account security are important parts of database administration. MySQL databases often contain confidential information such as customer details, employee records, financial transactions, application data, and business reports. If unauthorized users gain access to a database account, they may read, modify, or delete sensitive information.

MySQL provides several mechanisms to protect database accounts, including password strength validation, password expiration, password reuse restrictions, failed-login tracking, account locking, authentication plugins, and privilege management.

A password policy defines the requirements that users must follow when creating or changing passwords. Account security includes the broader practices and technical controls used to prevent unauthorized access to MySQL servers and databases.

Example Scenario

Suppose a company has a MySQL database containing customer information. The database administrator creates accounts for developers, analysts, and application services. If every account uses a weak password and unrestricted permissions, an attacker may be able to access confidential data.

To reduce this risk, the administrator can configure strong password validation, restrict failed login attempts, lock unused accounts, and grant each user only the privileges needed for their responsibilities.

2. Learning Objectives

After studying this topic, you will be able to:

• Understand password policies and account security in MySQL.
• Identify the risks associated with weak passwords.
• Understand the MySQL password validation component.
• Configure password strength requirements.
• Use password expiration and reuse restrictions.
• Configure failed-login tracking and temporary account locking.
• Create, modify, lock, unlock, and inspect user accounts.
• Understand authentication methods and account privileges.
• Apply security best practices to database environments.
• Troubleshoot common password and account security issues.

3. What Is a Password Policy?

A password policy is a collection of rules that determines which passwords are acceptable and how passwords should be managed. It helps prevent users from choosing passwords that are easy to guess or reuse across multiple accounts.

In MySQL, password management features can be configured using account-management statements and server components. The available features depend on the MySQL version, authentication method, and server configuration.

Common Password Policy Requirements

4. What Is Account Security in MySQL?

Account security is the process of protecting MySQL accounts against unauthorized authentication and access. It includes password configuration, authentication, permissions, account monitoring, and account lifecycle management.

Main Components of Account Security

• Authentication: Verifies the identity of the connecting user.
• Password validation: Checks whether a password meets configured requirements.
• Authorization: Determines which database operations the authenticated user can perform.
• Account locking: Prevents authentication for a locked account.
• Login failure tracking: Tracks consecutive incorrect password attempts when configured.
• Privilege management: Controls access through statements such as GRANT and REVOKE.
• Monitoring and auditing: Helps administrators investigate unusual access activity.

5. Understanding MySQL User Accounts

A MySQL account is identified by a username and a host value. The host indicates where the client is allowed to connect from.

Account Naming Format

'username'@'host'

Examples

'admin_user'@'localhost'
'report_user'@'192.168.1.25'
'app_user'@'%'

The first account is associated with local connections. The second specifies a particular client host. The percent sign in the third example is a host-pattern wildcard that can allow connections from multiple hosts, depending on MySQL's account-matching rules. Avoid using broad host patterns unnecessarily.

Creating a User Account

CREATE USER 'report_user'@'localhost'
IDENTIFIED BY 'Replace-With-A-Strong-Unique-Password';

This example illustrates account creation. Replace the placeholder with a unique password supplied securely. Do not use the example placeholder as an actual production password.

Viewing Existing Accounts

SELECT User, Host
FROM mysql.user
ORDER BY User, Host;

Reading the mysql.user system table requires appropriate privileges. In routine administration, use authorized account-management statements and inspection commands rather than manually editing MySQL system tables.

6. Introduction to the MySQL Password Validation Component

MySQL provides the validate_password component to improve password security. When enabled, it evaluates passwords supplied through supported account-management statements and rejects passwords that do not satisfy the configured policy.

The component provides configurable rules for password length, character composition, username matching, and dictionary checking. The exact checks depend on the configured policy and component variables.

Important Capabilities

• Enforces configured password strength requirements.
• Supports LOW, MEDIUM, and STRONG policy levels.
• Provides the VALIDATE_PASSWORD_STRENGTH() function.
• Allows administrators to inspect and configure policy variables.
• Can reject passwords that match the username under its configured username-checking rules.

Checking Whether the Component Is Available

SELECT *
FROM INFORMATION_SCHEMA.PLUGINS
WHERE PLUGIN_NAME = 'validate_password';

In MySQL 8.0 and later, password validation is implemented as a component. The information schema query can help identify a registered component, but component status and installation should also be verified using the appropriate component-management documentation and server privileges.

Viewing Password Validation Variables

SHOW VARIABLES LIKE 'validate_password%';

If the component is not installed, the related variables may not be available. Installation and configuration procedures vary by MySQL version and deployment environment.

7. Password Policy Levels

The MySQL password validation component supports three main policy levels: LOW, MEDIUM, and STRONG. These levels provide different degrees of password checking.

With the standard documented defaults, LOW requires at least eight characters. MEDIUM also requires at least one number, one lowercase letter, one uppercase letter, and one special character. These settings can be changed, so administrators should inspect the active configuration rather than assume every server uses the defaults.

Setting the Password Policy

SET GLOBAL validate_password.policy = 'MEDIUM';

This is an example of changing a global component variable when the component is installed and the variable is supported. The account performing the operation must have the required administrative privileges. Review the configuration and persistence requirements for the installed MySQL version before applying changes in production.

8. Configuring Password Length and Character Requirements

Password length and character requirements determine how the validation component evaluates a password. Administrators can configure the minimum length and the required counts of numeric, mixed-case, and special characters.

Viewing Current Settings

SHOW VARIABLES LIKE 'validate_password.length';
SHOW VARIABLES LIKE 'validate_password.number_count';
SHOW VARIABLES LIKE 'validate_password.mixed_case_count';
SHOW VARIABLES LIKE 'validate_password.special_char_count';

Example Configuration

SET GLOBAL validate_password.length = 12;
SET GLOBAL validate_password.number_count = 1;
SET GLOBAL validate_password.mixed_case_count = 2;
SET GLOBAL validate_password.special_char_count = 1;

This example configures a minimum length of 12 characters, at least one number, two mixed-case characters as counted by the component's rules, and at least one special character. These are illustrative values, not universal requirements. The component must be installed, the variables must be supported, and the administrator must have permission to change them.

Why Longer Passwords Matter

• Longer passwords generally increase the difficulty of brute-force guessing.
• Unique passwords reduce the impact of credentials exposed by another service.
• Passphrases can be easier to remember while still being difficult to guess.
• Length should be combined with uniqueness, secure storage, and access controls.

9. Checking Password Strength with VALIDATE_PASSWORD_STRENGTH()

MySQL provides the VALIDATE_PASSWORD_STRENGTH() function to estimate the strength of a candidate password according to the validation component's checks. It returns an integer score from 0 to 100.

Syntax

SELECT VALIDATE_PASSWORD_STRENGTH('Candidate-Password');

Example

SELECT VALIDATE_PASSWORD_STRENGTH('Replace-With-A-Candidate');

The returned score depends on the candidate and the component's rules. A higher score does not guarantee that a password has never been exposed or that it is safe to reuse.

Important Security Note

Do not place real production passwords in SQL scripts, screenshots, shared documents, source code, or recorded demonstrations. The example above uses a placeholder. If you test password strength, use a temporary candidate that is not used by any real account.

10. Changing a MySQL Account Password

Passwords should be changed when required by security policy, after suspected credential exposure, or when an account's credentials need to be rotated. MySQL provides the ALTER USER statement to change account authentication details.

Syntax

ALTER USER 'username'@'host'
IDENTIFIED BY 'Replace-With-A-New-Unique-Password';

Example

ALTER USER 'report_user'@'localhost'
IDENTIFIED BY 'Replace-With-A-New-Unique-Password';

The example changes the account password to the supplied value, provided it satisfies the active password policy and the executing account has the required authority. Use a secure method to supply and manage the actual secret.

Password Change Best Practices

• Use a unique password that is not shared with other systems.
• Do not embed real credentials in application code or SQL files.
• Update dependent applications securely when credentials change.
• Use a secret manager or other approved secure credential-storage mechanism.
• Investigate the cause of a password change when compromise is suspected.

11. Password Expiration Policies

Password expiration is a feature that can require an account's password to be changed after a specified period. MySQL supports account-level password expiration settings through CREATE USER and ALTER USER.

Expire a Password Immediately

ALTER USER 'report_user'@'localhost'
PASSWORD EXPIRE;

This marks the password as expired and requires a password change under MySQL's password-expiration behavior. Applications using the account should be prepared to handle password-expiration conditions correctly.

Set a Password Expiration Interval

ALTER USER 'report_user'@'localhost'
PASSWORD EXPIRE INTERVAL 90 DAY;

This example sets a 90-day expiration interval for the account. Choose an interval that fits organizational security requirements and applicable policies. Forced periodic changes are not a substitute for unique passwords, strong authentication, and prompt changes after suspected compromise.

Set a Password That Does Not Expire Automatically

ALTER USER 'service_user'@'localhost'
PASSWORD EXPIRE NEVER;

Use this setting cautiously. Service accounts that cannot easily support interactive password changes should have a documented credential-rotation process and appropriate monitoring.

Inspect Password Expiration Configuration

SHOW CREATE USER 'report_user'@'localhost';

The output includes account properties such as authentication configuration and password-expiration settings, subject to the version and the privileges of the executing account.

12. Password Reuse Restrictions

Password reuse restrictions prevent a user from selecting certain previously used passwords. They help reduce the risk associated with cycling repeatedly between a small number of passwords.

MySQL supports account-level password history and reuse restrictions. The applicable settings depend on the MySQL version and account configuration.

Prevent Reuse of Recent Passwords

ALTER USER 'report_user'@'localhost'
PASSWORD HISTORY 5;

This example specifies that the account cannot reuse passwords recorded within its most recent five-password history, subject to the server's password-management rules.

Set a Password Reuse Interval

ALTER USER 'report_user'@'localhost'
PASSWORD REUSE INTERVAL 365 DAY;

This example prevents reuse of passwords recorded within the specified 365-day period, subject to MySQL's supported password-history behavior.

Use Both Restrictions

ALTER USER 'report_user'@'localhost'
PASSWORD HISTORY 5
PASSWORD REUSE INTERVAL 365 DAY;

When configuring both restrictions, review the rules for the installed version. Password history and reuse restrictions should support a wider credential-management strategy rather than being the only security control.

13. Failed Login Tracking and Temporary Account Locking

Repeated incorrect password attempts can indicate a typing mistake, an outdated application credential, or a brute-force guessing attempt. MySQL supports failed-login tracking and temporary account locking for accounts configured with the appropriate options.

For failed-login tracking and automatic temporary locking to operate, both FAILED_LOGIN_ATTEMPTS and PASSWORD_LOCK_TIME must be nonzero.

Creating an Account with Failed-Login Protection

CREATE USER 'limited_user'@'localhost'
IDENTIFIED BY 'Replace-With-A-Strong-Unique-Password'
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LOCK_TIME 1;

This example configures the account to be temporarily locked after three consecutive password failures, with a lock period of one day. The password is a placeholder and must be replaced securely. Confirm the active password policy before creating the account.

Applying the Settings to an Existing Account

ALTER USER 'limited_user'@'localhost'
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LOCK_TIME 1;

When the configured number of consecutive password failures is reached, MySQL can temporarily lock the account for the configured period. A successful login before the threshold is reached resets the consecutive failure count.

Using an Unbounded Lock Duration

ALTER USER 'limited_user'@'localhost'
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LOCK_TIME UNBOUNDED;

This setting specifies an unbounded temporary lock duration under MySQL's failed-login locking mechanism. Plan an administrative recovery process before using it.

14. Locking and Unlocking MySQL Accounts

MySQL allows administrators to lock accounts when access is no longer required or when an account needs to be disabled. A locked account cannot authenticate normally, but the account still exists and its grants remain assigned.

Lock an Account

ALTER USER 'former_employee'@'localhost'
ACCOUNT LOCK;

Unlock an Account

ALTER USER 'former_employee'@'localhost'
ACCOUNT UNLOCK;

Locking an account is useful for temporarily disabling access without deleting the user. Unlock an account only after confirming that access should be restored and any security concerns have been addressed.

When to Lock an Account

• When an employee leaves the organization.
• When a service account is temporarily inactive.
• When an account's use needs to be investigated.
• When an application is retired but the account may be needed for a later migration.
• When access must be suspended while preserving account configuration for review.

15. Requiring Password Verification During Password Changes

MySQL supports password verification requirements that can require the current password when a password is changed. This helps reduce the risk of an unauthorized password change in situations where the current password is available and required by the account's policy.

Configure a Verification Requirement

ALTER USER 'report_user'@'localhost'
PASSWORD REQUIRE CURRENT;

This configures the account to require the current password during applicable password changes. The actual password-change command must provide the required verification information, and support can vary by authentication method and client.

Why Verification Matters

• It can help prevent a password change by someone who has access to an already authenticated session but does not know the current password.
• It adds a verification step to applicable password changes.
• It should be combined with secure session management and suitable account privileges.

16. Authentication Methods in MySQL

Authentication is the process by which MySQL verifies a client's identity. MySQL supports authentication plugins that determine how credentials are checked. The available methods and defaults depend on the MySQL version and deployment.

Common Authentication Concepts

Do not switch authentication plugins without checking client compatibility, operational requirements, and the security documentation for the installed MySQL release.

17. Principle of Least Privilege

The principle of least privilege means that every account should receive only the permissions needed to perform its intended tasks. Strong passwords cannot compensate for excessive database privileges.

Example: Read-Only Reporting Account

CREATE USER 'report_user'@'localhost'
IDENTIFIED BY 'Replace-With-A-Strong-Unique-Password';

GRANT SELECT ON company_db.*
TO 'report_user'@'localhost';

This example creates a reporting account with database-level read access. It should not receive permissions to update or delete records unless those operations are genuinely required.

Removing Unnecessary Privileges

REVOKE INSERT, UPDATE, DELETE
ON company_db.*
FROM 'report_user'@'localhost';

The statement removes the specified database-level privileges. If the account has equivalent privileges through global grants or active roles, those sources must also be reviewed.

18. Creating a Secure MySQL Account

A secure account should have a clearly defined purpose, a unique credential, an appropriate authentication method, and only the necessary privileges.

Step 1: Create the Account

CREATE USER 'app_user'@'localhost'
IDENTIFIED BY 'Replace-With-A-Strong-Unique-Password';

Step 2: Assign Required Privileges

GRANT SELECT, INSERT, UPDATE
ON company_db.orders
TO 'app_user'@'localhost';

Step 3: Configure Account Protection

ALTER USER 'app_user'@'localhost'
FAILED_LOGIN_ATTEMPTS 5
PASSWORD_LOCK_TIME 1;

This example configures failed-login tracking and temporary locking. Ensure that the password-lock settings meet the application's recovery requirements.

Step 4: Inspect the Account

SHOW CREATE USER 'app_user'@'localhost';
SHOW GRANTS FOR 'app_user'@'localhost';

Step 5: Test the Account

Connect using the account from its permitted host and test the operations it is expected to perform. Confirm that unauthorized operations are rejected. Conduct tests in a controlled environment and avoid exposing credentials in terminal history, logs, or screenshots.

19. Secure Connections and Network Access

Password protection is only one part of account security. Credentials can still be exposed if network connections, client configurations, or application environments are insecure.

Recommended Practices

• Use encrypted client-server connections, such as TLS, where appropriate.
• Configure certificate verification when required by the security environment.
• Restrict network access to trusted hosts and networks.
• Avoid exposing the MySQL server directly to untrusted public networks.
• Use firewall rules and network segmentation.
• Limit remote administrative access.
• Use supported authentication mechanisms and compatible client libraries.
• Keep MySQL and client software updated with supported security fixes.

Inspect Connection Security Requirements

SHOW VARIABLES LIKE 'require_secure_transport';

The require_secure_transport system variable controls whether the server requires secure transport for applicable connections. Review the official documentation and client compatibility before enabling it in a production environment.

20. Protecting Application Credentials

Applications frequently use MySQL accounts to retrieve or modify data. If credentials are stored insecurely, an attacker may obtain database access without directly attacking MySQL.

Credential Management Best Practices

• Do not hard-code passwords in application source code.
• Do not commit secrets to public or private source repositories.
• Use environment-specific configuration and an approved secret manager.
• Restrict access to configuration files and deployment settings.
• Use separate accounts for development, testing, and production.
• Give application accounts only the permissions they need.
• Rotate credentials after suspected exposure and update dependent services securely.
• Monitor for unexpected authentication failures and unusual database activity.

21. Monitoring Account Security

Monitoring helps administrators identify accounts with outdated settings, unnecessary privileges, unexpected login failures, or inappropriate access patterns.

Inspect Account Definitions

SHOW CREATE USER 'app_user'@'localhost';

Inspect Account Privileges

SHOW GRANTS FOR 'app_user'@'localhost';

Inspect Password Validation Configuration

SHOW VARIABLES LIKE 'validate_password%';

Inspect Secure Transport Configuration

SHOW VARIABLES LIKE 'require_secure_transport';

These commands provide useful configuration information. A complete audit may also require reviewing authentication logs, server logs, audit facilities, network controls, role assignments, and account ownership. The availability of monitoring information depends on server configuration and the privileges of the administrator.

22. Common Password and Account Security Errors

23. Practical Example: Securing a Reporting Account

Scenario: A company wants a reporting user to read information from the company_db database while reducing the risk of weak passwords and repeated login attempts.

Step 1: Create the Account

CREATE USER 'report_user'@'localhost'
IDENTIFIED BY 'Replace-With-A-Strong-Unique-Password';

Step 2: Grant Read-Only Access

GRANT SELECT ON company_db.*
TO 'report_user'@'localhost';

Step 3: Configure Failed-Login Protection

ALTER USER 'report_user'@'localhost'
FAILED_LOGIN_ATTEMPTS 5
PASSWORD_LOCK_TIME 1;

Step 4: Configure Password Expiration

ALTER USER 'report_user'@'localhost'
PASSWORD EXPIRE INTERVAL 90 DAY;

Step 5: Verify the Account

SHOW CREATE USER 'report_user'@'localhost';
SHOW GRANTS FOR 'report_user'@'localhost';

Step 6: Test Access

Confirm that the account can read permitted data but cannot insert, update, or delete records unless another grant or active role provides those privileges. Also verify that the account's password, expiration, and failed-login settings match the intended policy.

Expected result: The account has limited database access and configured password-management controls. The actual behavior depends on the server version, authentication method, existing grants, and active configuration.

24. Hands-On Exercises

1. Create a test MySQL account with a unique password and verify its account definition.
2. Inspect the validate_password variables on a test server where the component is installed.
3. Use VALIDATE_PASSWORD_STRENGTH() with temporary test candidates and compare the results.
4. Configure a password expiration interval for a test account.
5. Set a password history restriction for a test account.
6. Configure failed-login tracking and a temporary lock period.
7. Lock and unlock a test account using ALTER USER.
8. Grant read-only access to a reporting user and verify the assigned privileges.
9. Attempt permitted and denied operations using the test account.
10. Document the account's intended purpose, security settings, and recovery procedure.

25. Mini Project: MySQL Account Security Audit

Project objective: Create and review a set of MySQL accounts using appropriate password policies and least-privilege access.

Project Requirements

• Create separate test accounts for an administrator, reporting user, and application user.
• Assign each account only the privileges needed for its purpose.
• Review password validation settings.
• Configure password expiration or reuse restrictions where appropriate.
• Enable failed-login tracking for selected accounts.
• Test account locking and unlocking.
• Inspect account definitions using SHOW CREATE USER.
• Inspect permissions using SHOW GRANTS.
• Document account ownership, credential handling, and recovery steps.
• Prepare a final security checklist identifying unnecessary permissions and missing controls.

Sample SQL Workflow

CREATE DATABASE IF NOT EXISTS company_db;

CREATE USER IF NOT EXISTS 'report_user'@'localhost'
IDENTIFIED BY 'Replace-With-A-Strong-Unique-Password';

GRANT SELECT ON company_db.*
TO 'report_user'@'localhost';

ALTER USER 'report_user'@'localhost'
FAILED_LOGIN_ATTEMPTS 5
PASSWORD_LOCK_TIME 1;

ALTER USER 'report_user'@'localhost'
PASSWORD EXPIRE INTERVAL 90 DAY;

SHOW CREATE USER 'report_user'@'localhost';
SHOW GRANTS FOR 'report_user'@'localhost';

Run this example only in a practice environment with an authorized account. The placeholder password is not a real credential and must be replaced securely. The active password policy must accept the chosen password, and the executing administrator must have the necessary privileges.

26. Interview Questions and Answers

Q1. What is a password policy in MySQL?

Answer: A password policy defines rules for acceptable passwords and their management, such as length, character requirements, expiration, and reuse restrictions.

Q2. What is the validate_password component?

Answer: It is a MySQL component that validates password strength against configurable rules and provides a password-strength assessment function.

Q3. What are the password validation policy levels?

Answer: The component supports LOW, MEDIUM, and STRONG policies, with increasingly strict checks.

Q4. What does VALIDATE_PASSWORD_STRENGTH() do?

Answer: It returns a strength score from 0 to 100 for a candidate password according to the component's checks.

Q5. How can a MySQL password be changed?

Answer: An authorized administrator can use ALTER USER with an appropriate password clause.

Q6. What is password expiration?

Answer: It is a feature that marks a password as expired or requires it to be changed after a configured interval.

Q7. What is password history?

Answer: It records recent password usage for reuse restrictions so an account cannot immediately reuse certain previous passwords.

Q8. What is failed-login tracking?

Answer: It tracks consecutive incorrect password attempts and can temporarily lock an account when the configured threshold is reached.

Q9. How do you lock a MySQL account?

ALTER USER 'username'@'host' ACCOUNT LOCK;

Q10. How do you unlock a MySQL account?

ALTER USER 'username'@'host' ACCOUNT UNLOCK;

Q11. What is the principle of least privilege?

Answer: It means assigning only the privileges an account needs to perform its intended tasks.

Q12. How do you check a user's privileges?

SHOW GRANTS FOR 'username'@'host';

Q13. Why should application credentials not be hard-coded?

Answer: Hard-coded credentials can be exposed through source code, repositories, logs, or deployment artifacts. Secure secret management reduces this risk.

Q14. Does locking an account delete its privileges?

Answer: No. Account locking prevents normal authentication while preserving the account and its grants.

Q15. Is a strong password sufficient for complete database security?

Answer: No. Database security also requires appropriate privileges, secure connections, account monitoring, software updates, and safe credential management.

27. Quick Revision Checklist

• Password policies define rules for creating and managing passwords.
• Account security includes authentication, authorization, monitoring, and access control.
• The validate_password component checks password strength when enabled.
• LOW, MEDIUM, and STRONG provide different validation levels.
• VALIDATE_PASSWORD_STRENGTH() evaluates a candidate password.
• ALTER USER can change account passwords and password-management settings.
• Password expiration can require a password change.
• Password history and reuse intervals help prevent reuse of recent passwords.
• FAILED_LOGIN_ATTEMPTS and PASSWORD_LOCK_TIME can control temporary locking.
• ACCOUNT LOCK and ACCOUNT UNLOCK control account locking.
• SHOW CREATE USER and SHOW GRANTS help inspect account configuration and permissions.
• Least privilege limits the damage that can result from compromised credentials.
• Never expose production passwords in scripts, source code, or documentation.
• Secure connections, monitoring, and timely software updates remain essential.

28. Conclusion

Password policies and account security are essential for protecting MySQL databases from unauthorized access. MySQL provides features for password strength validation, expiration, reuse restrictions, failed-login tracking, account locking, authentication, and privilege management.

Database administrators should configure these features according to the server version and organizational requirements. They should also apply the principle of least privilege, secure application credentials, restrict network access, and regularly review account settings. These practices work together to reduce the risk of compromised accounts and unauthorized database operations.

29. Official Documentation and Course Resources

• MySQL Official Documentation – Password Validation Component
• MySQL Official Documentation – Password Management
• MySQL Official Documentation – Account Locking
• MySQL Official Documentation – CREATE USER Statement
• MySQL Official Documentation – ALTER USER Statement
• Explore MySQL Training at JustAcademy
• Register for a MySQL Training Course Demo