Configuring phpMyAdmin for multiple users

NOTE

Currently I’m re-checking this tutorial with the latest versions of phpMyAdmin & MySQL. All #1045 errors appear to be Authentication Errors where the password for the user is not set correctly. Under investigation.

Preface

This tutorial is for those who need to setup phpMyAdmin for multiple users so they could each see their own databases when they logged in but can’t see anyone elses databases. They also needed to be able to drop their own databases should they need to (responsibility lies with the end user if they drop the wrong one though).

NOTE: If you are using nginx or cherokee as your webserver, like I am, then don’t install phpMyAdmin from the repositories as this pulls in Apache as well :(

For this howto I’m going to use version 3.2.1-rc1

Prerequisites

  1. A webserver is installed and working (Apache, Lighttpd, nginx or Cherokee)
  2. PHP is installed and working with the webserver.
  3. PHP Modules required ( php5-gd, php5-mysql, php5-mcrypt )
  4. MySQL is installed, working and you know the root password.
  5. You have sudo access to create/edit files in non-user directories.

Download phpMyAdmin and install …

cd /usr/share
sudo wget http://prdownloads.sourceforge.net/phpmyadmin/phpMyAdmin-3.2.1-rc1-all-languages.tar.gz -O - | sudo tar -zxf -
sudo mv phpMyAdmin-3.2.1-rc1-all-languages phpmyadmin
sudo cp phpmyadmin/config.sample.inc.php phpmyadmin/config.inc.php

OK so now it is downloaded and installed in /usr/share/phpmyadmin

Configure phpMyAdmin …

nginx

sudo ln -s /usr/share/phpmyadmin /var/www/nginx-default/phpmyadmin

Apache

echo "alias /phpmyadmin /usr/share/phpmyadmin" | sudo tee -a /etc/apache2/conf.d/phpmyadmin.conf
sudo invoke-rc.d apache2 reload

Edit /usr/share/phpmyadmin/config.inc.php with your favourite editor and fill in the blowfish_secret line.

e.g.

$cfg['blowfish_secret'] = 'UltraSecretPassphrase';

Access via http://<webservername>/phpmyadmin/ and login with the MySQL root username and password.

Click on the SQL tab in the righthand pane and in the textbox labeled Run SQL query/queries on server “localhost” paste the following SQL statements and change pmapassword to something more secure., then click the Go button.

CREATE USER 'pma'@'localhost' IDENTIFIED BY 'pmapassword';

GRANT USAGE ON mysql.* TO 'pma'@'localhost' IDENTIFIED BY 'pmapassword';

GRANT SELECT (Host, User, Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv, Reload_priv, Shutdown_priv, Process_priv, File_priv, Grant_priv, References_priv, Index_priv, Alter_priv, Show_db_priv, Super_priv, Create_tmp_table_priv, Lock_tables_priv, Execute_priv, Repl_slave_priv, Repl_client_priv) ON mysql.user TO 'pma'@'localhost';
GRANT SELECT ON mysql.db TO 'pma'@'localhost';
GRANT SELECT ON mysql.host TO 'pma'@'localhost';
GRANT SELECT (Host, Db, User, Table_name, Table_priv, Column_priv) ON mysql.tables_priv TO 'pma'@'localhost';

GRANT SELECT (Host, User, Select_priv, Insert_priv, Update_priv, Delete_priv,
Create_priv, Drop_priv, Reload_priv, Shutdown_priv, Process_priv, File_priv, Grant_priv, References_priv, Index_priv, Alter_priv, Show_db_priv, Super_priv, Create_tmp_table_priv, Lock_tables_priv, Execute_priv, Repl_slave_priv, Repl_client_priv) ON mysql.user TO 'pma'@'localhost';

GRANT SELECT ON mysql.db TO 'pma'@'localhost';

GRANT SELECT ON mysql.host TO 'pma'@'localhost';

GRANT SELECT (Host, Db, User, Table_name, Table_priv, Column_priv) ON mysql.tables_priv TO 'pma'@'localhost';

Now we need to modify the configuration of phpMyAdmin to support multiple unique users.
Once again edit /usr/share/phpmyadmin/config.inc.php with your favourite editor and complete the following changes.

  1. Remove the // in front of these 2 lines and fill in the password chosen above
    $cfg['Servers'][$i]['controluser'] = 'pma';
    $cfg['Servers'][$i]['controlpass'] = 'pmapassword';
  2. Add the following line after the lines above
    $cfg['Servers'][$i]['hide_db'] = 'information_schema';
  3. Add the following line before the end ?>
    $cfg['AllowUserDropDatabase'] = true;
  4. Change the Server extension line from mysql to mysqli

    $cfg['Servers'][$i]['extension'] = 'mysqli';

Now phpMyAdmin is correctly configured for multiple users.

Final thoughts

One last bit of advice I will give is when creating users through phpMyAdmin, be sure to select the “Grant all privileges on wildcard name (username\_%)” option, and don’t forget to click the “Reload Priviledges” link, after creating the user, to ensure MySQL and phpMyAdmin are in sync. What this basically does is give administrative privileges to users for the databases that are prefixed with their username, which is a very handy function in a multi user environment.

Updates:

31/07/2009 – Updated PHPMyAdmin version fro 3.1.5 to 3.2.1-rc1
19/05/2009 – Updated PHPMyAdmin version fro 3.1.0-rc1 to 3.1.5

11 thoughts on “Configuring phpMyAdmin for multiple users

  1. Pingback: phpmyadmin - Separate account for each users

  2. #1045 – Access denied for user ‘cotemig’@’localhost’ (using password: YES)

    How could i fix it, please?

    I did all the same…

  3. This isn’t working. I have tried to modify config in many different locations on Ubuntu system and changed everything required but this just doesnt work. If there is something someone knows or could help with this would be much appreciated.

    Thanks…

  4. Pingback: Give non-root user access to log into phpMyAdmin | Life with Linux

  5. I followed the above steps, then created a new user and granted it all privileges on a specific new DB, but when I go to log into phpMyAdmin as that new user, I still get #1045 Cannot log in to the MySQL server.

    Any ideas? Thanks.

  6. Pingback: How to configure phpMyAdmin for multiple users – each with access to their database only

  7. Hi,

    I followed exactly the same steps above on my fresh install of MySQL 5.5.24 running of Fedora 17.

    After done as above when I created a new user following the “Final Thoughts” above through phpMyAdmin as root user. When I use this new user to login phpMyAdmin, it still shows all the DBs in the system.
    If I use pma user to login then only it hides all databases.

    Do I need to create every user like you explain creating the pma user above? And also add it’s credentials to the config file?

    Thanks,
    Sid

  8. Worked like a charm after I made only slight changes to accommodate Fedora Core 16 directory structure and naming!

    Totally cool!!!

  9. Do I have to Run the SQL code snippet above for each user that I want to give login access to phpmyadmin for their particular database? It would appear so, because if I create my users and associate them with their databases with privileges then I have to run that SQL for each user BUT pma is replaced with the user and the pmapassword is replaced with the user password. Comments?

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>