"login" to phpMyAdmin via different users

Question

I want a non-techie (marketing/sales) to have access to the data I manage regularly via phpMyAdmin:

1. without any ability to alter/insert/delete
2. without me having to build a special database-viewer-app

I have created a new User with limited privileges, but I don't want My non-techie to have to use a command prompt and learn mysql commands.

Optimally I want him to be able to go to phpmyadmin UI, but only have the abilities allowed (in this case read-only).

Do I need to set up a separate phpmyadmin where the controlling user is this weaker user? Or is there a special "log in as a specific user" interface I've missed in the documentation?

UPDATE:

I've seen login screens as googletorp suggested when i used pmas auto-installed by hosts but never on the out-of-the-box versions i've installed.

I tried commenting out $cfg['Servers'][$i]['user'], and $cfg['Servers'][$i]['pass'] googletorp suggested - that perhaps it would present me with a login screen. But instead I get error #1045:

#1045 - Access denied for user 'root'@'localhost' (using password: NO)

Then I thought to perhaps comment out the $cfg['Servers'][$i]['controluser'] and $cfg['Servers'][$i]['controlpass']. But the comments say they're already supposed to be a read-only user.

hm...

Answer 1

phpmyadmin is just an interface upon mysql. You can only do what you are allowed to do based on the user you use to login with. In the end phpmyadmin runs queries for you with the user credentials you login with and can't bypass mysql permissions.

So if you create a user with limited access, he can use the existing phpmyadmin.

You can try it out yourself and login with the user you created and see how the interface looks with limited permissions.

Update:

Of all the the phpmyadmin installs I've tried out among different hosting providers etc looks like this:

Now it sees from your other comment, that you have disabled this by setting the user in the config file. I haven't tested this as I don't use phpmyadmin locally anymore, but I would guess that if you remove the mysql credentials from the config file, that you will be presented with the above login screen instead. Allowing to use different users with different permissions.

Answer 2

If you have a default installation of phpMyAdmin,you are using the 'config' authentication, and your name and password are stored in the config.inc.php file. Since you are not seeing a log-in screen, you probably have $cfg['Servers'][$i]['auth_type'] = 'config' in there, if you check. You will also see your name and password. Something like $cfg['Servers'][$i]['user'] = 'root'; $cfg['Servers'][$i]['password'] = ''; $cfg['Servers'][$i]['AllowNoPassword'] = true;

To have the log in screen appear, you want to change to use the 'cookie' authentication. You have to make two changes to the the config.inc.php file in order for this to happen, and a third change is strongly recommended for security. You can see http://www.phpmyadmin.net/documentation/#faqmultiuser for more information, if you want to know your other options.

I am going to list the changes in the order they occur in the file.

1. (Recommended) Change the blowfish_secret to any random string. As the file comments say, this string is used to encrypt your password in cookie based authentication (which is what you are about to change to).

   $cfg['blowfish_secret'] = 'random string';
   

2. Around line 19, change the authentication type to cookie

   $cfg['Servers'][$i]['auth_type']            = 'cookie'; /*DEFAULT: 'config'
   

3. Delete the three lines that are storing your password. (If you are still using the user 'root' with password '', you probably should change that. While you are logged in phpMyAdmin as root, you can change/set the password.)

Save the file, and from now on, you will get the log-in screen and have to use a username and password.

Answer 3

I'm not quite understand what you asking for...

If you created mysql user in phpmyadmin, then your co-worker can make only those sql operations which you allowed him to use.

What do you mean by this:

but I don't want My non-techie to have to use a command prompt and learn mysql commands.

you have phpmyadmin on your local computer on which you have mysql installed? In any way he even can't do anything with command promt, ofcourse if your co-worker doesn't know any other user logins which has higher permissions.. If you worried about hacking mysql password, i doubt that he will manage to do this. The only risk is that he can delete your database files. But if you created user for him (in whatever Operation System you using) who permissions doesn't allow to access those database files then it's you safe..

Answer 4

As an alternative, you can use Oracle SQL Developer + MySQL JDBC Driver