Security is a critical component of any software. Because it only takes one weak link to destroy entire site or brand’s reputation. it’s often neglected due to lack of time or commitment. A default MySQL Security installation fails to provide adequate best practices in database security. This article discusses these limitations and various way to improve security including :
- MySQL Security Permissions
- Operating System Security
- Database Privileges
- Other Security Options
MySQL Security Installation Defaults
When first installed, MySQL allowed any user with physical permissions to the server to connect to MySQL server unauthentically. MySQL also provide complete access to all super user via root without using password. These are the following list of the default user :
There are 2 types of users.
- Root User which has MySQL super privileges even with no password.
- Unauthenticated user indicated by user column
However, MySQL provide an optional command for immediate improvements in default MySQL Security by using mysql_secure_installation command. When you use this command, you will be prompted for the following options, the output has been trimmed for presentations purposes.
If you revisit it now, you will see more initially secure installation. No more unauthenticated users.
Operating System Security
After performing most basic improvements to access MySQL, it’s time to improve operating system security process with following steps :
- Install software as highest OS user (root) and make all MySQL binary owned by root
- Restrict access to root via sudo privileges, Only grant limited access (if needed)
- Configure an OS MySQL user, but do not allow direct login access to this user
- Set permission of MySQL data directory chmod 700/mysql/datadir
- Make sure MySQL file only contains data and transactional logs
- Separate MySQL error log in a different directory. So you can allows for permissions of the user group to view logs
- MySQL socket file needs to be in a world readable directory. The pid file doesn’t
- Provide additional constraints on mysql port at a firewall level
These steps can be achieved when using mysql tar binary installation rather than OS packaged version. However, same rules apply both.
Improve MySQL Security Permissions
After securing mysql at OS level, you can improve mysql security client access with following steps :
- Always set a MySQL root password
- Change mysql “root” user id to a different name (for example datacenter, dataabc, and etc)
- Only allow super privileges to dba accounts, and localhost
- Application user permissions should be restrictive as possible
- Never use % for a hostname
- Make two types of user, a read/write user and a read user
- Never use ALL TO *.*.
These steps will protect you for common attack. For defence against advanced attack such as prevent mysql injection you need additional tools.