Last updated
Database
MySQL User & Permissions
Manage MySQL user accounts, roles, and fine-grained permissions.
Contents
Create a User
CREATE USER 'devuser'@'localhost' IDENTIFIED BY 'SecurePass!';
-- Allow connections from any host (use carefully)
CREATE USER 'devuser'@'%' IDENTIFIED BY 'SecurePass!';
Grant Permissions
-- Full access to one database
GRANT ALL PRIVILEGES ON mydb.* TO 'devuser'@'localhost';
-- Read-only access
GRANT SELECT ON mydb.* TO 'readonly'@'localhost';
-- Specific table permissions
GRANT SELECT, INSERT ON mydb.users TO 'appuser'@'localhost';
-- Apply changes
FLUSH PRIVILEGES;
View User Permissions
-- Show grants for a specific user
SHOW GRANTS FOR 'devuser'@'localhost';
-- List all users
SELECT user, host, authentication_string FROM mysql.user;
Revoke Permissions
REVOKE INSERT ON mydb.* FROM 'devuser'@'localhost';
REVOKE ALL PRIVILEGES ON mydb.* FROM 'devuser'@'localhost';
FLUSH PRIVILEGES;
Change Password
ALTER USER 'devuser'@'localhost' IDENTIFIED BY 'NewStrongerPass!';
FLUSH PRIVILEGES;
Delete a User
DROP USER 'devuser'@'localhost';
Privilege Levels Reference
ALL PRIVILEGES, full accessSELECT, read dataINSERT, add rowsUPDATE, modify rowsDELETE, remove rowsCREATE, create tables/databasesDROP, delete tables/databasesINDEX, manage indexesGRANT OPTION, can grant their own privileges to others
Troubleshooting
ERROR 1045 Access denied: check the user name, the password and the host part ('app'@'localhost' and 'app'@'%' are different accounts). ERROR 1044: the user has no rights on that database yet; grant them. List accounts and their login method to spot mismatches.
SHOW GRANTS FOR 'appuser'@'localhost';
SELECT user, host, plugin, account_locked FROM mysql.user;
FAQ
How do I see what a MySQL user can do?
Run SHOW GRANTS FOR 'user'@'host'; in the MySQL shell.
Should apps use the MySQL root account?
No. Give each app its own user with rights only on its own database, so a breach in one app cannot reach the others.