MySQL User Management & Security Guide 2026: Privileges, Roles & SSL

Key Takeaways

• Least privilege always: Create one user per application, grant only what it needs, never use root in app connections.
• @localhost ≠ @%: These are different users. Know which you’re creating.
• Roles for multi-user management: Group privileges into roles, assign roles to users — cleaner than per-user grants at scale.
• Require SSL: REQUIRE SSL on the user definition enforces encrypted connections — no plaintext passwords in transit.

---

Introduction

Direct Answer: How do I create and secure MySQL users with least-privilege access in 2026?

Connect as root: sudo mysql. Create an application user: CREATE USER 'appuser'@'localhost' IDENTIFIED BY 'strongpassword';. Grant only necessary privileges: GRANT SELECT, INSERT, UPDATE, DELETE ON appdb.* TO 'appuser'@'localhost';. Apply: FLUSH PRIVILEGES;. Verify: SHOW GRANTS FOR 'appuser'@'localhost';. For read-only reporting users: GRANT SELECT ON appdb.* TO 'reporter'@'localhost';. For admin users who need schema changes: GRANT ALL PRIVILEGES ON appdb.* TO 'dbadmin'@'localhost';. Never use GRANT ALL PRIVILEGES ON *.* TO user for application users — this gives access to all databases including mysql system tables.

---

Part 1: User Creation and Privilege Grants

-- Connect as root
sudo mysql

-- List existing users
SELECT user, host, plugin, authentication_string FROM mysql.user;

-- Create application user (localhost only — most secure)
CREATE USER 'webapp'@'localhost' IDENTIFIED BY 'use_a_strong_password_here';

-- Grant application-level privileges only
GRANT SELECT, INSERT, UPDATE, DELETE ON appdb.* TO 'webapp'@'localhost';

-- Grant schema-level access (for migration tools)
GRANT CREATE, ALTER, DROP, INDEX ON appdb.* TO 'migrations'@'localhost';

-- Read-only reporting user
CREATE USER 'reporter'@'localhost' IDENTIFIED BY 'reporter_strong_pass';
GRANT SELECT ON appdb.* TO 'reporter'@'localhost';

-- Apply grants
FLUSH PRIVILEGES;

-- Verify
SHOW GRANTS FOR 'webapp'@'localhost';

Expected output:

+--------------------------------------------------------------------------------------+
| Grants for webapp@localhost                                                          |
+--------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `webapp`@`localhost`                                           |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `appdb`.* TO `webapp`@`localhost`            |
+--------------------------------------------------------------------------------------+

---

Part 2: Roles (MySQL 8+)

-- Create roles
CREATE ROLE 'app_readwrite';
CREATE ROLE 'app_readonly';
CREATE ROLE 'app_admin';

-- Grant privileges to roles
GRANT SELECT, INSERT, UPDATE, DELETE ON appdb.* TO 'app_readwrite';
GRANT SELECT ON appdb.* TO 'app_readonly';
GRANT ALL PRIVILEGES ON appdb.* TO 'app_admin';

-- Assign roles to users
GRANT 'app_readwrite' TO 'webapp'@'localhost';
GRANT 'app_readonly'  TO 'reporter'@'localhost';

-- Make roles active by default
SET DEFAULT ROLE 'app_readwrite' TO 'webapp'@'localhost';
SET DEFAULT ROLE 'app_readonly'  TO 'reporter'@'localhost';

FLUSH PRIVILEGES;

-- Verify role assignment
SHOW GRANTS FOR 'webapp'@'localhost' USING 'app_readwrite';

---

Part 3: SSL-Required Connections

-- Require SSL for all connections from a user
ALTER USER 'webapp'@'localhost' REQUIRE SSL;

-- Or require a specific CA-signed certificate
ALTER USER 'webapp'@'localhost'
    REQUIRE SUBJECT '/CN=webapp/O=MyApp/C=US';

-- Verify SSL requirement
SELECT user, host, ssl_type FROM mysql.user WHERE user = 'webapp';

Expected output:

+--------+-----------+----------+
| user   | host      | ssl_type |
+--------+-----------+----------+
| webapp | localhost | ANY      |
+--------+-----------+----------+

