> ## Documentation Index
> Fetch the complete documentation index at: https://villagesql.com/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# MySQL User Management

> How to create, modify, and drop MySQL users: CREATE USER syntax, privilege levels, GRANT and REVOKE, roles in MySQL 8.4, and least-privilege best practices.

> [card] [VillageSQL is a drop-in replacement for MySQL with extensions.](https://villagesql.com/docs/en/8.4/stable/install-server.md)
>
> All examples on this page work on VillageSQL. Install Now →

MySQL's access control model separates authentication (who you are) from authorization (what you can do). You create a user account, then grant privileges to it separately.

## Creating Users

```sql theme={null}
CREATE USER 'alice'@'localhost' IDENTIFIED BY 'strong_password';
```

The `@'localhost'` part is the host restriction — this account can only connect from the local machine. Common host patterns:

| Host | Meaning |
| :- | :- |
| `'localhost'` | Local socket connections only |
| `'127.0.0.1'` | TCP loopback only |
| `'%'` | Any host (network access) |
| `'192.168.1.%'` | Any host in that subnet |
| `'app.example.com'` | Specific hostname |

`'alice'@'localhost'` and `'alice'@'%'` are two distinct accounts, even though the username is the same.

## Granting Privileges

```sql theme={null}
-- Read-only access to one database
GRANT SELECT ON myapp.* TO 'alice'@'localhost';

-- Full access to one database
GRANT ALL PRIVILEGES ON myapp.* TO 'alice'@'localhost';

-- Specific privileges on one table
GRANT SELECT, INSERT, UPDATE ON myapp.orders TO 'alice'@'localhost';

-- Global privilege (all databases)
GRANT PROCESS ON *.* TO 'alice'@'localhost';
```

The privilege object uses `database.table` notation:

* `*.*` — all databases, all tables (global)
* `myapp.*` — all tables in the `myapp` database
* `myapp.orders` — only the `orders` table

## Privilege Levels

| Level | Scope | Example |
| :- | :- | :- |
| Global | All databases | `GRANT RELOAD ON *.*` |
| Database | One database | `GRANT CREATE ON myapp.*` |
| Table | One table | `GRANT SELECT ON myapp.users` |
| Column | Specific columns | `GRANT SELECT (id, name) ON myapp.users` |
| Stored routine | Procedure or function | `GRANT EXECUTE ON PROCEDURE myapp.get_user` |

Common privileges:

| Privilege | What it allows |
| :- | :- |
| `SELECT` | Read rows |
| `INSERT` | Insert rows |
| `UPDATE` | Modify rows |
| `DELETE` | Delete rows |
| `CREATE` | Create tables and databases |
| `DROP` | Drop tables and databases |
| `INDEX` | Create and drop indexes |
| `ALTER` | Alter table structure |
| `EXECUTE` | Run stored procedures and functions |
| `PROCESS` | See all running queries (`SHOW PROCESSLIST`) |
| `SUPER` | Override restrictions; administrative operations |
| `ALL PRIVILEGES` | All privileges for the specified scope |

## Viewing Grants

```sql theme={null}
-- Show grants for the current user
SHOW GRANTS;

-- Show grants for a specific user
SHOW GRANTS FOR 'alice'@'localhost';
```

## Revoking Privileges

```sql theme={null}
REVOKE INSERT ON myapp.* FROM 'alice'@'localhost';
REVOKE ALL PRIVILEGES ON myapp.* FROM 'alice'@'localhost';
```

Revoking a privilege that was never granted returns `ERROR 1141: There is no such grant defined for user`. The user retains any privileges granted at other levels — revoking `SELECT ON myapp.*` does not remove `SELECT ON myapp.orders` if that was granted separately.

## Changing Passwords

```sql theme={null}
ALTER USER 'alice'@'localhost' IDENTIFIED BY 'new_password';
```

Force a password change on next login:

```sql theme={null}
ALTER USER 'alice'@'localhost' PASSWORD EXPIRE;
```

## Roles (MySQL 8.0+)

Roles are named collections of privileges. Instead of granting individual privileges to each user, grant a role.

```sql theme={null}
-- Create a role
CREATE ROLE 'app_reader', 'app_writer';

-- Grant privileges to the role
GRANT SELECT ON myapp.* TO 'app_reader';
GRANT SELECT, INSERT, UPDATE, DELETE ON myapp.* TO 'app_writer';

-- Grant the role to a user
GRANT 'app_reader' TO 'alice'@'localhost';
GRANT 'app_writer' TO 'bob'@'%';
```

Users must activate a role to use its privileges (or set it as a default):

```sql theme={null}
-- Activate in session
SET ROLE 'app_reader';

-- Set default role so it activates automatically on login
SET DEFAULT ROLE 'app_reader' TO 'alice'@'localhost';
```

## Dropping Users

```sql theme={null}
DROP USER 'alice'@'localhost';
```

Dropping a user automatically revokes all their privileges and removes them from all roles. If the user is currently connected, the session continues until it ends.

## Least-Privilege Best Practices

Give each account only the privileges it needs:

* Application read replicas: `SELECT` only
* Application write account: `SELECT`, `INSERT`, `UPDATE`, `DELETE`
* Application schema account (migrations): add `CREATE`, `ALTER`, `DROP`, `INDEX`
* DBA account: `ALL PRIVILEGES` on `*.*`
* Never grant `SUPER` or `ALL` to application accounts

Restrict hosts as tightly as possible. An application on `app.example.com` should use `'appuser'@'app.example.com'`, not `'appuser'@'%'`.

## Frequently Asked Questions

#### Does MySQL flush grants automatically?

All changes via `CREATE USER`, `GRANT`, `REVOKE`, and `ALTER USER` take effect immediately — no `FLUSH PRIVILEGES` needed. `FLUSH PRIVILEGES` is only necessary if you modify the grant tables directly with `INSERT`/`UPDATE` (which you shouldn't do).

#### What's the difference between `localhost` and `127.0.0.1`?

`localhost` in MySQL refers to the Unix socket connection (local file socket), not the network loopback. `127.0.0.1` is the TCP loopback. If your client connects via TCP even on the same machine, use `127.0.0.1` as the host restriction.

## Troubleshooting

| Problem | Solution |
| :- | :- |
| `ERROR 1045: Access denied for user` | Wrong password, wrong host, or account doesn't exist — verify with `SELECT user, host FROM mysql.user` |
| User can connect but can't query | Check grants with `SHOW GRANTS FOR 'user'@'host'` — the privilege level must match the scope (database vs global) |
| `GRANT` fails with `ERROR 1044` | Current user lacks the `GRANT OPTION` privilege — connect as root or a user with `GRANT OPTION` |
| User created but can't log in from app server | The `@'host'` pattern doesn't match the app server's hostname or IP — add or update the account host pattern |
| `DROP USER` fails | Verify the exact user/host combination: `SELECT user, host FROM mysql.user WHERE user = 'alice'` |

## See also

* [MySQL Security Hardening](https://villagesql.com/docs/en/guides/security-hardening.md) — server-level security that complements user privileges
* [MySQL INFORMATION\_SCHEMA](https://villagesql.com/docs/en/guides/information-schema.md) — querying grants and metadata behind the commands
* [Common MySQL Errors and How to Fix Them](https://villagesql.com/docs/en/guides/common-mysql-errors.md) — decoding access-denied and other frequent errors
* [JWT Authentication in MySQL](https://villagesql.com/docs/en/guides/jwt-authentication.md) — accounts that sign in with a token instead of a password


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.