Saturday, March 2, 2013

3 Transparent Data Encryption (TDE) in Oracle

 

Create wallet using GUI utilities: $ORACLE_HOME/bin/owm

Start the Oracle Wallet Manager from $ORACLE_HOME/bin/owm

image

image

image

Select NO for now.

image

When we close the wallet manger, it would ask whether we want to store the wallet.

 

 

MANUALLY Setup the Wallet location

The default location of the wallet is at $ORACLE_BASE/admin/$ORACLE_SID/wallet . If the folder does not exist, we would get the ORA-28368: cannot auto-create wallet while create the wallet  in next step.

image

We can also use sqlnet.ora to set up the location for the wallet. $ORALE_HOME/admin/network/sqlnet.ora

ENCRYPTION_WALLET_LOCATION=
  (SOURCE=(METHOD=FILE)(METHOD_DATA=
    (DIRECTORY=/u01/app/oracle/admin/oradb0/wallet2/)))
After updating the sqlnet.ora, we need to bounce the listener to make it effective.

 

Create wallet

The ewallet.p12 file would be created under wallet directory .
Below command would create and open the wallet .

alter system set encryption key IDENTIFIED by "XXXXX";

image

 

open wallet

alter system set encryption wallet open IDENTIFIED BY "XXXX";

image

Close wallet

alter system set encryption wallet close IDENTIFIED BY "XXXX";

image

Many document indicate can close the wallet by “ALTER SYSTEM SET WALLET CLOSE;” or “ALTER SYSTEM SET ENCRYPTION WALLET CLOSE;” However, I always get the error ORA-28390: auto login wallet not open but encryption wallet may be open

ENCRYPTION the table column

  • SYS object can not be encrypted . If we try to encrypt the sys object , we would get ORA-28336: cannot encrypt SYS owned objects error.

image

  • To encrypt the column, the wallet must be open first .otherwise we would get ORA-28365: wallet is not open error.
CREATE TABLE test_employee (
     first_name VARCHAR2(128),
     last_name VARCHAR2(128),
     empID NUMBER,
     salary NUMBER(6)
);
 
ALTER TABLE test_employee MODIFY (salary ENCRYPT);

image

image

Test the TDE

Create the new table space TEST_TDE and create the new user

 
CREATE TABLESPACE TEST_TDE
  DATAFILE '/u01/app/oracle/oradata/oradb0/TEST_TDE01.dbf' SIZE 256K
  AUTOEXTEND ON NEXT 64K;
 
 
CREATE USER tde IDENTIFIED BY tde DEFAULT TABLESPACE TEST_TDE;
ALTER USER tde QUOTA UNLIMITED ON TEST_TDE;
GRANT CONNECT TO tde;
GRANT CREATE TABLE TO tde;

image

Use the new user tde create 2 tables, test_employee and test_employee2. In the test_employee table, encrypt the first column.

CREATE TABLE test_employee (
     first_name VARCHAR2(10),
     nick_name VARCHAR2(20),
     salary NUMBER(6)
);
 
ALTER TABLE test_employee MODIFY (first_name VARCHAR2(10)  ENCRYPT);
INSERT INTO test_employee  VALUES ('SUMMER', 'PANDA_SUMMER',1000);
select * from test_employee;
 
CREATE TABLE test_employee2 (
     first_name VARCHAR2(10),
     nick_name VARCHAR2(20),
     salary NUMBER(6)
);
 
INSERT INTO test_employee2 VALUES ('SUNNY', 'PANDA_SUNNY',3000);
select * from test_employee2;

From query perspective, they looks identical. Data in both table are retrievable.

image

Let issue the ALTER SYSTEM FLUSH BUFFER_CACHE; to make sure the data is written to the data file.

Use the ghex to open the data file and search the PANDA

As you can see, the first row data “SUMMER” in the table test_employee is not visible in the data file

image

However, the 2nd table test_employee2 does not encrypted, therefore all data are visible in the hex editor.

image

We can use strings to convert the binary data to the text file to conclude the same result as well.

image

image

DBA_ENCRYPTED_COLUMNS/USER_ENCRYPTED_COLUMNS

select * from USER_ENCRYPTED_COLUMNS;

image

DECRYPT the data

ALTER TABLE test_employee MODIFY (first_name VARCHAR2(10)  DECRYPT);

image

Since data is decrypt, it is visible from hex editor or strings

image

3 comments:

  1. There are lots of information about latest technology and how to get trained in them, like Oracle Training Chennai have spread around the web, but this is a unique one according to me. The strategy you have updated here will make me to get trained in future technologies(Oracle Training Institutes in Chennai). By the way you are running a great blog. Thanks for sharing this.

    ReplyDelete
  2. I am following your blog from the beginning, it was so distinct & I had a chance to collect conglomeration of information that helps me a lot to improvise myself. I hope this will help many readers who are in need of this vital piece of information. Thanks for sharing & keep your blog updated.
    Regards,
    CCNA Training in Chennai | CCNA Training institute in Chennai | CCNA Training

    ReplyDelete
  3. The information and idea behind database is really super.Thanks for sharing your interesting blog of post.keep on blogging.
    web designing training in Chennai | web designing course in Chennai

    ReplyDelete

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates