MySQL RENAME USER

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 allows you to rename one or more existing user accounts. Here’s the basic syntax of the RENAME USER statement:

RENAME USER old_user1 
TO new_user;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 simultaneously, you use the following syntax:

RENAME USER 
    old_user1 TO new_user1,
    old_user2 TO new_user2,
    ...;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, assume 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.

1) Using MySQL RENAME USER to rename one user example

First, create a new user called john@localhost:

CREATE USER john@localhost 
IDENTIFIED BY 'Super!pass1';
Code language: SQL (Structured Query Language) (sql)

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

RENAME USER john@localhost
TO doe@localhost;
Code language: SQL (Structured Query Language) (sql)

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

SELECT host, user
FROM mysql.user
WHERE user = 'doe' and host = 'localhost';
Code language: SQL (Structured Query Language) (sql)

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

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

CREATE USER jill@localhost 
IDENTIFIED BY 'Super!pass1';

CREATE USER hill@localhost 
IDENTIFIED BY 'Super!pass1';
Code language: SQL (Structured Query Language) (sql)

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

RENAME USER     
    jill@localhost TO jin@localhost,
    hill@localhost TO hank@localhost;
Code language: SQL (Structured Query Language) (sql)

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

SELECT host, user
FROM mysql.user
WHERE user IN ('jin','hank');
Code language: SQL (Structured Query Language) (sql)

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

First, create a new user account called fx:

CREATE USER fx
IDENTIFIED BY 'Super!pass2';
Code language: SQL (Structured Query Language) (sql)

Second, grant all privileges to fx:

GRANT ALL ON *.* 
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:

DELIMITER $$

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

DELIMITER ;
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:

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

Fifth, rename the user account fx to fc:

RENAME USER fx TO fc;
Code language: SQL (Structured Query Language) (sql)

Sixth, call the GetPayments() procedure again:

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

MySQL issued the following message:

Error Code: 1449. The user specified as a definer ('fx'@'%') does not exist
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.

Summary

• Use the MySQL RENAME USER statement to rename one or more existing user accounts.