Resolved How to create a database user with a known password hash

[kevinjansen]

Server operating system version

Centos 7

Plesk version and microupdate number

Obsidian 18.0.51

Dear Community.

I am trying to make a migration script and came to the part where i need to export/import the database.
I can create a database and import the database content from the old mysql server without a problem.

But now i need to create the database user. And i like to dont change the customers password.
I can export the mysql.user password hash from the current Mysql server. But i am not able to give that password hash to plesk.

When i use the plesk cli command it thinks its a plain text password and create a new hash based of that hash.
And then i want to update the database i get a error:


UPDATE mysql.user SET Password='*81E76F9718E4ECC1DEF72BA47D2648A7818C7A2B' WHERE User='testdatabase_export'; FLUSH PRIVILEGES;
ERROR 1356 (HY000): View 'mysql.user' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

How can i reuse the password hash cause i dont know the actual password. (yes i can search in all the wp-config.php / configuration.php files ect)

 

[Kaspar]

Instead of using the CLI (or API) you'll have to run a SQL query either as the current database user (the one you are trying to import) or as another database user with privileges to change the password of other database user (like the default Admin database user) to directly update the password of the database user. You can use the follow query for that (similar to the query shown in the CLI error). Don't forget to run the flush privileges query

UPDATE mysql.user SET Password='HASHED PASSWORD' WHERE User='DATABASE USER NAME';
FLUSH PRIVILEGES;

So what you could do in your script is to perform the following steps:

1. Create database
2. Create database user with a temporary password
3. Import the database
4. Change the password of the database user with hashed password

Hope this helps.

 

[kevinjansen]

Dear,

That does not work.
If i do it was the psa admin user i get the error:

ERROR 1356 (HY000): View 'mysql.user' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
If i login with the temp password of the correct user i get the error:

ERROR 1142 (42000): UPDATE command denied to user 'testdatabase_export'@'localhost' for table `mysql`.`user`

 

[kevinjansen]

Have found a working solution for the psa/admin user:

ALTER USER 'db_username' IDENTIFIED WITH mysql_native_password AS 'hashed_password_here';
FLUSH PRIVILEGES;

 

[kevinjansen]

Setting the correct password hash works now. But now i am running against the problem that when you click on "phpmyadmin" in plesk it results in a error. Because of the SSO is broken. So i need to remove/update something in a plesk database.

 

[kevinjansen]

Also found the SSO fix:

update psa.accounts set password='$(cat /tmp/$DATABASE.hash)', type='crypt' where id=(select account_id from psa.db_users where login='${DATABASE}');"

 

[Kaspar]

I came across this topic again while I recently was performing a few database migrations myself. For which I too only had the password hash for the database users. With the help of Plesk support I was able to find a much easier method. So for future reference I am posting the solution here.

The Plesk CLI documentation for the database utility notes that the PSA_CRYPTED_PASSWORD environment variable can be used to create a database user with an excising hashed password. Which can be used like this:

PSA_CRYPTED_PASSWORD='<Password Hash>' plesk bin database --create-dbuser <database user name> -passwd '' -domain <domain> -server <database server> -database <database name>

So for example:

PSA_CRYPTED_PASSWORD='*36B9537D3A807A9A0C82B45C4302295054778ECA' plesk bin database --create-dbuser johndoe -passwd '' -domain example.com -server localhost -database db_1