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).

PCI DSS & MySQL: Requirement 3

Requirement 3 of the PCI DSS v1.2 is:

“Protect Stored Cardholder Data”

As vague as that sounds, the PCI DSS enumerates exactly what that covers:

* These data elements must be protected if stored in conjunction with the PAN. This protection should be per PCI DSS requirements for general protection of the cardholder data environment. Additionally, other legislation (for example, related to consumer personal data protection, privacy, identity theft, or data security) may require specific protection of this data, or proper disclosure of a company’s practices if consumer-related personal data is being collected during the course of business. PCI DSS, however, does not apply if PANs are not stored, processed, or transmitted.

For those data points that can be stored, MySQL offers a variety of encryption functions to secure the data. For the purposes of this example, we’ll use AES_ENCRYPT() and AES_DECRYPT(). Let’s begin by creating a table to house the data. The formula for determining the space requirements is 16*(CEILING(string_length/16)+1) so we’ll create the table as follows:

mysql> CREATE TABLE `cc_info` (
    ->    `id` int unsigned NOT NULL auto_increment,
    ->    `cc_num` binary(32) NOT NULL, -- Sorry AMEX
    ->    `service_code` varbinary(32) NOT NULL,
    ->    `name_on_card` varbinary(48) NOT NULL, -- Assuming a 32-char max,
    ->    PRIMARY KEY (`id`))
    ->    ENGINE=InnoDB;
Query OK, 0 rows affected (2.79 sec)

Populating it with some data, where I’ve chosen ‘secret_key’ as the key_str used to encrypt:

mysql> INSERT INTO `cc_info` (`cc_num`, `service_code`, `name_on_card`) VALUES (
    ->   AES_ENCRYPT('1234123412341234', 'secret_key'),
    ->   AES_ENCRYPT('1234', 'secret_key'),
    ->   AES_ENCRYPT('John Doe', 'secret_key'));
Query OK, 1 row affected (0.35 sec)

If we try and SELECT the data, we get

mysql> SELECT id, cc_num, service_code, name_on_card FROM cc_info\G
*************************** 1. row ***************************
          id: 1
      cc_num: ??
                ? q$?!~c?3Pg?"xu&3?:?,am?
service_code: y.??A??
?? ?a??
name_on_card: ?93s?!? X?8?|nZ
1 row in set (0.00 sec)

We need to use AES_DECRYPT as follows:

mysql> SELECT id,
    ->        AES_DECRYPT(`cc_num`, 'secret_key') AS `cc_num`,
    ->        AES_DECRYPT(`service_code`, 'secret_key') AS `service_code`,
    ->        AES_DECRYPT(`name_on_card`, 'secret_key') AS `name_on_card`
    ->   FROM `cc_info`\G
*************************** 1. row ***************************
          id: 1
      cc_num: 1234123412341234
service_code: 1234
name_on_card: John Doe
1 row in set (0.00 sec)

The PCI DSS v1.2 standard says that we must use “strong cryptography” to protect the data, where they define “strong cryptography” as “Cryptography based on industry-tested and accepted algorithms, along with strong key lengths and proper key-management practices. Cryptography is a method to protect data and includes both encryption (which is reversible) and hashing (which is not reversible, or “one way”). SHA-1 is an example of an industry-tested and accepted hashing algorithm. Examples of industry-tested and accepted standards and algorithms for encryption include AES (128 bits and higher), TDES (minimum double-length keys), RSA (1024 bits and higher), ECC (160 bits and higher), and ElGamal (1024 bits and higher).”

MySQL uses 128-bit key length, satisfying the requirement. You can, however, take things a step further by editing the file include/my_aes.h and updating the following line:

#define AES_KEY_LENGTH 128 /* must be 128 192 or 256 */

and compiling from source. I DO NOT recommend taking this approach unless you have a very compelling reason, as it is easy to mess up the build process, more difficult to manage upgrades, and generally more error-prone than simply using standard package managers for your chosen distribution.

So we’re done, right? Nope:) We must secure the information not only in the database, but in logs, backups, and any other place it resides on disk. If we’re using MySQL replication, which is quite typical, we must also consider the binary logs. Looking at the transactions above, we see:

%> mysqlbinlog log-bin.000001
...
BEGIN
/*!*/;
# at 172
#100406 16:35:31 server id 1  end_log_pos 200 	Intvar
SET INSERT_ID=1/*!*/;
# at 200
#100406 16:35:31 server id 1  end_log_pos 461 	Query	thread_id=1	exec_time=0	error_code=0
use cc/*!*/;
SET TIMESTAMP=1270596931/*!*/;
INSERT INTO `cc_info` (`cc_num`, `service_code`, `name_on_card`) VALUES (
   AES_ENCRYPT('1234123412341234', 'secret_key'),
   AES_ENCRYPT('1234', 'secret_key'),
   AES_ENCRYPT('John Doe', 'secret_key'))
