MySQL user management: create, grant, and revoke

MySQL users control who can access which databases and what operations they can perform. Each user has a username, password, and set of privileges.

Create MySQL user

CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';
# For remote access from any host:
CREATE USER 'username'@'%' IDENTIFIED BY 'password';

Grant permissions to user

Grant all privileges on specific database

GRANT ALL PRIVILEGES ON mydatabase.* TO 'username'@'localhost';

Grant specific privileges

GRANT SELECT, INSERT, UPDATE ON mydatabase.* TO 'username'@'localhost';

Grant on specific table

GRANT SELECT ON mydatabase.users TO 'username'@'localhost';

Apply changes

FLUSH PRIVILEGES;

Permission levels

• SELECT: Read data
• INSERT: Add new rows
• UPDATE: Modify existing rows
• DELETE: Remove rows
• CREATE: Create tables
• ALTER: Modify table structure
• INDEX: Create indexes
• ALL: All permissions

Revoke permissions

REVOKE ALL PRIVILEGES ON mydatabase.* FROM 'username'@'localhost';
REVOKE INSERT, DELETE ON mydatabase.* FROM 'username'@'localhost';
FLUSH PRIVILEGES;

Reset user password

ALTER USER 'username'@'localhost' IDENTIFIED BY 'newpassword';
FLUSH PRIVILEGES;

Related: Database administration

Related articles