In anticipation of the start of a new recruitment for the course “Database” We continue to publish a series of articles about encryption in MySQL.
The previous article in this series (MySQL encryption: keystore) we talked about keystores. In this article, we will look at how the master key is used and also discuss the pros and cons of envelope encryption.
The idea behind envelope encryption is that the keys (tablespace keys) used for encryption are encrypted with a different key (master key). The keys of the table spaces are actually used to encrypt the data. Graphically, this can be represented as follows:
The master key is in the keyring, and the table space keys are in the encrypted table space headers (on page 0 of the table space).
In the picture above:
Table A is encrypted with Key 1. Key 1 is encrypted with the master key and stored encrypted in the header of Table A.
Table B is encrypted with Key 2. Key 2 is encrypted with the masker key and stored encrypted in the header of Table B.
When the server needs to decrypt table A, it retrieves the master key from the store, reads encrypted key 1 from the header of table A, and decrypts key 1. Decrypted key 1 is cached in the server’s memory and used to decrypt table A.
In InnoDB, the actual encryption and decryption is done at the I / O layer. That is, the page is encrypted just before it is flushed to disk and decrypted as soon as it is read from the disk.
In InnoDB, encryption only works at the tablespace level. And by default, all tables are created in separate tablespaces (file-per-table tablespace). In other words, a tablespace is created that can only contain one table. Although you can create tables in the main tablespace as well (general tablespace). But in any case, the table is always in some kind of tablespace. And since encryption is done at the tablespace level, it is either fully encrypted or not. That is, you cannot encrypt only part of the tables in the main tablespace.
If for some reason you have disabled file-per-table, then all tables are created inside the system tablespace. AT Percona Server for MySQL it is possible to encrypt the system tablespace using the innodb variablesystablespaceencrypt or using encryption threads, but this is still an experimental feature. This is not the case in MySQL.
Before moving on, we need to look at the structure of the master key ID. It consists of UUID, KEYID and prefix “INNODBKey”. It looks like this: INNODBKey-UUID-KEYID.
The UUID is the uuid of the server with the encrypted tablespace. KEYID is just an ever-increasing value. When first creating the master key KEYID is 1. When rotating a key, when a new master key is created, KEYID = 2 and so on. We will talk in more detail about rotation of master keys in the next articles of this series.
Now that we know what the master key ID looks like, let’s look at the header of the encrypted tablespace. When a tablespace is encrypted, encryption information is appended to the header. It looks like this:
KEY ID is KEYID from the master key ID that we have already discussed. The UUID is the server uuid, which is also used in the master key identifier. TABLESPACE KEY – the key for the table space, which consists of 256 bits randomly generated by the server. The initialization vector (IV) also consists of 256 randomly generated bits (although it should be 128 bits). IV is used to initialize AES encryption and decryption (out of 256 bits, only 128 is used). At the end there is a CRC32 checksum for the TABLESPACE KEY and IV.
All this time, I’ve been simplifying a bit by saying that there is an encrypted tablespace key in the header. In fact, the tablespace key and initialization vector are stored and encrypted together using the master key. Remember, before encrypting the tablespace key and initialization vector, the CRC32 is calculated for them.
Why is CRC32 needed?
In a nutshell, it’s to make sure the master key is valid. After decrypting the tablespace key and initialization vector, the checksum is calculated and compared to the CRC32 stored in the header. If the checksums match, then we have the correct master and tablespace key. Otherwise, the tablespace is marked as missing (we still won’t be able to decrypt it).
You may ask: at what point are the keys checked? The answer is when the server starts. The server with encrypted tables / table spaces at startup reads UUID, KEYID from header and generates master key id. It then retrieves the required master key from the store (keyring), decrypts the tablespace key, and verifies the checksum. Once again, if the checksum matches, then everything is fine, no – the tablespace is marked as missing.
If you read the last article in this series (MySQL encryption: keystore), then perhaps remember that when using the server-side keystore, the server at startup only receives a list of key identifiers, or rather, key id and user id, since this pair uniquely identifies the key. Now I say that the server, at startup, gets all the keys it needs to verify that the tablespace keys can be decrypted. So why, during initialization, in the case of server storage, only key is loadedid and userid, not all keys? Because you may not need all the keys. This is mainly due to the rotation of the master key. Rotating a master key creates a new master key in the repository, but does not delete the old keys. Thus, you may have many keys in the server-side keystore that are not needed by the server and therefore are not retrieved when the server starts.
Now is the time to talk a little about the pros and cons of master key encryption. The biggest advantage is that you only need one encryption key (master key), which will be kept separate from your encrypted data. This makes server startup quick and storage small, making management easier. And also the single master key is easy to regenerate.
However, encryption with the master key has one big drawback: once a tablespace is encrypted with tablespace_key, it always remains encrypted with the same key. Rotating the master key does not help here. Why is this a disadvantage? We know that MySQL has bugs that can lead to a sudden crash and the creation of a core file. Since the core file contains a server memory dump, it may happen that the dump contains the decrypted tablespace key. To make matters worse, the decrypted tablespace keys are stored in memory, which can be swapped to disk. You can tell that this is not a drawback, as you need root privileges to access these files and the swap partition. Yes. But root is only needed for a while. Once someone gains access to the decrypted tablespace key, he / she can continue to use it to decrypt data, even without root privileges. In addition, the disk can be stolen and the swap / core files can be read with third-party tools. The goal of TDE is to make it unreadable even if the disc is stolen. AT Percona Server for MySQL it is possible to re-encrypt the tablespace with the newly generated keys. This feature is called encryption threads and is still experimental at the time of this writing.
MySQL encryption: keystore