DirectAdmin 1.660 is no longer creating a database user when Adding a new user

[pabloapico]

When a new account is created directadmin is supposed to create a database user in MariaDB/mysql (in my case MariaDB).
You can log in phpmyadmin as an administrator and find a user account per directadmin user with the same name.
Note that database users created manually always use the account name as prefix (ie. accountname_mydatabase) while this "primary" user for the account is just the name of the account.
This change of behaviour is unexpected and it is also not mentioned in the changelogs.

I could not find any related errors.
I am trying to figure out how to create the corresponding database user automatically like it always was. In the meantime we have to create the desired database user manually so that new accounts will work in the same way as the old ones.

 

[Richard G]

When a new account is created directadmin is supposed to create a database user in MariaDB/mysql (in my case MariaDB).

Since when? I presume you mean when a user creates a database, a database user is created.
I could be wrong but as far as I know, the database user is created on creation of the database. But there was some change which was also mentioned in the changelog.

You probably need to use the advanced mode. Check this changelog part:

Version 1.660 | Directadmin Docs

DirectAdmin Knowledge Base

docs.directadmin.com

 

[pabloapico]

Hello, Thanks for your reply
I am not referring to a user that is created when a database is created. That has nothing to do with this. I am referring to a database user that the system used to create silently for every account:

When an account is created (Add new user on directadmin) it used to create a database user; a main database user. This user was not listed in the account's database users list seen in the directadmin user GUI because it was not meant to be deleted or modified or used itself. It was meant to be used by the system only. While this "system database users" typically will not be seen in the user's GUI, definitely it will be seen in phpmyadmin if you log in with a da_admin, root, or a user with enough privileges to list all database users in the MariaDB Engine.

This database user has always been created silently prior the afore mentioned version of DirectAdmin. And whenever a database is created, this main user is suposed to get - again silently - full privileges automatically for that database. In order to verify that this is true, you would have to just log in phpmyadmin using an account like da_admin. Go to "User accounts" Then you could try to find a user with the name of any old account you created on your directadmin prior version 1.660. Note that a database user like that is impossible to create via directadmin's user GUI because those created manaully will be something like accountnameasprefix_assignedsuffix. Then using phpmyadmin you maye look into that database user details so you can look for database related to that database user and you will find that this db user will have privileges over every database related to the user account. In other words, prior to version 1.660, whenever a database was created, the main database user for the account was getting privileges for the created database without any manual action. Even if there is no db user explicitly created for that database. Just because a database belongs to the account then it will also "belong" to the main db user for that account, and that has nothing to do with db users created manually and its permissions assigned to databases.

This change has brought us some unexpected behavior in some specific scenarios and i strongly believe this was not on purpose ad it was rather a mistake taking into account that there is no mention about this change in the changelogs.

 

[Richard G]

Then you could try to find a user with the name of any old account you created on your directadmin prior version 1.660.

I would love to investigate, but I'm searching for where to look. Every server is on 1.660 now, but I do have accounts without database on that server, which were created way earlier than 1.660 even before 1.659.

I don't see any "User accounts" in phpmyadmin.

In the "mysql" table under "views" I found a table "user" and this contains all users present.
For users -with- a database present, I see 2 user accounts. 1 is just called "user" and the other one like "user_wp21" so the database name.
For the users which do or did not have a database prior to 1.660 I don't see any username.
So indeed, there are user accounts just called user (or rather accountname) without a _something suffix. But I can't find any of these names for accountnames without database.

So either this user is not created before the database is created, or the 1.660 upgrade must have removed them.
I don't have any server with an older DA versions so I can't check that way.

It's interesting anyway because I wasn't aware of this extra user yet and wonder where it's exactly used for or why it's created.
But why worry about something which isn't used anyway? Or which unexpected behaviour are you noticing?

 

[pabloapico]

Thanks Richard.

According to your info it is very likely that what i called "main db user for a DA account" was not created inmedatly on directadmin "Add New User" completion and rather it was created when at least one database was created for the corresponding directadmin user.

Also, since you found in your server db users that matches directadmin usernames, and those users are not listed in direcadmin GUI, nor they can be created there, we can conclude that this recent version of directadmin stopped creating this "main db users".

It's interesting anyway because I wasn't aware of this extra user yet and wonder where it's exactly used for or why it's created.
But why worry about something which isn't used anyway?

It made sense to me to have a main db user per account. One could have a database even if there is no user related to it and still have access to that database for instance.
In my case this is affecting phpmyadmin in the way it handles imported routines like we have been doing for years. Note that if you log in phpmyadmin from the user's directadmin dashboard, without specifying a db user from your database list, it will create a temporary user for the single sign on purposes (assuming you use that). You may check the user you are using running this SQL:
SELECT CURRENT_USER();
Importing things like routines/functions are showing some inconsistent behaviour (at least compared how it was before) because phpmyadmin will not know which database user is going to own the routine since there is simply no db user for the account. There is this SQL file that we import very frequently. It does not specify a user for a routine (we use one for everyone). It had no issues before. Now we have a strange issue where once we log out phpmyadmin there is no way to edit, access or modify the routines from the any the phpmyadmin launched from the account. Before if you had no db users manually created it made sense that you still had a way to manage whatever was in your reach thanks to that main db user. So far i do not know if there is any other impact but clearly the impact is low or none for moste users. Unluckily we have a very specific situation that is impacting us because of those problematic imported routines/functions.

 

[Richard G]

Also, since you found in your server db users that matches directadmin usernames, and those users are not listed in direcadmin GUI, nor they can be created there, we can conclude that this recent version of directadmin stopped creating this "main db users".

No I don't think that we can conclude this. As the user without database should have had a main db user (unvisible in GUI) too, which was not the case for the user created on the version before 1.660.

