BLACKBELT

Database MySQL

Authored by Corey Ballou

Securing MySQL Databases via GRANT Permissions

I’m of the personal belief that you should never grant all MySQL permissions to a user other than root. When deploying and setting up websites, one of the first things I do is create a per-environment database user with access solely to their perspective database. In the event an attacker gains access to your server, they’ll find your database config file. Luckily for you, they only have credentials to a user with restrictive permissions on a single database. Likewise, if your application has a security flaw, you can minimize damage by providing a restricted set of user privileges.


CREATE DATABASE YOUR_DATABASE;

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, INDEX 
ON 'YOUR_DATABASE'.* 
TO 'YOUR_USER'@localhost 
IDENTIFIED BY 'YOUR_PASSWORD';

FLUSH PRIVILEGES;

This solution is not fool-proof, but it’s a step in the right direction. Some people go one step further and split their READ/WRITEs across two separate MySQL user accounts. The permissions above are also still fairly lax as they allow for both DROP and CREATE table. It’s up to you to decide what the minimal set of necessary permissions are, but I urge you to use the above as a guideline and avoid using GRANT ALL PRIVILEGES.

Author: Corey Ballou

Corey Ballou is the CEO of POP.co. Whether you're a student, young professional, entrepreneur, startup, or small business, you can be up and online fast with your own custom domain, email, and webpage on POP. Corey is a professional PHP developer by trade, specializing in custom web applications development for startups, small businesses, and agencies. Follow Corey on Twitter @cballou.