/*!*/;
# at 461
#100406 16:35:31 server id 1  end_log_pos 488 	Xid = 6
COMMIT/*!*/;
...

Unfortunately we can see all of the information that we worked so hard to secure! As of MySQL 5.1, you can use row-based replication, which logs changes in individual table rows rather than simply replicating full SQL statements. The same statements as above, as executed when binlog_format=ROW, appear in the binary log as:

%> mysqlbinlog -v log-bin.000001
...
#100406 16:39:30 server id 1  end_log_pos 936 	Query	thread_id=3	exec_time=0	error_code=0
SET TIMESTAMP=1270597170/*!*/;
BEGIN
/*!*/;
# at 936
# at 989
#100406 16:39:30 server id 1  end_log_pos 989 	Table_map: `cc`.`cc_info` mapped to number 15
#100406 16:39:30 server id 1  end_log_pos 1090 	Write_rows: table id 15 flags: STMT_END_F

BINLOG '
Msa7SxMBAAAANQAAAN0DAAAAAA8AAAAAAAAAAmNjAAdjY19pbmZvAAQD/g8PBv4gIAAwAAA=
Msa7SxcBAAAAZQAAAEIEAAAQAA8AAAAAAAEABP/wBAAAACCY3AusAHEkreUIIX5jyzNQZ90ieHUm
M8Y6BgflLGFtjxB5Lo7nQeK6zQr+wQCVYabiELI5M3P+IYoAWOo4iHxuWhI=
'/*!*/;
### INSERT INTO cc.cc_info
### SET
###   @1=1
###   @2='??\x0b?\x00q$??\x08!~c?3Pg?"xu&3?:\x06\x07?,am?'
###   @3='y.??A??\x0a??\x00?a??'
###   @4='?93s?!?\x00X?8?|nZ\x12'
# at 1090
#100406 16:39:30 server id 1  end_log_pos 1117 	Xid = 23
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
...

Here, we can only see the encrypted binary data; thus meeting PCI standards. MySQL offers a third binlog_format, and that is MIXED. This is essentially where non-deterministic statements are executed with row-based logging, while deterministic statements use statement-based logging (this is an oversimplification, full details on MIXED logging format can be found here). Since these encryption functions are deterministic, the full SQL statements will be shown in the log and won’t work in a PCI environment.

We can’t obfuscate MySQL’s other logs, though. It is generally considered best practice to ensure that the slow query log, error log, and any other log in which full SQL queries may appear to be written on an encrypted volume if you’re using the “pure MySQL” approach to encryption. If encryption is done at the application layer, the logs are obfuscated for you.

These methodologies described above represent but one of the “Pure MySQL” ways to meet requirement 3. The other most common approaches are to call encryption functions within the application or to use a full-disk encryption solution. Be very careful with this approach, though; in my experience with these, they are mostly expensive, buggy, a performance killer, or some combination thereof (YMMV)! If this approach is chosen, I encourage one of your tests to be to physically yank the power cord during a load test; see if you can access the data after.

Much of the body of requirement 3 is dedicated to key management, documentation, distribution, storage, and generation. Here we’ll go through the remaining requirements that are relevant to our MySQL-only focus.

3.1 – Keep cardholder data storage to a minimum. Develop a data retention and disposal policy. Limit storage amount and retention time to that which is required for business, legal, and/or regulatory purposes, as documented in the data retention policy.

This is just generally good advice, not storing what you won’t or can’t use. It pollutes the database, requires more on-disk (and potentially in-memory) space while providing no business value. The important thing here is to develop a policy. A quick Google search for PCI Policy Templates can show some samples that can provide a jumping-off point while developing policies for your own organization.

3.2 – Do not store sensitive authentication data after authorization (even if encrypted)

The subsections of 3.2 basically boil down to a “don’t list”: DO NOT

• store sensitive authentication data after authorization (even if encrypted) [3.2]
• store the full contents of any track from the magnetic stripe (located on the back of a card, contained in a chip, or elsewhere). This data is alternatively called full track, track, track 1, track 2, and magnetic-stripe data [3.2.1]
• store the card- verification code or value (three- digit or four-digit number printed on the front or back of a payment card) used to verify card-not- present transactions [3.2.2]
• store the personal identification number (PIN) or the encrypted PIN block [3.2.3]

3.3 – Mask PAN when displayed (the first six and last four digits are the maximum number of digits to be displayed).

