MySQL User Management

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

Getting Started

Server Development

Extensions

Using Extensions

Creating Extensions

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

Creating Users

Granting Privileges

Privilege Levels

Viewing Grants

Revoking Privileges

Changing Passwords

Roles (MySQL 8.0+)

Dropping Users

Least-Privilege Best Practices

Frequently Asked Questions

Does MySQL flush grants automatically?

What’s the difference between `localhost` and `127.0.0.1`?

Troubleshooting

See also

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

​Creating Users

​Granting Privileges

​Privilege Levels

​Viewing Grants

​Revoking Privileges

​Changing Passwords

​Roles (MySQL 8.0+)

​Dropping Users

​Least-Privilege Best Practices

​Frequently Asked Questions

​Does MySQL flush grants automatically?

​What’s the difference between localhost and 127.0.0.1?

​Troubleshooting

​See also

Creating Users

Granting Privileges

Privilege Levels

Viewing Grants

Revoking Privileges

Changing Passwords

Roles (MySQL 8.0+)

Dropping Users

Least-Privilege Best Practices

Frequently Asked Questions

Does MySQL flush grants automatically?

What’s the difference between `localhost` and `127.0.0.1`?

Troubleshooting

See also