How do I add phpMyAdmin users?

[dmphotography]

I'm wondering how I can add new users to phpMyAdmin so that they can create their own databases, delete them, etc. but yet still not have access to any databases contained in any other user accounts on the same SQL server.

I solved this once by just creating a whole different MySQL server, which in one way seems like the easy way, but doesn't teach me crap about how to do it correctly. So what's the method to doing this?

Thanks in advance!

[Izzy]

Login as root and under Privileges>Add a new user.

The next window provides a place for login, database and privileges information.

Leave the default None for Database for user.

Under Global privileges when you mouse over an item a tool tip pops up with what each item allows when ticked - you normally wouldn't grant any Administration privileges but as root you can grant what ever you want your new users to able to do, even allocate Resource limits.

The user then logs in with their own username and password with the privileges you have granted and that is all they can do.

You can test out what they can and can't do or see by logging in using the newly created user/pass not your usual root credentials.

[dmphotography]

Hey Izzy,

The problem I'm having is I can't find anywhere in the phpMyAdmin documentation the explanation for the permissions for a user. When I create a new user, I've tried selecting everything BUT the admin section and when I log in with the new users, I can still see the databases created in the root user's account. I want to HIDE those, so the only databases that can be seen, managed, created, etc. are only the ones created by the new users and everything created or viewable in the root user's account is hidden from the new user.

The problem I'm having is either I can see them all in the second user's account or I manage to hide them, but can't create any new databases.

The only way I succeeded at this was by selecting under Database for user, "Grant all privileges on wildcard name", but this creates databases with the username as the prefix and I would rather not have that if possible.

I wish the phpMyAdmin documentation explained this, but it's all in regards to configuring phpMyAdmin and not hardly anything to do with using it.

[Izzy]

Looks like it may be configured in that damn config-inc.php again, see if this site helps:
http://www.electrictoolbox.com/hide-databases-phpmyadmin/

Edit.
The above method hides even from root but there is a clue.

What I did find out is, if you set any sort of Global privileges to a user then all the databases will be visible to that user because a Global privilege is effective on any database in the list.

You would need to modify the MySQL database permissions to prevent them access to particular databases. Note also that when you do limit the databases a user has access to, phpMyAdmin won't show them in the list of databases.

I had a bit of a play with this but sorry ran out of time before I could get a handle on it - may try again when I have more time to experiment.

I did hide information_schema as I could not see any use for it also test databases.

At this point I couldn't see a way round what you want to do other than a user name database prefix as defined in this:

4.5 Is it possible to let users create their own databases?

Starting with 2.2.5, in the user management page, you can enter a wildcard database name for a user (for example "joe%"), and put the privileges you want. For example, adding SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER would let a user create/manage his/her database(s)."

6.20 In MySQL 4, I see a lot of databases which are not mine, and cannot access them.

Upgrading to MySQL 4 usually gives users those global privileges: CREATE TEMPORARY TABLES, SHOW DATABASES, LOCK TABLES. Those privileges also enable users to see all the database names. See this bug report.

So if your users do not need those privileges, you can remove them and their databases list will shorten.

The above was from the docs:
http://www.phpmyadmin.net/documentation/

You may also be able to find a third party MySQL user manager with a GUI and hopefully free.
Search results

Alternative phpMyAdmin

Also some hosting control panels may be of use for what you want to do.


Sorry I could not be more help at this point in time.

[Izzy]

I posted another reply rather than add to my previous already over sized post.

I had time to take another look at this and there is no way to do this in PMA without creating a wild card database for a user that I could find, which for ease of recognition, a username prefix is used but this prefix can be anything you like as long as it ends with a percent % character.

When the db is created in PMA using the user's name then it adds 3 dots after the name which can be annoying and as yet I have not found a way of correcting this but the user can delete those dots or substitute an underscore or any other character they wish when creating a database from their PMA home page.

Also experiment with creating multiple wild card databases for a single user but they won't create a drop menu in the Create database entry in the user's PMA home page as one would expect.

Also adding this line to the config-inc.php to hide the information_schema database
$cfg['Servers'][$i]['hide_db'] = 'information_schema';
is all that you need to do to hide any of the system databases from your users as this is the only db that remains and can be seen by all.

It is only an information db and can't be altered by a user anyway but hiding it is wise as it contains info that would be normally only be useful to the super user root.

When creating a new user you must not include a single item from the Global privileges as this will immediately invoke all the available databases in the list.

Check before pressing the final Go button that there are no boxes ticked and check again after creating the user and remove if any have been inserted automatically.

You can Check all Database specific privileges for your user and it's db% or you can be selective from the available tick box list.

As yet I can find no way for a user to be able to delete (Drop) their own databases from within PMA, they can drop tables etc. but there is no Drop menu item displayed for their own databases.

A request to the db admin (root) seems, as yet, the only way round this which would mean a support request to drop a user's db.

As yet user management in PMA seems a bit clunky which means it has the potential to improve in later versions, one hopes.

[dmphotography]

Hey Izzy,

I can't thank you enough for taking the time and your expertise in researching this for me. I imagine you spent a substantial amount of time finding that info out, which is a shame because it should be on phpMyAdmin's website, but isn't. For a SQL database manager that's so popular and so widely used, you would think they'd have more extensive information regarding their product and even a support forum to ask such questions, but they don't.

Thanks again for your help!

[Izzy]

Your welcome.

There was a modicum of self interest there too as I was curious about this issue and had intended to dig deeper under the bonnet at PMA's 1920s engine anyway.

BTW I failed to mention above that I deleted the 2 users any which had no detrimental effect, just in case they were interfering with the privileges being set for a new user I created.

Good luck with it.