Home arrow PHP arrow Page 3 - Doing More With phpMyAdmin (Part 1)

Locking the Doors - PHP

You might not know this, but you can do a lot more with phpMyAdmin than just create tables and insert records. This first in a two-part series takes a look at some of the other features hidden under the hood of this popular PHP application, explaining how it can be used to secure access to the MySQL server, manage multiple servers, manipulate user privileges, view reports on server activity, and export MySQL data into different formats.

TABLE OF CONTENTS:
  1. Doing More With phpMyAdmin (Part 1)
  2. Start Me Up
  3. Locking the Doors
  4. The More the Merrier
  5. A Perfect State
  6. The Privileged Few
  7. In and Out
  8. Mood Ring
By: Harish Kamath, (c) Melonfire
Rating: starstarstarstarstar / 148
October 27, 2003

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement
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).

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

    blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

PHP ARTICLES

- Hackers Compromise PHP Sites to Launch Attac...
- Red Hat, Zend Form OpenShift PaaS Alliance
- PHP IDE News
- BCD, Zend Extend PHP Partnership
- PHP FAQ Highlight
- PHP Creator Didn't Set Out to Create a Langu...
- PHP Trends Revealed in Zend Study
- PHP: Best Methods for Running Scheduled Jobs
- PHP Array Functions: array_change_key_case
- PHP array_combine Function
- PHP array_chunk Function
- PHP Closures as View Helpers: Lazy-Loading F...
- Using PHP Closures as View Helpers
- PHP File and Operating System Program Execut...
- PHP: Effects of Wrapping Code in Class Const...

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: