Skip to content

# Some MySQL security tips

MySQL Security July 28, 2014

This is a brief list of security tips for MySQL. It is by no means complete.

- Follow the sudo example. Don’t let all you DBAs and Ops have the password for the root account. Have each and every one of them have their own personal super-duper account, with their own personal and private password. This makes it so easy when someone leaves the company. No need to change passwords, just to remove the employee’s account.
- Block root. Either remove it completely or forbid it from logging in. Yes, there’s a way hack in MySQL to have a valid account blocked from logging in. One way of making this happen is via common_schema‘s sql_accounts. Here’s how to block root account using common_schema:

>     mysql> CALL common_schema.eval("SELECT sql_block_account FROM sql_accounts WHERE USER = 'root'");

- Make lots of small users. Give nagios its own user. Give collectd its own user. Give orchestrator its own user. Give innotop its own user. Give whatever its own user. Yes, it’s more users to create, but you get to have each user as limited in privileges as possible, and you don’t get to wonder why your heartbeat script has SUPER, LOCK and SHUTDOWN privileges.
- Verify: set @@old_passwords=0; before setting a new password. Make sure your configuration does not specify old_passwords = 1. There is no reason to use “old passwords“. In fact, a 5.6 client will refuse connecting with an “old password”.
- Give no access to mysql.*. No one should be tampering directly with the mysql system tables.
- Run oak-security-audit or, if you have common_schema installed (you mean you don’t?), just CALL security_audit(); I can (almost) guarantee you’d be surprised and thankful for the security breakdown. Users without passwords, users sharing same passwords, users with unreasonable privileges, and more… You’ll see them all.
- If you have web interfaces to your database or some of its aspects (e.g. Anemometer, Propagator, Orchestrator, monitoring, …), protect it via LDAP group or similar. Not everyone who has access to your network needs to see you database. Neither does every employee.

- Twitter
- LinkedIn
- WhatsApp
- Email
- Facebook
- Print

###
4
Leave a Reply

4 Comment threads
0 Thread replies
0 Followers
Most reacted comment
Hottest comment thread
3 Comment authors
Recent comment authors

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Subscribe
Notify of
Guest
Todd Farmer

Hi Shlomi,

FYI, there’s a slightly less-hackish way to create valid accounts which prohibit all client connection logins:

http://mysqlblog.fivefarmers.com/2012/11/08/system-user-authentication-plugin/

Author
shlomi

@Todd,
cool.

There’s this login audit plugin I wrote (https://shlomi-noach.github.io/blog/mysql/introducing-audit_login-simple-mysql-login-logfile-based-auditing). The thing with plugins is that they are nightmarish to support cross versions.
Whenever I upgrade a MySQL server, I need to recompile the plugin (with same version) and install on service. I find that this is difficult to maintain, even with Chef. It becomes a macaroni of versions.

I wish there would be a way to write plugins such that they don’t need to be recompiled for every possible version.

Guest
Daniël van Eeden

My 2 favorite security tips:
– Install (security) updates
– Run mysql_secure_installation

Guest
Todd Farmer

Hi Shlomi,

FYI, the system auth plugin referenced earlier is now packaged as part of 5.7.5:

http://mysqlserverteam.com/the-mysql-5-7-5-milestone-release-is-available/
http://dev.mysql.com/doc/refman/5.7/en/mysql-no-login-plugin.html

We’re considering back-porting to 5.6 (since it’s an optional plugin, there’s less concern about regression) in a future release.

wpDiscuz
Send to Email Address Your Name Your Email Address Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.