Pablowe

Installing HandlerSocket on RHEL 6 + Percona-Server 5.5 in Five(ish) Easy Steps

As a followup to my previous post on installing HandlerSocket on CentOS 5 + Percona-Server 5.1, I’ve been asked to provide an updated HOWTO for RHEL 6 + Percona-Server 5.5. Although very similar to the original steps, there are some new traps (RHEL 6 comes with SELinux enabled) and there are some new ec2 gotchas!

More »

HandlerSocket execute_multi Curiosities

A post on the HandlerSocket-dev mailing list the other day got me thinking about the performance of MySQL’s IN() construct versus HandlerSocket’s execute_multi. So I started a little test, using MySQL 5.5 + HandlerSocket’s latest commits:

mysql> CREATE TABLE `test`.`t1` (
    ->   `id` int unsigned NOT NULL AUTO_INCREMENT,
    ->   `val` char(32) NOT NULL,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.00 sec)
 More »

HandlerSocket Edge Cases

A couple of weeks ago at the San Francisco MySQL Meetup, I gave a talk on HandlerSocket and got a couple of questions that, while I thought I knew the answer, I had never actually verified by testing. So, for the attendees who asked, here are the questions and answers:
More »

Speaking about HandlerSocket at the next SF MySQL Meetup

[shameless plug]

I’ll be at the next SF MySQL Meetup giving a talk on HandlerSocket called “NoSQL in MySQL: HandlerSocket plugin demystified”. We’ll ask, and answer, the following questions:

• What is HandlerSocket?
• Where does HandlerSocket fit in my application stack?
• Why would I want to use HandlerSocket?
• How do I use Handlersocket?

We’ll walk through relevant configuration & tuning options, and at the end of the presentation attendees will be able to identify candidate use cases within their application to use HandlerSocket.

Nagios Checks for HandlerSocket

I’ve written some new Nagios checks for HandlerSocket. check_handlersocket is a part of http://code.google.com/p/check-mysql-all/, and is meant to be called locally on the HandlerSocket server (usually via NRPE), but the perl-Net-HandlerSocket module must be installed. Feedback is welcome, usage is as follows:

More »

What The Community DOESN’T Know About InnoDB in MySQL 5.5

MySQL 5.5 introduced a number of InnoDB startup options and system variables. I did a quick Google search for each parameter mentioned on the official page and was a little surprised by the results (full results at the bottom of this post). There are a few clear variables with exponentially lower results indicating one of the following: there hasn’t been much research/benchmarking of the parameter, it’s an un-interesting or simple parameter, or the existing documentation is sufficient. The nine variables with the fewest results on Google (all under 1,000 results) are:

More »

Installing HandlerSocket on CentOS 5 + Percona-Server in Five Easy Steps

I’ve been spending a lot of time working on HandlerSocket these days, so I’ll be posting tidbits on this a bit more frequently than in the past. This first post is a quick one to help people get a test environment up and running quickly so they can do their functional testing. The title is a bit of a misnomer, though, because HandlerSocket is already included in Percona-Server, so we’ll really just be enabling it.

More »

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.