# Test connection without SSL (should fail)
mysql -u webapp -p --ssl-mode=DISABLED 2>&1 | head -2

Expected output:

ERROR 1045 (28000): Access denied for user 'webapp'@'localhost' (using password: YES)

---

Part 4: Authentication Plugins

-- Check current auth plugin
SELECT user, plugin FROM mysql.user WHERE user IN ('root', 'webapp');

-- MySQL 9.x default: caching_sha2_password (most secure)
-- If older clients need compatibility:
ALTER USER 'webapp'@'localhost'
    IDENTIFIED WITH mysql_native_password BY 'strongpassword';

-- For passwordless socket auth (root from Linux shell)
ALTER USER 'root'@'localhost'
    IDENTIFIED WITH auth_socket;
-- After this: sudo mysql (no password) works; mysql -u root -p fails

---

Part 5: Audit and Cleanup

-- List all users and their privileges
SELECT
    u.user,
    u.host,
    u.plugin,
    u.ssl_type,
    GROUP_CONCAT(DISTINCT p.Table_priv ORDER BY p.Table_priv SEPARATOR ',') AS table_privs
FROM mysql.user u
LEFT JOIN mysql.tables_priv p ON u.user = p.user AND u.host = p.host
WHERE u.user != 'mysql.sys'
GROUP BY u.user, u.host, u.plugin, u.ssl_type;

-- Find users with no password (security risk)
SELECT user, host FROM mysql.user
WHERE authentication_string = '' AND plugin = 'mysql_native_password';

-- Remove a user
DROP USER 'olduser'@'localhost';

-- Remove a privilege
REVOKE INSERT, UPDATE, DELETE ON appdb.* FROM 'reporter'@'localhost';

---

Conclusion

MySQL user management follows one principle: least privilege. One user per application, granting only the operations that application actually performs. Roles make this scalable when multiple users need the same access pattern. SSL requirement ensures credentials never transit in plaintext.

See How to Install MySQL 9 on Ubuntu 24.04 for the base installation, and MySQL Backup Guide 2026 for protecting the data these users access.

---

People Also Ask

What is the difference between @localhost and @'%' in MySQL?

In MySQL, the host part of a user definition controls where connections are accepted from. 'user'@'localhost' only allows connections from the same physical machine using the Unix socket or local TCP. 'user'@'%' allows connections from any IP address (controlled by MySQL’s bind-address setting and your firewall). 'user'@'10.0.0.%' restricts to a subnet. For application users on the same server: use @'localhost'. For application servers on a different host: use @'10.0.0.5' (specific IP) or @'10.0.0.%' (subnet).

---

Part 6: Host and Network Access Control for MySQL Users

Beyond MySQL privileges, a sovereign database architecture depends on host and network controls that restrict where credentials can be used. On Ubuntu 24.04, bind MySQL to a specific interface, use a hardened firewall, and limit access to trusted hosts only. In /etc/mysql/mysql.conf.d/mysqld.cnf, set:

[mysqld]
bind-address = 127.0.0.1
skip-networking = 0

If your application runs on a different machine, bind to the private network and only permit the application host via UFW or firewalld. For example:

sudo ufw allow from 10.0.0.5 to any port 3306 proto tcp
sudo ufw deny 3306
sudo ufw status verbose

This prevents webapp@'%' or other permissive MySQL users from accepting connections from unexpected sources. When a database user needs remote access, use specific host values such as user@'10.0.0.5' or user@'10.0.0.%' instead of the wildcard @'%'.

Host-based filtering adds a second layer of defense in depth. Even if an attacker obtains the MySQL credentials, they cannot connect unless they are on an approved host or subnet.

---

Part 7: Password Policies, Expiration, and Account Locking

Strong user management includes password lifecycle controls. MySQL 9 supports native password policy enforcement through validate_password and account locking policies.

Enable the password validation plugin:

INSTALL PLUGIN validate_password SONAME 'validate_password.so';
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;

This ensures new passwords meet modern complexity requirements. For application accounts, rotate passwords on a regular schedule, and store secrets in a vault rather than in code.

