MySQL General Security Tips

Monday May 6th 2013 by Rob Gravelle

Ease of use and speedy performance are just two of the many features that make MySQL one of the most popular databases in use today. Unfortunately, a high adoption rate makes MySQL a target for many malicious individuals and organizations. In today's article, Rob Gravelle highlights a few simple but effective ways of beefing up your database security to defend against both local and remote attacks.

Ease of use and speedy performance are just two of the many features that make MySQL one of the most popular databases in use today.  Unfortunately, a high adoption rate makes MySQL a target for many malicious individuals and organizations.   The inexperienced Database Administrator (DBA) may not realize that the default installation of MySQL is not nearly hardened enough to protect from attack.  In particular, the empty root password and the potential vulnerability to buffer overflow attacks are easy targets for hackers.   In today’s article, we’ll be looking at a few simple but highly effective ways of beefing up your database security to defend against both local and remote attacks.

General Security Activities

As a database Administrator (DBA), your security-related tasks should revolve around the following three activities:

  • Patching
  • Restricting access
  • Preventing meaningful info gathering

The rest of this article deals with the specifics of the three activities above with the emphasis being on restricting access to the network, OS, and database server.

Security Patching

Despite everyone’s best efforts to protect their data, from time to time a malicious individual finds a vulnerability that can be exploited.  When that happens, it’s only after an attack of sufficient magnitude is carried out that future threats can be thwarted.  The database vendor will examine the bug that caused the problem and create a patch that can be installed over your existing database installation to close the gap in the armor.   

One of the best places to find out about security patches for MySQL is Oracle’s Security Page.  Here is the latest security patch at the time of this writing.  You should probably keep your ear to the ground as well as visit MySQL forums from time to time; they’re usually one of the first places that the alarm bell will sound.

Preventing Access to the System

There are four main resources that you want to defend against unwanted visitors:

  • Network access
  • Direct database access
  • Access to backups
  • OS access, which includes data and log files

Each comes with its own set of challenges and solutions:

Network Access

You’ll definitely want to consider encrypting the network connection between the server and clients if your LAN or WAN is not secure.  Should an unauthorized user somehow gain access to a privileged user account (i.e. root), they can use a tool like tcpdump to sniff out a network stream and filter for packets that go to MySQL. Those packets will contain both queries and data!

By default, MySQL is configured with maximum performance in mind, so connections are unencrypted unless you manually setup secure connections. This employs the Secure Sockets Layer (SSL) protocol to encrypt all data sent between MySQL clients and the server.

MySQL enables encryption on a per-connection basis so you can use an unencrypted connection or a secure encrypted SSL connection according the requirements of each application.

Controlling Database Access

One of the first potential entry points for a hacker is the root account.  Therefore, it’s vitally important that you reset the password and ideally rename the ID as well. As mentioned in my Top 10 MySQL Best Practices article,

...the first thing you should do with a clean MySQL install is set a password for the root user.

$ mysqladmin -u root password NEWPASSWORD

Even better, once you've set the password, change the name of the "root" user to something else. A hacker on a MySQL server will likely target the root, both for its superuser status and because it is a known user. By changing the name of the root user, you make it all the more difficult for hackers to succeed using a brute-force attack. Use the following series of commands to rename the "root" user:

mysql> RENAME USER root TO new_user;

Beyond that, keeping the number of “superusers” down to an absolute minimum is crucial to keeping control over your database.  Take it from me, too many cooks ruins the broth.  In fact, where crucial data is concerned, your whole pantry can be usurped from you if you’re not careful! 

A Cautionary Tale, by Rob Gravelle (part-time landlord)

Say that you have a lock box on an apartment that is undergoing several renovations.  How long would you think that the contents of the apartments would be safe with multiple parties having access, day or night?  The answer, it turns out, is about six weeks.  Where valuable data is concerned, a breach would probably occur in a lot less time than that!

We now return to our regularly-scheduled article...

There is an account type that DBAs love; they’re called READ-ONLY users.  It’s one of the best account types because users who possess it have virtually no means of wreaking havoc on the database or its data.  Often times, users will make up any number of reasons why they need write privileges.  A good litmus test for determining the true value of a particular right is to simply remove it at some point and see if anyone complains.  Chances are good that nothing will happen.  In my experience, there are only one or two users who crave power.  The rest simply don’t use the extra privileges.  Now, I’m not advocating tricking your clients by turning off privileges willy-nilly, but it pays to properly analyze users’ work patterns – something that’s quite easily done with a little quality auditing.

Access to Backups

You can secure your database like Fort Knox, but that can only go so far if you neglect to protect your backups.  Ideally, you want to store them off-site so that a catastrophe to the primary site won’t affect the backups.  Moreover, all the steps in protecting your database server network apply to the backup box as well.  There are some excellent software modules that can encrypt your data so that even in the unlikely event that the backup files were to fall into the wrong hands, their contents would be useless to the thief.

Here is an encryption function written in PHP that utilizes the “rijndael-256” module:

public function encrypt( $msg, $k, $base64 = false ) {
 if ( ! $td = mcrypt_module_open('rijndael-256', '', 'ctr', '') ) return false;
 $msg = serialize($msg);
 $iv = mcrypt_create_iv(32, MCRYPT_RAND); 
 if ( mcrypt_generic_init($td, $k, $iv) !== 0 ) return false;
 $msg = mcrypt_generic($td, $msg); # encrypt
 $msg = $iv . $msg; # prepend iv
 $mac = $this->pbkdf2($msg, $k, 1000, 32); # create mac
 $msg .= $mac; # append mac
 mcrypt_generic_deinit($td); # clear buffers
 mcrypt_module_close($td); # close cipher module
 if ( $base64 ) $msg = base64_encode($msg); 
 return $msg; 


Limiting OS Access

The local operating system can be safeguarded using a combination of authentication, firewall, and other installed programs such as virus scanners.  Other access control mechanisms include Role & Password Policies, administered using Group Policy Objects (GPO’s), as well as filtering access to specific Objects.

Oracle has an excellent online resource on the subject.


Today’s article only scratched the surface in highlighting some of the myriad of ways in which to protect your MySQL-hosted data.  In a never-ending battle, one cannot rest on one’s laurels and assume that once steps have been taken, the database becomes forever impervious to attacks.  Quite to the contrary!  One must always remain vigilant and keep one’s self au courant of the latest threats and countermeasures.  Remember, you don’t have to have the most secure database in the world to thwart would-be attackers; you only need to make it sufficiently laborious enough to breach that hackers will move on to an easier target.

See all articles by Rob Gravelle

Mobile Site | Full Site