oracle TDE

connect / as sysdba
or creating a wallet a folder named wallet should be present in the directory
$ORACLEBASE/admin/$ORACLESID

Location of wallet can be changed if required. This can be done by entering the directory path for WALLET_LOCATION parameter in sqlnet.ora file as shown below

WALLET_LOCATION = (SOURCE=
(METHOD = FILE)
(METHOD_DATA =
(DIRECTORY=/u02/wallets)
)))



alter system set key identified by "welcome1";

For later sessions, you do not want to use the command given above; you need the wallet to be open (it has been closed when you shut down your database), but you don't want to create a new master key. Then the command is:

alter system set wallet open identified by "welcome1";


create table cust_payment_info
(first_name varchar2(11),
last_name varchar2(10),
order_number number(5),
credit_card_number varchar2(16) ENCRYPT NO SALT,
active_card varchar2(3));



insert into cust_payment_info values
('Jon', 'Oldfield', 10001, '5446959708812985','YES');
insert into cust_payment_info values
('Chris', 'White', 10002, '5122358046082560','YES');
insert into cust_payment_info values
('Alan', 'Squire', 10003, '5595968943757920','YES');
insert into cust_payment_info values
('Mike', 'Anderson', 10004, '4929889576357400','YES');
insert into cust_payment_info values
('Annie', 'Schmidt', 10005, '4556988708236902','YES');
insert into cust_payment_info values
('Elliott', 'Meyer', 10006, '374366599711820','YES');
insert into cust_payment_info values
('Celine', 'Smith', 10007, '4716898533036','YES');
insert into cust_payment_info values
('Steve', 'Haslam', 10008, '340975900376858','YES');
insert into cust_payment_info values
('Albert', 'Einstein', 10009, '310654305412389','YES');



/devel/app/oracle/admin/dev10g/wallets


view sqlnet.ora
WALLET_LOCATION = (SOURCE=
(METHOD = FILE)
(METHOD_DATA =
(DIRECTORY=/devel/app/oracle/admin/dev10g/wallets)
)))


SQL> alter system set key identified by "pass";

System altered.


Example 3-3 Adding Salt to an Encrypted Column

ALTER TABLE employee MODIFY (first_name ENCRYPT SALT);

Example 3-4 Removing Salt from an Encrypted Column

ALTER TABLE employee MODIFY (first_name ENCRYPT NO SALT);

expdp system/manager dumpfile=test.dmp directory=tmp_dir tables=scott.emp encryption_password=pass

impdp system/manager tables=emp dumpfile=test.dmp directory=tmp_dir encryption_password=pass

Import: Release 10.2.0.1.0 - Production on Tuesday, 18 September, 2007 12:17:55

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ORA-39001: invalid argument value
ORA-39180: unable to encrypt ENCRYPTION_PASSWORD
ORA-28365: wallet is not open


SQL> create directory test as 'c:\temp';
Directory created.

SQL> grant all on directory test to public;
Grant succeeded.


impdp system/manager tables=emp dumpfile=test.dmp directory=tmp_dir encryption_password=pass REMAP_SCHEMA=scott:system remap_tablespace=users:system


after reboot of database

SQL> alter system set encryption wallet open identified by "pass";

System altered.