MySQL - Drop Users

Secure User Management: Dropping MySQL Users

Managing user accounts is a critical security function for any database administrator. The DROP USER statement is used to permanently remove user accounts from MySQL, revoking all their access and privileges. This action is irreversible and essential for maintaining a secure database environment.

The DROP USER Statement

To execute DROP USER, you must have the global CREATE USER privilege or the DELETE privilege on the mysql system database. When a user is dropped, their account and all associated privilege records are removed.

Syntax

The full syntax specifies both the username and the hostname from which the user can connect.

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

A user is defined by both their name and hostname. 'user'@'localhost' is a different user from 'user'@'192.168.1.100' or 'user'@'%'.

Example: Dropping a Single User

First, let’s create a temporary user for demonstration.

CREATE USER 'temp_app_user'@'localhost' IDENTIFIED BY 'a_secure_password';

You can verify the user exists by querying the mysql.user table:

SELECT user, host FROM mysql.user WHERE user = 'temp_app_user';

Now, let’s drop this user.

DROP USER 'temp_app_user'@'localhost';

Querying the mysql.user table again will show that the user has been removed.

Dropping Multiple Users

You can drop multiple user accounts in a single command by listing them, separated by commas. This is efficient for cleanup scripts.

Example

Let’s create two more temporary users.

CREATE USER 'user1'@'localhost';
CREATE USER 'user2'@'localhost';

Now drop both at once:

DROP USER 'user1'@'localhost', 'user2'@'localhost';

Note: DROP ROLE is a different command used to remove roles, which are collections of privileges, not user accounts. The original tutorial incorrectly mixed these concepts.

Using IF EXISTS for Safe Scripting

Attempting to drop a user that does not exist will result in an error, which can stop automated scripts. Using the IF EXISTS clause makes your command idempotent—it ensures the user is gone without failing if they were already removed.

Example (Error without IF EXISTS)

DROP USER 'no_such_user'@'localhost';

This produces an error:

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

Example (Safe with IF EXISTS)

DROP USER IF EXISTS 'no_such_user'@'localhost';

This command completes successfully with a warning, allowing scripts to continue.

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

Dropping Users via a Client Program

Security Warning: Application code should almost never have the privileges to drop users. This functionality should be restricted to secure, administrator-run scripts for provisioning or de-provisioning environments.

Example (A Node.js de-provisioning script)

NodeJS
Python
PHP
Java



// This script is intended for an administrator, not for a web application.
const mysql = require('mysql2/promise');

async function deprovisionUser(username, hostname) {
    // Use a privileged account for this script.
    const adminConn = await mysql.createConnection({ /* admin user config */ });
    try {
        console.log(`Dropping user '${username}'@'${hostname}'...`);
        // Using prepared statements isn't possible for DDL identifiers,
        // so ensure inputs are validated and from a trusted source.
        await adminConn.query(`DROP USER IF EXISTS ?@?`, [username, hostname]);
        console.log('User dropped successfully.');
    } catch (error) {
        console.error('Failed to drop user:', error);
    } finally {
        await adminConn.end();
    }
}

// Usage:
deprovisionUser('legacy_service_account', '10.0.1.5');



# An administrative Python script for user cleanup.
import mysql.connector

def drop_mysql_user(config, username, hostname):
    try:
        with mysql.connector.connect(**config) as conn:
            with conn.cursor() as cursor:
                print(f"Dropping user '{username}'@'{hostname}'...")
                # Use parameter substitution for safety
                cursor.execute("DROP USER IF EXISTS %s@%s", (username, hostname))
                print("User dropped successfully.")
    except mysql.connector.Error as err:
        print(f"Error: {err}")

# Admin connection config
admin_config = {'user': 'admin_user', 'password': 'admin_pass', 'host': '127.0.0.1'}
drop_mysql_user(admin_config, 'test_user', 'localhost')



// A PHP script for server maintenance.
$adminPdo = new PDO(/* Admin DSN */);
$adminPdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

function dropUser(PDO $pdo, string $user, string $host)
{
    try {
        // Use placeholders for safety
        $stmt = $pdo->prepare("DROP USER IF EXISTS ?@?");
        $stmt->execute([$user, $host]);
        echo "User {$user}@{$host} dropped successfully.\n";
    } catch (PDOException $e) {
        echo "Error dropping user: " . $e->getMessage() . "\n";
    }
}

dropUser($adminPdo, 'old_report_user', '%');



// A Java utility for an administrative tool.
public void dropUser(String username, String hostname) {
    String sql = "DROP USER IF EXISTS ?@?";
    // Use an admin connection
    try (Connection conn = getAdminConnection();
         PreparedStatement pstmt = conn.prepareStatement(sql)) {

        pstmt.setString(1, username);
        pstmt.setString(2, hostname);
        pstmt.executeUpdate();
        System.out.printf("User '%s'@'%s' dropped successfully.%n", username, hostname);

    } catch (SQLException e) {
        e.printStackTrace();
    }
}