MySQL Database Asset Protection Standards:
Overview
Maintaining secure and reliable databases, upon which the Company enterprise and business processes depend, requires a consistent approach to security standards in the areas of database configurations, maintenance, and administration. In order to implement sound security at the database level, we must first surmount both functional and operational obstacles. Database servers do not provide the features required to implement, manage, monitor, and enforce the security controls we need to comply with the companies corporate security policy.
As a result, we must overcome obstacles in several database level areas:
- Authentication and password management
- Authorization and user security administration
- Intrusion detection and security risk assessment
- Policy enforcement
The purpose of this baseline, then, is to present instructions for securing MySQL databases from external and internal exploits.
The major focus include the following:
- Access standards
- Authentication mechanisms
- Database configurations
- Best practices for maintenance and administration
- Securing the host operating system
The MySQL database provides native security capabilities. Effective use of these security features is key to ensuring that the data is protected.
These features include:
- Security domains
- Privileges for discretionary access control
This document is divided into 5 major sections:
- Operating System (OS) level security
- Database Configuration
- Identification and Authentication
- Separation of Duties
- Replication
Document Life-cycle
This baseline should be reviewed no less than once every 12 months for technical accuracy and business validity. Modifications to this document must be submitted in writing to Database Security for review.
Security Considerations
In addition to standards and configurations in the Database and operating system, the following areas must be consulted in order to ensure all other areas have been addressed as well.
Information Classification
Control procedures must be implemented in the software programming methodology to ensure all data is reviewed during quality assurance processes for the level of classification. In addition, all data involved with the application must be reviewed to determine its sensitivity and ensure that,
the capability should exist, within the application, to mark each printed page appropriately with the information’s sensitivity level, i.e., whether it is Proprietary or Confidential. If this capability is non-existent, develop a plan, including a timeline, to integrate this capability into future releases.
Follow exception process, if you cannot meet above requirement.
Refer to Exception Process.
Refer to Information Classification Standard for requirements.
External Connectivity
Consideration must be given to connectivity requirements and must conform to Corporate Security Standards.
Refer to Connectivity Standard for requirements.
Regulations and Laws
Databases must comply with to all applicable laws and regulations. Particularly, refer to Privacy policy requirements for consumers.
Confidentiality
Confidentiality represents the need to control the disclosure of some attribute about a resource.
Examples:
- Server location
- Network traffic destination
- Customer information
Any data imported into test or development databases must not contain data of a sensitive nature unless the test or development environment is equally secured to compliance with the standards within this document.
Refer to the Sample Information Labeling Standard for requirements.
Application Security
Applications must be designed to use Enterprise Infrastructure Security Services when these services are deployed, and they must meet the requirements of the Application development Security standards.
Infrastructure security services are currently available for authenticating and authorizing users of Web-based applications.
An overall security design ensures that we meet Company standards for security related functions (e.g., Authentication, Authorization, Audit). This section covers areas that must be considered as part of the overall security design of the application and the environment in which it runs.
Application Security is not the responsibility of the MySQL Database Administrators. But, the Database administrators should discuss with application teams about the application standards before deploying into production.
Most security vulnerabilities are caused by poor applications. There are number of common pitfalls to avoid.
- You should remove any special characters from strings passed to MySQL.
- You should use quotes around numbers as well as strings.
Operating system
The methods presented herein should apply to most modern UNIX and UNIX-like operating systems.
Configuring the Server
The next step is to configure the database server in compliance with our security requirements. In case of default installation of MySQL, the main configuration file is /etc/my.cnf. In our case, however, because of running the server in a chrooted environment, we will use two configuration files: /chroot/mysql/etc/my.cnf and /etc/my.cnf. The first one will be used by MySQL server, and the latter will be used by MySQL tools (e.g. mysqladmin, mysql, mysqldump etc.). In both cases, some configuration changes will be required.
Disable Remote Access
The first change applies to the 3306/tcp port, on which MySQL listens by default. Because, according to the initial assumptions, the database will be used only by locally installed applications, we can freely disable listening on that port. This will limit possibilities of attacking the MySQL database by direct TCP/IP connections from other hosts. Local communication will be still possible throw the mysql.sock socket. In order to disable listening on the mentioned port, the following parameter should be added to the [mysqld] section of /chroot/mysql/etc/my.cnf:
skip-networking
If, for some reason, remote access to the database is still required (e.g. to perform remote data backup), the SSH protocol can be used as follows:
backuphost$ ssh mysqlserver /usr/local/mysql/bin/mysqldump -A > backup
Improve Local Security
The next change is to disable the use of LOAD DATA LOCAL INFILE command, which will help to prevent against unauthorized reading from local files. This matters especially when new SQL Injection vulnerabilities in PHP applications are found.
For that purpose, the following parameter should be added in the [mysqld] section in /chroot/mysql/etc/my.cnf:
set-variable=local-infile=0
In addition, to make the use of the database administrative tools convenient, the following parameter should be changed in the [client] section of /etc/my.cnf:
socket = /chroot/mysql/tmp/mysql.sock
Thanks to that, there will be no need to supply the mysql, mysqladmin, mysqldump etc. commands with the --socket=/chroot/mysql/tmp/mysql.sock parameter every time we run these tools.
Change Administrator Password
One of the most important steps in securing MySQL is changing the database administrator's password, which is empty by default. In order to perform that, we should run MySQL (if it is not already running):
chrootuid /chroot/mysql mysql /usr/local/mysql/libexec/mysqld &
Change the administrator's password as follows:
/usr/local/mysql/bin/mysql -u root
mysql> SET PASSWORD FOR root@localhost=PASSWORD('new_password');
It is good practice not to change passwords from the command line, for example, by using the "mysqladmin password" command. This is especially important when other users work on the server. In that case the password could be easily revealed, e.g. by using the "ps aux" command or reviewing history files (~/.history, ~/.bash_history etc), when improper access rights are set to them.
Remove Default Users and Database
Remove the sample database (test) and all accounts except the local root account:
mysql> drop database test;
mysql> use mysql;
mysql> delete from db;
mysql> delete from user where not (host="localhost" and user="root");
mysql> flush privileges;
This will prevent the database from establishing anonymous connections and -- irrespective of the skip-networking parameter in /chroot/mysql/etc/my.cnf -- remote connections as well.
Change Administrator Name
It is recommended to change the default name of administrator's account (root), to a different, harder to guess one. Such a change will make it difficult to perform brute-force and dictionary attacks on the administrator's password. In this case the intruder will have to guess not only the password, but first and foremost, the name of the administrator's account.
mysql> update user set user="mydbadmin" where user="root";
mysql> flush privileges;
Remove History
Remove the content of the MySQL history file (~/.mysql_history), in which all executed SQL commands are being stored (especially passwords, which are stored as plain text):
cat /dev/null > ~/.mysql_history
Chrooting the server
The first step of securing MySQL is to prepare the chrooted environment, in which the MySQL server will run.
Prepare Chroot Environment
In order to prepare the chrooted environment, we must create the following directory structure:
mkdir -p /chroot/mysql/dev
mkdir -p /chroot/mysql/etc
mkdir -p /chroot/mysql/tmp
mkdir -p /chroot/mysql/var/tmp
mkdir -p /chroot/mysql/usr/local/mysql/libexec
mkdir -p /chroot/mysql/usr/local/mysql/share/mysql/english
Set Access Rights
The access rights to the above directories should be set as follows:
chown -R root:sys /chroot/mysql
chmod -R 755 /chroot/mysql
chmod 1777 /chroot/mysql/tmp
Create Directory Structure
Next, the following files have to be copied into the new directory structure:
cp /usr/local/mysql/libexec/mysqld /chroot/mysql/usr/local/mysql/libexec/
cp /usr/local/mysql/share/mysql/english/errmsg.sys /chroot/mysql/usr/local/mysql/share/mysql/english/
cp /etc/hosts /chroot/mysql/etc/
cp /etc/host.conf /chroot/mysql/etc/
cp /etc/resolv.conf /chroot/mysql/etc/
cp /etc/group /chroot/mysql/etc/
cp /etc/master.passwd /chroot/mysql/etc/passwords
cp /etc/my.cnf /chroot/mysql/etc/
Tighten Passwords and Groups
From the files: /chroot/mysql/etc/passwords and /chroot/mysql/etc/group we must remove all the lines except the mysql account and group.
MySQL Password Encryption
MySQL encrypts MySQL passwords stored in the grant tables using the PASSWORD () function. This function must be considered for managing MySQL accounts.
There are various ways to setup MySQL passwords. You must not modify password in grant tables directly using “UPDATE” statement.
Localization
If any language other than English will be used, we should copy the proper charsets from the /usr/local/mysql/share/mysql/charsets directory as well.
MySQL Database
This database consists of several tables, responsible for various aspects of access privileges. The database MySQL contains six tables (five in earlier versions) of which five are for managing privileges. These five tables are often referred to as grant tables. The following list provides an overview of the tasks of these tables.
- User table controls who (user name) can access MySQL from which computer (host name). This table also contains global privileges. It is important to recognize that any privileges that are enabled in the user table are global privileges that apply to all databases. For example, if you enable the DELETE privileges in a user table entry, the account associated with the entry can delete records from any table in any database. Because of the super user nature of privileges specified in the user table, the privileges MUST be turned off for all accounts except “root” and other administrative accounts.
- Db table specifies which user can access which databases. The permissions must be granted to the users on Databases after careful scrutiny.
- Host table extends the db table with information on the permissible host names (those that are not present in db).
- Tables_priv specifies who can access which columns of a table.
- The columns_priv table lists column-specific privileges for accounts.
The admin with root permissions only must have permissions to access user, db, tables_priv, columns_priv, and host tables.
Test the Configuration
At this point MySQL is ready to run in the chrooted environment. We can test if it runs correctly by executing the following command:
chrootuid /chroot/mysql mysql /usr/local/mysql/libexec/mysqld &
If any error occurs, we should use the truss command or an alternative, such as ktrace/kdump, strace, etc. This will help us to determine and eliminate the cause of the problems.
Notice, that in order to run the mysqld process, the chrootuid program was used instead of chroot, as in case of Apache or PHP. The main difference is that chrootuid changes the owner of the executing process. In our example, mysqld is being executed in a chrooted environment, but the owner of the process is not root, but mysql user. The chrootuid is not installed by default in many operating systems and it may be necessary to download and install this program manually. The chrootuid software can be downloaded here.
Logs
MySQL has a lot of log files, which makes it easy to see what is going on. One must, however, from time to time clean up after MySQL to ensure that logs don’t take up too much disk space. The logs must be used for auditing, as MySQL does not provide any auditing features. Logs must be kept as per information security accountability standards.
Enabling logging will generate huge amounts of information, possibly filling up your disks. Backup logs periodically to avoid the risk of filling up your disks. The logs should be written to a mount point/directory different from the one on which your databases are stored.
The permissions on all log files must be restricted to 660. The owner MUST grant permissions to auditors as and when necessary.
The Error Log and Binary Log must be enabled.
Error Log
The error log MUST be used for recording diagnostic and error information. On Unix, the error log is not created by the server, unlike the other logs, but rather by the mysqld_safe script that is used to start up the server. Mysqld_safe creates the error log by redirecting the server’s standard output and standard error output. The default error log name is HOSTNAME. Err. You can specify error log name by passing an error log option to mysqld_safe on the command line or by including an error log line in the mysqld_safe group of an option file. It is always a best practice to create error log by using absolute path. The error log is created if you start the server using the mysql.server because mysql.server invokes mysqld_safe.
General Query Log
This log contains a record of when clients connect to the server, each query that is sent to it by clients, and various other events that are not represented as queries (such as server startup and shutdown). If you enable the general log by specifying the –log option without a filename, the default name is HOSTNAME.log in the data directory. As the login encrypted passwords are written to general query logs, the logs should be enabled for troubleshooting purposes only, as and when required.
Binary Log
The binary log is used to record statements that modify data, such as INSERT, DELETE, or UPDATE. SELECT statements are not written to this log. An UPDATE statement such as the following is not written to the binary log, either because it doesn’t actually change any values UPDATE t SET i = i;
MySQL MUST execute a statement first to determine whether it modifies data so queries are written to the binary log when they finish executing rather than when they are received. This log must be enabled.
The Slow Query Log
The slow-query log provides a record of which queries took a long time to execute, where “long” is defined by the value of the long_query_time server variable in seconds. Slow queries also cause the server to increment its slow queries status counter. The slow-query log can be useful for identifying queries that you might be able to improve if you rewrite them. However, you will need to take general load into account when interpreting the contents of this log. Query time is measured in real time (not CPU time), so if your server is bogged down, it is more likely that query will be assessed as being slow, even if at some other time it runs under the limit.
Final Steps
At this point we can create all databases and accounts which will be used by specific PHP applications. It should be emphasized that these accounts should have access rights only to the databases which are used by the PHP applications. In particular, they should not have any access rights to the mysql database, nor any system or administrative privileges (FILE, GRANT, ALTER, SHOW DATABASE, RELOAD, SHUTDOWN, PROCESS, SUPER etc.).
At last, we should also create a shell script that will be used to run MySQL during operating system start up.
You may download the script here: Media:MySQL-CHROOT.txt
Summary
Applying the methods described in the article allows us to significantly increase the security of MySQL. By running the database in a chrooted environment, disabling listening on 3306/tcp port and applying strong passwords to users' accounts we can make the database immune to a many of the attacks that would be possible with the default installation.
Although no method will let us achieve 100% security, applying the outlined methods will at least limit attack possibilities from users who visit our web servers with unfair intentions.