Last updated

Database

MySQL User & Permissions

Manage MySQL user accounts, roles, and fine-grained permissions.

Contents

  1. Create a User
  2. Grant Permissions
  3. View User Permissions
  4. Revoke Permissions
  5. Change Password
  6. Delete a User
  7. Privilege Levels Reference

More on this topic:

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 access
  • SELECT, read data
  • INSERT, add rows
  • UPDATE, modify rows
  • DELETE, remove rows
  • CREATE, create tables/databases
  • DROP, delete tables/databases
  • INDEX, manage indexes
  • GRANT 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.