MySQL Database Asset Protection Standards:: Difference between revisions

From HORSE - Holistic Operational Readiness Security Evaluation.
Jump to navigation Jump to search
Line 182: Line 182:
===Tighten Passwords and Groups===
===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.
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.


===Localization===
===Localization===

Revision as of 17:18, 18 May 2007

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:

  1. Operating System (OS) level security
  2. Database Configuration
  3. Identification and Authentication
  4. Separation of Duties
  5. 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.

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.

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.

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.