{"id":220,"date":"2017-05-17T13:04:31","date_gmt":"2017-05-17T13:04:31","guid":{"rendered":"https:\/\/www.spotonoracle.com\/?p=220"},"modified":"2017-05-17T13:04:31","modified_gmt":"2017-05-17T13:04:31","slug":"oracle-12-2-full-database-encryption-tde","status":"publish","type":"post","link":"https:\/\/www.spotonoracle.com\/?p=220","title":{"rendered":"Oracle 12.2 full database encryption (TDE)"},"content":{"rendered":"<p>Starting with Oracle 12.2 it is possible to encrypt all Tablespaces including SYSTEM, SYSAUX, TEMP, and UNDO. Off the top of my head I can think of a couple of reasons why encrypting SYSTEM and SYSAUX might be desired:<\/p>\n<ul>\n<li>Histograms in SYSAUX might contain sensitive data<\/li>\n<li>Application tables\/indexes in SYSTEM or SYSAUX Tablespace<\/li>\n<li>Hard-coded secrets in PL\/SQL objects (yes, I&#8217;ve seen it all!)<\/li>\n<\/ul>\n<p>Strangely enough, the 12.2 <a href=\"http:\/\/docs.oracle.com\/database\/122\/ASOAG\/frequently-asked-questions-about-transparent-data-encryption.htm#ASOAG10542\" target=\"_blank\">TDE FAQ<\/a> discourages encryption of &#8220;internal objects&#8221;:<br \/>\n<a href=\"https:\/\/www.spotonoracle.com\/wp-content\/uploads\/2017\/05\/tde-faq-1.png\"><img loading=\"lazy\" decoding=\"async\" width=\"1006\" height=\"47\" src=\"https:\/\/www.spotonoracle.com\/wp-content\/uploads\/2017\/05\/tde-faq-1.png\" alt=\"\" class=\"alignnone size-medium wp-image-224\" srcset=\"https:\/\/www.spotonoracle.com\/wp-content\/uploads\/2017\/05\/tde-faq-1.png 1006w, https:\/\/www.spotonoracle.com\/wp-content\/uploads\/2017\/05\/tde-faq-1-300x14.png 300w, https:\/\/www.spotonoracle.com\/wp-content\/uploads\/2017\/05\/tde-faq-1-768x36.png 768w, https:\/\/www.spotonoracle.com\/wp-content\/uploads\/2017\/05\/tde-faq-1-624x29.png 624w\" sizes=\"auto, (max-width: 1006px) 100vw, 1006px\" \/><\/a><br \/>\nI believe this to be a leftover from previous releases which hasn&#8217;t been cleaned up, yet. Because it is supported and the <a href=\"http:\/\/docs.oracle.com\/database\/122\/ASOAG\/toc.htm\" target=\"_blank\">Advanced Security Guide<\/a> provides plenty of information about it.<br \/>\n<a href=\"https:\/\/www.spotonoracle.com\/wp-content\/uploads\/2017\/05\/tde-doc-1.png\"><img loading=\"lazy\" decoding=\"async\" width=\"1125\" height=\"158\" src=\"https:\/\/www.spotonoracle.com\/wp-content\/uploads\/2017\/05\/tde-doc-1.png\" alt=\"\" class=\"alignnone size-medium wp-image-222\" srcset=\"https:\/\/www.spotonoracle.com\/wp-content\/uploads\/2017\/05\/tde-doc-1.png 1125w, https:\/\/www.spotonoracle.com\/wp-content\/uploads\/2017\/05\/tde-doc-1-300x42.png 300w, https:\/\/www.spotonoracle.com\/wp-content\/uploads\/2017\/05\/tde-doc-1-768x108.png 768w, https:\/\/www.spotonoracle.com\/wp-content\/uploads\/2017\/05\/tde-doc-1-1024x144.png 1024w, https:\/\/www.spotonoracle.com\/wp-content\/uploads\/2017\/05\/tde-doc-1-624x88.png 624w\" sizes=\"auto, (max-width: 1125px) 100vw, 1125px\" \/><\/a><\/p>\n<p>Splitting hairs, &#8220;Fully Encrypt Databases&#8221; is not entirely correct. There is currently no supported way to encrypt PDB$SEED Tablespaces&#8230;and I&#8217;ve tried for the fun of it:-)<br \/>\nForcing the PDB$SEED open read-write with &#8220;alter pluggable database PDB$SEED open force&#8221; and then open the keystore works. I could even create a new encryption key, but it won&#8217;t let me activate it:<\/p>\n<pre class=\"brush: sql; collapse: false; title: ; wrap-lines: false; notranslate\" title=\"\">\r\nSQL&gt; show con_name\r\nCON_NAME \r\n------------------------------\r\nPDB$SEED\r\n\r\nSQL&gt; administer key management create encryption key using tag 'PDBSEED_MK_1' force keystore identified by &quot;Strng-4-Scrty&quot; with backup using 'bck1';\r\nkeystore altered.\r\n\r\nSQL&gt; column key_id new_value new_key_id\r\nSQL&gt; select key_id from v$encryption_keys where con_id = 0 and tag = 'PDBSEED_MK_1';\r\nKEY_ID                                                                        \r\n------------------------------------------------------------------------------\r\nAVVfIyuTLk9sv2EyAw9dJ18AAAAAAAAAAAAAAAAAAAAAAAAAAAAA\r\n\r\nSQL&gt; administer key management use encryption key '&amp;new_key_id' identified by &quot;Strng-4-Scrty&quot; with backup using 'bck2';\r\n*\r\nERROR at line 1:\r\nORA-46656: operation not valid for container database\r\n<\/pre>\n<p>I know it doesn&#8217;t make a lot of sense as for instance, cloning from a encrypted PDB$SEED would be kind of a Catch-22.<\/p>\n<p>Anyway, back to encrypting Oracle&#8217;s &#8220;internal&#8221; Tablespaces. It&#8217;s pretty straight forward. It&#8217;s the same as you would setup the database for any TDE encryption.<br \/>\nAdd ENCRYPITON_WALLET_LOCATION to your sqlnet.ora<\/p>\n<pre class=\"brush: bash; collapse: false; title: ; wrap-lines: false; notranslate\" title=\"\">\r\nENCRYPTION_WALLET_LOCATION =\r\n  (SOURCE =\r\n    (METHOD = FILE)\r\n    (METHOD_DATA =\r\n      (DIRECTORY = \/u01\/app\/oracle\/admin\/$ORACLE_SID\/wallet\/tde)\r\n    )\r\n  )\r\n<\/pre>\n<p>Setup a keystore in the CDB$ROOT and generate a new key to use:<\/p>\n<pre class=\"brush: sql; collapse: false; title: ; wrap-lines: false; notranslate\" title=\"\">\r\nalter session set container=cdb$root;\r\n\r\n-- create PKCS#12 keystore (ewallet.p12)\r\nadminister key management\r\n  create keystore '\/u01\/app\/oracle\/admin\/DEV1\/wallet\/tde'\r\n  identified by &quot;Strng-4-Scrty&quot;\r\n;\r\n\r\n-- open the keystore\r\nadminister key management\r\n  set keystore open\r\n  identified by &quot;Strng-4-Scrty&quot;\r\n  container = current\r\n;\r\n\r\n-- create a new encryption key\r\nadminister key management\r\n  create encryption key using tag 'DEV1_ROOT_MK_1'\r\n  identified by &quot;Strng-4-Scrty&quot;\r\n  with backup using 'Before-Create-DEV1_ROOT_MK_1'\r\n  container = current\r\n;\r\n\r\n-- activate the key for use in this container\r\n--   note: newly created keys have &quot;con_id = 0&quot;, as soon as they are activated they get assigned to the respective PDB\r\ncolumn key_id new_value new_key_id\r\nselect key_id from v$encryption_keys where con_id = 0 and tag = 'DEV1_ROOT_MK_1';\r\nadminister key management\r\n  use encryption key '&amp;new_key_id'\r\n  using tag 'DEV1_ROOT_MK_1'\r\n  identified by &quot;Strng-4-Scrty&quot;\r\n  with backup using 'Before-Activate-DEV1_ROOT_MK_1'\r\n;\r\n\r\n-- we also want a local auto-login keystore (cwallet.sso)\r\n--   otherwise manual keystore password input is required during database startup\r\nadminister key management\r\n  create local auto_login keystore from keystore '\/u01\/app\/oracle\/admin\/DEV1\/wallet\/tde'\r\n  identified by &quot;Strng-4-Scrty&quot;\r\n;\r\n<\/pre>\n<p>Now, that we have the key setup it&#8217;s as simple as an ALTER TABLESPACE statement. We used the default algorithm AES128 when generating the encryption key so we specify the same here.<\/p>\n<pre class=\"brush: sql; collapse: false; title: ; wrap-lines: false; notranslate\" title=\"\">\r\nalter tablespace system\r\n  encryption online using 'AES128' encrypt\r\n  file_name_convert = ('system', 'system-enc')\r\n;\r\nalter tablespace sysaux\r\n  encryption online using 'AES128' encrypt\r\n  file_name_convert = ('sysaux', 'sysaux-enc')\r\n;\r\n<\/pre>\n<p>Be careful, if the specified algorithm is different from the one of the key the statement will still succeed. You&#8217;ll see following warning in the alert.log:<\/p>\n<pre class=\"brush: bash; collapse: false; title: ; wrap-lines: false; notranslate\" title=\"\">\r\nAn algorithm different from the database key is specified for SYSTEM tablespace. The algorithm has been overridden to AES128 per V$DATABASE_KEY_INFO view.\r\n<\/pre>\n<p>Let&#8217;s move to my PDB.<\/p>\n<pre class=\"brush: sql; collapse: false; title: ; wrap-lines: false; notranslate\" title=\"\">\r\nalter session set container=pdbdev1;\r\n\r\n-- assuming auto-login keystore is now open\r\n-- otherwise open password keystore\r\n\r\nadminister key management\r\n  create encryption key using tag 'PDBDEV1_MK_1'\r\n  force keystore\r\n  identified by &quot;Strng-4-Scrty&quot;\r\n  with backup using 'Before-Create-PDBDEV1_MK_1'\r\n;\r\n\r\ncolumn key_id new_value new_key_id\r\nselect key_id from v$encryption_keys where con_id = 0 and tag = 'PDBDEV1_MK_1';\r\nadminister key management\r\n  use encryption key '&amp;new_key_id'\r\n  using tag 'PDBDEV1_MK_1'\r\n  force keystore\r\n  identified by &quot;Strng-4-Scrty&quot;\r\n  with backup using 'Before-Activate-PDBDEV1_MK_1'\r\n;\r\n<\/pre>\n<p>To encrypt the SYSTEM\/SYSAUX Tablespace on our PDB you can use the same statements from above:<\/p>\n<pre class=\"brush: sql; collapse: false; title: ; wrap-lines: false; notranslate\" title=\"\">\r\nalter tablespace system\r\n  encryption online using 'AES128' encrypt\r\n  file_name_convert = ('system', 'system-enc')\r\n;\r\nalter tablespace sysaux\r\n  encryption online using 'AES128' encrypt\r\n  file_name_convert = ('sysaux', 'sysaux-enc')\r\n;\r\n<\/pre>\n<p>According to the documentation, undo, temp, and redo data originating from objects in encrypted tablespaces will also be encrypted.<br \/>\n<a href=\"https:\/\/www.spotonoracle.com\/wp-content\/uploads\/2017\/05\/tde-doc-2.png\"><img loading=\"lazy\" decoding=\"async\" width=\"1146\" height=\"105\" src=\"https:\/\/www.spotonoracle.com\/wp-content\/uploads\/2017\/05\/tde-doc-2.png\" alt=\"\" class=\"alignnone size-medium wp-image-223\" srcset=\"https:\/\/www.spotonoracle.com\/wp-content\/uploads\/2017\/05\/tde-doc-2.png 1146w, https:\/\/www.spotonoracle.com\/wp-content\/uploads\/2017\/05\/tde-doc-2-300x27.png 300w, https:\/\/www.spotonoracle.com\/wp-content\/uploads\/2017\/05\/tde-doc-2-768x70.png 768w, https:\/\/www.spotonoracle.com\/wp-content\/uploads\/2017\/05\/tde-doc-2-1024x94.png 1024w, https:\/\/www.spotonoracle.com\/wp-content\/uploads\/2017\/05\/tde-doc-2-624x57.png 624w\" sizes=\"auto, (max-width: 1146px) 100vw, 1146px\" \/><\/a><br \/>\nThere&#8217;s still reason to encrypt temporary Tablespaces in full:<\/p>\n<ul>\n<li>Temporary LOBs<\/li>\n<li>Global temporary tables<\/li>\n<\/ul>\n<p>Encrypting existing TEMP Tablespaces is not supported, we just create a new one and drop the old.<\/p>\n<pre class=\"brush: sql; collapse: false; title: ; wrap-lines: false; notranslate\" title=\"\">\r\ncreate temporary tablespace tempts_enc\r\n  tempfile '\/u02\/oradata\/DEV1\/tempts_enc-001.dbf' size 512M autoextend off\r\n  encryption using 'AES128' encrypt\r\n;\r\nalter database default temporary tablespace tempts_enc;\r\ndrop tablespace tempts including contents and datafiles;\r\n<\/pre>\n<p>Once I have encrypted SYSTEM, SYSAUX, TEMP, and all application Tablespaces there should be no need to encrypt the UNDO Tablespace, right? All undo data is then based on objects from encrypted Tablespaces and should therefore also be encrypted in the UNDO stream. This is to be further investigated. If you have any input, please leave a comment.<\/p>\n<p>One final note: if you want to understand the performance impact of TDE I highly recommend to use <a href=\"https:\/\/kevinclosson.net\/slob\/\" target=\"_blank\">SLOB<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Starting with Oracle 12.2 it is possible to encrypt all Tablespaces including SYSTEM, SYSAUX, TEMP, and UNDO. Off the top of my head I can think of a couple of reasons why encrypting SYSTEM and SYSAUX might be desired: Histograms in SYSAUX might contain sensitive data Application tables\/indexes in SYSTEM or SYSAUX Tablespace Hard-coded secrets [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[12,9],"tags":[],"class_list":["post-220","post","type-post","status-publish","format-standard","hentry","category-security","category-tde"],"_links":{"self":[{"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=\/wp\/v2\/posts\/220","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=220"}],"version-history":[{"count":22,"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=\/wp\/v2\/posts\/220\/revisions"}],"predecessor-version":[{"id":245,"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=\/wp\/v2\/posts\/220\/revisions\/245"}],"wp:attachment":[{"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=220"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=220"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=220"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}