Lock credentials when they are no longer needed:

ALTER USER 'tempuser'@'localhost' ACCOUNT LOCK;
ALTER USER 'tempuser'@'localhost' PASSWORD EXPIRE INTERVAL 30 DAY;

Use ACCOUNT LOCK for emergency deactivation, rather than dropping the account immediately, so you can restore access for audit.

SELECT user, host, account_locked, password_expired FROM mysql.user WHERE user LIKE 'app%';

A graceful account lifecyle includes creation, use, rotation, lock, and removal. Record each step in your change log and keep audit metadata in a secure operations notebook.

---

Part 8: Managing Credentials with Vault and Environment Variables

Never embed MySQL passwords in source control. For sovereign deployments, use a secret manager or an encrypted local configuration store.

Example vault integration:

vault kv put secret/mysql/webapp username=webapp password='strongpassword'

In your application startup script:

MYSQL_PASSWORD=$(vault kv get -field=password secret/mysql/webapp)
export DATABASE_URL="mysql://webapp:${MYSQL_PASSWORD}@127.0.0.1:3306/appdb"

For simpler local setups, use a permissioned config file:

[database]
user = webapp
password = strongpassword
host = 127.0.0.1
database = appdb

Store that file on an encrypted volume and restrict its access:

chmod 600 /etc/myapp/mysql.ini
chown appuser:appuser /etc/myapp/mysql.ini

Secrets management prevents accidental exposure through Git, logs, or shell history. Combine it with MySQL account locking and rotation policies for strong operational security.

---

Part 9: Role-Based Administration and Delegation

When multiple teams need MySQL access, avoid granting the same privileges directly to each user. Use roles to centralize policy and simplify change control.

The patterns below work well for development, analytics, migration, and emergency repair teams.

CREATE ROLE 'dev_ops';
CREATE ROLE 'reporting';
CREATE ROLE 'readwrite_app';

GRANT SELECT, INSERT, UPDATE, DELETE ON appdb.* TO 'readwrite_app';
GRANT SELECT ON appdb.* TO 'reporting';
GRANT RELOAD, PROCESS ON *.* TO 'dev_ops';

GRANT 'readwrite_app' TO 'webapp'@'localhost';
GRANT 'reporting' TO 'analyst'@'localhost';
GRANT 'dev_ops' TO 'dba'@'localhost';

If a role changes, update the role grants once rather than editing many users. This makes access reviews and compliance audits much easier.

Roles also support dynamic privilege revocation. To remove a team’s access, revoke the role and FLUSH PRIVILEGES;.

---

Part 10: MySQL Authentication Plugins and External Authentication

MySQL 9 supports multiple authentication plugins. The default caching_sha2_password is the most secure choice for modern clients. However, some legacy drivers require mysql_native_password or external identity sources.

For LDAP integration, use authentication_ldap_simple or authentication_ldap_sasl:

INSTALL PLUGIN authentication_ldap_simple SONAME 'authentication_ldap_simple.so';
CREATE USER 'ldapuser'@'%' IDENTIFIED VIA authentication_ldap_simple USING 'uid=ldapuser,ou=people,dc=example,dc=com';

For socket-based local authentication:

ALTER USER 'root'@'localhost' IDENTIFIED WITH auth_socket;

This is useful for administrative scripts and local maintenance because it removes password handling entirely on the host.

For applications that support it, prefer external authentication over stored passwords. That moves credential management out of MySQL and into your identity provider.

---

Part 11: Privilege Review and Audit Queries

A sovereign MySQL deployment requires regular audits. Use these queries to find high-risk accounts and privilege anomalies.

-- Users with global privileges
SELECT user, host, 
  SUPER_PRIV, PROCESS_PRIV, RELOAD_PRIV, SHUTDOWN_PRIV,
  FILE_PRIV, GRANT_PRIV, REPLICATION_SLAVE_PRIV, REPLICATION_CLIENT_PRIV,
  CREATE_USER_PRIV
FROM mysql.user
WHERE SUPER_PRIV='Y' OR PROCESS_PRIV='Y' OR RELOAD_PRIV='Y' OR SHUTDOWN_PRIV='Y';

