Changing database and database user architecture

[Croydon]

I noticed there is a feature request in the bugtracker
FS#1448 - one database user name and multiple databases

I think having multiple databases per user is nice, but there should be multiple users per database, too. I would even prefer different rights for db users like "read-only / read-write".

To accomplish this I think databases and database users have to be separated and linked on a n<->m basis instead of 1<->n or n<->1.

I don't know if I have the time to do it but before I start with something like that I'd like to discuss how it's done best.

My prefered feature would be:

• create new database
  - optionally create new database user for this db automatically
  - grant remote access on a per-database basis
• create new users / edit users
  - grant read-write or read-only to chosen database(s)
  - grant or revoke remote access option on a per-user basis

[till]

I think that most admins will use phpmaydmin for such a task.

If we built that functionality in ispconfig too, then we should be careful that it does not get too complicated for users without server much knowledge. Also updates from older ispconfig versions must work automatically so a a script must be integrated that detecs a old style config and migraes it automatically to the new style.

[Croydon]

Originally Posted by till

I think that most admins will use phpmaydmin for such a task.

I don't know if that is enough. Clients are not able to add database users through phpmyadmin, are they?
So they could not add further database users for their databases or use multiple databases with a single username.

Originally Posted by till

If we built that functionality in ispconfig too, then we should be careful that it does not get too complicated for users without server much knowledge. Also updates from older ispconfig versions must work automatically so a a script must be integrated that detecs a old style config and migraes it automatically to the new style.

Changing the database to the new style should be simple sql queries.
- add new table(s)
- copy relevant user data from site_database to the new table(s) via INSERT TO ... SELECT FROM ...
- delete old user columns from the site_database tables

Of course it should be simple to admin.
The current form/list layout should work with the changed database layout as long as there are only single database <-> user pairs.
So a client could switch to "extended database management" if he likes to manage the databases and users separately and/or use the read-only feature.
Once a client has at least one database with user count different than 1 or at least one user with database cout different than 1, he cannot switch back to simple database management.

[codingideas]

Hey,

Sorry to bump this thread but are there any plans to continue this? I was reading "FS#2288 - Importer for data from Plesk control panel" and I came to this thread and noticed that it was a few months old. Lost motivation?

[Croydon]

The database / user management has been changed in 3.0.5.
It is now possible to use the same user for multiple databases.
We decided not to allow multiple users for the same database through ispconfig but you can now choose a default database user and a read-only database user for each database.

[codingideas]

sweet, I have a friend who has 30 or so sites on a plesk server, so I got excited to see a migration for plesk.

so this is something you're going to finish because I can see myself hosting his sites.

next, whm/cpanel migration. The easier you make it to switch the more people you will get coming over. I really like ISPConfig.. a lot.