MySQL is no doubt one of the most widely used Database Management Systems in the world. It powers most of the sites on the internet. Its popularity stems from the fact that it is easier to set up, learn and provides really fast data reading performance. With the few points mentioned, it is definitely clear why anyone branching into web development will always come across MySQL.
The fact that MySQL is really simple doesn’t mean it isn’t secure. MySQL contains lots of security features which can be found in most enterprise database management systems. However, with very few people taking time out to really understand the whole concepts of database management, we find people deploying public facing application powered by MySQL with very weak security policies.
As an application developer, I have fallen prey to taking database security for granted in some applications I have worked on. If the database gets hacked and you lose most of your data, the beauty of your well-designed website will not salvage the rage and threat mails you get from the people making use of your application. One major mistake we make while developing application on MySQL, is the fact that we allow our web application to talk to the database as the root user.
Below are a few security tips to apply when developing database applications on MySQL and other databases as well:
- Don’t share root user password and mysql.user table access with anyone till you have full trust on it. Because that encrypted password is real password in MySQL so if anyone knows that than he/she can easily login with any user if he has access to the host.
- Check with “mysql -u root ” command, If you can easily login without asking password than you are in trouble. Anyone can login with root user in that server. In this case, you can use “mysql_secure_installation” utility. By running it, you can set root password, remove anonymous users and also restrict users which are connecting from outside of the server.
- There shouldn’t be any user without a password. Even try to avoid “%” in hosts. Frequently change root password.
- Check users permissions with “SHOW GRANTS” command and remove unnecessary permissions by “REVOKE” command if needed. Don’t give permissions to users for multiple hosts or database unless needed.
- Don’t use any words from dictionary in password. It can be broken easily by some hacking program.
- If data is that sensitive, then use SSL connections between MySQL client and server.
- As MySQL is using 3306 by default, it should be blocked from outside of network. It should not be accessible by un-trusted users/hosts.
- Don’t run the mysqld daemon as the Linux root user. It should always ran by MySQL user itself. If you run mysqld with another Linux user than root, you don’t need to change the root user name in mysql.user table because there is no any connection between MySQL users and Linux users.
- Don’t give the process privileges to all users. Because “show processlist” command can show all the running queries on the servers. It might be possible that someone is changing the password and another can see it by “show processlist” i.e update user set password = old_password(“abc”)
- If you don’t trust your DNS, you should use IP numbers instead of hostnames in the mysql.user table. In any case, you should be very careful about creating grant table entries using hostname values that contain wild cards! If you want to restrict the number of connections for a single user, you can do this by setting the max_user_connections variable in mysqld.
Practice the above tips to stay relatively safe and avoid embarrassing situations and avoidable sleepless nights.