-- Users with ANY privilege on *.*
SELECT user, host FROM mysql.db WHERE db='*' AND (SELECT COUNT(*) FROM mysql.user WHERE user = mysql.db.user AND host = mysql.db.host) > 0;

Review every user with SUPER, PROCESS, RELOAD, or SHUTDOWN privileges quarterly. Document why each high-privilege account exists and whether it can be replaced by a less powerful role.

---

Part 12: Temporary Elevated Access and Emergency Procedures

Sometimes you need temporary elevated access for a migration or incident response. Grant the least necessary privileges for the shortest time and remove them immediately after the task completes.

CREATE USER 'temp_maint'@'localhost' IDENTIFIED BY 'temporary_password';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, DROP ON appdb.* TO 'temp_maint'@'localhost';
-- perform maintenance
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'temp_maint'@'localhost';
DROP USER 'temp_maint'@'localhost';

Track all temporary accounts in your incident log. A clean removaal process ensures no artifacts remain after the emergency.

---

Part 13: Application Connection Security Best Practices

Configure your application to connect securely and avoid exposing credentials or connection details in logs.

• Use an encrypted service account store, not environment variables when possible.
• Connect with mysql://webapp:[email protected]:3306/appdb or via a Unix socket when the application is on the same host.
• Do not log full connection strings.
• Use --ssl-mode=REQUIRED or --ssl-mode=VERIFY_CA for remote connections.

Example my.cnf for a service account:

[client]
user=webapp
password=strongpassword
host=127.0.0.1
database=appdb
ssl-mode=VERIFY_CA
ssl-ca=/etc/mysql/certs/ca.pem
ssl-cert=/etc/mysql/certs/client-cert.pem
ssl-key=/etc/mysql/certs/client-key.pem

This ensures that connections use encrypted transport, and it avoids plaintext password exposure in start-up scripts.

---

Part 14: Common Mistakes and Troubleshooting

Mistake 1: Granting ALL PRIVILEGES too broadly

If you grant ALL PRIVILEGES ON *.* TO user, you are effectively giving a user full control over every database on the server, including mysql, performance_schema, and any future schemas. This is a common source of production outages and privilege abuse.

Mistake 2: Using @'%' for internal apps

A wildcard host makes user access much harder to reason about. Use specific host values or localhost whenever possible.

Mistake 3: Keeping default accounts

Remove or lock default accounts such as test@'localhost', test@'%', and any unused root clones.

DROP USER 'test'@'localhost';
DROP USER 'test'@'%';

Mistake 4: Not rotating passwords

A compromised credential can remain valid forever if it is never rotated. Automate password expiry or rotate credentials with a vault integration.

---

Part 15: Review Checklist for MySQL User Security

• All application users created with CREATE USER, not GRANT ... IDENTIFIED BY alone.
• Privileges limited to specific databases and tables.
• Roles used for repeatable access models.
• SSL required for remote users.
• Password policy enabled with validate_password.
• Host-based access restricted by UFW/firewall rules.
• No ALL PRIVILEGES ON *.* for app users.
• Temporary elevated access revoked promptly.
• Backups of grants and users stored securely.
• Regular audit of mysql.user and SHOW GRANTS output.

---

16. How to Back Up MySQL User Accounts

To capture the user grant state, back up the mysql.user, mysql.db, mysql.tables_priv, and mysql.proxies_priv tables as part of your regular backup plan.

mysqldump -u root -p --databases mysql --tables user db tables_priv proxies_priv > /var/backups/mysql-grants.sql

Store this file securely and encrypt it if it contains authentication strings. When restoring MySQL, reimport these grant tables after the data restore to preserve user definitions and privileges.

Part 17: Connection Pooling and Proxy Security

Most modern applications use connection pools, which means the database user is effectively shared across many sessions. Choose a connection pooler that supports secure credential handling and keep the pool user privileges minimal.

For Python and Java apps, tools like mysql.connector, HikariCP, and ProxySQL are common. When using ProxySQL, place it between the application and MySQL so you can centralize filtering and caching without exposing the database directly.

