Transparent Data Encryption TDE

Create a Wallet

ENCRYPTION_WALLET_LOCATION=
  (SOURCE=(METHOD=FILE)(METHOD_DATA=
    (DIRECTORY=D:\OraDb\admin\PROD\encryption_wallet\)))


Bounce Instance

C:\Users\farif.MOHE>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Jul 23 10:10:23 2015

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

SQL> ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY "Farhat123";

System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;

#########################################################

ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "Farhat123";

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

select * from v$encryption_wallet;

WRL_TYPE WRL_PARAMETER, STATUS
file D:\OraDb\admin\PROD\encryption_wallet OPEN

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

If you want to close the wallet you may get a ORA-28390 which is a expected , to close the wallet in 11.2.0.1 onwards there’s new syntax.

SQL> ALTER SYSTEM SET ENCRYPTION WALLET CLOSE;
ALTER SYSTEM SET ENCRYPTION WALLET CLOSE
*
ERROR at line 1:
ORA-28390: auto login wallet not open but encryption wallet may be open

-----------------------------------------------------------------
ALTER SYSTEM SET ENCRYPTION WALLET CLOSE IDENTIFIED BY "Farhat123";

System altered.

SQL>
-------------------------------------------------------------------
To Open Wallet Again

ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "Farhat123";


CREATE TABLESPACE tde_test ENCRYPTION USING 'AES256' DEFAULT STORAGE(ENCRYPT);

SELECT tablespace_name, encrypted, status FROM dba_tablespaces;

TABLESPACE_NAME                ENCRYPTED STATUS   
SYSTEM                         NO        ONLINE   
SYSAUX                         NO        ONLINE   
UNDOTBS1                       NO        ONLINE   
TEMP                           NO        ONLINE   
USERS                          NO        ONLINE   
TDE_TEST                       YES       ONLINE   


CREATE USER test IDENTIFIED BY test DEFAULT TABLESPACE tde_test;
ALTER USER test QUOTA UNLIMITED ON tde_test;
GRANT CONNECT TO test;
GRANT CREATE TABLE TO test;

D:\>sqlplus test/test

SQL*Plus: Release 11.2.0.4.0 Production on Thu Jul 23 09:55:24 2015

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

SQL> CREATE TABLE tde_test (
  2    id    NUMBER(10),
  3    data  VARCHAR2(50)
  4  )
  5  TABLESPACE tde_test;

Table created.

SQL> INSERT INTO tde_test (id, data) VALUES (1, 'This is a secret!');

1 row created.

SQL> COMMIT;

Commit complete.

SQL>

SELECT * FROM dba_encrypted_columns;

OWNER          TABLE_NAME  COLUMN_NAME  ENCRYPTION_ALG                SALT INTEGRITY_ALG
TEST           TDE_TEST_2  DATA         AES 192 bits key              YES  SHA-1        

ALTER SYSTEM SET ENCRYPTION WALLET CLOSE IDENTIFIED BY "Farhat123";

SQL> select * from tde_test_2;
select * from tde_test_2
              *
ERROR at line 1:
ORA-28365: wallet is not open


SQL> ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "Farhat123";

System altered.


SQL> select * from tde_test_2;

ID         DATA                                              
236        Data for 236                                      
237        Data for 237                                      
238        Data for 238                                      
239        Data for 239                                      
240        Data for 240                                      
241        Data for 241                                      
242        Data for 242