MySQL RENAME USER Statement – A Complete Guide

Renaming Users In MySQL

The MySQL RENAME USER statement can be used to rename an existing MySQL user. This statement can be useful if you want to change the name of a user for security reasons.

What is MySQL RENAME USER?

The Rename user statement is used to change the name of the user associated with the account. The syntax for this is very simple –

Rename User abc
To abc1;

Here, abc is the old user name and abc1 is the new user name. The keyword To is used to change the old user name.

If we want to rename multiple users, the syntax for it is-

Rename User 
    old_user1 To new_user1,
    user2 To new2,
    .....

Basically, Rename User gives access to all its services and operations to the new user and does not delete or truncate the database or its objects. One thing to note is that when we have stored procedure and its definer attribute specifies to the old user then in this case, if we rename the old user and execute the operations, it will give us an error. It is so because the stored procedure executes in the Definer’s context.

Using the MySQL RENAME USER Statement

Now we will implement different cases of Renaming users in MySQL.

A) To Rename One User in MySQL

First, we will create a user-

Create User abc@localhost
Identified by 'password';

Here, Identified by is used to set the password for that particular user by which it will login.

Next, we will rename this abc@localhost using Rename user statement.

Rename User abc@localhost To cba@localhost;

The user has been successfully renamed. To verify it, write the following command-

Select host, user
From mysql.user;

Output-

Mysql Rename User 1
Mysql Rename User 1

B) Rename Multiple Users in MySQL

Now we will create two users-

Create user neil@localhost
Identified by 'Password';

Create user efg@localhost
Identified by 'Password';

Next, we will rename both the users i.e., neil@localhost to neil1@localhost and efg@localhost to efg1@localhost.

Rename User
neil@localhost To neil1@localhost,
efg@localhost To efg1@localhost;

The user has been successfully renamed. To verify it, write the following command again-

Select host, user 
From mysql.user;
Mysql Rename Users
Mysql Rename Users

C) To Rename a User Account Associated With Stored Procedure

Create a user and grant all privileges to it-

Create User ab
Identified by 'Password';
Grant All On *.* 
To ab;

Now do the following – Login as ab and create a procedure on a database.

DELIMITER $$

CREATE DEFINER= ab PROCEDURE GetPayments()
SQL SECURITY DEFINER
BEGIN
    SELECT * FROM payments;
END$$

DELIMITER ;

Here, SQL SECURITY specifies that the procedure will execute with the privileges of the definer.

Now Call the function.

CALL GetPayments();

After this rename user ab to ba MYand then again call the function.

MySQL server will give an error.

Error Code: 1449. The user specified as a definer ('ab'@'%') does not exist

This error is occurring because the Definer of GetPayments() is different. So you need to change it and then execute it.

Conclusion

In this tutorial, we studied using Rename user statement to rename one user or multiple users in MySQL.

For more reference, refer to the official documentation of MySQL.