As a user, I actually consider this maximum to be incredibly rude! Imagine if you logged in to Amazon and they displayed your “masked” card as: 123412******1234! The standard method of display is **** 1234 (where the number of stars are variable). Since we generally don’t want unencrypted data floating around the network, we can access the data as follows:

mysql> SELECT RIGHT(AES_DECRYPT(`cc_num`, 'secret_key'),4) AS `last_four`
    ->   FROM `cc_info`
    ->   WHERE `id`=1;
+-----------+
| last_four |
+-----------+
| 1234      |
+-----------+
1 row in set (0.00 sec)

And then format it with leading *’s on the front-end as required.

3.4 – Render PAN, at minimum, unreadable anywhere it is stored (including on portable digital media, backup media, in logs) by using any of the following approaches: One-way hashes based on strong cryptography, Truncation, Index tokens and pads (pads must be securely stored)

This cannot be done natively in MySQL when using replication without one of the approaches listed above or writing logs to a dedicated encrypted partition.

3.4.1 – If disk encryption is used (rather than file- or column-level database encryption), logical access must be managed independently of native operating system access control mechanisms (for example, by not using local user account databases). Decryption keys must not be tied to user accounts.

When you evaluate a full-disk encryption solution, you must evaluate it primarily against the metrics outlined in the PCI DSS.

PCI DSS & MySQL – Requirement 2

Requirement 2 of the PCI DSS v1.2 is:

“Do not use vendor-supplied defaults for system passwords and other security parameters”

Understanding that we’re limiting the discussion solely to MySQL (OS, Network Devices, and other software will no doubt apply to overall compliance), we can do this easily. The vendor-supplied default MySQL 5.1.43 (they’re similar across other versions, but only this version was tested here) credentials can be seen as follows:

mysql> SELECT user, host, password FROM mysql.user;
+------+-----------+----------+
| user | host      | password |
+------+-----------+----------+
| root | localhost |          |
| root | testbox1  |          |
| root | 127.0.0.1 |          |
|      | localhost |          |
|      | testbox1  |          |
+------+-----------+----------+
5 rows in set (0.28 sec)

MySQL handily provides us with a script called mysql_secure_installation that will, assuming we choose Y to all questions, satisfy this requirement:

%> mysql_secure_installation
NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MySQL to secure it, we'll need the current
password for the root user.  If you've just installed MySQL, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none):
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MySQL
root user without the proper authorisation.

Set root password? [Y/n] Y
New password:
Re-enter new password:
Password updated successfully!
Reloading privilege tables..
 ... Success!

By default, a MySQL installation has an anonymous user, allowing anyone
to log into MySQL without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n] Y
 ... Success!

Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] Y
 ... Success!

By default, MySQL comes with a database named 'test' that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n] Y
 - Dropping test database...
 ... Success!
 - Removing privileges on test database...
 ... Success!

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n] Y
 ... Success!

Cleaning up...

All done!  If you've completed all of the above steps, your MySQL
installation should now be secure.

Thanks for using MySQL!

(Emphasis added was mine). This gives us the following:

mysql> SELECT user, host, password FROM mysql.user;
+------+-----------+-------------------------------------------+
| user | host      | password                                  |
+------+-----------+-------------------------------------------+
| root | localhost | *F169C0AFEEC30BFF924130B124E6AE3E875D5F60 |
+------+-----------+-------------------------------------------+
1 row in set (0.00 sec)

Note that the mysql_secure_installation has done more than the PCI minimum of removing default accounts, but also completed items considered to be best practices:

• Remove anonymous users
• Disallow root login remotely
• Remove test database and access to it

We should take this a step further and ensure that MySQL’s old (insecure) password format is not enabled:

mysql> SHOW GLOBAL VARIABLES LIKE 'old_passwords';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| old_passwords | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

To find any users with the old-style (or empty) passwords:

mysql> SELECT user, host FROM mysql.user WHERE LENGTH(password)!=41;
Empty set (0.00 sec)

Now we’ve done this for a new install, but few of us have the privilege of fresh installs. Instead, we work with existing systems. So, here’s a query (for 5.1.43) to find out if we have any insecure user accounts or system defaults:

mysql> SELECT user, host FROM mysql.user WHERE user='' OR password='' OR host='%';
Empty set (0.00 sec)

On the face of it, we seem to have satisfied PCI DSS Requirement 2. If only it were that easy … Requirement 2 actually consists of a variety of subsections. Most of these are outside the scope of MySQL directly, but the following will apply:

2.2 – Develop configuration standards for all system components. Assure that these standards address all known security vulnerabilities and are consistent with industry-accepted system hardening standards.

