SOURCE:
Encrypting Data in
Oracle 10g: Transparent Data Encryption (TDE)
The Transparent Data Encryption (TDE) feature
introduced in Oracle 10g Database Release 2 allows sensitive data to be
encrypted within the datafiles to prevent access to it from the operating
system.
Using any HEX editor e.g. Ultraedit software
allows the portion of data to be viewed present in the datafiles which
ultimately breaches the security of sensitive company information. The short
guide presented below provides steps for setting up a simple encryption
strategy on sensitive data columns to prevent access of data from operating
system e.g. using hex editors etc.
Steps of setting up
Data Encryption
1. Setting up a Wallet
a. Creating and Opening a Wallet
b. Closing a Wallet to prevent access
2. Encrypting the desired Data Column
a. Encrypting with Salt
b. Encrypting with Non-Default Algorithm
c. Encrypt for External Tables
d. Generating a New key for the column
e. Turning Off Column Encryption
3. Additional Resources
a. Creating and Opening a Wallet
b. Closing a Wallet to prevent access
2. Encrypting the desired Data Column
a. Encrypting with Salt
b. Encrypting with Non-Default Algorithm
c. Encrypt for External Tables
d. Generating a New key for the column
e. Turning Off Column Encryption
3. Additional Resources
1. Setting up a Wallet
Creating and Opening a
Wallet
In order to hold the encryption key of the
data columns, a wallet must be created before any column could be encrypted.
The parameter ENCRYPTION_WALLET_LOCATION in sqlnet.ora file provides the
location of where the wallet is to be found. The default location of the wallet
is $ORACLE_BASE/admin/$ORACLE_SID/wallet.
The following command creates and opens up the
wallet (using sys account)
• Connect sys/pwd as sysdba
• ALTER SYSTEM SET ENCRYPTION KEY AUTHENTICATED BY “AnyPassword”;
• ALTER SYSTEM SET ENCRYPTION KEY AUTHENTICATED BY “AnyPassword”;
To reopen the wallet, issue the following
command
• ALTER SYSTEM SET WALLET OPEN IDENTIFIED BY “AnyPassword”;
• ALTER SYSTEM SET WALLET OPEN IDENTIFIED BY “AnyPassword”;
Closing the Wallet to
prevent Access
In order to prevent access to sensitive data,
the wallet must be closed using the following command
• ALTER SYSTEM SET WALLET CLOSE;
2. Encrypting the
desired Data Column
The default encryption method used in oracle
is AES192. In order to encrypt the desired columns, use the ENCRYPT clause
after every column definition as below:
Create table Employee
(
EmpNo number primary key,
EmpName varchar2(100),
Login varchar2(100) ENCRYPT,
Pwd varchar2(10) ENCRYPT,
SSN number ENCRYPT
) tablespace employees;
(
EmpNo number primary key,
EmpName varchar2(100),
Login varchar2(100) ENCRYPT,
Pwd varchar2(10) ENCRYPT,
SSN number ENCRYPT
) tablespace employees;
If one requires to use any encryption method
other than ‘AES192’, then use ‘ENCRYPT USING’ clause within the column
definition as below:
Create table Employee
(
EmpNo number primary key,
EmpName varchar2(100),
Login varchar2(100) ENCRYPT,
Pwd varchar2(10) ENCRYPT,
SSN number ENCRYPT USING ‘3DES168’
) tablespace employees;
(
EmpNo number primary key,
EmpName varchar2(100),
Login varchar2(100) ENCRYPT,
Pwd varchar2(10) ENCRYPT,
SSN number ENCRYPT USING ‘3DES168’
) tablespace employees;
ENCRYPT with SALT
For increased encryption security, use the ‘ENCRYPT SALT’ for Salt or ‘ENCRYPT NO SALT’ for No Salt command to specify random encrypted string in the generated key for the desired column.
For increased encryption security, use the ‘ENCRYPT SALT’ for Salt or ‘ENCRYPT NO SALT’ for No Salt command to specify random encrypted string in the generated key for the desired column.
Create table Employee
(
EmpNo number primary key,
EmpName varchar2(100),
Login varchar2(100) ENCRYPT NO SALT,
Pwd varchar2(10) ENCRYPT SALT,
SSN number ENCRYPT
) tablespace employees;
(
EmpNo number primary key,
EmpName varchar2(100),
Login varchar2(100) ENCRYPT NO SALT,
Pwd varchar2(10) ENCRYPT SALT,
SSN number ENCRYPT
) tablespace employees;
ENCRYPT for External
Tables
Specify Encrypt identification key by using
‘ENCRYPT IDENTIFIED BY’ clause in the column definition as below:
Create table Employee
(
EmpNo number primary key,
EmpName varchar2(100),
Login varchar2(100) ENCRYPT,
Pwd varchar2(10) ENCRYPT,
SSN varchar2(11) ENCRYPT IDENTIFIED BY ‘xc7If3t9’
) tablespace employees;
(
EmpNo number primary key,
EmpName varchar2(100),
Login varchar2(100) ENCRYPT,
Pwd varchar2(10) ENCRYPT,
SSN varchar2(11) ENCRYPT IDENTIFIED BY ‘xc7If3t9’
) tablespace employees;
Generating a New key
for the column
To re-generate a fresh key for the column, use
the following command
• ALTER TABLE employee REKEY
OR
• ALTER TABLE employee REKEY USING ‘3DES168’;
OR
• ALTER TABLE employee REKEY USING ‘3DES168’;
Turning Off Column
Encryption
By using the ‘DECRYPT’ clause, the column
encryption can later be turned off as below:
• Alter table employees modify(ssn
varchar2(11) DECRYPT);
3. For more
information visit
• http://www.oracle-base.com/articles/10g/TransparentDataEncryption_10gR2.php
• http://download-uk.oracle.com/docs/cd/B19306_01/network.102/b14268/asotrans.htm
• http://download-uk.oracle.com/docs/cd/B19306_01/network.102/b14268/asotrans.htm
Hope this effort helps in any manner!
No comments:
Post a Comment