Oracle TDE -11gR2

1
Oracle TDE -11gR2
2
Objectives

• What and Why to Encrypt
• How to Encrypt
• Data Encryption
• Storage Level
• Salt
• Network Level
• Integrity Check
• Oracle and Cryptography
• Transparent Data Encryption
• TDE Column Encryption
• Configuring TDE
• Creation of tables with encrypted columns
• Comparison of table sizes
• Indexing of encrypted columns
• TDE Tablespace Encryption
• Creation of encrypted tablespaces
• Export/Import of tables with encrypted columns
• Re-Keying of encryption keys
• TDE and Standby DBs

3

• 1)What and Why to Encrypt
• Credit Card Numbers
• Contact Details
• Business Details (like bidding rate etc)
• Military Data
• Or, anything you think is a secret
• 2)How to Encrypt
• Using Algorithms
• Mr.Tom sends a message to Miss.Jerry
• "let us go for a movie" gtPlain Text
• TIGER gtKey

4
3)Data Encryption Encryption at Storage
level Confidential data is encrypted using
Algorithms and stored The algorithms widely
used are RC4 -Uses the modules from RSA
Inc. DES -Data Encryption Standard 3DES
-Triple DES AES -Advanced Encryption
Standard Encryption over the
network The above algorithms make
sure that your data is encrypted on the disk.
What if someone modifies the data packet before
it reaches the client or server. Data
Integrity algorithms are used to ensure the
integrity of the data. The algorithms widely
used are MD5 -Message Digest 5 SHA-1
hashing algorithms Both these algorithms create
checksums that would change if the data is
altered.
5
4)What is a SALT SALT is a random string
appended to plain text to disguise
cryptanalysts. If salt is not used, then the
cipher text of similar plain text would remain
the same for a KEY. 5)What is Integrity
Check Integrity check is necessary to thwart
Data Modification attack and Data Replay
Attack. Algorithms create checksums that would
change if the data is altered
6
6) Oracle and Cryptography Oracle supports
storage level encryption since 8i. 1)DBMS_OBFUSC
ATION_TOOLKIT -8i The application should be
re-written to include this. The key is hard
coded in the code and is accessible to
developers/dbas. 2)DBMS_CRYPTO -10gR1 Suppor
ts more algorithms than dbms_obsfucation_toolkit.
The application should be re-written to include
this. The key is hard coded in the code and is
accessible to developers/dbas. 3)Transparent
Data Encryption -10gR2
7

• 7) TDE- Transparent data encryption
• Data is safe even if media is stolen
• Need not modify the application
• Data is encrypted transparently without the
  knowledge of the application and users
• Key management is secure
• 5-8 overhead in CPU consumption
• Two Types
• --TDE Column Encryption -10gR2
• --TDE Tablespace Encryption -11gR1
• Table/Tablespace Keys
• Used to encrypt the table columns/tablespace.
• Stored inside the data dictionary/datafile
  headers respectively but encrypted using the
  Master Key.

8

• 7.1)TDE Column Encryption
• Used to protect confidential data in table
  columns.
• Master Key, Table Key and Wallet Password
• The data is encrypted/decrypted using Table Key
  and this table key is again encrypted using
  Master Key.

