 Hello everyone, let's welcome to our next talk in security of the room and let's welcome Robert and He's talked about transparent data encryption in my SQL and Percona server Hi, hello My name is Robert. I've been working on transparent data encryption in Percona server for last three years and before that I was working at Oracle Transparent data encryption in my SQL So today I want to kind of show how the internals works how it's all connected and What are what's that? What are things to look at when you decide to turn it on in your server? So First thing we need to cover our key rings So basically those are plugins Which Percona server and my SQL use to store encryption key or to communicate between vault server servers with keys key stores or Or just just a five so they come into favor flavors one is a server based So giving plug-in connects to server takes keys from the server put that into in cache memory and Provides that to my SQL or Percona server. So those keys are cached in memory and apart So as I said it can communicate with the server or it can just use a file to store the keys So when you install key ring the installation itself is always successful Because in my school and in Percona you need to have a plug-in installed to be able to use its variables and So we should get if you didn't provide enough information warnings in your server log file that you should You know set up key ring vault config options or for key ring vault Plugging or key ring file data for key ring five plug-in. So when you set up one of those variables Actually for key involved key involved config the Percona server would try to connect to vault server and Check it and check if it's if it's working and the same with key ring file data So that will try to load the keys from from from the file and check the version check if nobody manipulate the key ring file and it checks the checksum and If it's all right, it allows the Percona server on my square to use those keys So with key ring file All information are loaded immediately from from file with keys So when you select from a table that was encrypted the Key that is used to the crypto the table is available for sure in memory and With key ring vault, it's a bit different and it's comes with also key ring of TV or different other Keering AWS that you can use with my SQL It doesn't retrieve all the data on the startup from from the key server just the list of the Of of the fire of the keys So you may ask So what happens if I try to select from a table and cryptic table and keys not there So it will retrieve the keys also on the startup But just the active ones and I will show that a bit later The weird thing about queuing file is that we implemented it when you change when you do a single change into Keering file first the backup file is created. So whole content of a key ring file is created is copied and After that if the change in the in-memory cache is successful, it will be swapped with the current key ring file So the reason we did that is because we we don't know how many users are gonna use it and there are so So if it and it becomes a bottleneck ever we will kind of change it, but every single change to key ring is it means copies all the file or all the key ring file and Also dumps all the memory of key rings into the file. So it's rewritten each and every time With key ring vault, it's much less intensive I would say It just sends a key over a network or request of deletion of a key from both server There are problems like connection legs, of course with this kind of approach But you cannot do without them Also, there is a concept of that you should have a separate place in your key store where you Store your keys per key ring. So imagine you have a cluster and you have like a Master and slave and both of them using the same place in your vault server Where they store the keys? Then you would get the clash between the names of the keys, of course So with in my SQL master key is a unique has unique name I will talk about that also later, but when you use queuing UDF there is a special plugin that users can store their own keys keys into the Vault server that becomes a problem because those names are not unique So this kind of separation is natural for queuing file because queuing file state is next to the Server itself. So it's not a problem. There will be a file per each server But there is some work needed for when you use a hash of vaults server or Oracle key oracle key vault for for the matter I don't know if you guys use Vault, it's very cool for open source Server for key storing keys and you in In key vault in key involved you provides a mouth point Where the keys should be stored by killing vault in vault server? So you can do the separation but providing different mouth points to each key ring, but that's a kind of a High privilege thing to do so you would have to have a privileges to create mouth point and You may not want to do that to give that privileges to all your DBAs so the vault server also provides you that you can create a one mouth point and Provide a path to key ring vault configuration file and when the first Key is sent to key ring vault vault server will automatically create a directory in that Mount point and when you delete the last key from that path Vault server will also delete the The directory So yeah, so this is the configuration file for key ring vault Vault URL, so that's that's the URL of vault server The secret mouth point you need to create on vault server where the keys are gonna be stored and you can use this path to separate those Places for each server Percona server then you have a token a token is provided to you by vault server It's one one time token and vault certificate. So this one is optional because you can add your Vault certificate into a to your machine. So it would be trusted by machine or you can provide it here and Keering vol will use it to authenticate the vault server We store keys inside wall server in base 64 and coding so whenever you are In a position you want to list your keys in vault server. You should just decode it So here we have the code is something and It appears it's a master key and I will talk a bit about how this is structured Yes, and also the master key here has a UUID of a server embedded in its name. So it's a unique name. So you can Kind of use a common place in this vault server sorry to store keys from different my school or Percona servers and You can use also Keering UDF Plugging and this Keering UDF is users can use to store its own Keys in the vault server or or in this Keering file file and You can generate a new New key fetch you have also you can also fetch key length type Storic new key. I don't remove a key. So how does this you know db encryption is really done So in my school table space consists of pages so we have table spaces which are A set of tables or you can have a purified table space, which is a one type table has its own dedicated table space and that's it and In my school the encryption works on a table space level So you can have whole table space encrypted, but you cannot have some tables encrypted in table space So what is master key encryption? It's an envelope encryption. So you use one key To to encrypt other keys in the server So So we have a key master key resides in Keering that we already discussed and then we have a Server generated key per table Inside my school data directory. So Whenever a table is to be encrypted a key is generated for it by per cona server itself and and So it stays next to the data is encrypts So in order for it to stay next to the data it encrypts It has to be also encrypted and this is this key is encrypted by the master key coming from Keering so Encryption table space key is stored in a table space header so in for And This is how this kind of looks like so we have some versioning through version already key key ID and you ID are used for Combined combine together to get the master key ID Then we have a table space key and IV so any saturation vector that are together encrypted with the master key and As the net as the last part is a checksum of table space key and I be an IV But the checksum itself is taken from plain text table space key and I be not there unencrypted one so yeah, when we combine key ID and you ID we get the Master key ID So how do we know which master key we should fetch from Keering to the crypto table? Because we have this table a let's say and we have a key There can be multiple master keys in a Keering. So we need to have this ID by looking at Table space header we take key ID and you ID and We combine it and we have the master key ID So we know how to which key master key we should fetch from the Keering but the key rings can be swapped and we may have a Keering with a key ID that is Okay for us to fetch that we are looking for but it turns out that it's a wrong key Wrong key right because the kewing's were swapped or something So how we make sure that the key itself is correct because if we start using incorrect key for decrypting data We will get the all mess. Well, the server will crash basically So we do that by by checking this year's checksum We retrieve master key the crypt table space key and IV We do the checksum of those two and we check and we Sorry Yeah, and we much much it with the CRC 32 from the table space header So once that compared and it's okay. We know that it's safe to use this key to decrypt this table space So how do we make sure that we are able to the trip table when we need it as I showed at the beginning with Keering Well, we just fetch the list of the keys and active keys and this is how we know which keys are active So at the start of the table of the server We read page 0 of each table space where table space header resides So if there is encrypted table space, it says flux set to be encrypted with page 0 with table space header We get the master key from Keering. So we check if we can connect to Keering We do the decryption and we check the CRC 32 So by by that we know that we are Okay to decrypt this table, but if any of those steps fail, we have to say that the table space is just missing We don't say we cannot decrypt it is will just show us missing table space So this this part is done on the startup of the server. So it's important to Have a limited number of master keys active in the In my SQL because if you have a lot of them That would mean that there will be a lot of Transmission between fold server and your my SQL instance were on the startup. So when you're rebooting it will slow down your reboots So let's talk about what crypto we use in my school So First one is as 256 ECB. So this is the famous picture why it's insecure Because it's not randomized algorithm. So You can see the repetition in the plain text by looking at the cipher text So it will always like and code the same value to the same value If it's a if it's a black pixel it let's say it will encode it as a gray pixel and we can see where The shape of the pink wing by looking at the cipher text So why we are using it because we are using it only for table space key and IV and table space key Itself is a round on thing. So it should not have like a domestic Repetition in it and since that there is no The domestic repetition it's okay to use here and we kind of save IV because if you have this ECB you don't have to have IV Okay, so this is the block picture of higher how as basically works. It's takes 128 bits of text 256-bit long encryption key and provides the cipher text. So as to fix 256 in the AIS It means the long length of the key, but it all always works on like 128 bits long buck of text For page encryption we use a 256 cbc so change one changed and It's a randomized one. So you get no repetition this way So how do you get the randomization? Basically, you never encrypt the same plain text twice so to do that you add To your plain text IV so initialization vector So it's important to have the initialization vector and key itself always to be different when you start Encoding with cbc or CTR. We also use CTR, but I Don't have time Okay, a bit about master key rotation. So When it's useful There is two cases actually when you end up in the situation you have a lot of master keys in your vault server and your Startup is getting slow because of that. You can you can ask the MySquareOp.com server to re-encrypt all your table space keys with the new master key and That's also The master key rotation is also important when you For security reasons because you can lose your key ring and you don't know about it So it's good thing to to rotate it from time to time and how is it look like so We re-encrypt it with the new master key the table space key and IV and we We changed the key ID and new ID to the new ones So so it would all point to one master key We don't have to update this checksum of table space key and IV because it's always stays the same and it's Because of that because it's always stayed the same. It's also the biggest drawback drawback of master key encryption so imagine you have the stable space key and Somebody stalls these ones because he has or she has access to your cord down So he will be able to decrypt your data Although you are not aware of it. You can rotate your master key But that won't help you because the stable space key that is actually used to encrypt a table will stay the same So we are working on some mitigation for that in Percona server now So those were the dynamics We have also been a encryption. So been look is a place where my school and Percona server drops its Events we call it and those events are then replicated to your slave servers in your replication schema So for bin log encryption if I seven you have to Provide two variables encrypt bin look so it will just encrypt the bin look but you have also to provide master verified checksum and what why is it that because When masters a master server is encrypting. It's decrypting its master. It's been look After decryption it will have to check if it's used the correct key and for that It will just verify the checksum that the checksum of an event is okay before sending it to slave otherwise, it could You could it could send just trash to slave server and crush it so that would be also a point of attack and For bin long encryption if I seven we provide a new even that it's never transferred across the network so after this event all the Bin lock is rest of the bin lock is encrypted So my school reads the bin lock sees this even at the beginning. It knows it has to start decrypting this event is never sent over the network because slave server doesn't need to know that Been looking master master server is encrypted and this event is served over and a network in a plain text So you have to have a TLS connection between master and slave So My school well bin lock cannot decrypt However, there is an option that you can connect your my school of bin lock reader into your My school server my school server will read the bin lock and sell it over to my school bin lock So you can kind of use it when you have an encrypted bin lock also We have also been looking encryption in a zero and upstream implementation It basically was the same as master key encryption. You have just a replication master key in your key ring and The server generated key per bin lock When you turn it on Master key per Kona server will just rot enable rotation of a bin lock So the new file will be created with because you have number of five bin lock files in your my school New file will be created and from starting from that file each all belongs will be encrypted We have also under table space encryption and read work encryption. It's almost same as the bin lock encryption Also system table space and double right buffers encryption. So this is the thing that is only in Percona server So there is a system table space encryption in my school IB data one and in this table space There is also double right buffer Which is not encrypted my school and we think it's a it's a problem because all the data before it gets written to the disk To the pages to the table spaces goes through this table double right buffers So if it's unencrypted, it's basically mean you can read anything even though you have encryption turned on My school IB data is a new table space for storing data dictionary information, so it's also encrypted and In Percona server, we have parallel double right buffer, which you can also encrypt So thank you This is for Percona life So the solution with master key there is no solution You can just alter encryption to know and alter encryption to yes So it will generate a new table space key, but you have to do it on your own Yeah, yeah, but those are encryption threads that will work not with this To level encryption that you have table space key and master key It will just use key directly from key ring so you can have new versions of keys and Change the key ring Yeah, so we are out of the time. So if you've got some other questions, please reach out after the talk