Scope of article
MySQL server is widely used in the open source world. Let it be a small firm’s finance data or a mission critical ERP system, MySQL has its foot print due to great performance and compatibility. However it is also plagued with security challenges, just as any other open source database solution. Utmost care needs to be taken to configure MySQL in order to strengthen its security. This article talks about such challenges and solutions.
MySQL Security Challenges
MySQL architecture is based on typical database engine model. It consists of a storage manager, query processing engine, the authentication engine and client connectors at its core. As for the database engine, we all know that databases are a structured collection of data in a digital form. The structure consist of schema definitions, tables which store the data in an organized form, views which are virtual tables and stored queries to improve performance etc. Database engine is responsible to accept SQL query requests, execute those and provide data back to the querying computer. For this purpose, database service listens on one or more TCPIP ports. The service also runs very closely with the operating system, in order to gain better control on the disk and memory resources. As for authentication, MySQL allows to create users and roles, whereby each userid logging into the database is bound by a pre-define security context.
Each of the MySQL components mentioned above is vulnerable to some extent, if not configured properly. We must remember that the database is a widely and continuously accessible component, which makes it more vulnerable and susceptible to attacks. MySQL security requirements touch all networking layers, and hence need careful design and implementation. While MySQL guide is a great online resource, it only talks about securing database layer. Following are various steps to be carried out post installation on a MySQL server.
Note: The correct way to secure a database server starts with hardening the OS on which it is installed.
Harden the operating system – Security documentation can be followed to ensure a healthy OS installation, however following points quickly enlist the important steps to achieve that.
- Setup ip-tables firewall
- Disable unnecessary services
- Ensure strong password policy
- Install Antivirus and Antispam software
- Update to the latest security patch level
Set chroot – Once installed, the mysql service should be configured for its chroot. This is essential to restrict mysql service’s jurisdiction in terms of mounted disk volume usage, and ensure privilege separation for database applications and query engine. Following command can be used to setup chroot for /etc folder, and can be repeated for other important folders such as /dev, /var/tmp etc. Once created, the chown command can be used to set access rights, so that mysql daemon service has control on it.
mkdir -p /chroot/mysql/etc
Secure service binding – In the mysql configuration file, we can use the following command to bind daemon service to the machines IP address. This is essential because any additional IP address configured on the machine can start publishing mysql service by default, which can lead to exposed vulnerabilities.
bind-address = 127.0.0.1 (or machine_ip)
Secure root user – It is a common practice for administrators to configure database server, but forget to disable default accounts or at least to change the passwords. The root or admin user password is a critical setting because leaving it vulnerable can lead to serious attacks. Another common mistake is to leave the root password blank. Run this command on the MySQL server
mysql -u root
If you are able to connect it means that the password is blank. A stronger password policy should be implemented for root as well as other database users. This helps us achieve security against password guessing and brute forcing attacks.
Remove default users – MySQL installation typically creates few anonymous users by default. Upon a successful installation, these users have no reason to be present in the system, because their presence can potentially result into a backdoor for the attackers.
Remove default files – On all the open source distributions, mysql creates sample database found under user/db. While there is no sensitive information in it to steal, this database accepts anonymous connections and hence can act as a backdoor for attackers. Same applies to the history file (~/.mysql_history), because it contains installation and configuration steps performed already, which can expose the passwords for critical database users.
Change default port – By default mysql runs on TCP port 3306. For database servers hosted and being used internally this may not be a problem, but for externally exposed ones, changing default port is a good idea. Attackers typically use finger printing tools and figure out the services based on ports. Changing port from default to unknown or least used one can make attackers task much difficult.
Control host access – Typically a MySQL server is installed to run standalone, while application servers and web servers connect to it for data access. Using hosts.deny and hosts.allow files, the access to database server can be limited only to the web and application servers. Omitting this step can potentially lead to local area network based human or virus/spyware attacks, who usually look for database server to steal information.
Lower service privileges—It is not necessary to run mysql service with a root level privilege. This can create security challenges whereby the attacker can run malicious code in the same security context as mysql, thus gaining complete control over the server. The correct way to configure this is by creating a specific user account with only required permissions and using that user to run mysql service. This further helps in auditing and logging of mysql service.
SQL Injection – This attack is not a pure database attack, but in fact a combination of database and web vulnerability. It’s a very well known type, in which an attacker uses webpages to plant the attack. It is done by including parts of SQL query statements in the web form, which is usually to be filled up by web user with relevant information. If the web page is not capable of handling this situation in a secure manner, it ends up sending this bogus request to the database. The attacker can simply run a select query to dump the entire database on his machine by this method, which makes it a favorite of hackers and hence dangerous from firm’s security standpoint. To ensure protection from this type, all SQL queries must perform the input data validation. Using triggers and constraints can also help achieve some level of security.
Security Patching – Patching is a first level of defense for databases. A robust patching system must be deployed to secure all the servers connecting to a database server. There are two types of patches, one being the OS level security patch and the other is specific to database service security. Patching system must ensure to address both of the types.
Protecting from Network level attacks – MySQL database can be prone to typical network based attacks such as packet sniffing, packet crafting, Man-in-the-middle, denial of service etc. Implementing a robust firewall and intrusion detection service is imperative. In addition to this, a logging and monitoring system must be incorporated too. It helps in finding out historic data leakage and data theft attacks, which is required for compliance purposes. Utmost care needs to be taken for MySQL installations which are hosted in a DMZ (demilitarized zone).
General guidelines for MySQL Security
Since database servers are an important component of the whole system, multiple measures are to be deployed to ensure security. As for MySQL, sticking to one authentication method and that too the one provided by mysql service should be used. When we don’t use operating system based authentication, we automatically keep unauthorized users away, making the access control job easier. As for roles, a minimum set of roles should be created to avoid future confusions, and the roles should be application specific, rather than purpose specific. This helps in keeping track of which user did what, in terms of accessing the resources. Disabling unwanted mysql database features and services is a good idea too.
In terms of data integrity, using SSL or TLS encryption is useful, especially to avoid packet sniffing of the data flowing between requesting server and the database server. As for data encryption, there are built-in features available for database server, whereby the encryption can be achieved for entire database, or at a granular level such as table or schema. This helps achieving data security without compromising on the server performance..