Create, Revoke, and Delete Users using MySQL Command Line

Managing user accounts in MySQL is crucial for maintaining database security and access control. In this blog post, we will guide you through the process of creating a new user using the MySQL command line. Additionally, we will explore how to check, revoke, and delete user privileges to ensure effective user management.

Prerequisites:

Before proceeding with the steps outlined in this guide, ensure that you have the following:

1. MySQL installed on your system: Make sure you have MySQL installed and set up properly. If you haven't installed MySQL yet, you can refer to the official MySQL documentation for installation instructions.

2. Access to the MySQL command line: Open your command prompt or terminal and enter the MySQL command line by running the below command

   mysql -u your_username -p
   

   Replace `your_username` with a valid MySQL username. You will be prompted to enter the password for the specified user.

Create, Revoke, and Delete Users using MySQL Command Line

Creating a New User

To create a new user, execute the following SQL command:

CREATE USER 'new_username'@'localhost' IDENTIFIED BY 'user_password';

Replace 'new_username' with the desired username for the new user and 'user_password' with a secure password. This command creates a user with access from the localhost only. You can modify the 'localhost' part to allow access from specific IP addresses or any machine based on your requirements.

Checking User Privileges

To check the privileges assigned to a user, use the following command:

SHOW GRANTS FOR 'new_username'@'localhost';

Replace 'new_username' with the username you want to check. This command displays the privileges assigned to the specified user.

Revoking User Privileges

To revoke specific privileges from a user, execute the following command:

REVOKE privilege_type ON database_name.* FROM 'new_username'@'localhost';

Replace 'privilege_type' with the specific privilege you want to revoke, such as SELECT, INSERT, UPDATE, or DELETE. Replace 'database_name' with the name of the database from which you want to revoke privileges. Finally, replace 'new_username' with the username from which you want to revoke privileges.

Deleting a User

To delete a user and remove their privileges, execute the following command:

DROP USER 'new_username'@'localhost';

Replace 'new_username' with the username you want to delete. This command permanently removes the user and their associated privileges from the MySQL server.

Conclusion:

Effectively managing user accounts in MySQL is essential for maintaining a secure and controlled database environment. By following the steps outlined in this blog post, you now have the knowledge to create new users, check their privileges, revoke specific privileges, and delete user accounts using the MySQL command line. Handle user privileges with care to ensure data security and maintain the integrity of your MySQL databases.