Implementing Clean User Management and Password Policies in MySQL

1. Why granular user management matters in Magento database environments

A single database account with full privileges, shared by the application, cron jobs, reporting tools, and the occasional manual intervention, is convenient to set up but a significant operational risk: if that one account's password is compromised, say through an inadequately secured env.php in an old backup, the attacker instantly gets full read, write, and schema access all at once.

Granular user management limits the damage of a compromise to the scope an account actually needs, and beyond that makes every action attributable to a specific purpose, which considerably eases both troubleshooting and later audit reviews. Combined with audit logging, covered in a separate article in this series, this builds a foundation for accountability that a single catch-all account fundamentally cannot provide.

2. Installing and configuring the validate_password component

Since MySQL 8.0, password strength is no longer enforced through the older plugin system but through a component called validate_password, which, as a server-side component framework, integrates more deeply into the server than classic plugins do. After installation, three predefined policy levels are available: LOW only checks minimum length, MEDIUM additionally requires a mix of upper and lower case, digits, and special characters, and STRONG additionally checks against a configurable dictionary file of commonly used, insecure passwords.

For production Magento environments, the STRONG level combined with a company-specific dictionary file is recommended, and individual parameters such as minimum length and the required number of special characters can be tightened further through dedicated system variables, independent of the chosen policy level.

-- Install the component (no restart required)
INSTALL COMPONENT 'file://component_validate_password';

-- Enable the strict policy level and raise the minimum length
SET GLOBAL validate_password.policy = 'STRONG';
SET GLOBAL validate_password.length = 14;
SET GLOBAL validate_password.mixed_case_count = 1;
SET GLOBAL validate_password.number_count = 1;
SET GLOBAL validate_password.special_char_count = 1;

-- Prevents the password from containing the account name
SET GLOBAL validate_password.check_user_name = ON;

3. Password expiration and history: enforced rotation without reuse

The global variable default_password_lifetime sets a server-wide default password lifetime, which can be overridden per account with ALTER USER ... PASSWORD EXPIRE INTERVAL n DAY, for instance to enforce a shorter period for highly privileged accounts than for the regular application user. Once a password expires, MySQL refuses every statement except setting a new password on the next login, until it has been renewed.

So that an expired password cannot simply be replaced with the same old one, password_history and password_reuse_interval ensure that a set number of previous passwords, or a set period of time, cannot be reused. For accounts with direct human access, such as DBA accounts, combining both mechanisms makes sense, while purely technical application accounts are usually rotated through other means such as a secrets management system.

-- Password expires after 90 days, stricter for the DBA account
ALTER USER 'dba_maria'@'localhost' PASSWORD EXPIRE INTERVAL 60 DAY;

-- No reuse of the last 5 passwords within 365 days
ALTER USER 'dba_maria'@'localhost' PASSWORD HISTORY 5 PASSWORD REUSE INTERVAL 365 DAY;

4. The principle of least privilege: roles instead of individual grants

Since MySQL 8.0, privileges can be bundled into named roles through CREATE ROLE, which are then assigned to individual users instead of re-granting the full list of individual privileges for every new account. This approach not only reduces administrative overhead, it also makes later privilege changes centrally manageable: an adjustment to a role automatically affects every assigned user without touching each account individually.

For a typical Magento database setup, a split into at least three roles with clearly different privilege scopes works well, covered in detail below: a read-only role for reporting, a role with expanded schema privileges for migrations, and a role limited to data manipulation for ongoing application runtime.

-- Create the roles
CREATE ROLE 'role_reporting', 'role_migration', 'role_app_runtime';

-- Assign privileges to a role (reporting example)
GRANT SELECT ON magento.* TO 'role_reporting';

-- Assign the role to a specific user and set it as the default role
GRANT 'role_reporting' TO 'bi_readonly'@'10.0.4.%';
SET DEFAULT ROLE 'role_reporting' TO 'bi_readonly'@'10.0.4.%';

