Transcript Slide 1

Transparent Data Encryption
Explained
By: Cheryl Lei Bryll, OCP
Senior Oracle DBA
Mobile: 214-733-3869
[email protected]
http://www.onshoresolutions.net
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
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
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
Internal threats do
occur
Think internal threats don’t
exist?
Think again …
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
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)
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
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
Oracle Transparent Data Encryption addresses
data protection and privacy standards such as
PCI DSS
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
Transparent Data
Encryption addresses
strong encryption
The need for stronger data
security standards with
strong encryption is a
growing concern …
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
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
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
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
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
How to use Transparent Data
Encryption
Oracle 10g column level encryption
Oracle 11g tablespace level encryption
Key management
Backups & Exports
Replication
Troubleshooting
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 Oraclenet 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)
Steps for using column-level
Transparent Data Encryption:
1.Set compatibility parameter
2.Set up wallet location
3.Create wallet
4.Add ‘encrypt’ to column
5.Indexing encrypted columns
6.Closing wallet
7.Restarting database instance
Steps for using column-level
Transparent Data Encryption:
Set compatibility
parameter
– Compatibility level of 10.2
or higher
Steps for using column-level
Transparent Data Encryption:
Set up wallet location
search order for wallet location
1. If exists, the wallet location specified by
the parameter in the sqlnet.ora file
ENCRYPTION_WALLET_LOCATION
2. If exists, the wallet location specified by
the parameter in the sqlnet.ora file
WALLET_LOCATION
3. The default location for the wallet
($ORACLE_BASE/admin/$ORACLE_SID/wallet)
mkdir /app/oracle/admin/test/encryption_wallet
Steps for using column-level
Transparent Data Encryption:
Create the wallet to
hold the encryption key
and open the wallet
1. Must have ‘alter system’ privilege
2. Password is case sensitive, must use
quotes
3. The command will create a wallet file
(ewallet.p12)
4. Opens the wallet
5. Generates database server’s master
encryption key
Steps for using column-level
Transparent Data Encryption:
Add ‘encrypt’ to column
1. Include the ENCRYPT clause to
specific columns
2. You can specify the encryption
method using
ENCRYPT USING ‘<AES192>‘
3. An encryption key for the table is
created
4. 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;
Demo –
10g Column level
Demo –
10g Column level
Demo –
10g Column level
car
Card_num
------------------715088606525
car
Card_num
------------------X!wp#!9r4@ee
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
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
Steps for using column-level
Transparent Data Encryption:
Salt
1. By default all columns have
salt added
2. Salt adds an extra layer of
randomness
3. You can turn salt off
alter table cust_info modify (cust_last
encrypt no salt);
SQL> 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)
SQL> COLUMN table_name format a15;
SQL> COLUMN column_name format a15;
SQL> SELECT table_name,column_name,salt FROM
DBA_ENCRYPTED_COLUMNS;
TABLE_NAME
--------------TDE_TEST
TDE_DOCTOR
TDE_DOCTOR
TDE_TEST_2
TDE_PRIVATE
CUST_INFO
CUST_INFO
CUST_INFO
COLUMN_NAME
--------------DATA
DOC_FIRST
DOC_LAST
DATA
INFO
SSN
DOB
CUST_LAST
SALT
--------YES
YES
YES
YES
YES
YES
YES
NO
Steps for using column-level
Transparent Data Encryption:
Closing the wallet
ALTER SYSTEM SET WALLET CLOSE;
Encrypted columns cannot
be accessed
Restarting the
database
The wallet must be manually
opened
ALTER SYSTEM SET WALLET
OPEN IDENTIFIED BY “<password>“;
How to prove encryption is working?
SQL> conn cust_admin/<password>
Connected.
SQL> create table my_secrets (
v_special varchar2(100)) tablespace tde_ts;
Table created.
SQL> insert into my_secrets values ('TOP_SECRET');
1 row created.
SQL> COMMIT;
Commit complete.
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
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
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 !
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
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
v$encrypted_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);
Demo –
11g tablespace encryption
Demo –
11g tablespace encryption
Demo –
11g tablespace encryption
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';
Demo –
Lobs
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
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\decryption of keys. And should meet
the proper validation to ensure it meets industry
standards such as FIPS 140-2.
Transparent Data Encryption –
Implement HSM
Steps to implement the Hardware security
module:
Modify sqlnet.ora parameter
ENCRYPTION_WALLET_LOCATION=(SOURCE=(METHOD=HSM))
Configure PCKS#11 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_Id:password [MIGRATE USING wallet_password]
Open the wallet
Transparent Data Encryption –
Key Management
Two-tier key architecture
Resetting keys
Backup and recovery of keys
Autologin
External security module
Hardware security module
Wallet
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
Transparent Data Encryption –
Key Management
Reset master key
ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED
BY <password>;
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’;
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
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)
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
EXTERNAL SECURITY MODULE SUPPORT
BY DATABASE VERSION
DATABASE VERSION
MASTER KEY FOR …
Oracle RDBMS
10gR2
Column Encryption
Oracle RDBMS
11gR1
Column Encryption
… IN ORACLE
WALLET
… IN HSM
Yes
No
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
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
– RMAN> configure encryption for database on;
During recovery Oracle Wallet must be open
Backups & Exports –
Data Pump
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
Demo –
Data Pump
Creating an external table with encrypted columns, type ORACLE_DATAPUMP
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.
Transparent Data Encryption –
Replication
Clones
Materialized views
Data guard
Streams
RAC
Cloning Production
It is important that the
Oracle Wallet from the
‘source’ is copied to the
‘target’.
Production
Development
Copy
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.
Data Guard
It is important that the Oracle
Wallet from the ‘source’ is
copied to the ‘target’.
Primary
Standby
Creating a new wallet with the
same password will not work.
Copy
Encryption=data_only, all,
encrypted_columns_only
encryption_mode=transparent
Encrption_algorithm=AES192
.
Streams
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.
Local
car
Card_num
------------------X!wp#!9r4@e
e
decrypted
Buffered queue
car
Card_num
------------------715088606525
downstream
encrypted
car
Card_num
------------------X!wp#!9r4@ee
Copy wallet
RAC node 2
RAC
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
Troubleshooting
How do you determine
that Oracle Advanced
Security Option is
installed ?
Universal installer
opatch lsinventory
$ORACLE_HOME/bin/
adapters
Troubleshooting
How do you determine
that Oracle Advanced
Security Option is
installed ?
Universal installer
opatch lsinventory
$ORACLE_HOME/bin/
adapters
Troubleshooting
How do you determine
that Oracle Advanced
Security Option is
installed ?
Universal installer
opatch lsinventory
$ORACLE_HOME/bin/
adapters
Troubleshooting
If you create a new table
based on a table with
encrypted columns does
the ‘encrypt’ column
definition transfer with
the table?
No
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.
Troubleshooting
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.
SQL> 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
Troubleshooting
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
v$encryption_wallet view.
SQL> column WRL_TYPE format a5
SQL> column WRL_PARAMETER
format a50
SQL> select * from
V$ENCRYPTION_WALLET;
WRL_T WRL_PARAMETER
STATUS
----- -------------------------------------------------- --------file /app/oracle/admin/test/encryption_wallet/
OPEN
Questions/Comments?
Slides to be posted to:
http://www.onshoresolutions.net/knowledge.html
“White papers & tutorials”
References:
Oracle 10g Advanced Security
http://download.oracle.com/docs/cd/B19306_01/network.102/b14268/asotrans.htm#CHDCGBEA
Oracle 11g Advanced Security Guide
http://download.oracle.com/docs/cd/B28359_01/network.111/b28530/asotrans.htm#CJADBFGF
Oracle Advanced Security Transparent Data Encryption Best Practices;
August 2009 (version 11) Peter A. Wahl
PCI Standards
www.pcisecuritystandards.org/security_standards/pci_dss.shtml