Summary: in this tutorial, you will learn how to use the MySQL RENAME USER statement to rename existing user accounts.

Introduction to MySQL RENAME USER statement

The RENAME USER statement renames one or more existing accounts. Here is the basic syntax of the RENAME USER statement:

Code language: SQL (Structured Query Language) (sql)

In this syntax:

• First, specify the name of the existing user that you want to rename.
• Second, specify the new user name after the TO keyword. The new name must not exist or you will get an error.

If you want to rename multiple user accounts at once, you use the following syntax:

Code language: SQL (Structured Query Language) (sql)

The RENAME USER transfers all privileges of the old users to the new users. However, it does not drop or invalidate database objects that are dependent on old users.

For example, assuming that you have a stored procedure whose the DEFINER attribute specifies the old user. And this stored procedure executes in the definer security context. If you rename the old user, then you will get an error if you execute the stored procedure.

MySQL RENAME USER examples

Let’s take some examples of using the MySQL RENAME USER statement.

A) Using MySQL RENAME USER to rename one user example

First, create a new user called john@localhost:

Code language: SQL (Structured Query Language) (sql)

Second, use the RENAME USER to rename user john@localhost:

Code language: SQL (Structured Query Language) (sql)

Third, query data from the mysql.user to verify the rename:

Code language: SQL (Structured Query Language) (sql)

B) Using MySQL RENAME USER to rename multiple user accounts example

First, create two user accounts jill@localhost and hill@localhost

Code language: SQL (Structured Query Language) (sql)

Second, use the RENAME USER statement to rename these two users:

Code language: SQL (Structured Query Language) (sql)

Third, query data from the mysql.user to verify the rename:

Code language: SQL (Structured Query Language) (sql)

C) Using MySQL RENAME USER to rename a user account associated with a stored procedure

First, create a new user account called fx:

Code language: SQL (Structured Query Language) (sql)

Second, grant all privileges to fx:

Code language: SQL (Structured Query Language) (sql)

Third, login as fx and create a procedure that returns all rows from the payments table in the sample database:

Code language: SQL (Structured Query Language) (sql)

The definer of the procedure is fx and SQL SECURITY specifies that the procedure will execute with the privileges of the definer.

Fourth, login as root and call the GetPayments() procedure:

Code language: SQL (Structured Query Language) (sql)

Fifth, rename the user account fx to fc:

Code language: SQL (Structured Query Language) (sql)

Sixth, call the GetPayments() procedure again:

Code language: SQL (Structured Query Language) (sql)

MySQL issued the following message:

Code language: SQL (Structured Query Language) (sql)

To fix this issue, you need to manually change the definer in the stored procedure GetPayments() and save it.

In this tutorial, you have learned how to use the MySQL RENAME USER statement to rename one or more existing user accounts.