to be continued.
9
7.1 continued..
The algorithms supported are 3DES168, AES128,
AES192(default) and AES256 Storage Overhead of
max 52 bytes 16 bytes of SALT 20 bytes for
Integrity Check Encrypted value is padded to
multiple of 16bytes The below data types can be
encrypted using column encryption provided the
size after encryption doesn't exceed the maximum
allowable size for each data type BINARY_DOUBLE
BINARY_FLOAT CHAR DATE INTERVAL DAY TO
SECOND INTERVAL YEAR TO MONTH LOBs (Internal
LOBs and SECUREFILE LOBs Only) NCHAR NUMBER NVA
RCHAR2 RAW TIMESTAMP (includes TIMESTAMP WITH
TIME ZONE and TIMESTAMP WITH LOCAL TIME
ZONE) VARCHAR2
10
7.1 continued..
Restrictions for TDE Column Encryption TDE
column encryption encrypts and decrypts data at
the SQL layer. Oracle Database utilities and
features that bypass the SQL layer cannot
leverage the services provided by TDE column
encryption. Do not use TDE column encryption
with the following database features Index
types other than B-tree Range scan search
through an index External large objects
(BFILE) Synchronous Change Data
Capture Transportable Tablespaces Original
import/export utilities In addition, you cannot
use TDE column encryption to encrypt columns used
in foreign key constraints.
11
7.1.1)Configuring TDE 1)Create the wallet
directory and edit the sqlnet.ora file with
below Default path ORACLE_BASE/admin/DB_UNIQUE_N
AME/wallet/ ENCRYPTION_WALLET_LOCATION
(SOURCE(METHODFILE)(METHOD_DATA (DIRECTORY/
u01/app/oracle/admin/insys/wallet))) 2)Create
master encryption key ALTER SYSTEM SET
ENCRYPTION KEY IDENTIFIED BY "Spad3JacK" The
wallet ewallet.p12 will be created and the
generated master key and would be stored inside
this. oracle_at_localhost wallet ls -ltr
/u01/app/oracle/admin/insys/wallet total
4 -rw-r--r-- 1 oracle dba 1573 Oct 11 2314
ewallet.p12 -rw-r--r-- 1 oracle dba 1521 Oct 11
2347 cwallet.sso oracle_at_localhost wallet
After creation of the master key, the wallet
will be open by default. select from
vencryption_wallet WRL_TYPE
WRL_PARAMETER
STATUS -------------------- ---------------------
----------------------------- ------- file
/u01/app/oracle/admin/insys/wallet
OPEN The wallet should be manually
opened each time the server is bounced. ALTER
SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY
"Spad3JacK" 3)Auto-Open Wallet Wallet can be
set to open automatically using auto open
wallet. orapki wallet create wallet
ltwallet_locationgt -auto_login
12
7.1.2)Create tables with encrypted
columns CREATE TABLE cust_with_salt_integrity
(first_name VARCHAR2(20), last_name
VARCHAR2(20), order_number NUMBER(16), credit_
card_number VARCHAR2(16) ENCRYPT, active_card
VARCHAR2(3)) CREATE TABLE
cust_without_salt (first_name
VARCHAR2(20), last_name VARCHAR2(20), order_nu
mber NUMBER(16), credit_card_number
VARCHAR2(16) ENCRYPT NO SALT, active_card
VARCHAR2(3)) CREATE TABLE
cust_without_salt_integrity (first_name
VARCHAR2(20), last_name VARCHAR2(20), order_nu
mber NUMBER(16), credit_card_number
VARCHAR2(16) ENCRYPT NO SALT 'NOMAC', active_car
d VARCHAR2(3)) JOHNJ_at_insys gt select from
user_encrypted_columns TABLE_NAME
COLUMN_NAME
ENCRYPTION_ALG SAL
INTEGRITY_AL ------------------------------
------------------------------ -------------------
---------- --- ------------ CUST_WITHOUT_SALT
CREDIT_CARD_NUMBER AES 192
bits key NO SHA-1 CUST_WITHOUT_SALT_
INTEGRITY CREDIT_CARD_NUMBER AES
192 bits key NO NOMAC CUST_WITH_SALT
_INTEGRITY CREDIT_CARD_NUMBER
AES 192 bits key YES SHA-1
13
7.1.3)Comparison of encrypted tables storage
size
7.1.4)Altering Tables ALTER TABLE table_name ADD
(column_name datatype ENCRYPT) ALTER TABLE
table_name MODIFY (column_name ENCRYPT)
7.1.5)Indexing of encrypted columns Only B-Tree
indexing is allowed on encrypted columns provided
SALT is not used. 1)create index
credit_card_no_idx on cust_with_salt_integrity(cre
dit_card_number) JOHNJ_at_insys gt create index
credit_card_no_idx on cust_with_salt_integrity(cre
dit_card_number) create index
credit_card_no_idx on cust_with_salt_integrity(cre
dit_card_number) ERROR at line
1 ORA-28338 Column(s) cannot be both indexed
and encrypted with salt 2)create index
credit_card_no_idx on cust_without_salt(credit_car
d_number) JOHNJ_at_insys gt create index
credit_card_no_idx on cust_without_salt(credit_car
d_number) Index created.
14

• 7.2)TDE Tablespace Encryption
• Used to encrypt an entire tablespace

15
7.2 continued..

