TransparentDataEncryption (TDE) column implementation

Here are the TDE column implementation steps:

ð  Run below statements sequentially: One sqlplus session is enough for entire action plan.

Database : orcl

Server : xxxxxxxxx

OS User : tdeuser

conn / as syskm

define tde_wallet_full_path=&tde_wallet_full_path

define password=&password

col WRL_PARAMETER for a60

col WRL_TYPE for a8

col STATUS for a20

col WALLET_TYPE for a10

set lines 200

select * from gv$encryption_wallet;

administer key management create keystore '&&tde_wallet_full_path' identified by "&&password";

select * from v$encryption_wallet;

administer key management set keystore open identified by "&&password";

select * from v$encryption_wallet;

administer key management create key identified by "&&password" with backup;

select * from v$encryption_wallet;

select key_id,activation_time from v$encryption_keys;

administer key management use key '&Enter_Key_From_Above' identified by "&&password" with backup;

select key_id,activation_time from v$encryption_keys;

select * from v$encryption_wallet;

ð  Connect using your preferred schema:

conn hr/hr@orcl

conn OE/oe@orcl

conn IX/ix@orcl

conn SH/sh@orcl

conn BI/bi@orcl

conn HR/hr@orcl

conn PM/pm@orcl

ð  For example used hr.

conn hr/hr@orcl

create table test_enc(id number encrypt using 'AES256', message varchar2(50));

create sequence s1;

INSERT INTO test_enc values(s1.nextval,'Message '||s1.nextval);

commit;

select * from test_enc;

ð  Now close wallet and then try to fetch data.

conn / as sysdba

set pages 50000 lines 200

col owner for a5

col TABLE_NAME for a10

col COLUMN_NAME for a10

select * from dba_encrypted_columns;

administer key management set keystore close identified by "&&password";

select * from hr.test_enc;

conn hr/hr@orcl

select * from test_enc;

conn / as sysdba

administer key management set keystore open identified by "&&password";

select * from hr.test_enc;

conn hr/hr@orcl

select * from test_enc;

ð  Execution Log:

[tdeuser@xxxxxx tde_wallet]$ cat $ORACLE_HOME/network/admin/sqlnet.ora

# sqlnet.ora Network Configuration File: /u01/tde/app/product/12.1.0/dbhome_1/network/admin/sqlnet.ora

# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

ENCRYPTION_WALLET_LOCATION =

        (SOURCE =

                (METHOD = FILE)

                (METHOD_DATA =

                        (DIRECTORY = /u01/tde/app/admin/${ORACLE_SID}/tde_wallet/)

                )

        )

[tdeuser@xxxxxx tde_wallet]$

[tdeuser@xxxxxx admin]$ sqlplus / as syskm

SQL*Plus: Release 12.1.0.2.0 Production on Sat Aug 6 07:47:07 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> col WRL_PARAMETER for a60

col WRL_TYPE for a8

col STATUS for a20

col WALLET_TYPE for a10

set lines 200

select * from gv$encryption_wallet;

SQL> SQL> SQL> SQL> SQL>

   INST_ID WRL_TYPE WRL_PARAMETER                                                STATUS               WALLET_TYP WALLET_OR FULLY_BAC     CON_ID

---------- -------- ------------------------------------------------------------ -------------------- ---------- --------- --------- ----------

         1 FILE     /u01/tde/app/admin/orcl/tde_wallet/                          NOT_AVAILABLE        UNKNOWN    SINGLE    UNDEFINED          0

SQL> administer key management create keystore '/u01/tde/app/admin/orcl/tde_wallet/' identified by "welcome1";

keystore altered.

SQL> select * from v$encryption_wallet;

WRL_TYPE WRL_PARAMETER                                                STATUS               WALLET_TYP WALLET_OR FULLY_BAC     CON_ID

-------- ------------------------------------------------------------ -------------------- ---------- --------- --------- ----------

FILE     /u01/tde/app/admin/orcl/tde_wallet/                          CLOSED               UNKNOWN    SINGLE    UNDEFINED          0

SQL> administer key management set keystore open identified by "welcome1";

keystore altered.

SQL> select * from v$encryption_wallet;

WRL_TYPE WRL_PARAMETER                                                STATUS               WALLET_TYP WALLET_OR FULLY_BAC     CON_ID

-------- ------------------------------------------------------------ -------------------- ---------- --------- --------- ----------

FILE     /u01/tde/app/admin/orcl/tde_wallet/                          OPEN_NO_MASTER_KEY   PASSWORD   SINGLE    UNDEFINED          0

SQL> administer key management create key identified by "welcome1" with backup;

keystore altered.

SQL> select * from v$encryption_wallet;

WRL_TYPE WRL_PARAMETER                                                STATUS               WALLET_TYP WALLET_OR FULLY_BAC     CON_ID

-------- ------------------------------------------------------------ -------------------- ---------- --------- --------- ----------

FILE     /u01/tde/app/admin/orcl/tde_wallet/                          OPEN_NO_MASTER_KEY   PASSWORD   SINGLE    UNDEFINED          0

SQL> select key_id from v$encryption_keys;

KEY_ID

------------------------------------------------------------------------------

AcHteMileE+rv+6cPQKofjEAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

SQL> select key_id,activation_time from v$encryption_keys;

KEY_ID                                                                         ACTIVATION_TIME

------------------------------------------------------------------------------ ---------------------------------------------------------------------------

AcHteMileE+rv+6cPQKofjEAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

SQL> administer key management use key '&Enter_Key_From_Above' identified by "welcome1" with backup;

Enter value for enter_key_from_above: AcHteMileE+rv+6cPQKofjEAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

old   1: administer key management use key '&Enter_Key_From_Above' identified by "welcome1" with backup

new   1: administer key management use key 'AcHteMileE+rv+6cPQKofjEAAAAAAAAAAAAAAAAAAAAAAAAAAAAA' identified by "welcome1" with backup

keystore altered.

SQL> select key_id,activation_time from v$encryption_keys;

KEY_ID                                                                         ACTIVATION_TIME

------------------------------------------------------------------------------ ---------------------------------------------------------------------------

AcHteMileE+rv+6cPQKofjEAAAAAAAAAAAAAAAAAAAAAAAAAAAAA                           06-AUG-16 07.48.26.089877 AM +00:00

SQL> select * from v$encryption_wallet;

WRL_TYPE WRL_PARAMETER                                                STATUS               WALLET_TYP WALLET_OR FULLY_BAC     CON_ID

-------- ------------------------------------------------------------ -------------------- ---------- --------- --------- ----------

FILE     /u01/tde/app/admin/orcl/tde_wallet/                          OPEN                 PASSWORD   SINGLE    NO                 0

SQL> host ls -ltr /u01/tde/app/admin/orcl/tde_wallet

total 12

-rw-r--r-- 1 tdeuser tdegroup 2408 Aug  6 07:47 ewallet_2016080607475682.p12

-rw-r--r-- 1 tdeuser tdegroup 3664 Aug  6 07:48 ewallet_2016080607482606.p12

-rw-r--r-- 1 tdeuser tdegroup 3848 Aug  6 07:48 ewallet.p12