Doing More With phpMyAdmin (Part 1) - Locking the Doors

(Page 3 of 8 )

I've already written about the simplest authentication mechanism in phpMyAdmin - the "config" option, wherein the user name and password to access the MySQL server are stored in the phpMyAdmin configuration file. At noted previously, this method is not very secure, due to the risk of an unauthorized user gaining access to the configuration file.

In order to control access to the application and thereby the RDBMS, phpMyAdmin supports two alternative mechanisms: cookie-based authentication and HTTP-based authentication. Both are pretty straightforward, and simple to set up.

In order to use either of these authentication mechanisms, you need to first create a special MySQL user (I'll call this user "admin" in the examples that follow) with restricted permissions on the MySQL grant tables. phpMyAdmin's documentation assists in this process by listing the queries needed:

GRANT USAGE ON mysql.* TO admin@localhost IDENTIFIED BY "j823kfg2ld";

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 admin@localhost;

GRANT SELECT ON mysql.db TO admin@localhost;

GRANT SELECT ON mysql.host TO admin@localhost;

GRANT SELECT (Host, Db, User, Table_name, Table_priv, Column_priv)

        ON mysql.tables_priv TO admin@localhost;

This set of queries creates a MySQL "admin" user and gives that user the ability to use and perform SELECT queries on all tables in the "mysql" database.

Next, you need to tell phpMyAdmin about the new user. Open the configuration file, locate the "controluser" and "controlpass" variables for your server, and update the server configuration block so it looks like this:

$cfg['Servers'][$i]['host'] = 'localhost'; // MySQL hostname
$cfg['Servers'][$i]['port'] = ''; // MySQL port - leaveblank for default port
$cfg['Servers'][$i]['socket'] = ''; // Path to the socket -leave blank for default socket
$cfg['Servers'][$i]['auth_type'] = 'config'; // Authentication method
$cfg['Servers'][$i]['user'] = ''; // MySQL user
$cfg['Servers'][$i]['password'] = ''; // MySQL password
$cfg['Servers'][$i]['controluser'] = 'admin'; // MySQL controluser settings
$cfg['Servers'][$i]['controlpass'] = 'j823kfg2ld'; // access to thegrant tables

Notice that I have removed the user name and password previously set for the "user" and "password" parameters.

Now that the special control user has been created, you can choose to use either cookie- or HTTP-based authentication.

1. To use cookie-based authentication, change the value of the "auth_type" parameter in $cfg['Servers'] from "config" to "cookie", and also set a value for the special $cfg['blowfish_secret'] variable. Updated, your configuration should now look like this:
   
   $cfg['blowfish_secret'] = 'abracadabra';
   
   $cfg['Servers'][$i]['host'] = 'localhost'; // MySQL hostname
   $cfg['Servers'][$i]['port'] = ''; // MySQL port - leaveblank for default port
   $cfg['Servers'][$i]['socket'] = ''; // Path to the socket -leave blank for default socket
   $cfg['Servers'][$i]['auth_type'] = 'cookie'; // Authentication method
   $cfg['Servers'][$i]['user'] = ''; // MySQL user
   $cfg['Servers'][$i]['password'] = ''; // MySQL password
   $cfg['Servers'][$i]['controluser'] = 'admin'; // MySQL controluser settings
   $cfg['Servers'][$i]['controlpass'] = 'j823kfg2ld'; // access to thegrant tables
   
   Now, open phpMyAdmin in a new window and try accessing the server - you should see a neat little username and password prompt.
   
   Enter your MySQL user name and password - not the "admin" user created above, but the one you normally use for development. You should now be able to access all the databases that you have privileges for.
   
   
2. However, the value of "auth_type" is the same as the listed in the previous section on using the cookie-based authentication - $cfg['Servers'][$i]['auth_type'] = 'cookie'; // Authentication method
   
   $cfg['Servers'][$i]['host'] = 'localhost'; // MySQL hostname
   $cfg['Servers'][$i]['port'] = ''; // MySQL port - leaveblank for default port
   $cfg['Servers'][$i]['socket'] = ''; // Path to the socket -leave blank for default socket
   $cfg['Servers'][$i]['auth_type'] = 'cookie'; // Authentication method
   $cfg['Servers'][$i]['user'] = ''; // MySQL user
   $cfg['Servers'][$i]['password'] = ''; // MySQL password
   $cfg['Servers'][$i]['controluser'] = 'admin'; // MySQL controluser settings
   $cfg['Servers'][$i]['controlpass'] = 'j823kfg2ld'; // access to thegrant tables
   
   and try to access the phpMyAdmin application. This time around, you should see the browser-generated password prompt typical of HTTP based authentication, as seen here.
   
   Enter your MySQL user name and password - not the "admin" user created above, but the one you normally use for development. You should now be able to access all the databases that you have privileges for.
   
   Now, I'm sure you're wondering - why did you even bother creating that "admin" user if you never use its credentials anywhere? The answer is both simple and elegant - phpMyAdmin uses that "admin" account to check whether the user/password credentials you provide to the cookie- or HTTP-based authentication system are valid. Since the application only requires read access to the MySQL grant tables to validate this information, only restricted privileges are assigned to the "admin" user.
   
   Using this type of two-tiered authentication mechanism not only leaves the task of managing and authenticating MySQL users where it should be - with MySQL - but it also reduces the risk of unauthorized database manipulation by users who get their hands on the phpMyAdmin configuration file. Since the file now only contains credentials for the limited-access "admin" user, a hacker won't be able to do much with it (other than read the MySQL grant tables, itself not such a great security risk since MySQL automatically encrypts grant table passwords).

   Next: The More the Merrier >>
    
   

   More PHP Articles
   More By Harish Kamath, (c) Melonfire

   
   

   Comments On: Doing More With phpMyAdmin (Part 1)

   · Thanks,Your workout helped me to establish mysql authentication for phpmyadmin.I...    · I got solution for my problemI have hard coded IP Address in...    · This article is one that everyone who is learning about using phpMyadmin should...   >>> Post your comment now!