MySQL RENAME USER Statement – A Complete Guide

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;Code language: SQL (Structured Query Language) (sql)

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,
    .....
Code language: SQL (Structured Query Language) (sql)

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';Code language: SQL (Structured Query Language) (sql)

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;Code language: SQL (Structured Query Language) (sql)

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

Select host, user
From mysql.user;Code language: SQL (Structured Query Language) (sql)

Output-

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';Code language: SQL (Structured Query Language) (sql)

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;Code language: SQL (Structured Query Language) (sql)

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

Select host, user 
From mysql.user;Code language: SQL (Structured Query Language) (sql)

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;Code language: SQL (Structured Query Language) (sql)

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 ;
Code language: SQL (Structured Query Language) (sql)

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

Now Call the function.

CALL GetPayments();Code language: SQL (Structured Query Language) (sql)

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
Code language: SQL (Structured Query Language) (sql)

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.