{"id":97667,"date":"2020-10-20T08:42:25","date_gmt":"2020-10-20T06:42:25","guid":{"rendered":"https:\/\/prohoster.info\/blog\/administrirovanie\/shifrovanie-v-mysql-ispolzovanie-master-key"},"modified":"2020-10-20T08:42:25","modified_gmt":"2020-10-20T06:42:25","slug":"shifrovanie-v-mysql-ispolzovanie-master-key","status":"publish","type":"post","link":"https:\/\/prohoster.info\/en\/blog\/administrirovanie\/shifrovanie-v-mysql-ispolzovanie-master-key","title":{"rendered":"Encryption in MySQL: Using Master Key","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"<h2><\/h2>\n<p><strong><em>As we approach the start of a new course enrollment&nbsp;<\/em><\/strong><noindex><a rel=\"nofollow\" href=\"https:\/\/otus.pw\/wMyj\/\"><strong><em>\"Databases\"<\/em><\/strong><\/a><\/noindex><strong><em>&nbsp;we continue to publish a series of articles on encryption in MySQL.<\/em><\/strong><\/p>\n<p><img decoding=\"async\" alt=\"Encryption in MySQL: Using Master Key\" src=\"\/wp-content\/uploads\/2020\/10\/762866d2c321acd726e30606c9108122.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p>In the previous article of this series (<noindex><a rel=\"nofollow\" href=\"https:\/\/habr.com\/ru\/company\/otus\/blog\/522092\/\"><u>Encryption in MySQL: key storage<\/u><\/a><\/noindex>) we discussed key stores. In this article, we will examine how the master key is used and also discuss the advantages and disadvantages of envelope encryption.&nbsp;<\/p>\n<p>The concept of envelope encryption is that the keys used for encrypting data (tablespace keys) are encrypted with another key (the master key). Tablespace keys are effectively used to encrypt the data. Graphically, this can be represented as follows:<\/p>\n<p><img decoding=\"async\" alt=\"Encryption in MySQL: Using Master Key\" src=\"\/wp-content\/uploads\/2020\/10\/385afda0eff739f4720f59799c35b51f.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p>The master key is stored in the keyring, while the tablespace keys are located in the headers of the encrypted tablespaces (on page 0 of the tablespace).&nbsp;<\/p>\n<p>In the image above:<\/p>\n<ul>\n<li>\n<p>Table A is encrypted with Key 1. Key 1 is encrypted using the master key and is stored in an encrypted form in the header of Table A.<\/p>\n<\/li>\n<li>\n<p>Table B is encrypted with Key 2. Key 2 is encrypted using the master key and is stored in an encrypted form in the header of Table B.<\/p>\n<\/li>\n<li>\n<p>And so on.<\/p>\n<\/li>\n<\/ul>\n<p>When the server needs to decrypt Table A, it retrieves the master key from the keyring, reads the encrypted Key 1 from the header of Table A, and decrypts Key 1. The decrypted Key 1 is cached in the server's memory and used to decrypt Table A.<\/p>\n<h3>InnoDB<\/h3>\n<p>In InnoDB, actual encryption and decryption occur at the input\/output level. That is, a page is encrypted right before it is flushed to disk and decrypted immediately after being read from the disk.<\/p>\n<p>In InnoDB, encryption works only at the tablespace level. By default, all tables are created in separate tablespaces (<noindex><a rel=\"nofollow\" href=\"https:\/\/dev.mysql.com\/doc\/refman\/8.0\/en\/innodb-file-per-table-tablespaces.html\"><u>file-per-table tablespace<\/u><\/a><\/noindex>). In other words, a tablespace is created that can contain only one table. However, you can also create tables in the general tablespace (<noindex><a rel=\"nofollow\" href=\"https:\/\/dev.mysql.com\/doc\/refman\/8.0\/en\/general-tablespaces.html\"><u>general tablespace<\/u><\/a><\/noindex>). However, the tablespace is always located within some tablespace. Since encryption is implemented at the tablespace level, it is either fully encrypted or not. This means that you cannot encrypt only part of the tables in the main tablespace.&nbsp;<\/p>\n<p>If for some reason you have file-per-table disabled, all tables are created within the system tablespace (system tablespace). In <noindex><a rel=\"nofollow\" href=\"https:\/\/www.percona.com\/software\/mysql-database\/percona-server\"><u>Percona Server for MySQL<\/u><\/a><\/noindex> the system tablespace can be encrypted using the innodb variable<em>sys<\/em>tablespace<em>encrypt or by using encryption threads, but this is still an experimental feature. This is not available in MySQL.<\/em><\/p>\n<p><em>Before proceeding, we need to examine the structure of the master key ID. It consists of a UUID, KEY<\/em>The ID and prefix 'INNODBKey'. It looks like this: INNODBKey-UUID-KEY<em>ID.<\/em><\/p>\n<p><em>UUID is the server's uuid associated with the encrypted tablespace. KEY<\/em>ID is simply a constantly increasing value. Upon the initial creation of the master key KEY<em>ID equals 1. When rotating the key and a new master key is created, the KEY<\/em>ID = 2 and so on. We will discuss key rotation in more detail in the subsequent articles of this series.<\/p>\n<p>Now that we know what the master key ID looks like, let's look at the header of the encrypted tablespace. When the tablespace is encrypted, encryption information is added to the header. It looks like this:<\/p>\n<p><img decoding=\"async\" alt=\"Encryption in MySQL: Using Master Key\" src=\"\/wp-content\/uploads\/2020\/10\/0703933a85f0a106c07c0dbd6b7f4186.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p>KEY ID is this KEY<em>The ID from the master key identifier we already discussed. UUID is the server's uuid, which is also used in the master key identifier. TABLESPACE KEY is a 256-bit key 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 (only 128 of the 256 bits are used). At the end, there is a CRC32 checksum for the TABLESPACE KEY and IV.<\/em><\/p>\n<p><em>All this time, I have simplified a bit by saying that there is an encrypted tablespace key in the header. In reality, both the tablespace key and the initialization vector are stored and encrypted together using the master key. Remember that before encrypting the tablespace key and the initialization vector, a CRC32 is calculated for them.<\/em><\/p>\n<h3>What is the purpose of CRC32?<\/h3>\n<p><em>In short, it is to ensure the validity of the master key. After decrypting the tablespace key and the initialization vector, a checksum is calculated and compared with the CRC32 stored in the header. If the checksums match, then we have the correct master key and tablespace key. Otherwise, the tablespace is marked as missing (we still won't be able to decrypt it).<\/em><\/p>\n<p><em>You may ask: when is key verification performed? The answer is at server startup. The server with encrypted tables\/tablespaces reads the UUID, KEY<\/em>The ID from the header and generates the master key identifier. It then retrieves the necessary master key from the keyring, decrypts the tablespace key, and checks the checksum. Once again, if the checksum matches, all is well; if not, the tablespace is marked as missing.<\/p>\n<p>If you read the previous article in this series (<noindex><a rel=\"nofollow\" href=\"https:\/\/habr.com\/ru\/company\/otus\/blog\/522092\/\"><u>Encryption in MySQL: key storage<\/u><\/a><\/noindex>), you might remember that when using a server key store, the server at startup receives only a list of key identifiers, specifically, key id and user id, as this pair uniquely identifies the key. Now I am saying that the server at startup obtains all the keys it needs to check the ability to decrypt the tablespace keys. So why during initialization, in the case of the server key store, are only key<em>id and user<\/em>id loaded, and not all keys? Because you might not need all the keys. This is mainly related to the rotation of the master key. When the master key is rotated, a new master key is created in the store, but the old keys are not deleted. Thus, in the server key store, you may have many keys that the server does not need and therefore do not get retrieved during server startup.<\/p>\n<p>It\u2019s time to discuss the advantages and disadvantages of encryption using a master key. The biggest advantage is that you only need one encryption key (the master key), which is stored separately from your encrypted data. This makes server startup fast, and the storage small, simplifying management. Additionally, the single master key is easy to regenerate.<\/p>\n<p>However, encrypting with a master key has one significant drawback: once a tablespace is encrypted using a tablespace_key, it remains encrypted with the same key indefinitely. Rotating the master key does not help here. Why is this a drawback? We know that MySQL has bugs that can cause sudden crashes and the creation of a core file. Since the core file contains a memory dump of the server, it\u2019s possible that the dump will reveal the decrypted tablespace key. What\u2019s worse, decrypted tablespace keys are stored in memory, which may be swapped to disk. You might argue this isn\u2019t a drawback because root access is required to access these files and the swap space. That\u2019s true, but root access is only necessary temporarily. Once someone gains access to the decrypted tablespace key, they can continue using it to decrypt data without needing root privileges. Furthermore, a disk may be stolen, and the swap\/core files can be read with third-party tools. The goal of TDE (Transparent Data Encryption) is to make it unreadable even if the disk is stolen. <noindex><a rel=\"nofollow\" href=\"https:\/\/www.percona.com\/software\/mysql-database\/percona-server\"><u>Percona Server for MySQL<\/u><\/a><\/noindex> There is an option to re-encrypt the tablespace with newly generated keys. This feature is called encryption threads and, at the time of writing this article, is still experimental.<\/p>\n<p><noindex><a rel=\"nofollow\" href=\"https:\/\/otus.pw\/wMyj\/\"><strong>Theory and practice of using ClickHouse in real applications. Alexander Zaitsev (2018)<\/strong><\/a><\/noindex><\/p>\n<h3>Read more:<\/h3>\n<ul>\n<li>\n<p><noindex><a rel=\"nofollow\" href=\"https:\/\/habr.com\/ru\/company\/otus\/blog\/522092\/\">Encryption in MySQL: key storage<\/a><\/noindex><\/p>\n<\/li>\n<\/ul>\n<p>Source: <a content=\"nofollow\" rel=\"nofollow\" href=\"https:\/\/habr.com\/ru\/company\/otus\/blog\/524050\/\">habr.com<\/a> <\/p>","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"excerpt":{"rendered":"<p>\u0412 \u043f\u0440\u0435\u0434\u0434\u0432\u0435\u0440\u0438\u0438 \u0441\u0442\u0430\u0440\u0442\u0430 \u043d\u043e\u0432\u043e\u0433\u043e \u043d\u0430\u0431\u043e\u0440\u0430 \u043d\u0430 \u043a\u0443\u0440\u0441&nbsp;\u00ab\u0411\u0430\u0437\u044b \u0434\u0430\u043d\u043d\u044b\u0445\u00bb&nbsp;\u043f\u0440\u043e\u0434\u043e\u043b\u0436\u0430\u0435\u043c \u043f\u0443\u0431\u043b\u0438\u043a\u043e\u0432\u0430\u0442\u044c \u0441\u0435\u0440\u0438\u044e \u0441\u0442\u0430\u0442\u0435\u0439 \u043f\u0440\u043e \u0448\u0438\u0444\u0440\u043e\u0432\u0430\u043d\u0438\u0435 \u0432 MySQL. \u0412 \u043f\u0440\u0435\u0434\u044b\u0434\u0443\u0449\u0435\u0439 \u0441\u0442\u0430\u0442\u044c\u0435 \u044d\u0442\u043e\u0439 \u0441\u0435\u0440\u0438\u0438 (\u0428\u0438\u0444\u0440\u043e\u0432\u0430\u043d\u0438\u0435 \u0432 MySQL: \u0445\u0440\u0430\u043d\u0438\u043b\u0438\u0449\u0435 \u043a\u043b\u044e\u0447\u0435\u0439) \u043c\u044b \u0433\u043e\u0432\u043e\u0440\u0438\u043b\u0438 \u043e \u0445\u0440\u0430\u043d\u0438\u043b\u0438\u0449\u0430\u0445 \u043a\u043b\u044e\u0447\u0435\u0439. \u0412 \u044d\u0442\u043e\u0439 \u0441\u0442\u0430\u0442\u044c\u0435 \u043c\u044b \u0440\u0430\u0441\u0441\u043c\u043e\u0442\u0440\u0438\u043c, \u043a\u0430\u043a \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u0443\u0435\u0442\u0441\u044f \u0433\u043b\u0430\u0432\u043d\u044b\u0439 \u043a\u043b\u044e\u0447 (master key), \u0430 \u0442\u0430\u043a\u0436\u0435 \u043e\u0431\u0441\u0443\u0434\u0438\u043c \u0434\u043e\u0441\u0442\u043e\u0438\u043d\u0441\u0442\u0432\u0430 \u0438 \u043d\u0435\u0434\u043e\u0441\u0442\u0430\u0442\u043a\u0438 \u0448\u0438\u0444\u0440\u043e\u0432\u0430\u043d\u0438\u044f \u043c\u0435\u0442\u043e\u0434\u043e\u043c \u043a\u043e\u043d\u0432\u0435\u0440\u0442\u043e\u0432 (envelope encryption).&nbsp; \u0418\u0434\u0435\u044f \u0448\u0438\u0444\u0440\u043e\u0432\u0430\u043d\u0438\u044f \u043a\u043e\u043d\u0432\u0435\u0440\u0442\u043e\u0432 [&hellip;]<\/p>\n","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"author":1,"featured_media":97668,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[688],"tags":[],"class_list":["post-97667","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-administrirovanie"],"aioseo_notices":[],"aioseo_head":"\n\t\t<!-- All in One SEO 5.0.3 - aioseo.com -->\n\t<meta name=\"description\" content=\"\u0412 \u043f\u0440\u0435\u0434\u0434\u0432\u0435\u0440\u0438\u0438 \u0441\u0442\u0430\u0440\u0442\u0430 \u043d\u043e\u0432\u043e\u0433\u043e \u043d\u0430\u0431\u043e\u0440\u0430 \u043d\u0430 \u043a\u0443\u0440\u0441 \u00ab\u0411\u0430\u0437\u044b \u0434\u0430\u043d\u043d\u044b\u0445\u00bb \u043f\u0440\u043e\u0434\u043e\u043b\u0436\u0430\u0435\u043c.\" \/>\n\t<meta name=\"robots\" content=\"max-image-preview:large\" \/>\n\t<meta name=\"author\" content=\"Yuri Gagarin\"\/>\n\t<link rel=\"canonical\" href=\"https:\/\/prohoster.info\/en\/blog\/administrirovanie\/shifrovanie-v-mysql-ispolzovanie-master-key\" \/>\n\t\t<meta name=\"generator\" content=\"All in One SEO (AIOSEO) 5.0.3\" \/>\n\t\t<meta property=\"og:locale\" content=\"en_US\" \/>\n\t\t<meta property=\"og:site_name\" content=\"ProHoster | \u041a\u0443\u043f\u0438\u0442\u044c \u043d\u0430\u0434\u0435\u0436\u043d\u044b\u0439 \u0445\u043e\u0441\u0442\u0438\u043d\u0433 \u0434\u043b\u044f \u0441\u0430\u0439\u0442\u043e\u0432 \u0441 \u0437\u0430\u0449\u0438\u0442\u043e\u0439 \u043e\u0442 DDoS, VPS VDS \u0441\u0435\u0440\u0432\u0435\u0440\u044b\" \/>\n\t\t<meta property=\"og:type\" content=\"article\" \/>\n\t\t<meta property=\"og:title\" content=\"\ud83e\udd47\u0428\u0438\u0444\u0440\u043e\u0432\u0430\u043d\u0438\u0435 \u0432 MySQL: \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u043d\u0438\u0435 Master Key | ProHoster\" \/>\n\t\t<meta property=\"og:description\" content=\"\u0412 \u043f\u0440\u0435\u0434\u0434\u0432\u0435\u0440\u0438\u0438 \u0441\u0442\u0430\u0440\u0442\u0430 \u043d\u043e\u0432\u043e\u0433\u043e \u043d\u0430\u0431\u043e\u0440\u0430 \u043d\u0430 \u043a\u0443\u0440\u0441 \u00ab\u0411\u0430\u0437\u044b \u0434\u0430\u043d\u043d\u044b\u0445\u00bb \u043f\u0440\u043e\u0434\u043e\u043b\u0436\u0430\u0435\u043c.\" \/>\n\t\t<meta property=\"og:url\" content=\"https:\/\/prohoster.info\/en\/blog\/administrirovanie\/shifrovanie-v-mysql-ispolzovanie-master-key\" \/>\n\t\t<meta property=\"og:image\" content=\"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg\" \/>\n\t\t<meta property=\"og:image:secure_url\" content=\"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg\" \/>\n\t\t<meta property=\"og:image:width\" content=\"350\" \/>\n\t\t<meta property=\"og:image:height\" content=\"350\" \/>\n\t\t<meta property=\"article:published_time\" content=\"2020-10-20T06:42:25+00:00\" \/>\n\t\t<meta property=\"article:modified_time\" content=\"2020-10-20T06:42:25+00:00\" \/>\n\t\t<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/prohoster\" \/>\n\t\t<meta property=\"article:author\" content=\"https:\/\/www.facebook.com\/prohoster\" \/>\n\t\t<!-- All in One SEO -->\n\n","aioseo_head_json":{"title":"\ud83e\udd47Encryption in MySQL: Using the Master Key | ProHoster","description":"In anticipation of the new enrollment for the \"Databases\" course, we continue.","canonical_url":"https:\/\/prohoster.info\/en\/blog\/administrirovanie\/shifrovanie-v-mysql-ispolzovanie-master-key","robots":"max-image-preview:large","keywords":"","webmasterTools":{"miscellaneous":""},"schema":null,"og:locale":"en_US","og:site_name":"ProHoster | \u041a\u0443\u043f\u0438\u0442\u044c \u043d\u0430\u0434\u0435\u0436\u043d\u044b\u0439 \u0445\u043e\u0441\u0442\u0438\u043d\u0433 \u0434\u043b\u044f \u0441\u0430\u0439\u0442\u043e\u0432 \u0441 \u0437\u0430\u0449\u0438\u0442\u043e\u0439 \u043e\u0442 DDoS, VPS VDS \u0441\u0435\u0440\u0432\u0435\u0440\u044b","og:type":"article","og:title":"\ud83e\udd47\u0428\u0438\u0444\u0440\u043e\u0432\u0430\u043d\u0438\u0435 \u0432 MySQL: \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u043d\u0438\u0435 Master Key | ProHoster","og:description":"\u0412 \u043f\u0440\u0435\u0434\u0434\u0432\u0435\u0440\u0438\u0438 \u0441\u0442\u0430\u0440\u0442\u0430 \u043d\u043e\u0432\u043e\u0433\u043e \u043d\u0430\u0431\u043e\u0440\u0430 \u043d\u0430 \u043a\u0443\u0440\u0441 \u00ab\u0411\u0430\u0437\u044b \u0434\u0430\u043d\u043d\u044b\u0445\u00bb \u043f\u0440\u043e\u0434\u043e\u043b\u0436\u0430\u0435\u043c.","og:url":"https:\/\/prohoster.info\/en\/blog\/administrirovanie\/shifrovanie-v-mysql-ispolzovanie-master-key","og:image":"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg","og:image:secure_url":"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg","og:image:width":350,"og:image:height":350,"article:published_time":"2020-10-20T06:42:25+00:00","article:modified_time":"2020-10-20T06:42:25+00:00","article:publisher":"https:\/\/www.facebook.com\/prohoster","article:author":"https:\/\/www.facebook.com\/prohoster"},"aioseo_meta_data":{"post_id":"97667","title":null,"description":null,"keywords":null,"keyphrases":null,"primary_term":null,"canonical_url":null,"og_title":null,"og_description":null,"og_object_type":"default","og_image_type":"default","og_image_url":null,"og_image_width":null,"og_image_height":null,"og_image_custom_url":null,"og_image_custom_fields":null,"og_video":null,"og_custom_url":null,"og_article_section":null,"og_article_tags":null,"twitter_use_og":false,"twitter_card":"default","twitter_image_type":"default","twitter_image_url":null,"twitter_image_custom_url":null,"twitter_image_custom_fields":null,"twitter_title":null,"twitter_description":null,"schema":{"blockGraphs":[],"customGraphs":[],"default":{"data":{"Article":[],"Course":[],"Dataset":[],"FAQPage":[],"Movie":[],"Person":[],"Product":[],"ProductReview":[],"Car":[],"Recipe":[],"Service":[],"SoftwareApplication":[],"WebPage":[]},"graphName":"","isEnabled":true},"graphs":[]},"schema_type":null,"schema_type_options":null,"pillar_content":false,"robots_default":true,"robots_noindex":false,"robots_noarchive":false,"robots_nosnippet":false,"robots_nofollow":false,"robots_noimageindex":false,"robots_noodp":false,"robots_notranslate":false,"robots_max_snippet":null,"robots_max_videopreview":null,"robots_max_imagepreview":"large","priority":null,"frequency":null,"local_seo":null,"seo_analyzer_scan_date":null,"breadcrumb_settings":null,"limit_modified_date":false,"reviewed_by":null,"ai":null,"created":"2021-02-28 10:15:28","updated":"2022-10-03 07:13:51","focus_keyword":null,"additional_keywords":null,"truseo_locale":null},"gt_translate_keys":[{"key":"link","format":"url"}],"_links":{"self":[{"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/posts\/97667","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/comments?post=97667"}],"version-history":[{"count":0,"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/posts\/97667\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/media\/97668"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/media?parent=97667"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/categories?post=97667"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/tags?post=97667"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}