Code360 powered by Coding Ninjas X Naukri.com. Code360 powered by Coding Ninjas X Naukri.com
Last Updated: Mar 27, 2024

MySQL Drop User

Leveraging ChatGPT - GenAI as a Microsoft Data Expert
Speaker
Prerita Agarwal
Data Specialist @
23 Jul, 2024 @ 01:30 PM

Introduction

MySQL DROP User statement allows you to remove one or more users along with all their privileges from the database server. In this article, we will discuss how to accomplish it in detail. We will also see examples of single-user dropping, multiple user dropping, and dropping a connected user step by step.

                                                        

                                                                        Source: memgenerator

DROP USER 

Syntax: The syntax to delete a user along with all privileges from MySQL is given below:

DROP USER ‘account_name’;

where, account_name is the name of the user which you want to remove. Moreover,  ‘account_name’ is identified with the username@hostname syntax.

Here, the username is the name of the user account which you want to delete, and the hostname is the name of the server for that user account.

Removing multiple users: To remove multiple user accounts, specify a list of comma-separated user accounts. The syntax for that DROP clause is as follows: 

DROP USER ‘account_name’, ’account_name2’......

But the above clause will cause an error if the mentioned account names don't exist. To deal with that, we can use the 'IF EXISTS' clause, which got introduced in MySQL 5.7.8+.

DROP USER IF EXISTS ‘account_name’, ’account_name2’.....

This conditionally drops a user only if it exists.

Now, let's see some examples of dropping users.

Using MySQL DROP USER Statement To Drop A User 

Follow the steps to drop a user:

NOTE: The outputs are given for better understanding. They might not be exactly similar to the result your system produces.

Step1: Connect to MySQL server using the root account.

mysql -u root -p

 

Step2: Enter the password.

Enter password: ********

 

Step3: Execute the following command to see the current list of user accounts.

mysql> select user, host from mysql.user;

OUTPUT:

+-------------------------------+-----------+
| user                               | host      |
+-------------------------------+-----------+
| test                                | localhost |
| mysql.infoschema         | localhost |
| mysql.session               | localhost |
| mysql.sys                      | localhost |
| root                                | localhost |
+-------------------------------+-----------+
5 rows in set (0.00 sec)

 

Step4: Create some users using the command given below. Here, we are creating 4 new users.

mysql> create user user1@localhost, user2, ninja@localhost, shinzo@localhost identified by 'Secure1Pass!';

We created four users, namely 'user1', 'user2', 'ninja' and 'shinzo.' (To learn more about creating users, you can refer to MySQL create user). 

Step5: Again, execute the command to show the list of users.

mysql> select user, host from mysql.user;

OUTPUT:

+-------------------------------+-----------+
| user                               | host       |
+-------------------------------+-----------+
| user1                             | localhost |
| user2                             |     %       |
| ninja                              | localhost |
| shinzo                           | localhost |
| test                                | localhost |
| mysql.infoschema         | localhost |
| mysql.session               | localhost |
| mysql.sys                      | localhost |
| root                                | localhost |
+-------------------------------+-----------+
9 rows in set (0.00 sec)

We can see now that 4 created users increased in the user account list.

Step6: To drop a user run the following command.

mysql> drop user shinzo@localhost

User name ‘shinzo’ will be dropped.

Step7: Again, execute the command to show the list of users.

mysql> select user, host from mysql.user;

OUTPUT:

+-------------------------------+-----------+
| user                               | host       |
+-------------------------------+-----------+
| user1                             | localhost |
| user2                             |     %       |
| ninja                              | localhost |
| test                                | localhost |
| mysql.infoschema         | localhost |
| mysql.session               | localhost |
| mysql.sys                      | localhost |
| root                                | localhost |
+-------------------------------+-----------+
8 rows in set (0.00 sec)

The user account shinzo@localhost has been removed successfully.

Using MySQL DROP USER Statement To Drop Multiple User Accounts

We can also remove multiple user accounts at once. Let's continue our above example, and let's remove two users simultaneously, which we created before.

Step1: Run the following command.

mysql> drop user user1@localhost, user2;

Here user2’s hostname is not mentioned because at the time of the creation of user we haven’t specified it( as it is optional and user2 means user2@%, and ‘%’ means remote host can login to MySQL server from any other server). 

