I was going to put this into my other thread about changing clients for a site but this is a topic of it's own... A database user is linked to a client. A database is linked to a site, and assigned a database user. When we change the client for a site, the client associated with databases users for that site are changed. Assume a database user is assigned to two sites for a client. Now move one of the sites to another client. The database user associated client is changed. But that user is still linked to a database for another site. Does the database user's relationship to one client or another affect their access to databases? I don't think it should, as an individual could be working on multiple databases for different clients, or there could be a single application in multiple client sites that uses the same database user to access a common database. So I was wondering if database users should be associated with a client at all. We can create database users that are not associated with a client - these get a "default" prefix. I've seen questions about this in the forum about how to override that icky prefix. There might not be a reason to associate a db user with a client except to standardize that prefix. If that's the case, then rather than setting the db user's client from the owner of one site to a new owner, I think it might be better to leave the user ID as-is and just remove the client association. Thoughts?
They have to be connected to a client, e.g. to limit the amount of database users a client can add, and when deleting the client, deleting all related records.
Just to clarify, you're talking about Sites > Domains, and changing the client assigned to a domain? (Or maybe you have domain control off and there's a client select list in the website?) It doesn't change the db username/password, nor permissions on the databases, it just changes what each client will see/have access to in the ui. Yes, that's how the ui allows a client to change one db user and not change another, and for applying limits as @Thom mentioned. There is a bug you found there, where in the scenario you created, the database user is assigned to the new client - hence that client controls the password and username for another client's database. With a little more thought due for other potential scenarios, probably changing the client for a domain (I have domain control on) should be prohibited if the client has a website for this domain and another domain, and each website has a database is assigned, and the same database user is assigned to each database. A simple error would suffice along the lines of, "Cannot change the client of this domain as it shares the database user db_username with another domain (domain.name)." And if the same condition applies to changing the website directly when domain control is off, a similar message changing "domain" to "website".
@jesse you see exactly the RI issue that I'm talking about. I think there are valid scenarios for cross-client database users. Yes, this creates hell with regard to limits, deleting accounts, etc, but it is what it is. Ultimately, I think it would be too much to build in all of the housekeeping this implies. Rather, it might be better to have a single page show the relationships of users to clients so that client management can manually make their own decisions. If an account can't be closed because one of their db users is responsible for db in another client, so be it, fix the problem manually. I can also picture a script posted on GitHub where the API is used to manage these relationships: There might be one function that removes the client from a db user, and assigns a different client to that db user. I'd need to look at the API again to see the auth details for this - appropriately there should be two login/auths for this operation. Another function might create a new db user under the new client for each db user to be removed from an existing client. The admin needs to manage their own application DB settings before anything breaks - a change log should help with that. I kinda don't care about the diversity of solutions that could arise from this. I just wanted to bring attention to the anomaly. Yeah, maybe in the near term a simple check and reporting of the anomaly might help someone.
