In this post I will show up some dangers of the MySQL-Server usage you should care of. These are the first steps to enhance the security of your database server, because with this you are able to limit the access and the privileges.
I recommend at least basic knowledge of MySQL to understand most of the things explained in the following part.
Some dangers that people underestimate:
- Empty passwords:
- Careless hosts settings:
- Lack of permissions:
- Bind-address in my.cnf file:
The first thing you should check and avoid on are empty passwords. On worst circumstance everyone can log on to your MySQL-Server!
With a simple SELECT-Statement you can check this and identify all users without any password.
SELECT User FROM mysql.user WHERE Password = "";
To solve this security issue you need to assign a password to the user with following UPDATE-statement.
# Replace $PASS with the password
# Replace $USER with the user
UPDATE User SET Password=PASSWORD("$PASS") WHERE User="$USER";
CREATE USER 'user'@'%' IDENTIFIED BY 'password';
This is a bad example for creating an user in MySQL. The percent sign means that you can connect with this user from any host, what can have serious security impact.
There are better ways to do this. You can set exact IP-addresses, host-names or use wildcards for both ones to limit the connection possibilities of offenders significantly.
CREATE USER 'user'@'localhost' IDENTIFIED BY 'password';
CREATE USER 'user'@'10.11.%.%' IDENTIFIED BY 'password';
The MySQL permission system is a very time consuming section. If you want to manage this correctly, you need to know all necessary permissions to every table in every database for every single user!
Because of this people often want to make their life easier and so they use a simple GRANT-statement, which sets all permissions.
GRANT ALL PRIVILEGES ON *.* TO 'user'@'host';
But, as you can imagine, this can have a very bad influence on your security. If your software is for example vulnerable for SQL-Injection and you do not know this, attackers can simply drop all databases!
To prevent this I recommend setting only the required privileges to the users.
If you need an overview about all privileges you should have a look at the MySQL privileges section.
GRANT SELECT,INSERT,UPDATE,DELETE ON *.* TO 'user'@'host';
A better way than only setting the limited permissions to all databases and tables is, setting different privileges for every table!
GRANT SELECT,INSERT,UPDATE,DELETE ON DB.TABLE TO 'user'@'host';
The bind-address restricts all connections to the MySQL-Server to a given IP-address.
In most cases you will see the line below commented out in the my.cnf file. This is necessary if you need to connect to the MySQL-Server from different hosts.
#bind-address = 127.0.0.1
But if you only need access to the MySQL-Server on localhost it is better and more secure to use this as bind-address, because the MySQL-Server does not listen on external connections then.
bind-address = 127.0.0.1
I hope this tutorial helps you to secure your MySQL-Server. In fact there are much things you can do to make it more secure, but like I said this is a first step to enhance it.
If you have feedback, regards, corrections or questions please let me know and do not hesitate to comment!