> ## 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. All examples in this guide 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](/guides/security-hardening) — server-level security that complements user privileges