change_user

The mysqli_change_user() function changes the user — and optionally the default database — of an already-open MySQL connection, without dropping and reconnecting. This page explains what the function does, its parameters and return value, when it is genuinely useful, and the gotchas that surprise people the first time they use it.

What mysqli_change_user() does

mysqli_change_user() re-authenticates the existing connection as a different MySQL user. The TCP/socket connection stays open; only the identity (and therefore the privileges) attached to it changes. It is a thin wrapper around MySQL's COM_CHANGE_USER command.

It is available both as a procedural function and as an object-oriented method:

// Procedural style
mysqli_change_user($mysqli, $username, $password, $database);

// Object-oriented style (used in the examples below)
$mysqli->change_user($username, $password, $database);

Parameters

Return value

It returns true on success and false on failure (for example, wrong credentials or insufficient privileges). Always check the return value before running further queries.

A basic example

You call the method on a valid MySQLi object and pass the new username and password. The database argument is optional:

<?php
$mysqli = new mysqli("localhost", "username", "password", "database");
if ($mysqli->connect_errno) {
    echo "Failed to connect to MySQL: " . $mysqli->connect_error;
    exit();
}

// Switch to a new user on the existing connection
if (!$mysqli->change_user("newusername", "newpassword", null)) {
    echo "Error changing user: " . $mysqli->error;
    exit();
}

// Run queries with the new user's privileges from here on
// ...

$mysqli->close();
?>

We open a connection, then call change_user() to re-authenticate as a different user on the same connection. We check the return value to confirm the switch succeeded before running any further queries.

Switching the user and the database together

The third argument lets you change the default database at the same time as the user. This avoids a separate select_db() call:

<?php
$mysqli = new mysqli("localhost", "username", "password", "database");
if ($mysqli->connect_errno) {
    echo "Failed to connect to MySQL: " . $mysqli->connect_error;
    exit();
}

// Switch to a different user and select a new default database
if (!$mysqli->change_user("newusername", "newpassword", "newdatabase")) {
    echo "Error changing user: " . $mysqli->error;
    exit();
}

// Queries now run as "newusername" against "newdatabase"
// ...

$mysqli->close();
?>

If you omit the database argument (or pass null), the connection ends up with no default database selected — note this is not the same as keeping the previous one. If you need to keep working with the same database, pass its name explicitly.

When would I use this?

mysqli_change_user() is a niche tool. The most common real-world use is connection pooling: a long-lived pooled connection is handed out to different requests, and change_user() resets it to a clean state for the next consumer. It is also handy when a single script must perform some work with elevated privileges and other work with a restricted account, without paying the cost of opening a second connection.

For most everyday applications you simply open a connection as the right user and never call this function at all.

Gotchas to watch for

• State is reset. change_user() resets the connection as if it were freshly opened: it rolls back the current transaction, deactivates LOCK TABLES, releases temporary tables, and resets session variables (except character set, which is preserved). Don't call it in the middle of a transaction you care about.
• The database is not preserved. As noted above, leaving out the third argument clears the default database rather than keeping the current one.
• Privileges follow the new user. After the switch, every query is evaluated against the new user's grants. A query that worked before may now fail with a permissions error — that is expected behavior, not a bug.
• Don't confuse it with changing a user's data. This function changes which MySQL account the connection uses. It does not update rows in a users table; for that you run an ordinary UPDATE query with mysqli_query().

Conclusion

mysqli_change_user() re-authenticates an open MySQL connection as a new user, optionally selecting a new default database, and returns true/false so you can verify the switch. Knowing that it resets connection state and clears the default database when the third argument is omitted lets you use it safely — most often for connection pooling or privilege switching on a shared connection.

To keep exploring the MySQLi extension, see mysqli_connect() and mysqli_query().

Practice