Pablowe

PCI DSS & MySQL – Requirement 6

Requirement 6 of PCI DSS v1.2 states that in order to be compliant, an organization must:

“Develop and maintain secure systems and applications”

“Unscrupulous individuals use security vulnerabilities to gain privileged access to systems. Many of these vulnerabilities are fixed by vendor- provided security patches, which must be installed by the entities that manage the systems. All critical systems must have the most recently released, appropriate software patches to protect against exploitation and compromise of cardholder data by malicious individuals and malicious software.

“Note: Appropriate software patches are those patches that have been evaluated and tested sufficiently to determine that the patches do not conflict with existing security configurations. For in-house developed applications, numerous vulnerabilities can be avoided by using standard system development processes and secure coding techniques.”

Most of Requirement 6 is outside the scope of MySQL directly and covers things like:

• Validation of all input
• Validation of proper error handling
• Validation of secure cryptographic storage (although you’ll want to validate MySQL’s at-rest encryption as part of your validation processes)
• Validation of secure communications
• Separate development/test and production environments
• Separation of duties between development/test and production environments
• Production data (live PANs) are not used for testing or development
• Web Application Security (XSS, CSRF, etc)
• Formalizing the process for security updates

The parts that specifically apply to MySQL are as follows:

6.2 Establish a process to identify newly discovered security vulnerabilities (for example, subscribe to alert services freely available on the Internet). Update configuration standards as required by PCI DSS Requirement 2.2 to address new vulnerability issues.

Personally, I like a formalized subscription to BugTraq and MySQL’s Announce List, where each relevant bug and announcement are formally evaluated against your target environment. In the event that there is something applicable to your environment, you can begin the (formally documented) process of introducing the mitigation, upgrade, or other required change(s).

6.4 – Follow change control procedures for all changes to system components

The procedures must include the following:

• Documentation of impact
• Management sign-off by appropriate parties
• Testing of operational functionality
• Back-out procedures

Unfortunately, Change Control for databases is not nearly as widely practiced as for code. We, as a community of database administrators, need to make a greater effort to make this a de facto standard for organizations. In the interest of the greater good, I wanted to call out this requirement directly and provide some relevant links:

• Versioning Your Schema
• dbRecorder (I’ve never personally used this)
• MySQL Workbench for Database Management
• mysql-compare
• SVN & MySQL Schema Synchronization

PCI DSS & MySQL – Requirement 4

Requirement 4 of PCI DSS v1.2 states that we must:

“Encrypt transmission of cardholder data across open, public networks”

Specifically, “Sensitive information must be encrypted during transmission over networks that are easily accessed by malicious individuals. Misconfigured wireless networks and vulnerabilities in legacy encryption and authentication protocols can be continued targets of malicious individuals who exploit these vulnerabilities to gain privileged access to cardholder data environments.”

On the face of it, this seems generally irrelevant to MySQL as nobody in their right mind has their databases on open, public networks. If all of your databases are all located locally to one another (same switch in private network), you’re likely compliant as far as MySQL is concerned (same caveat as earlier posts apply: the other aspects of your stack must be evaluated as this post is solely concerned with MySQL). If you have replication across datacenters, you must ensure that at least one of the following holds true:

• The traffic between datacenters is encrypted at the network layer (secure VPN, for example)
• Applicable data is encrypted before being inserted into the database (either by encrypting in the application layer or using RBR as described in my previous post on PCI DSS & MySQL).
• You use MySQL Replication Over SSL.

Even if we meet the specific goals of Requirement 4, we still will not have satisfied generally-accepted best practices of always encrypting data in transit! A case in point of where this requirement falls short is the 2006-2009 Heartland Data Breach, where (if I recall correctly), malware located on their internal network was able to sniff unencrypted traffic and compromise account information.

The bottom line is that if you’re taking the time to become PCI compliant, take the time to go the extra mile. It could ease any transitional burdens when PCI DSS is updated with stronger requirements (for example, it is rumored that new encryption guidance for end-to-end encryption will be part of the next standard).

Nagios Checks For MMM

I’ve written some new Nagios checks for MMM (MMM on Google Code … MMM on Launchpad). check_mmm is a part of http://code.google.com/p/check-mysql-all/, and is meant to be called locally on the MMM Monitor server (usually via NRPE). Feedback is welcome, usage is as follows:

