RENAME USER in a stored procedure

0

I'm trying to create a stored procedure to rename users in my MySQL database:

CREATE DEFINER=`admin`@`%` PROCEDURE `renamedbuser`(
  IN _oldname VARCHAR(200),
  IN _newname VARCHAR(200)
)
BEGIN
  IF (SELECT COUNT(User) FROM mysql.user WHERE User = _oldname) > 0 THEN
  BEGIN
    RENAME USER _oldname TO _newname;
  END;
  END IF;
END

When I run this procedure I get the following error:

MySql.Data.MySqlClient.MySqlException (0x80004005): Operation RENAME USER failed for '_oldname'@'%'

It seems to me that when the RENAME USER line executes, it uses the name of the variable _oldname as the original username, rather than the value that the variable contains.

If I execute something like

RENAME USER 'test' TO 'testing';

If works as I'd expect, renaming the 'test'@'%' user.

Am I calling RENAME USER incorrectly? Does that statement just not work this way when called from a stored procedure? Is there any way to achieve the behavior I'm looking for?

mysql
stored-procedures
asked on Stack Overflow Apr 29, 2019 by jmatula

1 Answer

0

In the documentation of account names it says:

The user name and host name need not be quoted if they are legal as unquoted identifiers.

I guess that takes precedence over your stored procedure variables. It also says:

Quote user names and host names as identifiers or as strings, using either backticks (`), single quotation marks ('), or double quotation marks (").

You could try it with backticks, like so:

RENAME USER `_oldname` TO `_newname`;
answered on Stack Overflow Apr 29, 2019 by KIKO Software • edited May 8, 2019 by marc_s

User contributions licensed under CC BY-SA 3.0