Therefore, ‘user1’ and ‘user2’ will be dropped simultaneously.

Step2: Show the results.

mysql> select user, host from mysql.user;

OUTPUT:

+-------------------------------+-----------+
| user                               | host       |
+-------------------------------+-----------+
| ninja                              | localhost |
| test                                | localhost |
| mysql.infoschema         | localhost |
| mysql.session               | localhost |
| mysql.sys                      | localhost |
| root                                | localhost |
+-------------------------------+-----------+
6 rows in set (0.00 sec)

The user accounts user1@localhost and user2 have been removed successfully.

Using MySQL DROP USER Statement To Drop A Connected User

Follow the steps given below to understand it better:

Step1: Create a new database called 'school.'

mysql> create database school;

Step2: Select the school database.

mysql> use school;

Step3: Create a new table ‘students’ in the ‘school’ database.

mysql> create table students(
    ->     id int auto_increment,
    ->     firstName varchar(100) not null,
    ->     lastName varchar(100) not null,
    ->     primary key(id));

Step4: Grant all privileges on the 'school' database to the user account 'david.'

mysql> grant all privileges on school.* to david@localhost;

Step 5: Launch another session and connect to the database using user david@localhost.

mysql -u david -p

Step6: Enter the password.

Enter password: ************

Step7: Select 'school' database.

mysql> use school;

Step8:  Insert a row into the students table.

mysql> insert into students(firstname, lastname) values('Alice','Bob');

Note: Now suppose you want to drop the user david@localhost. In this case, you have to inform the user first as it is currently connected to the MySQL server. If you drop a currently connected user without informing him/her, it will operate as normal until the next login. 

Another option is to kill the user session before dropping the user account.

Step 9: Use the SHOW PROCESSLIST command from the root session to find the id of the connection. 

SHOW PROCESSLIST

Step10: Terminate the corresponding Id process.

KILL Id

You will receive an error message if the user issues a query.

Step11: Finally, drop the user using the DROP USER command.

DROP USER david@localhost;

The user account david@localhost has been removed successfully.

Get the tech career you deserve, faster!
Connect with our expert counsellors to understand how to hack your way to success
User rating 4.7/5
1:1 doubt support
95% placement record
Akash Pal
Senior Software Engineer
326% Hike After Job Bootcamp
Himanshu Gusain
Programmer Analyst
32 LPA After Job Bootcamp
After Job
Bootcamp

FAQs

  1. What is a user in MySQL?
    A user is a record in the USER table of the MySQL server. It contains the login information, account privileges, and host information for the MySQL account.
     
  2. What is the use of DROP DATABASE command?
    The DROP DATABASE command is used to delete the database from an instance of SQL server and delete the physical disk files used by the database.
     
  3. What is the difference between DROP and DELETE in MySQL?
    DELETE is used to delete one or more rows from the table, while DROP is used to remove the entire table from the database. DELETE is a Data Manipulation Language, DML command, and DROP is a Data Definition Language, DDL command. 
     
  4. What is the difference between DROP and TRUNCATE COMMAND?
    The DROP command is used to remove the entire table(table definition and its content) from the database, while the TRUNCATE command is used to only remove all rows from the table.
     
  5. What is the difference between SQL and MySQL?
    SQL stands for Structured Query Language, and it's a language used by databases, whereas MySQL is an open-source relational database management system developed by Oracle, which is based on SQL. 

Key Takeaways

In this article, we discussed how to use the MySQL DROP USER statement to remove one or multiple user accounts from the MySQL database server. We also discussed examples of removing/dropping single, multiple, and connected users.

You can also check out the Top 100 SQL Problems to get hands-on experience with frequently asked interview questions.
Refer to the Guided Path to learn more about DBMS. You can visit Coding Ninjas Studio to practice programming problems for your complete interview preparation and land your dream job.

Topics covered
1.
Introduction
2.
DROP USER 
2.1.
Using MySQL DROP USER Statement To Drop A User 
2.2.
Using MySQL DROP USER Statement To Drop Multiple User Accounts
2.3.
Using MySQL DROP USER Statement To Drop A Connected User
3.
FAQs
4.
Key Takeaways