How to use phpmyadmin to secure mysql database

Its been a while since I’ve wrote a full fledged blog. I should really be writing 2 to 3 articles in order to make up. But making sure to make it a quality post. Since my blog has really been stagnating. I only get a minimum of 93 views each day and a maximum of 383 views each day. Haven’t got any higher amount of views.

So enough with the little blabber. Let’s get to the main point of this article. This time I’m going to show you how you can use phpmyadmin to configure your mysql database to have more security.

I know that most of us, especially the beginners often use the root account when developing web applications. Its actually fine to use the root account when in a development environment. But once you deploy the application, it would be wise to create another account that has privileges which are only applicable to the actions that the users are going to perform.

Go ahead and launch Wampserver and access phpmyadmin on your browser. Yes, its totally fine to use IE here but I don’t really recommend it. As you can see from the screenshot below, the root user has actually all the privileges. Pretty much every feature, functionality that is embedded in the mysql database can be performed and used by this user. If you examine the screenshot further, you will notice that the configuration is not in any way secure. Passwords hasn’t been setup for most of the accounts, only the root account accessing from the localhost has a password.

image


Creating a new user

To address the issue, we have to create a new user. And limit its privileges.
The first section is the login information. Here we define the username, password, and the host. The host is pretty much the location of the user or the access point. It might be local, any, or specified host. Localhost means, the user is accessing the database from the computer where it is installed.

image

 

Assigning Database

Next, is the database for the user. You can pretty much ditch this one and just select none. But doesn’t mean that if you select none, the user won’t actually have any database to play with.

image

 

Setting up privileges

Now, for the privileges. Most of the options are self-explanatory. But we’ll go ahead and try the most obvious ones. Under the data group box. Just check the select checkbox and then click go.

image

 

Database configuration file

And then go ahead and write a database configuration file:

<?php
include('ez_sql/shared/ez_sql_core.php');
include('ez_sql/mysql/ez_sql_mysql.php');

$db = new ezSQL_mysql('reader', '1234', 'orgbpls', 'localhost');
?>

I’m using a php library called ezsql for manipulating databases. Just make use of the usual way of how you connect to mysql database if you don’t  use the same library as I am.

 

Selector file

Next, create a testing folder, then create a new php file called selector.php. Which will contain the following code:

<?php
require_once('reader_config.php');

$select = $db->get_results("SELECT * FROM sys_users");

if(!empty($select)){
?>
<table border="1">
<thead>
	<tr>
		<th>User</th>
		<th>Password</th>
	</tr>
</thead>
<tbody>
	<?php foreach($select as $s){ ?>
	<tr>
		<td><?php echo $s->UserID; ?></td>
		<td><?php echo $s->strPassword; ?></td>
	</tr>
	<?php } ?>
</tbody>
</table>
<?php } ?>

In the above code, we just did a normal select query. Which yields us the following output:

image

Yup the hashed passwords are pretty much the same. Since I used 1234 on all of them. But I’m telling you not to use that dumb password once you’re going to deploy your application. Better yet generate a strong random password if you can’t think of anything secure.

As you can see, the query executed without problems. That’s because we checked the select action a while ago.

Updater file

This time, create another file called updater.php. And yes, were going to perform an update query using this file.

<?php
require_once('reader_config.php');
$db->query("UPDATE sys_users SET strPassword='1234' WHERE UserID='badmin'");
?>

Go ahead, and access that php file in your browser. And see what happens:

image

It issues an error saying that update command is denied to the user. I can’t think of anything that  an attacker could do if this is the case. Since the user that’s been set to manipulate the database doesn’t have enough privileges to perform the action.

 

Other settings

There are more settings that you can check out and play around with. One of those is the resource limits. Which lets you setup the maximum number of queries that can be performed on an hourly basis, and other interesting stuff.

image

There’s also the structure settings. Most of the time, you can uncheck these guys if the users are just going to perform CRUD operations on the database. These are pretty much admin stuff.

image

 

Conclusion

You have learned how to setup user privileges using phpmyadmin. Making applications secure is a must for developers. Even if you think there might be no hackers that might try to hack your system. Its still important to protect the system that you have created.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s