Transparent Data Encryption Explained

1
Transparent Data Encryption Explained
2
By Cheryl Lei Bryll, OCP Senior Oracle
DBA Mobile [phone] cbryll_at_onshoresolutions.
net http//www.onshoresolutions.net

3
Transparent Data Encryption

• Why there is a need for Transparent Data
  Encryption
• What does Transparent Data Encryption address in
  the security model
• When to use Transparent Data Encryption
• How to implement Transaction Data Encryption

4
Why there is a need for Transparent Data
Encryption

• Security threats
• External threats - backup tapes
• Internal threats privileged users\outsourcing
• U.S laws - regulatory compliance issues
• PCI DSS
• FIPS 140-2
• SOX Act of 2002
• HIPPA
• International regulations

5
Security threats are an ever growing concern

• Findings from 2009 IOUG Data Security report
• 50 percent increase in data breaches since last
  year
• Managers see internal threats more pressing than
  external threats
• Outsourcing of database administration,
  development and testing functions
• Close to half of organizations employ actual
  production data within non-production
  environments
• corporate management is still complacent about
  data security. Management commitment needed

6
Internal threats do occur

• Think internal threats dont exist?
• Think again

7
(No Transcript)
8
The PCI Payment Card Industry

• PCI DSS is a set of requirements designed to
  ensure that ALL companies that process, store or
  transmit credit card information maintain a
  secure environment. 
• Created by Visa, MasterCard, Discover card, JCB
  and American Express
• Protect Cardholder Data - requirement 3

9
SOX - Sarbanes-Oxley requirement

• Sets standards for public companies
• Information technology governance for financials
• Section 404
• Assessment of internal controls
• Most costly to implement
• 2007 showed avg. 1.9 -1.3 million to comply
• External auditors to access compliance
• Focused on write events (tampering)

10
HIPAA - Health Insurance Portability and
Accountability Act

• Includes privacy protection provisions for
  personal health information
• Compliance has been required since 2005
• Includes a privacy rule and a security rule

11
What does Transparent Data Encryption address?

• Preventing privacy and identity theft
• Protecting data at rest, meaning data on the
  disks (in datafiles) or in backup media
• Protecting against unauthorized access by use of
  encryption keys
• Allows for an easy to implement solution to data
  protection

12
Oracle Transparent Data Encryption
addresses data protection and privacy standards
such as PCI DSS
13
Protecting data at rest with Transaction Data
Encryption

• The encryption is done at the operating system
  level, where data is stored
• Encryption keys are stored external of the
  database
• Table columns or entire tablespaces are encrypted
  
• The datafiles, archive logs, redo logs and backup
  media contain these objects in encrypted format
• Strong encryption algorithms are used

14
Transparent Data Encryption addresses strong
encryption

• The need for stronger data security standards
  with strong encryption is a growing concern

15
Need for strong encryption techniques

• PCI defines strong encryption
• The KEY determines the strength of an
  encryption algorithm. At a minimum 80 bits.
• FIPS (Federal Information Processing Standards)
• 140-2 defines strong encryption algorithms
• NIST (National Institute of Standards and
  Technology)
• Special publication 800-57 - Recommendation for
  Key Management

16
Encryption techniques

• Symmetric ciphers same key for both decryption
  and encryption
• DES,3DES,AES
• The NSA (National Security) has approved to use
  the AES 192 or 256 key length algorithms for top
  secret data
• Asymmetric ciphers different keys for both
  encryption and decryption
• RSA/DSA
• Hashing algorithms - One way encryption
• MD5

17
Protects against unauthorized access

• For each encrypted table column or tablespace a
  key is created
• The table and tablespace keys are encrypted with
  a master database key
• The master database key is stored external to the
  database (external security module)
• The external security module is the Oracle wallet
• Oracle 11g supports the Hardware Storage Module

18
Allows for an easy to implement solution to data
protection

• Before Transparent Data Encryption
• Oracle 8i API for data encryption called
  DBMS_OBFUSCATION_TOOLKIT package
• Oracle 9i provided support for the 3DES algorithm
• Oracle 10g the package DBMS_CRYPTO package was
  added