• Cannot encrypt an existing tablespace, but can
  move table using impdp or CTAS or move to a
  encrypted tablespace
• Has no storage overhead
• TDE tablespace encryption encrypts/decrypts data
  during read/write operations, as opposed to TDE
  column encryption, which encrypts/decrypts data
  at the SQL layer. This means that most
  restrictions that apply to TDE column encryption,
  such as data type restrictions and index type
  restrictions, are not applicable to TDE
  tablespace encryption.
• The algorithms supported are
• 3DES168,
• AES128 (default),
• AES192 and
• AES256
• Restrictions for TDE Tablespace Encryption
• External Large Objects (BFILEs) cannot be
  encrypted using TDE tablespace encryption.
  -This is because these files reside outside the
  database.
• Original import/export utilities are not
  supported. Use the Oracle Data Pump instead.

16
7.2 continued..
CREATE TABLESPACE users_secure DATAFILE
'/u02/app/oradata/insys/users_secure01.dbf' SIZE
10M ENCRYPTION DEFAULT STORAGE(ENCRYPT) CREATE
TABLESPACE users_secure DATAFILE
'/u02/app/oradata/insys/users_secure01.dbf' SIZE
10M ENCRYPTION USING '3DES168' DEFAULT
STORAGE(ENCRYPT) CREATE TABLE
cust_on_secure_tblspce (first_name
VARCHAR2(20), last_name VARCHAR2(20), order_numb
er NUMBER(16), credit_card_number
VARCHAR2(16), active_card VARCHAR2(3))
tablespace users_secure JOHNJ_at_insys gt
create index credit_card_no_tblspce_idx on
cust_on_secure_tblspce(credit_card_number) Index
created.
17
7.3 Exporting encrypted tables
Use Oracle Data Pump to export/import tables
containing encrypted columns 1) The encryption
wallet should be open before export 2) The data
is first decrypted and is moved to the dump
file expdp directoryDATA_PUMP_DIR
tablesJOHNJ.CUST_WITH_SALT_INTEGRITY
dumpfileCUST_WITH_SALT_INTEGRITY.dmp
logfileCUST_WITH_SALT_INTEGRITY.log
Once exported the encrypted data is visible if
open using a text editor
18
7.3 continued..
Use Data Pump with the ENCRYPTION parameter to
encrypt the dump file.
expdp directoryDATA_PUMP_DIR tablesJOHNJ.CUST_WI
TH_SALT_INTEGRITY dumpfileCUST_WITH_SALT_INTEGRIT
Y_enc.dmp logfileCUST_WITH_SALT_INTEGRITY_enc.log
ENCRYPTIONALL ENCRYPTION_PASSWORDKla2Kli
19
7.4 Importing encrypted tables
The encryption wallet should be open before the
import
impdp directoryDATA_PUMP_DIR dumpfileCUST_WITH_S
ALT_INTEGRITY_enc.dmp logfileCUST_WITH_SALT_INTE
GRITY_enc_imp.log remap_schemaJOHNJTOMJ
ENCRYPTION_PASSWORDKla2Kli
7.5 Re-Keying the encryption keys
Master Key- Re-keying of master key doesn't
impact the performance Re-keying of master key
decrypts and re-encrypts the table and tablespace
keys Table Key- Requires planning It is
equivalent to performing a full upgrade EgALTER
TABLE CUST_WITHOUT_SALT REKEY Tablespace Key-
Not possible to re-key this. Before
any re-keying operation both the original
encryption wallet and auto-login wallet(if
created) should be available.
20
7.6 TDE and Standby DBs

• Copy the encryption wallet from the Primary to
  the Standby
• Re-Copy the encryption wallet to Standby if the
  master re-key operation is done.
• The redo log entries will remain encrypted even
  during the transit.

7.7 TDE and RMAN

• RMAN works seamlessly with TDE encrypted data.

21
7.8 Backup of Encryption Wallet

• Periodically backup the wallet.
• Backup the wallet immediately if the master key
  is re-keyed.
• Do not backup the wallet along with the encrypted
  data.
• If the wallet is lost data is lost.

7.9 TDE Data Dictionary Views

• DBA_ENCRYPTED_COLUMNS
• VENCRYPTED_TABLESPACES
• VWALLET

22
Thank You