Example ProxySQL configuration for an application user:

INSERT INTO mysql_users (username, password, active, default_hostgroup) VALUES ('webapp', 'strongpassword', 1, 10);
LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;

ProxySQL also supports credentials rotation without changing application configuration immediately. Rotate the backend MySQL password, update the proxy user, and then restart the application pool to pick up the new secret.

Connection pooling amplifies the risk of a compromised user because one credential can open many concurrent database sessions. Treat pooled user accounts like high-risk assets and protect them with strong host restrictions, SSL, and a vault-backed secret flow.

---

Part 18: Secret Rotation and Vault Integration

Credentials should be rotated automatically when possible. A rotating MySQL user is much safer than a static password that lives for years.

An automated vault workflow looks like this:

1. Generate a new password in Vault.
2. Update the MySQL user with ALTER USER ... IDENTIFIED BY.
3. Update the application secret store.
4. Restart or recycle application processes.
5. Revoke the old secret after a transition window.

Example script using HashiCorp Vault:

NEW_PASS=$(vault kv get -field=password secret/mysql/webapp)
mysql -u root -p -e "ALTER USER 'webapp'@'localhost' IDENTIFIED BY '${NEW_PASS}';"
vault kv put secret/mysql/webapp password="${NEW_PASS}"

Automation reduces human error. Combine it with temporary access controls so the old secret expires automatically in the application if the rollout fails.

---

Part 19: Privilege Escalation Hardening

MySQL user privileges should be reviewed not just for what users can read, but for what they can change. Privilege escalation comes from granting CREATE, ALTER, DROP, or GRANT OPTION excessively.

Protect against escalation by:

• denying GRANT OPTION to application users
• granting CREATE and ALTER only to migration or admin accounts
• keeping DROP privileges off production application users entirely
• using SHOW GRANTS regularly to identify unexpected privileges

Example query to find users with schema-change privileges:

SELECT user, host 
FROM mysql.user
WHERE Create_priv='Y' OR Alter_priv='Y' OR Drop_priv='Y' OR Grant_priv='Y';

A role-based model makes this easier because you can lock down schema-change privileges to just a handful of DBA or migration roles.

---

Part 20: Audit Logging and Compliance for MySQL Users

For a sovereign deployment, visibility is essential. Enable MySQL audit logging and combine it with host-level logs.

Install the MySQL Enterprise Audit plugin or use open-source alternatives such as audit_log:

INSTALL PLUGIN audit_log SONAME 'audit_log.so';
SET GLOBAL audit_log_policy = 'ALL';
SET GLOBAL audit_log_format = 'JSON';

Audit logs help answer questions like:

• who created or dropped a user?
• which user attempted a denied statement?
• when did a privileged account login?

Store audit logs separately from the database and rotate them with the same retention policy. A separate audit archive is a governance best practice for sovereignty.

---

Part 21: MySQL User Lifecycle Automation

Define a lifecycle for every MySQL account:

• Request
• Approval
• Creation
• Use
• Rotation
• Deactivation
• Removal

Use a ticketing system or an internal request form to track approvals. Do not create long-lived accounts without documented justification. For temporary maintenance, use time-boxed accounts and automatically lock them after the window expires.

A lifecycle checklist helps ensure no service account remains active longer than needed and that every account corresponds to a real business purpose.

---

Part 22: Backup and Restore of User Grants

Preserve the user grant state as part of your backup strategy. This is especially important if you recover to a different host or repoint an application to a restored database.

Backup user grant metadata with:

mysqldump -u root -p --databases mysql --tables user db tables_priv proxies_priv > /var/backups/mysql-grants-$(date +%Y%m%d).sql

When restoring, import grants after the data restore. If you use external authentication or a vault, ensure the restored host has access to the same authentication backends.

---

Part 23: MySQL User Security in Hybrid and Multi-Cloud Environments

In a hybrid or multi-cloud deployment, user identity should still be locally controlled. Use the same privilege model across hosts and ensure host-specific user definitions are clearly documented.

