{"id":3771,"date":"2018-04-11T13:15:02","date_gmt":"2018-04-11T04:15:02","guid":{"rendered":"http:\/\/haisins.epac.to\/wordpress\/?p=3771"},"modified":"2018-04-11T13:15:02","modified_gmt":"2018-04-11T04:15:02","slug":"transparent-data-encryption-tde-in-oracle-12c","status":"publish","type":"post","link":"http:\/\/haisins.synology.me\/wordpress\/?p=3771","title":{"rendered":"Transparent Data Encryption (TDE) In Oracle 12c"},"content":{"rendered":"<p>TDE (Transparent Data Encryption)\ub294 OS \ub370\uc774\ud130 \ud30c\uc77c\uc5d0 \uc800\uc7a5\ub41c \ub370\uc774\ud130\ub97c \uc554\ud638\ud654\ud558\ub294 \uba54\ucee4\ub2c8\uc998\uc744 \uc81c\uacf5\ud569\ub2c8\ub2e4.<\/p>\n<p>TDE\ub97c \uc0ac\uc6a9\ud558\uba74 \uc800\uc7a5\uc18c \uc218\uc900\uc758 \ub370\uc774\ud130\ub97c \uc554\ud638\ud654\ud558\uc5ec \ub370\uc774\ud130\ubca0\uc774\uc2a4 \uc678\ubd80\uc5d0\uc11c \ub370\uc774\ud130\uac00 \uc720\ucd9c \ub418\ub294 \uac83\uc744 \ubc29\uc9c0 \ud560 \uc218 \uc788\uc2b5\ub2c8\ub2e4.<\/p>\n<p>Transperant \ub370\uc774\ud130 \uc554\ud638\ud654\ub97c \ud65c\uc131\ud654\ud558\uae30 \uc704\ud574 oracle 12c\uc5d0 \uc0c8\ub85c\uc6b4 \uba85\ub839\uc774 \ub3c4\uc785\ub418\uc5c8\uc2b5\ub2c8\ub2e4.<\/p>\n<p>\uad00\ub9ac\uc790 \ud0a4 \uad00\ub9ac\ub294 ALTER SYSTEM SET ENCRYPTION WALLET\uacfc \uac19\uc740 \uc774\uc804 \uba85\ub839\uc744 \ub300\uccb4\ud558\uba70 Wallet\uc740 12c \uc5d0\uc11c keystore\ub85c \uc54c\ub824\uc838 \uc788\uc2b5\ub2c8\ub2e4.<\/p>\n<p>\uc801\uc6a9 \ubc29\ubc95 \uc785\ub2c8\ub2e4.<\/p>\n<p>&nbsp;<\/p>\n<p style=\"text-align: justify;\"><strong>1. Create a wallet\/keystore location.<\/strong><\/p>\n<p>&nbsp;<\/p>\n<pre class=\"lang:plsql decode:true \">mkdir -p \/oracle12c\/haisins\/WALLET<\/pre>\n<div><\/div>\n<p style=\"text-align: justify;\"><strong>2. update the wallet\/keystore location in sqlnet.ora. It should look like.<\/strong><\/p>\n<p>&nbsp;<\/p>\n<pre class=\"lang:plsql decode:true \">cat\u00a0\u00a0$ORACLE_HOME\/network\/admin\/sqlnet.ora\r\n\r\n# sqlnet.ora Network Configuration File: \/home\/oracle\/app\/oracle\/product\/12.1.0\/dbhome_1\/network\/admin\/sqlnet.ora\r\n\r\n# Generated by Oracle configuration tools.\r\n\r\nNAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)\r\n\r\nENCRYPTION_WALLET_LOCATION =\r\n\r\n\u00a0\u00a0(SOURCE =(METHOD = FILE)(METHOD_DATA =\r\n\r\n\u00a0\u00a0\u00a0\u00a0(DIRECTORY = \/oracle12c\/haisins\/WALLET)))<\/pre>\n<div><\/div>\n<p style=\"text-align: justify;\"><strong>Create keystore:<\/strong><\/p>\n<p>&nbsp;<\/p>\n<pre class=\"lang:plsql decode:true\">SQL&gt; ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '\/oracle12c\/haisins\/WALLET\/' IDENTIFIED BY walletpass#123;\r\n\r\nkeystore altered.\r\n\r\nSQL&gt; host ls \/oracle12c\/haisins\/WALLET\/\r\n\r\newallet.p12<\/pre>\n<div><\/div>\n<p style=\"text-align: justify;\"><strong>Now open the keystore:<\/strong><\/p>\n<p>&nbsp;<\/p>\n<pre class=\"lang:plsql decode:true \">SQL&gt; ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY walletpass#123;\r\n \r\nkeystore altered.\r\n<\/pre>\n<div><\/div>\n<div><\/div>\n<p style=\"text-align: justify;\"><strong>Now activate the key:<\/strong><\/p>\n<p>&nbsp;<\/p>\n<pre class=\"lang:plsql decode:true \">SQL&gt; SET LINESIZE 100\r\nSELECT con_id, key_id FROM v$encryption_keys;\r\nSQL&gt; \r\nno rows selected\r\n \r\nSQL&gt; ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY walletpass#123 WITH BACKUP;\r\nkeystore altered.\r\n \r\nSQL&gt; SET LINESIZE 100\r\nSQL&gt; SELECT con_id, key_id FROM v$encryption_keys;\r\n    CON_ID KEY_ID\r\n---------- ------------------------------------------------------------------------------\r\n 0 AS6cSkI4u09zv9+RRWMrX2QAAAAAAAAAAAAAAAAAAAAAAAAAAAAA\r\n \r\n \r\nSQL&gt; SET LINESIZE 200\r\nSQL&gt; COLUMN wrl_parameter FORMAT A50\r\nSQL&gt; SELECT * FROM v$encryption_wallet;\r\n \r\nWRL_TYPE\t     WRL_PARAMETER STATUS\t       WALLET_TYPE\t    WALLET_OR FULLY_BAC     CON_ID\r\n-------------------- -------------------------------------------------- ------------------------------ -------------------- --------- --------- ----------\r\nFILE      \/oracle12c\/haisins\/WALLET\/ OPEN\t       PASSWORD \t    SINGLE    NO 0\r\n<\/pre>\n<div><\/div>\n<p style=\"text-align: justify;\"><strong>\u00a0create a encrypted a tablespace\u00a0<\/strong><\/p>\n<p>&nbsp;<\/p>\n<pre class=\"lang:plsql decode:true \">SQL&gt; CREATE TABLESPACE TEST_ENCRY \r\ndatafile '\/home\/oracle\/app\/oracle\/oradata\/cdb1\/testencry.dbf' size 2G \r\nENCRYPTION USING 'AES256'\r\nDEFAULT STORAGE(ENCRYPT);  2    3    4  \r\n \r\nTablespace created.\r\n \r\n \r\nSQL&gt; create table emp_ency(\r\nempno   Number(3),\r\nName     varchar(10)\r\n) tablespace TEST_ENCRY;  2    3    4  \r\n \r\nTable created.\r\n \r\n \r\n \r\nSQL&gt; select tablespace_name,encrypted from dba_tablespaces where tablespace_name='TEST_ENCRY';\r\n \r\nTABLESPACE_NAME \t       ENC\r\n------------------------------ ---\r\nTEST_ENCRY\t\t       YES\r\n<\/pre>\n<div><\/div>\n<p style=\"text-align: justify;\"><strong>Create a table with encrypted column:<\/strong><\/p>\n<p>&nbsp;<\/p>\n<pre class=\"lang:plsql decode:true \">SQL&gt; CREATE TABLE employee (\r\n     first_name VARCHAR2(128),\r\n     last_name VARCHAR2(128),\r\n     empID NUMBER,\r\n     salary NUMBER(6) ENCRYPT\r\n); \r\n \r\nTable created.\r\n \r\n \r\n \r\nSQL&gt; select owner,table_name,column_name,encryption_alg from dba_encrypted_columns where table_name='EMPLOYEE';\r\n \r\nOWNER\t   TABLE_NAME COLUMN_NAME  ENCRYPTION_A\r\n---------- ------------ ------------ ------------\r\nRAJ\t   EMPLOYEE SALARY\t     AES 192 bits   key\r\n<\/pre>\n<div><\/div>\n<p style=\"text-align: justify;\"><strong>ENABLE AUTOLOGIN :<\/strong><\/p>\n<p>&nbsp;<\/p>\n<pre class=\"lang:plsql decode:true \">SQL&gt; SELECT * FROM v$encryption_wallet;\r\n\r\nWRL_TYPE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WRL_PARAMETER STATUS\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WALLET_TYPE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0WALLET_OR FULLY_BAC\u00a0\u00a0\u00a0\u00a0 CON_ID\r\n\r\n-------------------- -------------------------------------------------- ------------------------------ -------------------- --------- --------- ----------\r\n\r\nFILE \u00a0\u00a0\u00a0\u00a0 \/oracle12c\/haisins\/WALLET\/ OPEN\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 PASSWORD \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0SINGLE\u00a0\u00a0\u00a0\u00a0NO 0<\/pre>\n<div><\/div>\n<p>\uc5ec\uae30\uc11c wallet_type\uc740 PASSWORD\uc785\ub2c8\ub2e4. \uc989, \ub370\uc774\ud130\ubca0\uc774\uc2a4\ub97c \ub2e4\uc2dc \uc2dc\uc791\ud560 \ub54c\ub9c8\ub2e4 \ud0a4 \/ \uc9c0\uac11\uc744 \uba85\uc2dc \uc801\uc73c\ub85c \uc5f4\uc5b4\uc57c\ud569\ub2c8\ub2e4.<\/p>\n<p>\uc774\ub97c \ubc29\uc9c0\ud558\uae30 \uc704\ud574 \uc790\ub3d9 \ub85c\uadf8\uc778\uc744 \ud65c\uc131\ud654\ud558\uc5ec \ub2e4\uc74c\uc5d0 db\uac00 \ub2e4\uc2dc \uc2dc\uc791\ub420 \ub54c \uc790\ub3d9\uc73c\ub85c \uc9c0\uac11\uc744 \uc5fd\ub2c8 \ub2e4.<\/p>\n<p>&nbsp;<\/p>\n<pre class=\"lang:plsql decode:true \">SQL&gt; ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE '\/oracle12c\/haisins\/WALLET\/' IDENTIFIED BY walletpass#123;\r\n\r\nkeystore altered.\r\n\r\nSQL&gt;\u00a0\u00a0SELECT * FROM v$encryption_wallet;\r\n\r\nWRL_TYPE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WRL_PARAMETER STATUS\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WALLET_TYPE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0WALLET_OR FULLY_BAC\u00a0\u00a0\u00a0\u00a0 CON_ID\r\n\r\n-------------------- -------------------------------------------------- ------------------------------ -------------------- --------- --------- ----------\r\n\r\nFILE \u00a0\u00a0\u00a0\u00a0 \/oracle12c\/haisins\/WALLET\/ OPEN\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 PASSWORD \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0SINGLE\u00a0\u00a0\u00a0\u00a0NO 0\r\n\r\nSQL&gt;\r\n\r\nSQL&gt; startup force\r\n\r\nORACLE instance started.\r\n\r\nTotal System Global Area\u00a0\u00a0838860800 bytes\r\n\r\nFixed Size \u00a0\u00a0\u00a0\u00a02929936 bytes\r\n\r\nVariable Size \u00a0\u00a0570428144 bytes\r\n\r\nDatabase Buffers \u00a0\u00a0260046848 bytes\r\n\r\nRedo Buffers \u00a0\u00a0\u00a0\u00a05455872 bytes\r\n\r\nDatabase mounted.\r\n\r\nDatabase opened.\r\n\r\nSQL&gt; SELECT * FROM v$encryption_wallet;\r\n\r\nWRL_TYPE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WRL_PARAMETER STATUS\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WALLET_TYPE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0WALLET_OR FULLY_BAC\u00a0\u00a0\u00a0\u00a0 CON_ID\r\n\r\n-------------------- -------------------------------------------------- ------------------------------ -------------------- --------- --------- ----------\r\n\r\nFILE \u00a0\u00a0\u00a0\u00a0 \/oracle12c\/haisins\/WALLET\/ OPEN\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AUTOLOGIN\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0SINGLE\u00a0\u00a0\u00a0\u00a0NO 0<\/pre>\n<p>\uc774\uc81c\u00a0 wallet_type\uc774 PASSWORD\uc5d0\uc11c AUTOLOGIN \uc73c\ub85c \ubcc0\uacbd\ub418\uc5c8\uc2b5\ub2c8\ub2e4.<\/p>\n<div><\/div>\n<p>&nbsp;<\/p>\n<p style=\"text-align: justify;\"><strong>For multi-tenant database:<\/strong><\/p>\n<p>&nbsp;<\/p>\n<p style=\"text-align: justify;\">\uba40\ud2f0 \ud14c\ub10c\ud2b8 \ub370\uc774\ud130\ubca0\uc774\uc2a4 (CDB)\uc5d0\uc11c \ud0a4 \uc2a4\ud1a0\uc5b4\ub294 ROOT \ucee8\ud14c\uc774\ub108 (CDB $ ROOT)\uc5d0 \ub9cc\ub4e4\uc5b4\uc57c\ud569\ub2c8\ub2e4.<\/p>\n<p style=\"text-align: justify;\">\uc774 \ub2e8\uc77c \ud0a4 \uc800\uc7a5\uc18c\ub294 CDB $ ROOT \ucee8\ud14c\uc774\ub108\ubfd0\ub9cc \uc544\ub2c8\ub77c \ubaa8\ub4e0 \uc5f0\uad00\ub41c PDB\uac00 \uacf5\uc720\ud569\ub2c8\ub2e4.<\/p>\n<p style=\"text-align: justify;\">\ub530\ub77c\uc11c \ubaa8\ub4e0 pdbs\uc5d0\uc11c keystore\ub97c \uc5f4\uace0 \ud65c\uc131\ud654\ud558\ub824\uba74 CONTAINER = ALL \uc808\uc744 \uc0ac\uc6a9\ud574\uc57c\ud569\ub2c8\ub2e4.<\/p>\n<p>&nbsp;<\/p>\n<pre class=\"lang:plsql decode:true \">ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY walletpass#123 CONTAINER=ALL;\r\n\r\nADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY walletpass#123 WITH BACKUP CONTAINER=ALL;<\/pre>\n<div><\/div>\n<p>&nbsp;<\/p>\n<p style=\"text-align: justify;\"><em>\ucc38\uace0 : \ud0a4 \uc800\uc7a5\uc18c \uc0ac\uc6a9\uc790\ub97c \ub9cc\ub4e4\ub824\uba74 ADMINISTER KEY MANAGEMENT \ub610\ub294 SYSKM \uad8c\ud55c\uc774 \uc788\uc5b4\uc57c\ud569\ub2c8\ub2e4.<br \/>\n<\/em><\/p>\n<p>&nbsp;<\/p>\n<p style=\"text-align: justify;\"><strong>Related dictionary tables for TDE:<\/strong><\/p>\n<p>&nbsp;<\/p>\n<pre class=\"lang:plsql decode:true \">----What tables contain TDE encrypted columns?\r\n\r\nsql&gt; select table_name, column_name from dba_encrypted_columns;\r\n\r\n-------What tables are stored in TDE encrypted tablespaces?\r\n\r\nsql&gt; select a.table_name, a.tablespace_name from dba_tables a, dba_tablespaces b\r\n\r\nwhere a.tablespace_name = b.tablespace_name and b.encrypted = 'YES';\r\n\r\n-------What indexes are stored in TDE encrypted tablespaces?\r\n\r\nsql&gt; select a.index_name, a.tablespace_name from dba_indexes a, dba_tablespaces b\r\n\r\nwhere a.tablespace_name = b.tablespace_name and b.encrypted = 'YES' and index_name not like 'SYS_IL%';\r\n\r\n------- getting key\/wallet details:\r\n\r\nSQL&gt; SELECT * FROM v$encryption_wallet;\r\n\r\nSQL&gt; SELECT con_id, key_id FROM v$encryption_keys;\r\n\r\n<\/pre>\n<div><\/div>\n<p style=\"text-align: justify;\">\n","protected":false},"excerpt":{"rendered":"<p>TDE (Transparent Data Encryption)\ub294 OS \ub370\uc774\ud130 \ud30c\uc77c\uc5d0 \uc800\uc7a5\ub41c \ub370\uc774\ud130\ub97c \uc554\ud638\ud654\ud558\ub294 \uba54\ucee4\ub2c8\uc998\uc744 \uc81c\uacf5\ud569\ub2c8\ub2e4. TDE\ub97c \uc0ac\uc6a9\ud558\uba74 \uc800\uc7a5\uc18c \uc218\uc900\uc758 \ub370\uc774\ud130\ub97c \uc554\ud638\ud654\ud558\uc5ec \ub370\uc774\ud130\ubca0\uc774\uc2a4 \uc678\ubd80\uc5d0\uc11c \ub370\uc774\ud130\uac00 \uc720\ucd9c \ub418\ub294 \uac83\uc744 \ubc29\uc9c0 \ud560 \uc218 \uc788\uc2b5\ub2c8\ub2e4. Transperant \ub370\uc774\ud130 \uc554\ud638\ud654\ub97c \ud65c\uc131\ud654\ud558\uae30 \uc704\ud574 oracle 12c\uc5d0 \uc0c8\ub85c\uc6b4 \uba85\ub839\uc774 \ub3c4\uc785\ub418\uc5c8\uc2b5\ub2c8\ub2e4. \uad00\ub9ac\uc790 \ud0a4 \uad00\ub9ac\ub294 ALTER SYSTEM SET ENCRYPTION WALLET\uacfc \uac19\uc740 \uc774\uc804 \uba85\ub839\uc744 \ub300\uccb4\ud558\uba70 Wallet\uc740 12c \uc5d0\uc11c keystore\ub85c \uc54c\ub824\uc838 [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":2427,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"spay_email":""},"categories":[336],"tags":[760,74,1073,1074,761,824,762],"jetpack_featured_media_url":"https:\/\/i0.wp.com\/haisins.synology.me\/wordpress\/wp-content\/uploads\/2018\/01\/data-encryption-facts.jpg?fit=468%2C327","amp_enabled":true,"_links":{"self":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/3771"}],"collection":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=3771"}],"version-history":[{"count":2,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/3771\/revisions"}],"predecessor-version":[{"id":3773,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/3771\/revisions\/3773"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/media\/2427"}],"wp:attachment":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=3771"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=3771"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=3771"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}