• With Transparent Data encryption
• Oracle 10g rel.2 introduced TDE with encryption
  at the column level
• Oracle 11g further enhances Transparent Data
  Encryption with tablespace encryption and support
  for HSM

19
When to use Transparent Data Encryption

• When data at rest needs to be protected
• When only certain data needs encryption
• When you need to adhere to regulatory standards
• When used as a contributing component of the
  overall security solution

20
How to use Transparent Data Encryption

• Oracle 10g column level encryption
• Oracle 11g tablespace level encryption
• Key management
• Backups Exports
• Replication
• Troubleshooting

21
Transparent Data Encryption 10g rel.2
Restrictions

• Transparent Data Encryption is not included in
  Standard Edition
• Transparent Data Encryption is an add-on product
  bundled with Oracle-net server or Oracle net
  client
• Transparent Data Encryption is only available in
  Oracle 10g rel. 2 and higher
• Indexes b-tree only
• TDE cannot be used in foreign key constraints
• TDE can't be enabled on a SYS-owned table
• TDE cannot be used in standard export and import
• The COMPATIBLE initialization parameter must be
  at least 10.2.x.x.
• RMAN backups not with image copies
• Materialized view logs
• Transportable tablespaces
• External large objects (BFILE)

22
Steps for using column-level Transparent Data
Encryption

• Set compatibility parameter
• Set up wallet location
• Create wallet
• Add encrypt to column
• Indexing encrypted columns
• Closing wallet
• Restarting database instance

23
Steps for using column-level Transparent Data
Encryption

• Set compatibility parameter
• Compatibility level of 10.2 or higher

24
Steps for using column-level Transparent Data
Encryption

• Set up wallet location
• search order for wallet location
• If exists, the wallet location specified by the
  parameter in the sqlnet.ora file
  ENCRYPTION_WALLET_LOCATION
• If exists, the wallet location specified by the
  parameter in the sqlnet.ora file WALLET_LOCATION
• The default location for the wallet
  (ORACLE_BASE/admin/ORACLE_SID/wallet)

mkdir /app/oracle/admin/test/encryption_wallet
25
Steps for using column-level Transparent Data
Encryption

• Create the wallet to hold the encryption key and
  open the wallet
• Must have alter system privilege
• Password is case sensitive, must use quotes
• The command will create a wallet file
  (ewallet.p12)
• Opens the wallet
• Generates database servers master encryption key

26
Steps for using column-level Transparent Data
Encryption

• Add encrypt to column
• Include the ENCRYPT clause to specific columns
• You can specify the encryption method using
• ENCRYPT USING ltAES192gt
• An encryption key for the table is created
• See all columns in your database that are
  encrypted
• SELECT FROM DBA_ENCRYPTED_COLUMNS

• CREATE TABLE tde_private (
• id NUMBER(10) primary key,
• info VARCHAR2(50) ENCRYPT USING 'AES192'
• )
• TABLESPACE transtable

27
Demo 10g Column level
28
Demo 10g Column level
29
Demo 10g Column level
car Card_num ------------------- X!wp!9r4_at_ee
car Card_num -------------------715088606525
30
Steps for using column-level Transparent Data
Encryption

• Indexing encrypted columns
• index columns cannot contain a salted encryption
  so be sure to create those columns as 'no salt
• Only b-tree indexes
• Do not use an encrypted column on an index used
  in range scans

31
Steps for using column-level Transparent Data
Encryption

• Foreign key columns cannot be encrypted
• This is because every table has a unique column
  encryption key

32
(No Transcript)
33
Steps for using column-level Transparent Data
Encryption

• Salt
• By default all columns have salt added
• Salt adds an extra layer of randomness
• You can turn salt off
• alter table cust_info modify (cust_last encrypt
  no salt)

• SQLgt desc DBA_ENCRYPTED_COLUMNS
• Name Null?
  Type
• -----------------------------------------
  -------- ----------------------------
• OWNER NOT
  NULL VARCHAR2(30)
• TABLE_NAME NOT NULL
  VARCHAR2(30)