This requirement is to ensure that all configurations are codified and put into use. In addition to written policies and procedures, the use of an automated configuration management system (cfengine, Chef, Puppet, etc) can simplify adherence to corporate policies and standards.

2.2.3 – Configure system security parameters to prevent misuse.

I’m still waiting for the NSA to release a Security Configuration Guide for MySQL as it has for other databases, but there are other common security configurations that prevent misuse. As every installation is different, do not consider this list comprehensive, it is meant to be a starting point.

• Be judicious with your GRANTs. If an application requires only SELECT, INSERT, UPDATE, and DELETE privileges on a particular database, limit the GRANT statement accordingly. Specifically avoid WITH GRANT OPTION and SUPER wherever possible (I mention SUPER in particular because it allows the one reserved administrative connection to be used by the app, meaning what may have only been a momentary blip in service results in extended downtime while mysqld is restarted; this goes against the “A” in the CIA Triad)
• Disable local_infile. I won’t repeat the common wisdom here, but you can read more about the security implications of this on MySQL’s website
• Disable old_passwords. The hashing algorithm has gotten more secure over the years, it is best practice (as described above) to use only the newer hash algorithm. You can read more about password hashing in MySQL 5.1 here
• Set read_only=ON. Although not typically viewed as a security configuration parameter, this setting can provide an additional layer of protection that ensures data on your replication slaves remains in sync with your masters (think I in the CIA Triad)
• Enable secure_auth. This blocks connections from all accounts that have passwords stored in the old (pre-4.1) format. Even though old_passwords will be OFF, this can provide a bit of redundancy to that option

I hope this post has helped secure at least one MySQL server. Look for more PCI DSS + MySQL coming up soon!

Cassandra “Quick Install” on CentOS 5

Cassandra is notoriously a pain to install on CentOS. So partially as documentation for myself, here are the “quick install” steps for getting Cassandra up and running. For explanation, see the official Cassandra documentation. I think the biggest reason this differs so significantly from other install guides out there is that I wanted it to work on RightScale (know issues w/Java) as well as CentOS 5.1-5.4. I can’t wait until I can get Cassandra from yum …..

Knowing your PERC 6/i BBU

I’ve recently become supremely disappointed in the availability of Nagios checks for RAID cards. Too often, I see administrators rely on chance (or their hosting provider) to discover failed drives, a dying BBU, or a degrading capacity on their RAID cards. So I began work on check_raid (part of check_mysql_all) to provide a suite of checks. One of the first cards I wanted to support was the PERC 6/i, so I scoured the documentation, forums, and picked the brains of my friends before finally getting on a marathon 4 hour call with Dell support. I’ll now share the interesting things that I’ve learned.

More »

HTTP Over SSL Best Practices

Encrypting connections to your web server using HTTPS is a trade-off between performance and security. Therefore, there’s no “right answer” for exactly what traffic to encrypt; it’s a business decision. There are, however, best practices that can guide you in the decision making process. In fact, there is a “golden rule” for making this decision:

If it were your session, would it bother you if I got the data? If your answer is yes, then the data should be encrypted!

More »

Getting my money’s worth!

Several months ago I purchased an HP Mini 1000 to do some work for a client in their SQL Server 2005 environment. The work ended rather quickly and the Mini’s been sitting around gathering dust. I came across it in a pile of junk the other day while cleaning and decided that it would be the machine to replace my MacBook Air when the MBA goes in for repair. The basic tasks I would be performing are:

• Lots of database work
• Linux, Solaris, and FreeBSD administration
• Penetration Testing and Security Audits

Right away I realized I would need to kick it up a notch by upgrading to 2G of RAM. I would also need to be able to run Linux, but couldn’t sacrifice my only Windows installation (virtualization is not an option on either the MBA or the Mini). Dual booting also wasn’t an option because the 16G hard drive on the Mini is barely enough to run Windows. So my only real option was to do a persistent install of Ubuntu on USB (using the NetBook Remix, of course). HP made some awesome decisions with USB on the Mini: SD Card and Mini Flash slots in addition to the regular USB port. So I purchased an 8G JetFlash USB Drive to serve as my Linux machine. After then spending a few hours on my typical Linux install (which is pretty much half of the top 100 SecTools and a few other base packages), I was ready to go.

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

VPNC Quick Install on OS X

I find myself constantly battling with Cisco VPN client on my Mac. As such, I’ve recently turned to vpnc as a replacement. VPNC is available as a Darwin Port but won’t work with a simple install.

%> sudo port install libgpg-error openssl libgcrypt tuntaposx vpnc

That’s all there is to it. The command took less than 5 minutes on my first-generation MacBook Air.