Usage:
     check_mmm --cluster C# 

     Options:
       --cluster=    The MMM Cluster to check
       -c, --critical=
    The level at which a critical alarm is raised.
       -h, --help                Display this message and exit
       -v, --verbose             Increase verbosity level
       -V, --version             Display version information and exit
       -w, --warning             The level at which a warning is raised.

     Defaults are:

     ATTRIBUTE                  VALUE
     -------------------------- ------------------
     cluster                    No default value
     critical                   HARD_OFFLINE,REPLICATION_FAIL
     help                       FALSE
     verbose                    1 (out of 3)
     version                    FALSE
     warning                    ADMIN_OFFLINE,AWAITING_RECOVERY,REPLICATION_DELAY

Nagios MySQL Plug-Ins

There currently exist many plugins for MySQL to use with Nagios. Many of them, however, are not version-independent, leaving organizations that use multiple versions of MySQL to either install multiple plugins or not monitor specific versions of MySQL. As such, I’ve compiled what I consider to be the most useful checks into a single plugin: check_mysql

Usage:
     check_mysql check_name [options]

     Options:
       --args|a     Optional arguments.  Comma-separated.  Check-specific.
       --critical|c The level at which a critical alarm is raised.  Check-specific.
       --database   The database to use (defaults to mysql)
       --help|?     Display this message and exit
       --hostname|H     The target MySQL server host (defaults to localhost)
       --password|p The password of the MySQL user
       --port       The port MySQL is listening on (defaults to 3306)
       --user|u     The MySQL user used to connect
       --version|V  Display version information and exit
       --warning|w  The level at which a warning is raised.  Check-specific.

     defaults are:

     ATTRIBUTE                  VALUE
     -------------------------- ------------------
     args                       No default value
     critical                   Check-specific
     database                   mysql
     help                       FALSE
     host                       localhost
     password                   No default value
     port                       3306
     timeout                    10 seconds
     user                       No default value
     verbose                    1 (out of 3)
     version                    FALSE
     warning                    Check-specific

Current Checks Supported:

* connect – Check to see whether or not one can connect to MySQL (USAGE)
* repl_io – Check to see whether on not the IO Replication thread is running (REPLICATION CLIENT)
* repl_sql – Check to see whether or not the SQL Replication thread is running (REPLICATION CLIENT)
* repl_sbm – Check how many seconds behind the master the slave is (REPLICATION CLIENT)
* mysql_query – Run a given query, test if it executes properly (SELECT)
* connections – Test if the percentage of used connections is over a given threshold (PROCESS)

I am open to requests for additional checks etc.

OmniSQL 0.0.7 Released

OmniSQL (a command line tool for DBAs needing to issue ad-hoc queries against sharded data) version 0.0.7 is officially released.

Instead of logging in separately to multiple databases to issue the same query, groups of databases can be specified in a configuration file and queries will be automatically issued against all targeted MySQL instances.

Let me know of any bugs found or features you would like to see in upcoming releases!

Download at http://code.google.com/p/omnisql/.

CHANGE LOG
- Fixed Bug #3: Script failure when only one group is defined
- Fixed Bug #4: Too Slow (now it forks)
- Fixed Bug #5: Host-Specific parameters don't work
- INCOMPATIBLE CHANGE: Changed format of config file
- INCOMPATIBLE CHANGE: Use XML::SimpleObject instead of XML::Simple
- Added group summary (Issue #6)

Managing MySQL Configuration Files

It is good practice to manage changes to MySQL configuration files (/etc/my.cnf) by using a version control system. I usually use a home-brewed (not brewed by me!) svn+cfengine application to propagate my.cnf (and other configuration file) changes to defined classes of machines (classes are based on application role, replication role, etc).

When managing hundreds of different database servers with dozens of roles, templating my.cnf becomes a necessity! If I have to change a variable for a given class, that can mean editing a dozen my.cnf files on as many servers. Not a productive use of time!!! Usually, the only parameters that differ between database hosts in the same class are the replication options. As such, I find it useful to have a base template for each class of machine and use the !include startup option to specify host-specific startup options.

Here is a sample of what I would have as a base my.cnf for billing-class MySQL Replication Slaves:

And then I would store host-specific replication options in a smaller file:

To re-iterate, templating MySQL configuration files can help ensure consistency between servers of the same class as well as making it easier to push changes to all relevant hosts instead of doing it individually.