If data security is not a top priority at your organization, it's time to make it one. External regulations like PCI-DSS or HIPAA are being introduced to make companies' data handling more secure, but there's no reason to wait for regulations to force your hand. It's up to you to protect both your customers' sensitive data and your reputation. In today's article, you’ll learn some tips and tricks for securing your MySQL or MariaDB database(s).
The Default Account and Port
Let's start with something that would seem like a no-brainer, if it weren't for the fact that a lot of people still aren't doing it! MySQL runs on port 3306 by default, using the superuser "root" account. To change the port, you need to edit the my.cnf or my.ini file and set the "port" variable to some other value (ideally not 3307). Regarding the root user account, there are two ways to go: The first is that you can change the password; The second is to create a new superuser using the GRANT ALL...WITH GRANT OPTION command and then remove any and all existing "root@" accounts (the latter obviously being the preferable course of action).
Use Stored Procedures
All stored procedures, as well as functions and views, run within a SECURITY CONTEXT, of which there are two: INVOKER and DEFINER. A procedure created with the invoker security context will be executed using the privileges of the invoker account whereas a procedure created with the definer security context (the default) will be executed with the privileges of the definer at execution time. This means that a procedure can raise the permissions and execute privileged code, but the privileges remain restricted to the code within the procedure.
It is possible to run almost any MySQL statement in a procedure. For instance, you can start and stop replication, change master, change both local and global variables and more. However, the LOCK TABLES/UNLOCK TABLES, ALTER VIEW, LOAD DATA and LOAD TABLE commands are verboten.
Disable LOAD DATA LOCAL INFILE
Some time ago, vulnerability was discovered whereby the LOAD DATA statement could load a file that was located on the server host, or it could load a file that is located on the client host when the LOCAL keyword was specified. If both server and client had the ability to run LOAD DATA LOCAL INFILE, a client would be able to load data from a local file to a remote MySQL server. This potentially could help to read files the client has access to. For example, on an application server, one could access any file that the HTTP server has access to. Although MySQL 8.x.x is not susceptible to LOCAL INFILE abuse, enabling local_infile however opens up this vulnerability.
To avoid it, you need to set local-infile=0 in the my.cnf or my.ini file. If you use LOAD DATA LOCAL in Perl scripts or other programs that read the [client] group from option files, you can add the local-infile=1 option to that group. However, to keep this from causing problems for programs that do not understand local-infile, specify it using the "loose-" prefix:
Use Encrypted Connections
With an unencrypted connection between the MySQL client and the server, someone with access to the network could watch all your traffic and inspect the data being sent or received between client and server. For that reason, when you must transfer information across a network in a secure fashion, an unencrypted connection won't cut it. The best way to make any kind of data unreadable is use encryption. Moreover, encryption algorithms must include security elements to resist many kinds of known attacks such as changing the order of encrypted messages or replaying data twice.
MySQL supports encrypted connections between clients and the server using the Transport Layer Security (TLS) protocol. TLS is sometimes referred to as SSL (Secure Sockets Layer) but MySQL does not actually use the SSL protocol for encrypted connections because its encryption is deemed to be too weak. TLS uses highly robust encryption algorithms to ensure that data received over a public network can be trusted. It has mechanisms to detect data change, loss, or replay. TLS also incorporates algorithms that provide identity verification using the X.509 standard. X.509 makes it possible to identify someone on the Internet using a "Certificate Authority" (CA) that assigns electronic certificates to anyone who needs them. A certificate owner can then present the certificate to another party as proof of identity. A certificate consists of its owner's public key. Data that is encrypted using this public key can only be decrypted using the corresponding secret key, which is held by the owner of the certificate.
Database audits are one of the important compliance components that need to be set up properly but quickly so that your organization does not miss out on business opportunities requiring the storage of user data. With the new GDPR regulations kicking in, data protection rules will need to be integrated into the application, product, or service from the initial phase so that the team is well-versed at every level and defaults to code that protects the data.
MySQL Enterprise Edition includes MySQL Enterprise Audit, implemented using a server plugin named "audit_log". MySQL Enterprise Audit uses the open MySQL Audit API to enable standard, policy-based monitoring, logging, and blocking of connection and query activity executed on specific MySQL servers. Designed to meet the Oracle audit specification, MySQL Enterprise Audit provides an out of box, easy to use auditing and compliance solution for applications that are governed by both internal and external regulatory guidelines.
When installed, the audit plugin enables MySQL Server to produce a log file named audit.log in the server data directory containing an audit record of server activity. The log contents are written in new-style XML format and includes:
- when clients connect and disconnect
- what actions they perform while connected
- which databases and tables they access
The contents of audit log files produced by the audit log plugin are not encrypted and may contain sensitive information, such as the text of SQL statements. To keep audit information safe, audit log files should be written to a directory accessible only to the MySQL server and to users with a legitimate reason to view the log. You can set a custom file format by setting the audit_log_format system variable at server startup.
The adage "Security through obfuscation is no security at all" certainly holds true here. It's always better to employ tried and true security measures than trying to obfuscate object names and locations. While MySQL 8 introduced many new security features, there are numerous aspects of security that you still need to keep in mind, including network access, operating system security, grants, encryption and so on.
See All Articles by Columnist Rob Gravelle