• COLUMN_NAME NOT NULL
  VARCHAR2(30)
• ENCRYPTION_ALG
  VARCHAR2(29)
• SALT
  VARCHAR2(3)
• SQLgt COLUMN table_name format a15
• SQLgt COLUMN column_name format a15
• SQLgt SELECT table_name,column_name,salt FROM
  DBA_ENCRYPTED_COLUMNS
• TABLE_NAME COLUMN_NAME SALT
• --------------- --------------- ---------
• TDE_TEST DATA YES
• TDE_DOCTOR DOC_FIRST YES
• TDE_DOCTOR DOC_LAST YES
• TDE_TEST_2 DATA YES

34
Steps for using column-level Transparent Data
Encryption

• Closing the wallet
• Encrypted columns cannot be accessed
• Restarting the database
• The wallet must be manually opened

ALTER SYSTEM SET WALLET CLOSE ALTER SYSTEM
SET WALLET OPEN IDENTIFIED BY ltpasswordgt
35
How to prove encryption is working?

• SQLgt conn cust_admin/ltpasswordgt
• Connected.
• SQLgt create table my_secrets (
• v_special varchar2(100)) tablespace tde_ts
• Table created.
• SQLgt insert into my_secrets values
  ('TOP_SECRET')
• 1 row created.
• SQLgt COMMIT
• Commit complete.

36
Hacker on the OS can see data in your physical
files

• Without encryption the redo logs show cleartext
  of
• your DML
• bash-3.2 pwd
• /app/oracle/oradata/test
• bash-3.2 strings redo02.log grep TOP_SECRET
• TOP_SECRET

37
Hacker on the OS can see data in your physical
files

• With encryption the datafiles and redo logs do
  NOT show cleartext of your DML

38
Implementation steps

• Implementation Steps
• 1. Identify columns that require data protection
• credit cards, ssn, medical info
• 2. Verify supported datatype
• no bfiles
• 3. Verify column is not part of a foreign key
• query the data dictionary to find this
  information
• 4. Encrypt existing and new data
• a. may want to do a 'move' of tablespace to
  remove ghost copies
• b. perform the ddl
• c. alter tables
• d. backup database and wallet !

39
Transparent Data Encryption 11g

• Tablespace encryption
• No more searching for columns to encrypt
• Eliminates the foreign key limitation
• Less of a performance impact
• Oracle E-Biz 11i version 11.5.10.2 or higher
• Support for SecureFiles
• Support for hardware security modules (HSM)
• Stores master key on separate hardware device
• Share keys across servers

40
Steps for using tablespace Transparent Data
Encryption

• Tablespace
• No restriction on Foreign Key columns
• Default algorithm is AES 128
• Range scans are no longer a
• problem
• view vencrypted_tablespaces
• COMPATIBLE parameter to 11.1

• Create tablespace securets datafile
  '/u99/app/oracle/oradata/fins/fins/securets_01.dbf
  ' size 300M encryption using 'AES192' Default
  storage (encrypt)

41
Demo 11g tablespace encryption
42
Demo 11g tablespace encryption
43
Demo 11g tablespace encryption
44
Steps for SecureFile LOBsTransparent Data
Encryption

• SecureFile LOBs
• COMPATIBLE parameter to 11.1
• Block level encryption of LOBs
• Cannot change encryption
• algorithm, must do a rekey

• CREATE TABLE lob_tab (
• id NUMBER,
• cmment_info VARCHAR2(300),
• clob_data CLOB )
• LOB(clob_data) STORE AS SECUREFILE encrypt_lob(
  ENCRYPT USING 'AES256' )
• ALTER TABLE lob_tab MODIFY ( clob_data CLOB
  ENCRYPT USING '3DES168' )
• ! DOES NOT WORK
• ALTER TABLE lob_tab REKEY USING 'AES192'

45
Demo Lobs
46
Transparent Data Encryption support for HSM

• Support for hardware security modules (HSM)
• Allows master key to be stored in one place and
  used by many RAC nodes
• A rekey operation is needed to change or upgrade
  to using HSM in 11g

