ORACLE TRANSPARENT DATABASE ENCRYPTION 11gR2 : eNCRYPTING eNTIRE tABLESPACE
Create oracle encryption wallet:
create a directory on the oracle server.
$ cd /u01/app/oracle/product/11.2.0/db_1/network/admin
$ mkdir tde_wallet
Open the sqlnet.ora and add these lines to set the location of the wallet.
ENCRYPTION_WALLET_LOCATION=
(SOURCE=(METHOD=FILE)(METHOD_DATA=
(DIRECTORY=/u01/app/oracle/product/11.2.0/db_1/network/admin/tde_wallet)))
Set the master encryption key in the wallet.
$ sqlplus / as sysdba
SQL> alter system set encryption key authenticated by “ora11g”;
System altered.
This creates a wallet at the location defined in the sqlnet.ora, sets the password forthe wallet and set the master encryption key that will be used to decrypt the tablespace encryption keys stored in the database for every encrypted tablespace.
SQL> alter system set encryption wallet open authenticated by “ora11g”;
System altered.
SQL> alter system set encryption wallet close identified by “ora11g”;
System altered.
How Transparent Tablespace Encryption works:
CREATE TABLESPACE ts_tde
DATAFILE ‘/u01/app/oracle/oradata/ora11g/ts_tde01.dbf’
SIZE 20m ATOEXTEND ON NEXT 5m
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO
ENCRYPTION USING ‘3DES168’
DEFAULT STORAGE (ENCRYPT);
At the time of tablespace creation specify the encryption and default storage clause.Define the encryption algorithm as ” using ‘algorithm’ ” along with the encryption clause.You can use the following algorithms while creating an encrypted tablespace.
AES128
AES192
AED256
3DES168
If you don’t specify any algorithm with the encryption clause it will use AES128 as default.
An existing tablespace cannot be encrypted. If you need your existing data to be encrypted, create a new encrypted tablespace and move your data to it by using:
ALTER TABLE table_name MOVE encrypted_tablespace_name;
OR
CREATE TABLE table_in_encrypted_form
TABLESPACE encrypted_tablespace_name
AS
SELECT * FROM table_in_clear_text_form;
Or any others means you have, to move data from one tablespace to the other. The best way to do is using export – import (DataPump)
Steps to import an existing schema in non-encrypted tablespace to encrypted tablespace
______________________________________________________________________________________
1. Create the New encrypted tablespace using command :
CREATE TABLESPACE ts_tde
DATAFILE ‘/u01/app/oracle/oradata/ora11g/ts_tde01.dbf’
SIZE 20m ATOEXTEND ON NEXT 5m
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO
ENCRYPTION USING ‘3DES168’
DEFAULT STORAGE (ENCRYPT);
2. Take Backup of the Schema using command :
expdp swx/swx123 directory=export_dir dumpfile=exp_swx_27thApril2011.dmp logfile=exp_swx_27thApril2011.log schemas=swx
3. Create script for the schema (“swx” in our case ) and keep it aside. i generally do this from toad. It is much easier to do.
CREATE USER SWX
IDENTIFIED BY <password>
DEFAULT TABLESPACE SWX
TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT
ACCOUNT UNLOCK;
— 9 Roles for SWX
GRANT SELECT_CATALOG_ROLE TO SWX;
GRANT AQ_ADMINISTRATOR_ROLE TO SWX;
GRANT AUTHENTICATEDUSER TO SWX;
GRANT CONNECT TO SWX;
GRANT EXP_FULL_DATABASE TO SWX;
GRANT IMP_FULL_DATABASE TO SWX;
GRANT AQ_USER_ROLE TO SWX;
GRANT DBA TO SWX;
GRANT SCHEDULER_ADMIN TO SWX;
ALTER USER SWX DEFAULT ROLE ALL;
— 5 System Privileges for SWX
GRANT SELECT ANY TRANSACTION TO SWX;
GRANT SELECT ANY TABLE TO SWX;
GRANT UNLIMITED TABLESPACE TO SWX;
GRANT SELECT ANY SEQUENCE TO SWX;
GRANT SELECT ANY DICTIONARY TO SWX;
4. Drop the old schema using
SQL > DROP USER SWX CASCADE;
5. Create the schema again with its default tablespace as the new encrypted tablespace “ts_tde”
CREATE USER SWX
IDENTIFIED BY <password>
DEFAULT TABLESPACE ts_tde
TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT
ACCOUNT UNLOCK;
— 9 Roles for SWX
GRANT SELECT_CATALOG_ROLE TO SWX;
GRANT AQ_ADMINISTRATOR_ROLE TO SWX;
GRANT AUTHENTICATEDUSER TO SWX;
GRANT CONNECT TO SWX;
GRANT EXP_FULL_DATABASE TO SWX;
GRANT IMP_FULL_DATABASE TO SWX;
GRANT AQ_USER_ROLE TO SWX;
GRANT DBA TO SWX;
GRANT SCHEDULER_ADMIN TO SWX;
ALTER USER SWX DEFAULT ROLE ALL;
— 5 System Privileges for SWX
GRANT SELECT ANY TRANSACTION TO SWX;
GRANT SELECT ANY TABLE TO SWX;
GRANT UNLIMITED TABLESPACE TO SWX;
GRANT SELECT ANY SEQUENCE TO SWX;
GRANT SELECT ANY DICTIONARY TO SWX;
6. Import the schema backup
impdp swx/******* directory=export_dir dumpfile=EXP_SWX_26THAPRIL2011.DMP logfile=import.log remap_tablespace=swx:ts_tde
The objects of the user “SWX” is now in the newly encrypted tablespace. If the wallet is not explicitly open after the database is restarted it will give the ora error.
ORA-28365: wallet is not open
Open the wallet using command :
SQL> alter system set encryption wallet open authenticated by “ora11g”;