5. Separate accounts: a read-only reporting user without write access

Business intelligence tools, custom reporting scripts, and analytics dashboards need exclusively read access for their work, often even just a subset of tables, such as orders and product catalogs, but not sensitive core areas like admin credentials. A dedicated reporting account with only SELECT privileges ensures that a broken or compromised reporting script can never accidentally modify production data.

Where available, this account should additionally connect exclusively against a read replica instead of the primary database, so that even resource-intensive, poorly optimized reporting queries cannot affect the performance of actual store transactions.

6. The migration user: expanded DDL privileges, active only for a limited window

Schema migrations, as executed on every Magento setup upgrade through setup:upgrade, need considerably broader privileges than ongoing application runtime, particularly CREATE, ALTER, DROP, and INDEX. These privileges should never be permanently assigned to the regular application user but reserved for a dedicated account used exclusively for deployments, typically only invoked by the CI/CD pipeline.

In particularly security-sensitive environments, this account can even be activated only for a limited window, locked outside an active deployment window through ALTER USER ... ACCOUNT LOCK and unlocked automatically right before a scheduled deployment, which minimizes the time window during which this highly privileged account could potentially be misused.

-- Lock the migration user outside of deployments
ALTER USER 'ci_migration'@'10.0.3.%' ACCOUNT LOCK;

-- Unlock automatically shortly before a scheduled deployment
ALTER USER 'ci_migration'@'10.0.3.%' ACCOUNT UNLOCK;

7. The application runtime user: minimal privileges for day-to-day Magento operation

The account Magento connects with during normal operation via app/etc/env.php needs, for the vast majority of its work, only SELECT, INSERT, UPDATE, and DELETE, but no schema privileges such as CREATE, ALTER, DROP, or GRANT. That deliberate restriction has a concrete security benefit: even a SQL injection vulnerability in a third-party extension cannot drop tables or create new admin accounts through a manipulated schema change if the account is that restricted.

In practice, many organically grown Magento installations grant the application user broad privileges out of convenience, because individual extensions occasionally run DDL operations. Those cases should be identified and, where possible, shifted to the migration user instead of leaving the permanent runtime account with blanket privileges.

8. Auditing existing privileges: SHOW GRANTS and information_schema

Before an existing, organically grown privilege structure can be cleaned up, the current state must first be captured completely. The SHOW GRANTS FOR command provides a readable overview for a single account, while for a systematic, server-wide review, a query against information_schema.USER_PRIVILEGES and information_schema.SCHEMA_PRIVILEGES is considerably more efficient, since that view can be filtered directly and joined with other metadata.

A sensible first step is an inventory of every account holding GRANT OPTION or administrative privileges such as SUPER, since those accounts carry the greatest potential for damage in a compromise scenario and should therefore be checked for actual necessity first.

-- Show privileges for a single account
SHOW GRANTS FOR 'magento_app'@'10.0.2.%';

-- Identify accounts with broad administrative privileges
SELECT GRANTEE, PRIVILEGE_TYPE
FROM information_schema.USER_PRIVILEGES
WHERE PRIVILEGE_TYPE IN ('SUPER', 'GRANT OPTION', 'DROP', 'SHUTDOWN')
ORDER BY GRANTEE;

9. The default authentication plugin caching_sha2_password, and a conclusion

Since MySQL 8.0, caching_sha2_password is the default authentication plugin and replaces the older, cryptographically weaker mysql_native_password, which is now considered legacy and disabled by default in current MySQL versions. Older PHP PDO drivers or legacy applications can sometimes struggle with the new plugin, which is why a targeted compatibility test before a production switch is worthwhile, rather than sticking with the older plugin out of convenience.

Taken together, validate_password, consistent password rotation, and a clear role separation following the principle of least privilege add up to user management that not only satisfies compliance requirements but genuinely limits the damage of a compromise in a real incident, instead of needlessly amplifying it through a single catch-all account.

10. Summary

11. FAQ: User Management and Password Policies at a Glance