Database Security Tips

When developing website or ecommerce solution, important aspect of the design is the database security. The database needs to be protected from any security loopholes. If you’re using MySQL, one way to protect your db is by using MySQL access privileges system. You create specific user type for different user activity.

For example, if you developing website where user can register and add content inside your website. You might have three type of users:

Public : general user who might just select and browse through your site.

Registered : User who can add content to your site.

Admin : User who manage your users and content. Ban the users and some other admin function.

Based on the user types , you create separate MySQL user accounts with the following permission.

Public : SELECT

Customer : SELECT, INSERT, UPDATE

Admin : SELECT, INSERT, UPDATE, DELETE

You could write your configuration file as follow:

DEFINE(‘DB_HOST’,’localhost’);

DEFINE(‘DB_NAME’,’databasename’);

If (isset($user) && ($user==’Admin’)) {

DEFINE(‘DB_USERNAME’,’usernameA’);

DEFINE(‘DB_PASSWORD’,’passwordA’);

} 

elseif (isset($user) && ($user==’Customer’)) {

DEFINE(‘DB_USERNAME’,’usernameB’);

DEFINE(‘DB_PASSWORD’,’passwordB’);

}

else {

DEFINE(‘DB_USERNAME’,’usernameC’);

DEFINE(‘DB_PASSWORD’,’passwordC’);

}

Keep the connection file outside of your web root, in a private folder. This prevents outsider’s access to site. If you don’t have a private folder, then protect the file by using .htaccess

Try not to provide the following permissions to users who connect from website. If you got hacked, you will give lots of fire power to the hacker to do the damage.

PROCESS, FILE, SHUTDOWN, DROP, CREATE and ALTER.

By limiting the users’ permission you can protect your site from any harm. Even if you site has been hacked, the damage could be limited.

Credit: Effortless E-Commerce with Php and MySQL, Larry Ullman.