Managing MySQL users is essential for secure and efficient database operations. This guide simplifies creating, updating, and managing user permissions.

1.How to Create a User in MySQL

To create a new user in MySQL, use the CREATE USER command.    

CREATE USER 'readonly_user'@'localhost' IDENTIFIED BY 'securepassword';

2.Granting Permissions to Users

• Grant Read-Only Permissions to All Databases

  GRANT SELECT ON *.* TO 'readonly_user'@'localhost';

• Grant Read-Only Permissions to a Specific Database

  GRANT SELECT ON `specific_database`.* TO 'readonly_user'@'localhost';

• Grant All Permissions to All Databases

  GRANT ALL PRIVILEGES ON *.* TO 'admin_user'@'localhost';

• Grant All Permissions to a Specific Database

  GRANT ALL PRIVILEGES ON `specific_database`.* TO 'admin_user'@'localhost';

3.Update a User's Password

To change a user's password:

• MySQL 5.7 and Older:

  SET PASSWORD FOR 'username'@'host' = PASSWORD('newpassword');

• MySQL 5.7.6 and Newer:

  ALTER USER 'username'@'host' IDENTIFIED BY 'newpassword';

4.Modify Existing Grants

To modify user permissions, revoke the existing grants and apply the new ones.

• Revoke a Permission:

  REVOKE SELECT ON *.* FROM 'readonly_user'@'localhost';

• Grant New Permissions:

  GRANT INSERT, UPDATE ON `specific_database`.* TO 'readonly_user'@'localhost';

5.View current Grants

SHOW GRANTS FOR 'readonly_user'@'localhost';

6. List of Available Permissions

Here are some common permissions and their purposes:

DROP USER 'username'@'host';