MySQL User Permissions Best Practices: Least Privilege and Password Policy

MySQL permissions are often granted too broadly, creating unnecessary security risk. Here is how to implement least-privilege access for production MySQL deployments.

Never Use Root for Applications

-- Create application user with only needed privileges
CREATE USER 'app_service'@'10.0.%' IDENTIFIED BY 'str0ng_p@ss';
GRANT SELECT, INSERT, UPDATE, DELETE ON myapp.* TO 'app_service'@'10.0.%';

-- Read-only analytics user
CREATE USER 'analytics'@'10.0.%' IDENTIFIED BY 'read_p@ss';
GRANT SELECT ON myapp.* TO 'analytics'@'10.0.%';

-- Schema migration user (restricted to deploy window)
CREATE USER 'migrator'@'localhost' IDENTIFIED BY 'mig_p@ss';
GRANT ALTER, CREATE, DROP, INDEX ON myapp.* TO 'migrator'@'localhost';

Restrict by Host

-- 'user'@'%' allows connections from anywhere — avoid in production
-- 'user'@'10.0.0.0/255.255.0.0' limits to subnet
-- 'user'@'app.internal' limits to specific host

-- Check current users and hosts
SELECT user, host, plugin, password_expired
FROM mysql.user
ORDER BY user, host;

Password Policy

-- Enable password validation plugin
INSTALL COMPONENT 'file://component_validate_password';

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

Password Expiry and Account Locking

-- Expire password every 90 days for interactive users
CREATE USER 'dba_user'@'localhost'
  IDENTIFIED BY 'p@ssword'
  PASSWORD EXPIRE INTERVAL 90 DAY;

-- Lock account after 5 failed attempts
ALTER USER 'dba_user'@'localhost'
  FAILED_LOGIN_ATTEMPTS 5 PASSWORD_LOCK_TIME 1;

-- Unlock a locked account
ALTER USER 'dba_user'@'localhost' ACCOUNT UNLOCK;

Audit Overprivileged Users

-- Find users with dangerous global privileges
SELECT user, host
FROM information_schema.user_privileges
WHERE privilege_type IN ('SUPER','FILE','PROCESS','REPLICATION SLAVE')
ORDER BY user;

-- Find users with wildcard host access
SELECT user, host FROM mysql.user WHERE host = '%';

Build Permissions from Workload Actions

Start with an inventory of identities and operations, not a copy of another account's grants. Separate runtime reads and writes, schema migration, replication, backup, monitoring, and human administration into distinct accounts or roles. For each, list required schemas, tables, routines, and administrative actions, then test denied actions as deliberately as allowed ones. A host pattern is one control, not a firewall: retain network segmentation, private endpoints, and server-side TLS requirements. MySQL 8.4 documents % and _ host wildcards as deprecated, so prefer an exact host or IPv4 CIDR account value and retain network policy as a separate control.

Use Roles and Verify Effective Grants

MySQL roles are named collections of privileges. Grant privileges to roles, grant roles to accounts, and set only required roles as defaults. A granted role is not necessarily active, so an audit that sees only role names is incomplete. Use SHOW GRANTS FOR 'account'@'host' USING 'role' to expand selected role privileges, and test CURRENT_ROLE() in a new session. Keep powerful dynamic privileges such as SYSTEM_USER, CONNECTION_ADMIN, BINLOG_ADMIN, and REPLICATION_APPLIER out of application roles. Modern dynamic privileges are more precise than a blanket legacy administrative grant, but each still needs a documented task.

CREATE ROLE 'app_reader', 'app_writer';
GRANT SELECT ON myapp.* TO 'app_reader';
GRANT INSERT, UPDATE, DELETE ON myapp.* TO 'app_writer';
GRANT 'app_reader', 'app_writer' TO 'app_service'@'10.0.0.0/16';
SET DEFAULT ROLE 'app_reader', 'app_writer' TO 'app_service'@'10.0.0.0/16';
SHOW GRANTS FOR 'app_service'@'10.0.0.0/16' USING 'app_reader', 'app_writer';

Protect Authentication in Transit and at Rest

Require encrypted transport for remote accounts with the appropriate REQUIRE SSL or certificate clauses, then configure clients to verify the server identity rather than merely negotiate encryption. Keep passwords out of migration files, command histories, ticket text, and SQL logs. MySQL can generate random passwords, but the returned cleartext still needs immediate transfer into an approved secret store and careful output handling. Password-expiration policy is useful for interactive users; expiring an unattended application account without an automated rotation path creates an outage rather than security.

Rotate Without a Connection Cliff

MySQL's dual-password capability supports a reversible rotation. Assign the new primary password with RETAIN CURRENT PASSWORD, wait for the change to reach every server, update all clients, recycle pools, and prove new connections use the primary credential. Only then use DISCARD OLD PASSWORD. During the overlap, monitor successful connection creation by application version and look for clients still using the secondary credential through controlled tests or secret-deployment inventory. Keep the overlap short because two valid passwords increase exposure.

Audit, Revoke, and Roll Back Safely

Export account definitions and grants before a permissions change. Review direct grants, inherited roles, default roles, routine definers, proxy grants, and wildcard schema names. Test from the same network path and connector as the workload; an administrative session does not reproduce account and host matching. Revoke one capability at a time, canary the workload, and monitor access-denied errors, job failures, replication, backups, and deployment tasks. Rollback is restoring the minimal prior grant or role, not granting ALL under pressure. Lock dormant accounts before dropping them, observe for a full business cycle, and retain an independently controlled break-glass administrator with audited use.

Version and Compliance Caveats

Privilege names and deprecations change across MySQL release families, so compare the exact server's privilege list and upgrade notes. A query against mysql.user is not a complete effective-permissions report, and access to system grant tables is itself sensitive. Compliance evidence should record who approved a role, its expanded grants, the test account and server version, denied-action results, rotation date, and the next review—not only a screenshot of account names.

Official MySQL References

• MySQL roles and effective grants
• Password management and dual-password rotation
• Static and dynamic privileges
• Encrypted MySQL connections
• MySQL account host matching and wildcard deprecation