MySQL - Change Password

MySQL - Modern Password Management

Managing user passwords is a critical security task in MySQL. Modern versions of MySQL provide a secure and standardized way to handle password changes. This tutorial covers the best practices and contrasts them with outdated methods.

The Modern Approach: ALTER USER Statement

The ALTER USER statement is the recommended, secure, and most reliable method for changing a user’s password in MySQL 5.7.6 and newer. It handles password hashing automatically using the user’s configured authentication plugin.

Syntax

ALTER USER 'username'@'hostname' IDENTIFIED BY 'new_plain_text_password';

You can also change the password for the currently logged-in user:

ALTER USER USER() IDENTIFIED BY 'new_plain_text_password';

Example: Changing a User’s Password

First, let’s create a new user. In MySQL 8+, the default authentication plugin is caching_sha2_password, which is more secure than the older mysql_native_password.

CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'InitialP@ssw0rd123';

Now, let’s change the password for app_user.

ALTER USER 'app_user'@'localhost' IDENTIFIED BY 'UpdatedS3cur3P@ss!';

The server will respond with:

Query OK, 0 rows affected (0.01 sec)

The password is now updated. No FLUSH PRIVILEGES is needed. The change is immediate and secure.

Legacy and Deprecated Methods (Avoid in Modern Systems)

1. The SET PASSWORD Statement

The SET PASSWORD statement was a common way to change passwords. While it still works, ALTER USER is preferred because it offers more options for account management beyond just passwords (e.g., setting account locks, password expiration).

-- Legacy syntax
SET PASSWORD FOR 'app_user'@'localhost' = 'another_legacy_password';

2. Direct UPDATE on mysql.user Table (Highly Discouraged)

In older MySQL versions, it was possible to change a password by directly updating the mysql.user system table. This is now considered a very bad practice and is unreliable in modern MySQL for several reasons:

• Bypasses Security Checks: It bypasses internal validation and password management logic.
• Authentication Plugin Issues: It doesn’t correctly interact with authentication plugins like caching_sha2_password. Directly setting the authentication_string column can lead to authentication failures.
• Deprecated Functions: The PASSWORD() function, previously used to hash passwords for manual insertion, is deprecated and was removed in MySQL 8.0.

-- DO NOT USE THIS METHOD IN MODERN MYSQL
-- The PASSWORD() function is removed, and this approach is unsafe.
UPDATE mysql.user SET authentication_string = PASSWORD('bad_idea') WHERE User = 'app_user' AND Host = 'localhost';
FLUSH PRIVILEGES;

Changing a Password via a Client Program

When changing a password from an application, always use the ALTER USER statement. Use parameterized queries to prevent SQL injection, even if the inputs seem safe.

Example (Node.js with mysql2)

This example demonstrates how to securely change a user’s password from a Node.js application.

const mysql = require('mysql2/promise');

async function changeUserPassword(adminConnection, username, hostname, newPassword) {
  try {
    // Use parameterized query for safety, though values here are hardcoded.
    const sql = 'ALTER USER ?@? IDENTIFIED BY ?';
    const params = [username, hostname, newPassword];

    await adminConnection.execute(sql, params);
    console.log(`Password for user '${username}'@'${hostname}' changed successfully.`);

  } catch (error) {
    console.error('Failed to change password:', error);
    throw error;
  }
}

async function main() {
  let connection;
  try {
    // Connect with a user that has privileges to alter other users (e.g., root)
    connection = await mysql.createConnection({
      host: 'localhost',
      user: 'root',
      password: 'your_root_password'
    });

    await changeUserPassword(connection, 'app_user', 'localhost', 'NewP@ssFromApp123');

  } catch (err) {
    console.error('An error occurred:', err);
  } finally {
    if (connection) connection.end();
  }
}

main();