MySQL - Drop User

Dropping users in MySQL will remove a user's access and permissions on a specific database. This is performed by database administrators to maintain security and control over who can interact with the database system, ensuring that only authorized users can access and manipulate the data.

The MySQL Drop User Statement

You can drop/delete one or more existing users in MySQL using the DROP USER Statement. Once you delete an account, all privileges of it are deleted. To execute this statement, you need to have CREATE USER privilege.

Syntax

Following is the syntax of the DROP USER statement −

DROP USER [IF EXISTS] 'username'@'hostname';

Where, user_name is the name of the MySQL user you need to delete.

Example

Suppose, we have created a MySQL user account named 'TestUser' as shown below −

CREATE USER TestUser@localhost IDENTIFIED BY 'password1';

Following is the output obtained −

Query OK, 0 rows affected (0.04 sec)

You can verify the list of users using the following query −

SELECT user FROM MySQl.user;

The table will be displayed as shown below −

Now, let us delete the 'TestUser' account created above using the DROP USER statement as shown below −

DROP USER TestUser@localhost;

After executing the above code, we can see the output as shown below −

Query OK, 0 rows affected (0.02 sec)

Verification

Once a table is dropped, if you verify the list of the users as shown below using the SELECT statement, you will find that its name is missing from the list −

SELECT user FROM MySQl.user;

The table obtained is as follows −

Removing Multiple Users

You can also delete multiple users at once using the DROP ROLE statement. Roles are used to manage permissions and access control in a database system. By dropping a role, you revoke all privileges associated with that role. −

Example

Let us start by creating two roles 'MyAdmin' and 'MyDeveloper' −

CREATE ROLE 'MyAdmin', 'MyDeveloper';

The output obtained is as follows −

Query OK, 0 rows affected (0.01 sec)

Now, let us remove these roles using the DROP ROLE statement −

DROP ROLE 'MyAdmin', 'MyDeveloper';

This query will effectively delete both roles from the database −

Query OK, 0 rows affected (0.01 sec)

The IF EXISTS clause

If you try to drop a MySQL user that doesn't exist, an error will be generated. To address this issue, MySQL provides the IF EXISTS clause, which can be used with the DROP USER statement.

Hence, the IF EXISTS clause allows you to drop a user if they exist, and it handles situations where the specified user is not found in the database.

Example

In the below query, we are attempting to drop the 'demo' user. However, it results in an error because the user doesn't exist in the database −

DROP USER demo@localhost;

The output produced is as shown below −

ERROR 1396 (HY000): Operation DROP USER failed for 'demo'@'localhost'

If you use the IF EXISTS clause along with the DROP USER statement as shown below, the specified user will be dropped and if a user with the given name doesn't exist, the query will be ignored −

DROP USER IF EXISTS demo;

The output obtained is as follows −

Query OK, 0 rows affected, 1 warning (0.01 sec)

Dropping User Using a Client Program

In this section we are going to see various client programs to drop an existing user from MySQL.

Syntax

Following are the syntaxes to drop a MySQL user in various programming languages −

$sql = "DROP USER 'username'@'localhost'";
$mysqli->query($sql);

sql= "DROP USER [IF EXISTS] user_name ...";
con.query(sql, function (err, result) {
   if (err) throw err;
      console.log(result);
});

String sql = "DROP USER "USER_NAME'@'LOCALHOST'";
statement.execute(sql);

sql = "DROP USER 'UserName'@'localhost'";
cursorObj.execute(sql);

Example

Following are the client programs to drop an user in MySQL −

$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass);
if($mysqli->connect_errno ) {
   printf("Connect failed: %s
", $mysqli->connect_error);
   exit();
}
//printf('Connected successfully.
');
$sql = "DROP USER 'Revathi'@'localhost'";
if($mysqli->query($sql)){
   printf("User dropped successfully...!");
}
if($mysqli->error){
   printf("Failed..!" , $mysqli->error);
}
$mysqli->close();     

User dropped successfully...!

var mysql = require('mysql2');
var con = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "Nr5a0204@123"
});

  //Connecting to MySQL
  con.connect(function (err) {
  if (err) throw err;
  console.log("Connected!");
  console.log("--------------------------");

  //Deleting Users
  sql = "DROP USER TestUser1@localhost;"
  con.query(sql);
  sql = "DROP USER TestUser2@localhost;"
  con.query(sql);

  //Listing the users after deleting
  sql = "select user from MySQl.user;"
  con.query(sql, function(err, result){
    console.log("**List of Users after deleting:**")
    if (err) throw err
    console.log(result)
  })
});

Connected!
--------------------------
*List of Users after deleting:**
[
  { user: 'TestUser3' },
  { user: 'TestUser4' },
  { user: 'mysql.infoschema' },
  { user: 'mysql.session' },
  { user: 'mysql.sys' },
  { user: 'root' },
  { user: 'sample' }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class DropUsers {
	public static void main(String[] args) {
		String url = "jdbc:mysql://localhost:3306/TUTORIALS";
		String user = "root";
		String password = "password";
		try {
			Class.forName("com.mysql.cj.jdbc.Driver");
            Connection con = DriverManager.getConnection(url, user, password);
            Statement st = con.createStatement();
            //System.out.println("Database connected successfully...!");
            String sql = "DROP USER 'Vivek'@'localhost'";
            st.execute(sql);
            System.out.println("User 'Vivek' dropped successfully...!");
		}catch(Exception e) {
			e.printStackTrace();
		}
	}
}

User 'Vivek' created successfully...!

import mysql.connector
# creating the connection object
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password'
)
# Create a cursor object for the connection
cursorObj = connection.cursor()
cursorObj.execute("DROP USER 'UserNew'@'localhost'")
print("User 'UserNew' is dropped successfully.")
cursorObj.close()
connection.close()

User 'UserNew' is dropped successfully.