How to Create User Accounts in MySQL Database

Every security incident I’ve ever reviewed involving a database had one thing in common: user account management that was either too loose, too old, or never audited. Creating MySQL user accounts properly is one of those tasks that looks trivial on the surface — one line of SQL — but has enormous implications for security, auditability, and operational sanity. In this guide, I’ll walk through everything I do when provisioning MySQL accounts, from the basic syntax to the account-lifecycle practices I follow as a DBA.

Why User Account Design Matters So Much

MySQL’s privilege system is built around the combination of username + host, not just username alone. 'app_user'@'10.0.0.5' and 'app_user'@'%' are treated as two completely separate accounts by MySQL, even though they share a name. Understanding this is the foundation of everything else in this article.

graph TD
    A[Client connects] --> B{Match user@host?}
    B -->|Exact host match found| C[Use that account's privileges]
    B -->|No exact match| D[Check wildcard host entries]
    D --> E[Apply most specific matching account]
    C --> F[Authenticate password/plugin]
    E --> F
    F --> G[Grant or deny connection]

MySQL evaluates the most specific host match first — an entry for 'app_user'@'192.168.1.10' takes priority over 'app_user'@'%' if both exist.

Basic Syntax for Creating a User

CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'StrongPassword123!';

Breaking this down:

• 'app_user' — the username
• 'localhost' — the host the user is allowed to connect from
• IDENTIFIED BY — sets the authentication method (password, by default using caching_sha2_password in MySQL 8.0+)

To allow connections from any host (common for application servers behind a private network, though I’m cautious with this in public-facing setups):

CREATE USER 'app_user'@'%' IDENTIFIED BY 'StrongPassword123!';

To restrict to a specific subnet, which I do whenever possible instead of using %:

CREATE USER 'app_user'@'10.0.0.%' IDENTIFIED BY 'StrongPassword123!';

Choosing an Authentication Plugin

MySQL 8.0 defaults to caching_sha2_password, which I always keep unless I have a specific legacy client compatibility need:

CREATE USER 'app_user'@'10.0.0.%'
  IDENTIFIED WITH caching_sha2_password BY 'StrongPassword123!';

For older client libraries that don’t yet support caching_sha2_password (some legacy PHP or Python drivers), I fall back to:

CREATE USER 'legacy_user'@'10.0.0.%'
  IDENTIFIED WITH mysql_native_password BY 'StrongPassword123!';

I try to phase this out wherever possible since mysql_native_password is deprecated.

Creating Users for Different Roles

I never create a single generic account for everything. A typical setup I use for a web application looks like this:

-- Application read/write account
CREATE USER 'shop_app'@'10.0.0.%' IDENTIFIED BY 'AppPass123!';

-- Read-only account for reporting/BI tools
CREATE USER 'shop_readonly'@'10.0.1.%' IDENTIFIED BY 'ReadOnlyPass123!';

-- Backup account
CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'BackupPass123!';

-- Replication account
CREATE USER 'repl_user'@'10.0.2.%' IDENTIFIED BY 'ReplPass123!';

-- Monitoring account
CREATE USER 'monitor_user'@'10.0.3.%' IDENTIFIED BY 'MonitorPass123!';

Each of these gets a distinct, narrow set of privileges later with GRANT — I always separate account creation from privilege assignment conceptually, even when I run them back to back.

Setting Password Policies at Creation Time

I enforce password expiration and complexity requirements directly when I create sensitive accounts:

CREATE USER 'admin_dba'@'10.0.0.5'
  IDENTIFIED BY 'ComplexPass!456'
  PASSWORD EXPIRE INTERVAL 90 DAY
  FAILED_LOGIN_ATTEMPTS 5
  PASSWORD_LOCK_TIME 1;

• PASSWORD EXPIRE INTERVAL 90 DAY forces a password change every 90 days for this account.
• FAILED_LOGIN_ATTEMPTS 5 combined with PASSWORD_LOCK_TIME 1 locks the account for 1 day after 5 consecutive failed logins — a basic brute-force mitigation built directly into MySQL 8.0.

I also check the server-wide validation plugin settings, which apply as a baseline for every account:

INSTALL COMPONENT 'file://component_validate_password';
SHOW VARIABLES LIKE 'validate_password%';
SET GLOBAL validate_password.policy = 'MEDIUM';
SET GLOBAL validate_password.length = 12;

Verifying Users Were Created Correctly

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

Sample output:

+------------+----------+-----------------------+----------------+------------------+
| user       | host     | plugin                | account_locked | password_expired |
+------------+----------+-----------------------+----------------+------------------+
| shop_app   | 10.0.0.% | caching_sha2_password  | N               | N                |
+------------+----------+-----------------------+----------------+------------------+

Renaming, Locking, and Removing Accounts

-- Rename a user
RENAME USER 'shop_app'@'10.0.0.%' TO 'shop_service'@'10.0.0.%';

-- Lock an account temporarily (e.g., an employee on leave)
ALTER USER 'admin_dba'@'10.0.0.5' ACCOUNT LOCK;

-- Unlock it later
ALTER USER 'admin_dba'@'10.0.0.5' ACCOUNT UNLOCK;

-- Remove an account entirely
DROP USER 'legacy_user'@'10.0.0.%';

I always run DROP USER as part of offboarding — an old account with stale privileges left in the system is a classic audit finding, and I’ve seen it flagged in every security review I’ve been part of.

Role-Based Account Management (MySQL 8.0+)

Rather than granting the same set of privileges account by account, I define roles once and assign them to users — much easier to maintain at scale.

CREATE ROLE 'app_read_write', 'app_read_only';

GRANT SELECT, INSERT, UPDATE, DELETE ON shop_db.* TO 'app_read_write';
GRANT SELECT ON shop_db.* TO 'app_read_only';

CREATE USER 'shop_service'@'10.0.0.%' IDENTIFIED BY 'AppPass123!';
GRANT 'app_read_write' TO 'shop_service'@'10.0.0.%';
SET DEFAULT ROLE 'app_read_write' TO 'shop_service'@'10.0.0.%';

flowchart LR
    R1["Role: app_read_write"] --> U1["shop_service (10.0.0.x)"]
    R2["Role: app_read_only"] --> U2["reporting_tool (10.0.1.x)"]
    R2 --> U3["analyst_dashboard (10.0.1.x)"]

When five different application accounts need the same permission set, updating a single role is far less error-prone than editing five separate GRANT statements.

Security Best Practices I Follow for Account Creation

• Never share accounts across services — each service or person gets its own account so activity is traceable.
• Avoid 'user'@'%' for anything reachable from the public internet. Scope hosts as tightly as the network topology allows.
• Use strong, unique passwords — I generate these with a password manager or secrets tool, never anything memorable/predictable.
• Set expiration policies on interactive/human accounts (DBAs, developers), though I’m more careful with service accounts since an unexpected expiry can cause an outage — I use longer intervals or exempt them with monitoring in place instead.
• Audit accounts regularly:

SELECT user, host FROM mysql.user WHERE user NOT IN ('mysql.sys','mysql.session','mysql.infoschema','root');

• Store credentials in a secrets manager (Vault, AWS Secrets Manager, etc.), never in plaintext config files committed to version control.

Real-World Scenario: Onboarding a New Microservice

When a new microservice needed database access, my checklist looked like this:

1. Create a dedicated account scoped to the service’s subnet: CREATE USER 'orders_svc'@'10.0.5.%' IDENTIFIED BY '<generated-password>';
2. Assign the least-privilege role matching its actual needs (usually SELECT, INSERT, UPDATE on specific tables, rarely DELETE).
3. Store the credential in our secrets manager, injected into the service’s environment at deploy time.
4. Add the account to our quarterly access review list.

This process takes maybe five extra minutes compared to just reusing an existing account, and it’s saved us during at least one incident where isolating exactly which service caused unexpected write activity was trivial because each service had its own identifiable account.

Troubleshooting Common Issues

Frequently Asked Questions

What’s the difference between 'user'@'localhost' and 'user'@'127.0.0.1'? localhost typically uses a Unix socket connection on Linux, while 127.0.0.1 forces a TCP/IP connection — MySQL treats them as different hosts entirely, and they can even have different privileges.

Should I use IDENTIFIED BY or IDENTIFIED WITH ... BY? IDENTIFIED BY alone uses the server’s default authentication plugin. I prefer being explicit with IDENTIFIED WITH caching_sha2_password BY so there’s no ambiguity about what a new account uses.

How do I create a user without immediately granting any privileges? CREATE USER on its own creates an account with the USAGE privilege (essentially no access) until you run a GRANT statement — it’s a good pattern to separate account creation from access grants.

Can I create multiple users in a single statement? Yes: CREATE USER 'u1'@'%' IDENTIFIED BY 'pass1', 'u2'@'%' IDENTIFIED BY 'pass2';

Interview Questions on This Topic

1. Why does MySQL treat 'user'@'host1' and 'user'@'host2' as different accounts?
2. What’s the difference between caching_sha2_password and mysql_native_password?
3. How would you enforce password expiration and account lockout policies at account creation?
4. What’s the advantage of using roles instead of granting privileges directly to individual users?
5. Why is it risky to use 'user'@'%' for a publicly reachable database account?

Key Takeaways

• MySQL accounts are identified by user@host combinations, not username alone.
• Scope the host portion as tightly as your network topology allows — avoid % for anything internet-facing.
• Use dedicated accounts per service or person, never shared logins.
• Roles simplify privilege management at scale far better than per-user GRANT statements.
• Regularly audit mysql.user and remove stale accounts as part of offboarding.

References

• MySQL 8.0 Reference Manual — Account Management: https://dev.mysql.com/doc/refman/8.0/en/user-account-management.html
• MySQL 8.0 Reference Manual — CREATE USER Statement: https://dev.mysql.com/doc/refman/8.0/en/create-user.html
• MySQL 8.0 Reference Manual — Using Roles: https://dev.mysql.com/doc/refman/8.0/en/roles.html