Activities

September 2011
M T W T F S S
« Aug   Oct »
 1234
567891011
12131415161718
19202122232425
2627282930  

PHP : Advanced usage of PhpMyAdmin

All those who familiarize with PHP are known to PhpMyAdmin UI commonly called “pma” which is the best opensource application to manage/administrate MySQL database.

Also this url is a favourite feed to hackers to enter in application if it is not secured or properly configured. I had seen many such crawlers attack which is being searched for certain known PMA url ( check your apace error logs). Most of the cases (for a biginers they download and extract the PMA on their hosting root) we can access it over the url http://domainname.com/PhpMyAdmin or http://domainname.com/phpMyAdmin-X.X.X.x-english/ which is poorly copied.

Another interesting point is PMA have a test php page which would display the system specific information once “ShowPhpInfo” option enabled. Suppose I installed the php over http://mydomain.com/pma and I can able to access the php info pages over the url http://mydomain.com/pma/phpinfo.php.

1. Scenario 1 Blocking specific user in PMA

I have a setup of Mysql Master and Slave. PMA access already open to Master and Slave as well as if someone likes to verify the replication consistency. The issue is the same mysql users can login to slave PMA with full read/write access and can harm the slave data consistency. So I need to block that specific user.

Open the PMA config file and add the following line.

# vi /var/www/html/opendb/config.inc.php

$cfg['Servers'][$i]['AllowDeny']['order']  = 'deny,allow';
$cfg['Servers'][$i]['AllowDeny']['rules'][] = 'allow % from 127.0.0.1';
$cfg['Servers'][$i]['AllowDeny']['rules'][] = 'deny db_usr from all';

This will deny the user “db_usr” login to PMA. And I have create another read only user to view the slave data by ” grant select on *.* to db_usr@localhost identified by ‘pass’;

1. Scenario 2 Enabling SQL logging

Some peoples are using PMA access to add/modify the tables those are very sensitive and I need to track down certain alter queries which is executed and send alert to certain users.

There is no any way to track the “specific” query and send alert in PMA. But there an option to track all the sql statement executed through the PMA. So that we can identify all the query from the database but the checking will be done manually.

To enabling sql logging you need to create separate database and user account. In PMA words control user accounts
Go to PMA installation folder and switch to “scripts” directory. There is a script file named “create_table.sql”. Delete the first few lines containing database creation and user grant statements since we are created it manually.

[root@slave_host ec2-user]# cd /var/www/html/opendb/scripts/
[root@slave_host scripts]# ls
check_lang.php                  find_unused_messages.sh
convertcfg.pl                   setup.php
create_tables_mysql_4_1_2+.sql  signon.php
create_tables.sql               upgrade.pl
decode_bug.php                  upgrade_tables_mysql_4_1_2+.sql
[root@slave_host scripts]# mysql

mysql> create database pmadb;
mysql>grant all on pmadb.* to pmauser@localhost identified by 'pmapass';
mysql> use pmadb;
mysql > source create_tables.sql

Now you need to update the config. file /var/www/html/pma/config.inc.php. Modify the database credentials accordingly.

$cfg['Servers'][$i]['controluser'] = 'controluser';
$cfg['Servers'][$i]['controlpass'] = 'pass';
$cfg['Servers'][$i]['pmadb'] = 'pmadb';
$cfg['Servers'][$i]['history'] = 'pma_history';

That’s it.
Would be fine once you could restart the web server once after that.

Scenario 3 : Enabling Track changes in PMA
A requirement is to closely watch the table modification that done trough the PMA. After googling it’s lead me that new version has the feature of “change tracking” which would be helpful to identify the table changes and logged the querry executed against.

Unfortunately you may need higher version of PHP ( 5.2.xx) and PMA version 3.22 or higher. It’s very sad that latest Redhat/Centos servers are still not update the PHP version to 5.2 yet. 🙁

Then only difference with enabling sql tracking is that you may need to enable one addition options in config. files as shown below,

  $cfg['Servers'][$i]['tracking'] = 'pma_tracking';




Have you ever forget to delete the script inside the PMA once after the installation ?

The similar link https://184.x.212.218/pma/scripts/setup.php gives a door to hacker to enter in to your data base and hosting area, They can download the “config.inc.php” file and edit the values through UI. Also it’s possible to add more servers preferable to their mysql server where malformed scripts stored and they can call it over the web through the pma. Whenever the apache serve the actual scripts stored in the database, it’s executed at the webserver end. So the hackers can create files (prepared for thier purpose) in the hosting and call it over the pma url. Isn’t it simple one who know the PMA well ?
Haaaa there is always a door ..to knock …

The best security practice for PMA hosting are,

1. Set a Apache basic authentication
2. Can limit the pma access to certain known IP’s or IP ranges. try “all from 172.121.0.0/255.255.0.0
3. Rename the default PMA folder to some other name
4. Serve the PMA over the https url
5. Delete the scripts folder from install directory.
6. Set PMA file permission to read only. Eg. #chmod -R 744

See one the open PMA door

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>