47
Transparent Data Encryption HSM

• How does the Hardware security module work?
• Basically a separate tamper-resistant
  hardware is used to create, store and use
  cryptographic keys. The HSM device adds
  increased processing power for encryption\decrypti
  on of keys. And should meet the proper
  validation to ensure it meets industry standards
  such as FIPS 140-2.

48
Transparent Data Encryption Implement HSM

• Steps to implement the Hardware security module
• Modify sqlnet.ora parameter ENCRYPTION_WALLET_LOCA
  TION(SOURCE(METHODHSM))
• Configure PCKS11 library
• /opt/oracle/extapi/32,64/hsm/VENDOR/VERSION/
  libapiname.ext
• Configure HSM device
• Create user/password
• Create the master key in the database
• ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY
  user_Idpassword MIGRATE USING wallet_password
• Open the wallet

49
Transparent Data Encryption Key Management

• Two-tier key architecture
• Resetting keys
• Backup and recovery of keys
• Autologin
• External security module
• Hardware security module
• Wallet

50
Transparent Data Encryption Key Management

• Two-tier key architecture
• Master database key
• Used to encrypt the column and tablespace keys
• Stored in the Oracle wallet
• Table \ tablespace key
• Used to encrypt columns indexes
• Stored in the data dictionary in encrypted format

51
Transparent Data Encryption Key Management

• Reset master key
• ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY
  ltpasswordgt
• Rekeying the table key
• ALTER TABLE cust_info REKEY
• Rekeying to change encryption algorithm
• ALTER TABLE cust_info REKEY USING '3DES168'
• ALTER TABLE cust_info ENCRYPT USING AES128

52
Transparent Data Encryption Key Management

• Backup of keys
• Must backup the ewallet.p12 file
• Every time you reset the master you should backup
  the wallet file
• Recovery of keys
• To restore simply apply a backup copy of
  ewallet.p12 to the wallet location
• If wallet is not the most recent master key, then
  you must perform a data recovery

53
Transparent Data Encryption Key Management

• Autologin
• Implicitly opens wallet
• Set up using
• mkwallet utility
• Oracle Wallet Manager
• Cannot have already opened wallet
• Not recommended for TDE (lessens security)

54
Transparent Data Encryption Key Management

• External security module the storing of master
  keys outside of the database
• Hardware security module (HSM)
• Oracle Wallet
• Default database wallet
• Separate wallet
• set in sqlnet.ora ENCRYPTION_WALLET_LOCATION

55
EXTERNAL SECURITY MODULE SUPPORT BY DATABASE
VERSION
DATABASE VERSION MASTER KEY FOR IN ORACLE WALLET IN HSM

Oracle RDBMS 10gR2 Column Encryption Yes No

Oracle RDBMS 11gR1 Column Encryption Yes Yes
(11.1.0.6) Tablespace Encryption Yes No

Oracle RDBMS 11gR1 Column Encryption Yes Yes
(11.1.0.7) Tablespace Encryption Yes Yes (no re-key)

Oracle Advanced Security Transparent Data
Encryption Best Practices August 2009 (version
11) Peter A. Wahl
56
Backups Exports RMAN

• TDE encrypted columns will be encrypted a second
  time during the backup
• RMAN Transparent mode is the default
• No DBA intervention no need to enter a password
  during daily backups
• RMANgt configure encryption for database on
• During recovery Oracle Wallet must be open

57
Backups Exports Data Pump
58
Demo Data Pump
ENCRYPTION_PASSWORD is the data pump password
used and not the wallet password. This password
must be similarly entered during the import
59
Demo Data Pump
Creating an external table with encrypted
columns, type ORACLE_DATAPUMP
60
Demo Data Pump
Oracle Data Pump prohibits the export of an
external table ORA-39214 Data Pump does not
support external tables with encrypted columns.
61
Transparent Data Encryption Replication

• Clones
• Materialized views
• Data guard
• Streams
• RAC

62
Cloning Production

• It is important that the Oracle Wallet from the
  source is copied to the target.

