Skip to main content

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:
HostMeaning
'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

LevelScopeExample
GlobalAll databasesGRANT RELOAD ON *.*
DatabaseOne databaseGRANT CREATE ON myapp.*
TableOne tableGRANT SELECT ON myapp.users
ColumnSpecific columnsGRANT SELECT (id, name) ON myapp.users
Stored routineProcedure or functionGRANT EXECUTE ON PROCEDURE myapp.get_user
Common privileges:
PrivilegeWhat it allows
SELECTRead rows
INSERTInsert rows
UPDATEModify rows
DELETEDelete rows
CREATECreate tables and databases
DROPDrop tables and databases
INDEXCreate and drop indexes
ALTERAlter table structure
EXECUTERun stored procedures and functions
PROCESSSee all running queries (SHOW PROCESSLIST)
SUPEROverride restrictions; administrative operations
ALL PRIVILEGESAll 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?

In MySQL 8.0, 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

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