Note that if you log in phpmyadmin from the user's directadmin dashboard, without specifying a db user from your database list,

I presume that is with SSO? Because I can't get into phpmyadmin without specifying both a db user and the password belonging to that user. I get a login prompt as soon as I click phpmyadmin.
I don't use SSO so maybe that is the difference.

 

[floyd]

I just looked at an older server of mine. I can confirm at least originally DA created a database user for the user when the user was created. I remember it from the past and just checked to confirm.I have some old users and there are database users that are just their username.

 

[Richard G]

I have some old users and there are database users that are just their username.

Ah oke, so in my case, when upgraded to 1.660 then that name was removed. Because I've some users without database for years, and there is no database user for him present at the moment.

So if you create a test user, then a databaseuser test would also be created now on the older DA, right?

 

[floyd]

So if you create a test user, then a databaseuser test would also be created now on the older DA, right?

I no longer have old DA installations. This was just an old server that had an old DA at one time. DA has been upgraded over time. Now when creating a user the database user does not get created. But the old users that did get created are still there. The new DA did not delete them.

 

[johannes]

I`m on 1.659 and i have several account-name db users, but not for all accounts with databases. Seems to have stopped much earlier, maybe 1-2 years ago.

 

[Richard G]

But the old users that did get created are still there. The new DA did not delete them.

Must indeed have stopped way earlier, because my users without a database which don't have such invisible database account exists already for several years. More than 5 even I guess.

 

[floyd]

My question for pabloapico is why is this a problem now when it has probably been like this for the last 5 years?

 

[swisshuttles]

Same problem here. I had a previous DA installation which also added the main DA user account to the new created database along with the new created database user. This was an installation from about 8 years ago. I updated this server regularly and still had the behavior. Now I have a new DA-installation and this one lacks the option.

I loved this behavior because as the admin of the DA-account you can login to PMA and see and control all databases of the user. Since this new behavior you have to login multiple times to view the different databases of a single DA-user.

Don't see why this feature is removed or to have a simple way of re-enabling this behavior by adding the main DA account as database user.

In the enhanced skin you still find the option to reset the main database account password. This option proves the existence in the past. Don't see why it's still there when it doesn't really work anymore. Please bring back this option or does someone knows a fix?

 

[floyd]

Since this new behavior you have to login multiple times to view the different databases of a single DA-user.

Or you can just login as da_admin and have control of all the databases for all users.

 

[swisshuttles]

Let's give a DA user-level user access to the main da_user account. That would be a wonderful idea. Thank you ?

We are talking about a DA user-level user here and it should be nice when he can get access to all his databases with his login. It has been possible for years.

 

[floyd]

You said:

I loved this behavior because as the admin of the DA-account you can login to PMA and see and control all databases of the user.

A user is not an admin. If you were talking about user level you would have said "as the user of the DA-account." Stop being sarcastic when you are the one that lead to the confusion. You could have left out that first sentence and your point would have still been made without pissing off someone trying to help you..

The user is free to create database users and assign them to his own databases if that is what he wants to do. I understand that is not the default and its what you would like in your own situation but it is not desirable to everyone. But the ability is there to do it manually. You can even do it with a hook https://docs.directadmin.com/developer/hooks/

 

[swisshuttles]

I'm sorry. I thought this was clear out of pabloapico's post. Thanks for your help anyway. It's appreciated. I will take a look at the database hooks option and maybe create a new one in the near future.

Still strange the Evolution skin has the option to reset the database password for the DA-user. Why is it there? Only reason I can think of is because DA created a database user in the past for the DA-user like my old VPS did. I just did love it.

 

[Stije]

I think you are talking about SSO for phpmyadmin. Where you can login at user level and still see all the databases of the user.

Just do this:

cd /usr/local/directadmin/
./directadmin config-set one_click_pma_login 1
service directadmin restart
cd custombuild
./build update
./build phpmyadmin

 

[zEitEr]

Hello,

If DirectAdmin removed this feature, you can use POST hooks and create the user in database with it:

- https://docs.directadmin.com/developer/hooks/database.html#database-hooks

I am trying to figure out how to create the corresponding database user automatically like it always was.

Found some mentions in change logs:

---

#1.58.0 Released: 2019-08-12

When creating all new databases from a User restore with MySQL 8.0 installed, DA needs to create a system account to put on this DB.

- https://docs.directadmin.com/change...sword-during-restore-on-new-system-db-account

---

#1.659 Released: 2024-02-02

Access to newly created databases will be granted to newly created users (using same user name as database name), but will NOT be granted for the database user that has the same name as DirectAdmin user account.

- https://docs.directadmin.com/changelog/version-1.659.html#database-management-interface-and-api

---

I did not find information on when DirectAdmin dropped this feature though.

 

[swisshuttles]

I think you are talking about SSO for phpmyadmin. Where you can login at user level and still see all the databases of the user.

Just do this:

cd /usr/local/directadmin/
./directadmin config-set one_click_pma_login 1
service directadmin restart
cd custombuild
./build update
./build phpmyadmin

Thank you. This looks like a workaround for the older evolution skin. I didn't find the SSO button in the enhanced skin.

But it is still not exactly what I ment before. When I look at the user accounts page in phpmyadmin (logged in as da-admin) I see an user for every in the past created DA user account. The one_click_pma_login var doesn't do that.

You can even see it in the attached image borrowed from https://docs.directadmin.com/changelog/version-1.659.html#database-management-interface-and-api too. There is an account called admin on top. Which I assume is the directadmin username. You are not allowed to create such an user through the DA interface (without underscore and an extra name).

At this point you have to create such an user manually or by a hook. Really regret that I don't have the old VPS's anymore to investigate this further.