In a world driven by computers, most companies rely on systems that are entirely built around databases. Losing data, or even as little as losing the full control over it, could bring any business down. Frequently databases hold sensitive information such as personal details, transaction statements, credit card data – among many other things. This is also why running certain types of databases is regulated in many countries by local as well as international laws – especially in Europe.
What is at stake?
- Availability. When a database or data disappear, business stops. Assuming you have working backups you can restore from, this is the least of all concerns.
- Confidentiality. Your secrets, private information of your customers and anything else that you decided not to make publicly available may be leaked.
- Privacy. Personal information which, according to certain laws, you may be legally liable for, may be stolen and used for malicious purpose.
- Integrity. Without your knowledge, data may be modified to serve someone else's purpose.
The security features of MySQL
Users & Privileges
The privilege system is the most basic security feature MySQL has to offer. It allows defining who can access database.
GRANT ... TO 'app_prod'@'10.0.5.29' IDENTIFIED BY 'some_password';
The implementation goes one step further beyond a simple user-password scheme and enables associating each user with a network location to prevent access from unwanted or untrusted locations. For example an application user in a production database should only be allowed to log in from addresses which belong to other production servers, but not from development or QA environments.
GRANT ... TO 'app_prod'@'10.0.5.29' IDENTIFIED BY 'some_password';
Defines that user app_prod can only connect from 10.0.5.29.
In fact, MySQL can use the knowledge of those extra restrictions to prevent authentication attempts for connections coming from unknown locations, i.e. such that do not belong to any user.
# mysql -u user -p -h 10.0.9.204 Enter password: ERROR 1130 (HY000): Host '10.0.9.19' is not allowed to connect to this MySQL server Host 10.0.9.19 was not a valid source of connections for any existing user.
Each user may be assigned a number of privileges. They define what each user can do once they have been authorized to connect. The privileges can be either global, granting specific right without restrictions, or limited to a specific object such as database or table. Every user must have at least one privilege called USAGE (global), which simply states that a user is permitted to log in.
GRANT SELECT,SUPER,RELOAD ON *.* TO 'backup'@'10.0.5.254' IDENTIFIED BY 'some_password'; Global privileges SELECT, SUPER and RELOAD to user backup@10.0.5.254. GRANT SELECT,UPDATE ON app_production.* TO 'app_prod'@'10.0.5.29' IDENTIFIED BY 'some_password'; Database-level privileges SELECT and UPDATE to user app_prod@10.0.5.29.
By default, MySQL does not support groups of users that could share the same set of privileges (e.g. for each developer). However with the introduction of authentication plug-ins in MySQL 5.5, it is now possible to proxy user through authentication plug-in that can map them to another user's context (including privileges).
Connection encryption
MySQL implements SSL library to provide connection encryption. All endpoints – server and clients – require their own set of private keys and certificates to be able to use encryption. Setting up SSL in MySQL is not a straightforward process, because database provides no assistance nor tools for this purpose, however MySQL documentation covers the process of creating very basic certificates quite thoroughly.
Unfortunately debugging problems with SSL connections and determining why is something not working correctly can be quite difficult. The error messages MySQL returns are often quite vague, so it takes time to understand what the problem might actually be, especially for those less experienced in dealing with SSL and certificates.
More problems can arise from the fact that MySQL binaries can be built with one of two different SSL libraries – OpenSSL or yaSSL – that sometimes results in compatibility issues. Certain functions or MySQL options that work one way when the binary was built with OpenSSL, may work differently or not at all in binaries built with yaSSL. At the same time there is no clear indication to be found inside MySQL which library was used for the particular build.
"MySQL distributions built with OpenSSL support the --ssl-capath option. Distributions built with yaSSL do not because yaSSL does not look in any directory and does not follow a chained certificate tree. yaSSL requires that all components of the CA certificate tree be contained within a single CA certificate tree and that each certificate in the file has a unique SubjectName value. To work around this yaSSL limitation, concatenate the individual certificate files comprising the certificate tree into a new file." - a snippet from MySQL documentation.
Certificate based authentication
Certificate based authentication is not really a separate feature of MySQL. It simply exists as a combination of the SSL used in connection encryption as well as the privilege system.
It enables two-factor authentication where it is no longer sufficient to know username and password, but one also has to provide a valid certificate to be allowed to connect. The most basic requirement that can be set is that both server's and clients' certificates must be signed by the same certificate authority, which means they cannot be issued or forged by a third party.
GRANT SELECT ON app_production.* TO 'app_user'@'10.0.5.29' IDENTIFIED BY 'some_password' REQUIRE X509; Validates that client's certificate has been signed with the same CA certificate as the server's. GRANT SELECT ON app_production.* TO 'app_user'@'10.0.5.29' IDENTIFIED BY 'some_password' REQUIRE ISSUER '/C=PL/L=Krakow/O=PSCE/CN=PSCE SSL Signing CA'; Validates that client's certificate has been issued by a specific CA.
Database logs
Quality logs are always among the keys elements of computer security. They allow monitoring for malicious activity as well as performing analysis when any irregularities are discovered.
MySQL provides two types of logs that are the closest to being useful for these purpose – general log and error log.
The former records the complete activity of all database connections. It contains a mix of connection attempts along with the basic details, queries and other miscellaneous operations performed by clients. As general log configuration ends on either enabling or disabling it, its usefulness often ends with growing traffic when dealing with the log size becomes a significant problem.
130201 0:27:18 12 Connect root@localhost on
12 Query select @@version_comment limit 1
130201 0:27:35 12 Query SELECT "Hello, world!"
130201 0:27:39 12 Quit
The error log, on the other hand, only receives problem notifications, so by definition it is incomplete.
Audit plug-ins
Another addition to MySQL which appeared in version 5.5 was an interface for so called audit plug-ins. Such plug-in receives events about all database activity and can make its own decisions what to do with the information. While the feature still appears to be under active development (the interface changed slightly in MySQL 5.6), with some coding skills it is possible to create a customized audit trail log or even integrate MySQL with a IDS system.
Host bans
One of the early security-oriented features of MySQL is the ability to ban remote systems based on their history of unsuccessful authentications. It is a very simple functionality that does not improve database security by any significant means. Controlled by max_connect_errors variable, MySQL instance can block certain address from further connecting to it after a number of successive connection attempts without establishing a successful connection. Such banned client receives Host 'host_name' is blocked error message and is disconnected.
Until MySQL 5.6, there is no insight into what addresses were blocked. The most recent version adds host_cache table to performance_schema database, where such details are exposed. However, it remains impossible to selectively remove blocked entries as FLUSH HOSTS still does not take any parameters and always clears everything.
Cryptographic functions
MySQL offers a number of cryptographic and hashing functions that can be used to either encrypt or decrypt data directly in queries – AES_ENCRYPT(), AES_DESCRYPT(), DES_ENCRYPT(), DES_DECRYPT(), SHA(), etc.. Relying on those functions to encrypt data, however, is not safe as potentially confidential information might be either returned or permanently stored as open text in such places in MySQL as process list, InnoDB status dump, general log, slow log, binary log or even error log.
130201 0:29:35 20 Query SELECT AES_ENCRYPT('secret', 'encryption_key') An example of a general log entry.
Therefore, while it is safe to run decryption in MySQL, data encryption should rather be performed externally in applications.
Summary
While MySQL may be missing some enterprise enhancements such as transparent column or table encryption, it still offers a strong set of security features that should be sufficient in almost every case. The ability to create and use custom authentication as well as audit plug-ins can become a powerful asset for anyone with higher than usual security requirements.