Transcript Column Level Encryption

All About Encryption
Encryption Overview
• Two main types we are concerned with
–
–
Data in motion, over the network
Data at rest, datafiles, backups, redo, exports
• We will be concentrating on data at rest
• Question & Discussion:
–
–
–
What is the goal behind encrypting data?
Why do we do it?
What doesn’t it do for us? What can it NOT protect us
from?
Encryption Overview
• Data in motion is easily done with SQL*Net and
ASO
–
–
–
Network traffic entirely encrypted, snoop proof
Encrypted checksum as well – to prevent “replay”
attacks (eg: let’s do that bank transfer twice)
And to prevent modification (eg: let’s change the
leading 1 to a 9 in that transaction)
Encryption Overview
• Data at rest options…
–
–
–
–
DBMS_OBFUSCATION_TOOLKIT
• 8i-9iR2
• Would not use this anymore
• Will not talk about it beyond this slide
• Let’s have a quick talk about wrapper packages…
DBMS_CRYPTO
• 10gR1 and above
• Would not use this unless I had to (because of the next two
bullets)
Column Level Encryption
• 10gR2 and above (ASO)
Tablespace Encryption
• 11gR1 and above (ASO)
DBMS_CRYPTO
• Encrypt/Decrypt data procedurally
–
–
–
DES, 3DES
AES
RC4
• Hash functions
–
–
MD5, SHA-1, MD4
Can use secret key as well
• Random functions
–
–
Raw keys
Number and Integers as well
DBMS_CRYPTO
• The major problem – KEY MANAGEMENT
–
–
–
Do you store the key in the application?
• How do you secure it there?
• You need to retrieve it and transmit it
Do you store the key in the database?
• If I steal your database, I have your keys
• You will have code that retrieves the key, I will find
out how
There are no good answers to this problem.
DBMS_CRYPTO
• API driven.
• You code it, definitely not transparent.
• Definite performance impact (compared to column and/or
tablespace encryption)
• Supports as inputs
–
–
–
RAW
CLOB
BLOB
• And always returns binary output
–
–
–
You will use BLOB or RAW to store
If you use varchar2, you need to round up to multiple of 16 and
double the length and RAWTOHEX or base64 encode the data.
Discuss legacy obfuscation toolkit and varchar2 flaw
DBMS_CRYPTO
• Simple Examples
–
–
–
–
Input raw after converting
Specify typ – the stream or block cipher type. Block
cipher is what we use for storing data persistently
Key – the encryption key
Use varchar2 interface and the CLOB
• Performance
–
–
What impact will this have? (it will be different for
everyone)
Encrypt1.sql
How to measure it?
Encrypt2.sql
Encrypt3.sql
DBMS_CRYPTO
• In review
–
–
–
–
Labor intensive
Key management is hard
• Keys are likely very easy to compromise
Definitely not transparent
Adds a lot to the runtime
Column and Tablespace Encryption
• Designed to be transparent
• Uses a wallet
–
–
–
–
Must steal database and
Steal wallet and
Kidnap the DBA in order to make use of the data
As opposed to dbms-crypto, where I likely just need your database
and some intelligence
• Or Hardware Security Modules (HSM)
–
–
–
Physical device that stores keys
Provides protected memory space to encrypt/decrypt so keys are
never visible
11g
Column Level Encryption
•
•
•
•
•
•
Data is cached encrypted in the SGA
Decrypted only when you dereference it
Encrypted every time you modify it
Undo generated is encrypted
Redo generated is encrypted
You can index it, but the index is indexing
encrypted data (not sorted!)
Column Level Encryption
• Example
–
–
–
–
–
Setup
Functionality
Performance impact on modification & retrieval
Sizing (20 byte checksum, padded out and salted)
Indexing Issues, check out cardinality
column1.sql
Column Level Encryption
• Restrictions
–
–
–
–
Must not ‘salt’ if you index
Cannot be used with foreign keys
Issues with exp/imp (clear text issues)
In 11g, datapump can handle encryption, preserving the
protection.
Tablespace Level Encryption
•
•
•
•
•
Data is stored on disk encrypted
Data is cached in the SGA unencrypted
Undo generated is encrypted
Redo generated is encrypted
Data is stored on block in clear text and encrypted
on way out to disk
–
–
–
So, index has ‘clear text’ in it
Range scan – solved
Foreign key – solved
Tablespace Level Encryption
• Example
–
–
–
Setup
Functionality
Performance impact on modification & retrieval
tablespace1.sql
Tablespace Level Encryption
• Restrictions
–
You cannot alter an existing tablespace to be encrypted
• You create a new encrypted tablespace and move
segments into it
• You can alter a column to be encrypted
o But you probably don’t want too!
o It’ll leave old copies of itself
Questions