MySQL: self-administration for multiple users

MySQL

My MySQL users have rights to administer their own databases, including create/drop database. I do not have a shared phpMyAdmin installation, however I do let users install it themselves. Most of the users do not need it and they end up mailing me to create databases for them. That is not a problem for me but I want to give the users more control and at the same time keep things simple.

Users have access to a simple web based control panel and I want to add a GUI where they can create, drop, rename their databases and do one or two more basic MySQL things like view stats. Most of the users do not need the capabilities of phpMyAdmin, just the ability to create and drop databases.

Which MySQL user should do these actions like create/drop etc?
Should I:

  1. Create an additional admin user which has grants to create/drop databases for all users and set right grants on them. I would not let users create/drop databases by themselves although allowing create/drop would probably not break anything.

  2. Let the user do it with his own MySQL user? That would mean the user will need to enter his MySQL password every time he wants to create/drop/whatever. I could save his password as plain text in his account table (arghhhh… no way).

I like the idea of letting the user do everything with his own MySQL user and password because he can only break his own things and it makes my application more secure at the same time.

However, I do not want to make it complicated by forcing users to remember their MySQL password. On the other hand I do not want to save clear text passwords for their MySQL account. This approach is broken because user could change his password with SET PASSWORD = PASSWORD(?) and my clear text password would not be up-to-date and web GUI would no longer work.

The first solution is surely the way to go but I still have a feeling there is perhaps a third solution I have missed. Is there something similar used by some big webserver adminstration tools? Or are they also using a dedicated admin user for this stuff?

Best Answer

I think using a MySQL gui tool like SQLyog would solve your problem. It has User manager tool where you can set privilege to different users.

enter image description here