For cross-cloud replication, avoid @'%' and instead use explicit host names or IP ranges. Keep replication users separate from application users, and use SSL with mutual authentication for replication traffic.

A sovereign model in hybrid environments means you own the credential lifecycle, not the cloud provider. Use locally generated certificates, your own CA, and a private user policy that applies consistently across all database nodes.

Part 24: ProxySQL, Firewalls, and Multi-Layer Defense

When MySQL is exposed to application servers across a network lane, use a proxy tier to mediate access. ProxySQL can enforce query rules, manage failover, and centralize user credentials.

A proxy adds a third layer of protection:

• the application connects to the proxy
• the proxy connects to MySQL with a dedicated backend user
• the proxy enforces host and SQL policies

Example ProxySQL query rule to block risky operations:

INSERT INTO mysql_query_rules (rule_id, active, match_digest, destination_hostgroup, apply)
VALUES (1, 1, 'DROP TABLE', 20, 1);
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

A proxy also lets you centralize SSL configuration and rotate backend credentials without changing the application immediately. This is especially useful in sovereign systems where credential churn is an operational requirement.

---

Part 25: Lifecycle Documentation for Each MySQL User

For each MySQL account, maintain a record with:

• the owner or team
• the purpose of the account
• allowed hosts
• assigned roles and privileges
• creation date
• password rotation schedule
• deactivation date

A central spreadsheet or ticket system is enough if it is kept current. The key is that you can answer the question: “Why does this user exist?” within minutes.

---

Part 26: Synchronizing Users Across Replicas and Failover Nodes

In replicated topologies, users must exist on every node that can accept writes or switch roles. Use a consistent provisioning process such as:

1. create the user on the primary
2. export the grant statements
3. apply them to replicas during maintenance windows

For MySQL Group Replication, user changes on the primary are replicated automatically, but it is still best practice to validate users on each node after a failover.

Use scripted verification:

for host in db1 db2 db3; do
  mysql -h "$host" -u root -p -e "SHOW GRANTS FOR 'webapp'@'localhost'";
done

This ensures the user’s privileges are identical across the cluster.

---

Part 27: Account Review Automation

Automate periodic user reviews with a script that flags anomalies:

• users without passwords
• users with GRANT OPTION
• @'%' users
• users who have not connected in 90 days

A basic review query:

SELECT user, host, password_last_changed, account_locked, password_expired
FROM mysql.user
WHERE user NOT IN ('mysql.sys','root');

For any flagged account, investigate whether it is still needed. Automating this review helps catch stale service accounts before they become attack paths.

---

Part 28: MySQL User Governance for Auditors

Auditors want evidence. Maintain a package of proofs for each audit cycle:

• current SHOW GRANTS output for all privileged users
• user creation and modification logs
• password policy configuration
• SSL requirement checks
• firewall rules for MySQL access

Keep these proofs archived in a local secure repository. A sovereign environment is audit-ready when you can deliver this evidence without relying on a remote service.

---

Part 29: Hardening MySQL User Access in Containers

If MySQL runs in a container, the same user rules apply, but you also need container-level controls.

For Podman/Docker:

• mount credentials read-only
• limit network exposure with container network policies
• use container-specific host entries for @'host' definitions
• keep the MySQL process user confined with SELinux or AppArmor

A containerized MySQL user is still a database user; the container boundary is an additional layer, not a replacement.

Part 30: Operational Playbooks for MySQL User Incidents

Create a short incident playbook that operators can follow when a MySQL user is compromised or misused. The playbook should include:

• identify the affected account
• lock the account immediately with ALTER USER ... ACCOUNT LOCK
• rotate the password or revoke the account
• review recent logs for suspicious activity
• verify application connectivity and restore service

Having a predefined sequence reduces response time and prevents operators from making ad hoc privilege changes that can create further risk.

Further Reading

• How to Install MySQL 9 on Ubuntu 24.04 — MySQL installation prerequisite
• MySQL Backup Guide 2026 — protect the data these users access
• PostgreSQL vs MySQL 2026 — when to choose PostgreSQL instead

Tested on: Ubuntu 24.04 LTS. MySQL 9.0.1. Last verified: April 30, 2026.