Creating Database User - How to Reuse User without Adding Password on Database Creation?

I came across this as a new user to HestiaCP (that was previously using VestaCP).

I can’t recall, but I thought there was a way to simply reuse an existing database user within the users account. This functionality is working, however the HestiaCP interface is a bit strange on how it’s handled.

For example, I have 3 databases which I use the same database username and password. This works just fine. I believe in VestaCP, when you create a NEW database, you can simply select the existing database username to apply to that database and hence you can use the same password in your Wordpress config file or whatever you are using to connect to it.

However, in HestiaCP, everytime you create a new database and you want to use that username, you have to go dig up the password and include it in the password field. If you leave it blank, it will give you an error. So you must set it. But this behavior is strange as it would seem that you are setting up a new user. However, if you set this password to something different (new), then obviously it will change it to that new password which then means that your existing websites using that same username are now broken, because the password has changed (and you will need to either go into those website config files and update the new password or resave another database creation with that same username and the old password used across your websites.

This is very strange in my opinion, why is it configured this way? I may be missing something, but I cannot find any section in the control panel where I can just edit or create database users. I could have sworn this was available in the VestaCP interface.

You can’t currently

Okay, so I’m making sense what I’m saying right?

Was this just something not considered at the time of building the control panel or lower on the priority list?

Is this on the roadmap? Thanks!

I don’t think this ever was available in Vesta. if I recall right you would have to grant permissions to the existing user via mysql directly instead… but it’s been a while, maybe I am missing something.

however, it may be a nuisance to have multiple users and passwords for different databases, but to be fair this is the better approach security wise.
the only reason to have it under the same user usually is the convenience for lazy admins to have all databases available in parallel in phpmyadmin or so :man_shrugging:

Thanks Falzo. I could be mistaken, but I’m pretty sure it existed. When creating a new database, I believe there was an option to select/assign an existing mysql user, which is what I did, and hence wouldn’t need to input the password again.

And yes, I fully agree with you that for security, you should have separate mysql username and passwords, but I only have a few websites, all running on Wordpress, running under the same user with secured wp-config.php files. It doesn’t make a lot of sense to have to manage creating separate mysql users for each one, because as you mention, then I would have to log in and out of phpmyadmin or sequelpro to access each. For my particular case, it’s secure enough, but in practice in most cases, I fully agree with you.

I guess for now, I will just need to open up the wp-config.php file to copy and paste that password whenever I create a new database. :wink:

If you want one user which can access all databases in phpmyadmin, there’s nothing to stop you adding one using the command line.

CREATE USER ‘dba’@‘localhost’ IDENTIFIED BY ‘123password’;
GRANT ALL PRIVILEGES ON db1.* TO ‘dba’@‘localhost’ ;
GRANT ALL PRIVILEGES ON db2.* TO ‘dba’@‘localhost’ ;

etc

1 Like

Thanks for sharing that method Pluto. That could work, but I want to try to keep everything within the configuration of HestiaCP so I know what has been set whenever possible.

So I’m trying to limit taking actions like that. But that’s helpful to know and I’m sure others that see this thread may want to go about it that way.

Your point about the management of database users in HestiaCP is certainly valid. The existing mechanism seems to be a bit cumbersome

Yes, I understand it’s a better approach for security, but sometimes devs make the same website with two separate databases and every time log in and log out from phpMyAdmin is a real mess .

I’m wondering if the HestiaCP team has plans to address this in future updates?

Thank you for bringing this to our attention. Looking forward to a resolution.

The easiest method I discovered is to set up a root password.

ALTER USER 'root'@'localhost' IDENTIFIED BY 'PASSWORD'; FLUSH PRIVILEGES;

Modify the /usr/local/Hestia/conf/mysql.conf and /root/.my.cnf files

Now I can log in to view all databases without having to log in to each one individually.

Hello @pluto, this may required Root user access to do so, right? I met “#1227 - Access denied; you need (at least one of) the CREATE USER privilege(s) for this operation” when query this

Yes you’d need to use the mysql root user to set this up.

1 Like