Production
Development
Copy
63
Materialized Views

• Encrypted columns cannot be used with 10g
  Materialized view logs.
• When creating a materialized view the target
  columns do not take on the encrypt attribute of
  the data types.

64
Data Guard

• It is important that the Oracle Wallet from the
  source is copied to the target.
• Creating a new wallet with the same password will
  not work.
• Encryptiondata_only, all,
• encrypted_columns_only
• encryption_modetransparent
• Encrption_algorithmAES192

Primary
Standby
Copy
65
Streams

• .

Local
car Card_num ------------------- X!wp!9r4_at_ee
decrypted

• The data is decrypted by the streams engine prior
  to transporting to the target system.
• However, if the target system does not
  successfully accept the message the data is
  stored in a temp location encrypted.

Buffered queue
car Card_num -------------------715088606525
downstream
encrypted
car Card_num ------------------- X!wp!9r4_at_ee
Copy wallet
66
RAC
RAC node 2

• Copy the Oracle Wallet from the first node to
  each additional node servers in the Real
  Application Cluster.
• Open the wallet manually on each node database.

RAC node 1
copy
RAC node 3
SAN Datafiles,redo ,archive logs
67
Troubleshooting

• How do you determine that Oracle Advanced
  Security Option is installed ?
• Universal installer
• opatch lsinventory
• ORACLE_HOME/bin/adapters

68
Troubleshooting

• How do you determine that Oracle Advanced
  Security Option is installed ?
• Universal installer
• opatch lsinventory
• ORACLE_HOME/bin/adapters

69
Troubleshooting

• How do you determine that Oracle Advanced
  Security Option is installed ?
• Universal installer
• opatch lsinventory
• ORACLE_HOME/bin/adapters

70
Troubleshooting

• If you create a new table based on a table with
  encrypted columns does the encrypt column
  definition transfer with the table?
• No

71
Troubleshooting

• What happens to my encrypted data when the Oracle
  wallet is closed?
• The data is inaccessible. However, you can still
  access all the other columns. Just do not
  perform select queries.

72
Troubleshooting

• SQLgt CREATE TABLE cust_info
• 2 ( cust_id NUMBER(12) PRIMARY KEY,
• 3 cust_last VARCHAR2(30) ENCRYPT USING
  'AES192' NO SALT,
• 4 cust_first VARCHAR2(30),
• 5 dob DATE,
• 6 state VARCHAR2(5),
• 7 ssn VARCHAR2(9) ENCRYPT USING
  'AES256'
• 8 ) TABLESPACE tde_ts
• ssn VARCHAR2(9) ENCRYPT USING 'AES256'
• ERROR at line 7
• ORA-28340 a different encryption algorithm has
  been chosen for the table

• Is it possible to apply to different encryption
  algorithms on the same table?
• NO! An error will result, the encryption
  algorithm is based on the table. Remember only
  one table key is created regardless of the amount
  of columns set to ENCRYPT.

73
Troubleshooting

• SQLgt column WRL_TYPE format a5
• SQLgt column WRL_PARAMETER format a50
• SQLgt select from VENCRYPTION_WALLET
• WRL_T WRL_PARAMETER
  STATUS
• ----- --------------------------------------------
  ------ ---------
• file /app/oracle/admin/test/encryption_wallet/
  OPEN

• How can the DBA determine if the wallet is open
  and how can the DBA determine the OS location of
  the wallet?
• Simply query the vencryption_wallet view.

74
Questions/Comments? Slides to be posted
to http//www.onshoresolutions.net/knowledge.html
White papers tutorials
75
References
Oracle 10g Advanced Security http//download.orac
le.com/docs/cd/B19306_01/network.102/b14268/asotra
ns.htmCHDCGBEA Oracle 11g Advanced Security
Guide http//download.oracle.com/docs/cd/B28359_01
/network.111/b28530/asotrans.htmCJADBFGF Oracle
Advanced Security Transparent Data Encryption
Best Practices August 2009 (version 11) Peter
A. Wahl PCI Standards www.pcisecuritystandards.or
g/security_standards/pci_dss.shtml