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.

VillageSQL is a drop-in replacement for MySQL with extensions.

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

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

-- 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

-- Show grants for the current user
SHOW GRANTS;

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

Revoking Privileges

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

ALTER USER 'alice'@'localhost' IDENTIFIED BY 'new_password';

Force a password change on next login:

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.

-- 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):

-- 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

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 — server-level security that complements user privileges