Create User With @variable For The Password

[larsen]

Hi,

I want to create a user with the password from another user like this:

SELECT PASSWORD FROM USER WHERE USER = ‘jon’ INTO @var;

CREATE USER ‘jane’@'localhost’ IDENTIFIED BY PASSWORD @var;

Unfortunately, this only gives me “You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘@var’ at line 1″.

I have tried other ways but didn´t succeed. Is what I´m trying to do even possible?

Lars

[peterlaursen]

As far as I can understand the first statement will save a HASHED password into a user variable. In CREATE USER you shall not specify a hashed password but a clear-text password.

Also there is a bug report here http://bugs.mysql.com/bug.php?id=28406 that I understand that ” … IDENTIFIED BY @uservar” is not accepted by the server in CREATE USER.

What about something like

Code:

SELECT PASSWORD FROM USER WHERE USER = 'jon' INTO @var;

INSERT INTO USER (user, host, password) VALUES ('jane','localhost',@var);

FLUSH PRIVILEGES:

(Note that the 2nd statement will fail in 'strict mode'. In 'strict mode' you will need to specify empty string defaults for all BLOB and TEXT columns in the user table. But SQLyog runs in non-strict mode as default).

[larsen]

Thanks for the bug ID. I thought that this must be possible so I didn´t even bother to search for it.

'peterlaursen' wrote:

As far as I can understand the first statement will save a HASHED password into a user variable. In CREATE USER you shall not specify a hashed password but a clear-text password.

Yes, that´s right. I just want to duplicate an account for another IP without the need to lookup the password. This is necessary when configuring a dedicated server and I want to automate as much as possible so I just have to execute the commands (via copy&paste) and not type anything manually. Therefore, I simply copy the hash value.

Thanks for the workaround! Had almost forgotten about that way 😉

[Author]

Posts