How to rotate keys when using component_keyring

1

Hi All,

I am struggling to find a document that will help me to rotate encryption key.
I am referring mysql documentation:
https://dev.mysql.com/doc/refman/8.0/en/keyring-functions-general-purpose.html#keyring-function-installation

I installed the keyring functions and
I generated keys using:
SELECT keyring_key_generate(‘prodSharedKey’, ‘RSA’, 32);

And now I want to rotate this key.
But unable to find how to rotate this.

Any help would be appreciated.

1 Like
2

Hello @ulka01,
“rotating the key” is the same thing as generating a new key to replace the old one. You generated key ‘X’. You can’t rotate that key; it’s a key. You instead generate a new key, ‘Y’ and replace ‘X’ with ‘Y’. That procedure is “rotating the key.”

In MySQL, when you use InnoDB encrypted tables, there is a MASTER KEY. You can rotate this using ALTER INSTANCE ROTATE INNODB MASTER KEY; but what that does it the process I said above, a new key is generated and the old one is replaced.

1 Like
3

Hello,

Does that mean the tables that were encrypted using the ‘X’ key will be now be using ‘Y’ key. And the data in those tables will still be valid and useful.

1 Like
4

Specifically, each InnoDB table is encrypted by its own encryption key. This key is stored in the header of each table file and is encrypted itself using the master key. When you rotate the master key, you make a new key, decrypt the tablespace keys with the old key, and re-encrypt the table’s key with the new master key. Yes, your data is always 100% valid and useful. Rotating a specific tablespace key requires rebuilding the entire table. Rotating the master key is a <5s non-blocking operation.

1 Like
5

There is a difference:

mysql> SELECT KEY_ID FROM performance_schema.keyring_keys;
±-------------------------------------------------+
| KEY_ID |
±-------------------------------------------------+
| INNODBKey-85e36858-56a3-11ed-90a5-005056ab627a-1 |
| prodSharedKey |
±-------------------------------------------------+

I want to rotate prodSharedKey.
As per your explanation, does that mean I need to rebuild all the tables that are originally used this prodSharedKey key.

1 Like
6

prodSharedKey is some key that you created manually. This key has absolutely nothing to do with InnoDB or encrypting anything within MySQL. This key is for you to use as you wish. As I said above, you can’t “rotate” this key. You simply create a new key prodSharedKey_New and then replace prodSharedKey with the new one.

InnoDB has an internal mechanism called “key rotation” which, again, as I explained above, does these same steps, but in an automated way.

Please explain what it is that you are trying to accomplish and maybe I can give better direction.

1 Like
7

Thanks for the explanation.
We have few encrypted tables that are using this prodSharedKey.
Now we want to simulate a situation wherein the key is compromised(shared with someone unknowingly), and so we want to generate a new key, but the table data must be valid.
And all users authorized to view the data must get same result as they saw before the new key.
I hope to explain what I want.

1 Like
8

Please provide the SQL you used to do this. I cannot find any commands in the MySQL manual that show how you can use custom keys for tablespace encryption.

1 Like
9

We used below query to insert records in the table:
INSERT INTO job (
start_at, end_at, updated_at, created_at, warrant_id, target_id, created_by, interception_status, deleted_at
)
VALUES (
AES_ENCRYPT(NOW(), ‘${keyPair.secret}’, ‘${keyPair.iv}’),
AES_ENCRYPT(NOW(), ‘${keyPair.secret}’, ‘${keyPair.iv}’),
AES_ENCRYPT(NOW(), ‘${keyPair.secret}’, ‘${keyPair.iv}’),
AES_ENCRYPT(NOW(), ‘${keyPair.secret}’, ‘${keyPair.iv}’),
AES_ENCRYPT(?, ‘${keyPair.secret}’, ‘${keyPair.iv}’),
AES_ENCRYPT(?, ‘${keyPair.secret}’, ‘${keyPair.iv}’),
AES_ENCRYPT(?, ‘${keyPair.secret}’, ‘${keyPair.iv}’),
AES_ENCRYPT(?, ‘${keyPair.secret}’, ‘${keyPair.iv}’),
AES_ENCRYPT(?, ‘${keyPair.secret}’, ‘${keyPair.iv}’)
);

${keyPair.secret}: is the value of the key and
${keyPair.iv} is the first 16 characters of the key

1 Like
10

Right, that’s exactly what I thought you were doing. That is not tablespace encryption. That is data/column-level encryption using a custom key. MySQL is not managing that key; you are managing that key.

In order to “rotate” the key, you do exactly as I said above: first generate a new key ‘Y’, then for each row in the table: decrypt the column using key ‘X’, then re-encrypt the row using key ‘Y’. After all rows have been re-encrypted, delete key ‘X’.

Again, you are not using tablespace encryption. You are doing manual encryption and thus all key management must be done by you.

1 Like
11

Superb explanation, I got exactly what I was looking for and I know what needs to be done.
Many thanks.

1 Like