Transcript Migrating Data Across Databases

Oracle11g Transparent Data Encryption
Carl Dudley
University of Wolverhampton, UK
UKOUG SIG Director
Oracle ACE Director
[email protected]
Introduction
Working with Oracle since 1986
Oracle DBA - OCP Oracle7, 8, 9, 10
Oracle DBA of the Year – 2002
Oracle ACE Director
Regular Presenter at Oracle Conferences
Consultant and Trainer
Technical Editor for a number of Oracle texts
UK Oracle User Group Director
Member of IOUC
Day job – University of Wolverhampton, UK
Carl Dudley University of Wolverhampton, UK
2
Oracle 11g Transparent Data Encryption
TDE Architecture and Usage
Transparent Column Encryption
Transparent Tablespace Encryption
Encrypted Backups
Encryption Outside of the Database
Master Key Maintenance
Performance Tests
Carl Dudley University of Wolverhampton, UK
3
The Drivers
 Regulatory Compliance
— SOX, Basel II, HIPAA, J-SOX, GLB, Privacy Laws
— Adequate ITDATE
controls
RECORDS
ORGANISATIONS
— Separation of
Duty
130,000,000
2009-01-20
Heartland Payment Systems
— Proof of compliance
94,000,000
2007-01-17
TJX Companies Inc.
— Risk assessment and monitoring
30,000,000
2004-06-24
America Online
 Insider Threat Estimates
26,500,000
2006-06-24
U.S. Department of Veterans Affairs
— 60% of threats come from insiders
25,000,000
2007-11-20
HM Revenue and Customs, TNT
— 65% of internal
threats are undetected
17,000,000
2008-10-06
T-Mobile, Deutsche Telekom
 Costs $6 – $16 to protect each customers account
data
 Costs $90 per customer account when breached
 Break of disclosure laws - $239 per record
Source -- http://datalossdb.org
Carl Dudley University of Wolverhampton, UK
4
Transparent Data Encryption (TDE)
 Available with Oracle Advanced Security Option
 Protects data on storage media by encryption
— If disks are stolen, unauthorized users cannot decrypt
• Need to steal disks, wallet and extract password from the DBA
 Satisfies requirements of HIPAA, PCI-DSS, Sarbanes-Oxley and VISA
Cardholder Information Security Program
 Not full-scale encryption
— Does not decrypt data depending on who attempts to select it
• Need to use supplied PL/SQL packages to selectively decrypt
HIPAA = Health Insurance Portability and Accountancy Act (USA)
Carl Dudley University of Wolverhampton, UK
5
Two Levels of Transparent Data Encryption (TDE)
1. Column level encryption
— Subsets of columns in tables can be encrypted
— For example, credit card information
— The ‘column’ encryption keys are stored in the data dictionary
• These keys are themselves encrypted by the Master Key held in an
external wallet outside of the database files
— Security hierarchy
• Table encryption key
• Master encryption key (database wide)
• Password protected wallet
2. Tablespace level encryption
(Oracle11g)
— Tablespace master encryption key is held in wallet
Wallet can be an OS file (software wallet) or in Hardware Security Module (HSM)
Carl Dudley University of Wolverhampton, UK
6
Transparent Data Encryption (continued)
 No change in application
 No application key management
 No database triggers/views
 No stored procedures
 No change in query plans (?)
 Component of ASO
— ASO includes encrypted backups
 Should be only one layer in an overall security strategy
Carl Dudley University of Wolverhampton, UK
7
Decrypting Data in Encrypted Columns
Wallet (software
or HSM)
Data Dictionary (enc$)
Encrypted
table key
1
1
1
Decrypt
table key
External to
the database
Decrypted
table key
2
Master
Key
2
Database
ID
NAME
JOB
SAL
345
COX
CLERK
350
263
FORD
CASHIER
200
481
WOOD
PORTER
300
981
WARD
SALES
475
572
BELL
CLERK
560
668
HALL
CLERK
200
1. Use master key from wallet to
decrypt the column/table key(s)
2. Use decrypted column/table key
to decrypt job and sal values
Encrypted columns
Carl Dudley University of Wolverhampton, UK
8
Setting up TDE (1)
1. Set up a wallet directory in sqlnet.ora and start the listener
ENCRYPTION_WALLET_LOCATION= (SOURCE= (METHOD=FILE)
(METHOD_DATA=(DIRECTORY=<pathname>)))
— There must not be an obfuscated wallet in the directory ('cwallet.sso‘)
2. Initialise the database master key
ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY "ZQ$H!928";
— Creates a default password protected Oracle wallet to store the key
• Builds a file called ewallet.p12
• If no entry in sqlnet.ora, wallet is created in default directory
• All passwords stored as uppercase unless enclosed by quotes
Carl Dudley University of Wolverhampton, UK
9
Setting up TDE (2)
 The wallet needs to be opened after each startup of the database
ALTER SYSTEM SET ENCRYPTION WALLET OPEN
IDENTIFIED BY "ZQ$H!928";
― The double quotes and ‘ENCRYPTION’ may not be necessary
 Testing for an OPEN or CLOSED wallet (11g)
SELECT * FROM v$encryption_wallet;
WRL_TYPE WRL_PARAMETER
STATUS
-------- ----------------- -----File
C:\ORACLE\ENCRYPT OPEN
 Wallet can be closed (password not necessary on 11g R1)
ALTER SYSTEM SET WALLET CLOSE IDENTIFIED BY "ZQ$H!928";
Carl Dudley University of Wolverhampton, UK
10
Setting up TDE (3)
3a. Introduce encryption for particular columns (if required)
ALTER TABLE emp MODIFY (sal ENCRYPT NO SALT);
ALTER TABLE emp MODIFY (job ENCRYPT SALT);
— SALT can be added for columns having repetitive values (default)
• Random 16 byte string is added to encrypted value to prevent discovery
of patterns in the encryption
• Indexes on SALTed columns are not allowed
— NO SALT allows ONLY equality searches via an index
• Index values are encrypted along with the data
• Index nodes are not sorted in column value order
– Encrypted indexes do not support range scans
Carl Dudley University of Wolverhampton, UK
11
Setting up TDE (4)
3b. Introduce tablespace level encryption (if required)
CREATE TABLESPACE enc2
DATAFILE 'c:\encrypted_tablespaces\enc2_f1.dbf' size 100M
AUTOEXTEND ON NEXT 1M
ENCRYPTION -- using algorithm (AES128 is default)...
DEFAULT STORAGE ( ENCRYPT );
 Populate with
― ALTER TABLE ... MOVE
― CTAS
― Online redefinition
― Data Pump import
 Key is held in tablespace file headers and segment headers
Carl Dudley University of Wolverhampton, UK
12
Job Separation
DBA starts up database but
no access to wallet
STARTUP
Security DBA
Opens wallet
Carl Dudley University of Wolverhampton, UK
13
Oracle 11g Transparent Data Encryption
TDE Architecture and Usage
Transparent Column Encryption
Transparent Tablespace Encryption
Encrypted Backups
Encryption Outside of the Database
Master Key Maintenance
Performance Tests
Carl Dudley University of Wolverhampton, UK
14
Column Encryption Methods
 Supported encryption methods (default is AES192)
— Security depends on secrecy of key , not the algorithm
Algorithm
Key Size
Parameter
Name
Triple DES (Data Encryption Standard) 168 bits
3DES168
AES (Advanced Encryption Standard)
128 bits
AES128
AES
192 bits (default) AES192
AES
256 bits
AES256
 Recommended not to use DES3 (Triple DES)
— Slowest and weakest - someone will break it
• Only present because of FIPS
• Found to be 10% slower on encryption
Carl Dudley University of Wolverhampton, UK
15
Column Encryption – Dictionary Information
DESC scott.empt
Name
Null?
----------------------------- -------ENAME
JOB
JOBNOSALT
JOB_FILLER
JOBSALT
ENAME_FILLER
Type
------------------VARCHAR2(10)
VARCHAR2(9)
VARCHAR2(9) ENCRYPT
VARCHAR2(9)
VARCHAR2(9) ENCRYPT
VARCHAR2(10)
SELECT * FROM user_encrypted_columns;
OWNER
----SCOTT
SCOTT
SCOTT
SCOTT
SCOTT
SCOTT
SCOTT
SCOTT
SCOTT
TABLE_NAME
---------EMPE
EMPE
EMPE
EMPE
EMPE
EMPE
EMPENC
EMPT
EMPT
COLUMN_NAME
--------------JOB_SALT
JOB_NOSALT
HIREDATE_SALT
HIREDATE_NOSALT
EMPNO_SALT
EMPNO_NOSALT
JOB
JOBSALT
JOBNOSALT
ENCRYPTION_ALG
----------------------------AES 192 bits key
AES 192 bits key
AES 192 bits key
AES 192 bits key
AES 192 bits key
AES 192 bits key
3 Key Triple DES 168 bits key
AES 192 bits key
AES 192 bits key
Carl Dudley University of Wolverhampton, UK
SALT
---YES
NO
YES
NO
YES
NO
YES
YES
NO
16
Column Encryption Features
 Encryption is preserved in hot standbys and Data Pump exports
— if the export itself is encrypted via password protection
 Backups contain the encrypted data
— Retired Master keys are maintained in the wallet so that backed up
encrypted data remains accessible via the old keys
 Data remains encrypted in temp files, undo segments and buffer cache
— No residual data is left in the clear on disk – but see later
• Encryption occurs on table creation and data always remains encrypted
Carl Dudley University of Wolverhampton, UK
17
Column Encryption Features (continued)
 Not all datatypes are supported
— Foreign keys and referenced columns cannot be encrypted
— VARCHAR2 <= 3932bytes
— No FBIs
— If partitions/subpartitions are exchanged then both tables must be similarly
encrypted
 Different encryption key mechanisms can be used within the same
database
 Converting a column to be encrypted implies a whole table update
— High consumption of resources and locking if large tables are involved
— Drop and recreate indexes to save time
 Heavily dependent on security of encryption key(s)
— TDE is not a substitute for properly designed security and access controls
Carl Dudley University of Wolverhampton, UK
18
Retrieving Encrypted Column Data
1. Wallet opened using the wallet password by DBA on startup
ALTER SYSTEM SET ENCRYPTION WALLET OPEN
IDENTIFIED BY "ZQ$H!928";
— User is able to select encrypted data
SELECT empno,job FROM emp;
EMPNO JOB
----- --------7369 CLERK
7900 MANAGER
2. Wallet remains unopened after startup
— User receives error only when attempting to retrieve encrypted data
SELECT empno,job FROM emp;
ORA-28365 : wallet is not open
SELECT empno FROM emp;
EMPNO
----7369
7900
Carl Dudley University of Wolverhampton, UK
19
Transparent Encryption
SELECT DUMP(jobsalt) FROM empe WHERE ROWNUM < 6;
DUMP(JOBSALT)
---------------------------------------Typ=1 Len=5: 67,76,69,82,75
Typ=1 Len=8: 83,65,76,69,83,77,65,78
Typ=1 Len=8: 83,65,76,69,83,77,65,78
Typ=1 Len=7: 77,65,78,65,71,69,82
Typ=1 Len=8: 83,65,76,69,83,77,65,78
Jobsalt
column
encrypted
SELECT VSIZE(jobsalt)
FROM empe WHERE ROWNUM < 6;
VSIZE(JOBSALT)
-------------5
8
8
7
8
 No evidence of encryption
— Optimizer is aware of encryption
— Underestimates load on CPU
Carl Dudley University of Wolverhampton, UK
20
Storage Required for Column Encryption
 Single column table of 3584 rows
 Column is VARCHAR2(12) and contains repeated instances of 5 different
jobs of average length 7 bytes
 Tablespace has 8K blocks
Conventional
table
job encrypted job encrypted
without salt
with salt
Number of
blocks
7
22
29
Rows per block
563
173
125
 Encrypted columns cannot be compressed
Carl Dudley University of Wolverhampton, UK
21
Indexes on Encrypted Columns
Table name
Encryption
empjob
job column not encrypted
empnosalt
job column encrypted without salt
empsalt
job column encrypted with salt
CREATE INDEX empjob$job ON empjob(job);
-- Occupies 8 blocks
CREATE INDEX empnosalt$job ON empnosalt(job);
-- Occupies 28 blocks
CREATE INDEX empsalt$job ON empsalt(job);
ORA-28338: cannot encrypt indexed column(s) with salt
— All tables have ~ 900,000 rows
Carl Dudley University of Wolverhampton, UK
22
The emptest Table
 Another test table constructed so that encrypted data can be tracked
— Sandwiched between ‘normal’ character data
CREATE TABLE emptest (
ename
VARCHAR2(10),
job
VARCHAR2(9),
jobnosalt
VARCHAR2(9) ENCRYPT NO SALT,
jobmid
VARCHAR2(9),
jobsalt
VARCHAR2(9) ENCRYPT SALT,
lastename
VARCHAR2(10));
 Populated with 986 rows of repetitive data
SELECT * FROM scott.emptest WHERE ROWNUM < 10;
ENAME
---------SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
JOB
--------CLERK
SALESMAN
SALESMAN
MANAGER
SALESMAN
MANAGER
MANAGER
ANALYST
PRESIDENT
JOBNOSALT
--------CLERK
SALESMAN
SALESMAN
MANAGER
SALESMAN
MANAGER
MANAGER
ANALYST
PRESIDENT
JOBMID
--------CLERK
SALESMAN
SALESMAN
MANAGER
SALESMAN
MANAGER
MANAGER
ANALYST
PRESIDENT
JOBSALT
--------CLERK
SALESMAN
SALESMAN
MANAGER
SALESMAN
MANAGER
MANAGER
ANALYST
PRESIDENT
LASTENAME
--------SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
Carl Dudley University of Wolverhampton, UK
23
Hex Dump of Encrypted Table Using HEDIT
07
34
Length byte
showing 52
encrypted
characters
Length byte
showing 7
characters
Carl Dudley University of Wolverhampton, UK
24
Structure of Final Test Table
SQL> DESC empe
Name
Null? Type
--------------- ----- --------------------FILL1
VARCHAR2(3)
EMPNO
NUMBER(12,6)
FILL2
VARCHAR2(3)
EMPNONOSALT
NUMBER(12,6) ENCRYPT
FILL3
VARCHAR2(3)
EMPNOSALT
NUMBER(12,6) ENCRYPT
FILL4
VARCHAR2(3)
HIREDATE
DATE
FILL5
VARCHAR2(3)
HIREDATENOSALT
DATE ENCRYPT
FILL6
VARCHAR2(3)
HIREDATESALT
DATE ENCRYPT
FILL7
VARCHAR2(3)
CHARCOL
VARCHAR2(100)
FILL8
VARCHAR2(3)
CHARCOLNOSALT
VARCHAR2(100) ENCRYPT
FILL9
VARCHAR2(3)
CHARCOLSALT
VARCHAR2(100) ENCRYPT
FILL10
VARCHAR2(3)
Values
--------AAA
BBB
CCC
DDD
EEE
FFF
GGG
X
HHH
X Encrypted without salt
III
X Encrypted with salt
JJJ
Carl Dudley University of Wolverhampton, UK
25
Dumping Oracle Blocks
SELECT file_id,block_id
FROM dba_extents
WHERE segment_name = 'EMPTEST';
FILE_ID BLOCK_ID
------- -------7
11
7
13
7
15
7
17
ALTER SYSTEM DUMP DATAFILE 7 BLOCK 15;
 Produces a trace file in the USER_DUMP_DEST or trace directory
<oracle_sid>_ora_<spid>.trc
— spid is from spid in v$process
Carl Dudley University of Wolverhampton, UK
26
Oracle Hexadecimal Dump of Datablock
9747470
9747480
9747490
97474A0
97474B0
97474C0
97474D0
97474E0
97474F0
9747500
9747510
9747520
9747530
9747540
9747550
9747560
9747570
9747580
9747590
97475A0
97475B0
13012C4A
4D75C624
B765BEE0
C93670DF
17382796
84AA4E41
109A3A5D
77074444
9DD941A3
B994DE07
03279521
678B5DB8
9701FDF0
321F8109
48035801
DE2F4385
5286A4C4
324022B6
8AAC1F31
D2259BBC
B3901ABF
41414103
263D8301
033E427D
43430349
EEF700DA
0F7920AE
EFF599BC
011602B5
4969D6E9
BFF989E4
34464646
73213CB4
FFD84631
E3D9212E
E9244848
68FE6297
C74DF1B9
FDE49833
CD9CD845
48E690DD
4A4A4A03
164CC203
4A122CA6
18210733
F28E3443
1464E07F
E7B98C9E
53666A32
45030101
0327759C
47E3D46F
19353C33
EFD4E5C0
CB625933
61369397
2840AB8D
06B36BDA
038C9DBD
32BB1A66
72073567
EB4CD0BC
0313012C
42424203
85E65358
0CBDC26B
D3F2D41F
851B2241
92788311
4403D183
36244545
104F0370
89396194
AFEBEF0A
47AD0BD4
EA0659BF
47474703
2333F7A5
3223540D
34494949
A58D0037
B70430E3
1C926222
03414141
[J,...AAA..L..BBB]
[$.uM..=&.,.JXS..]
[..e.}B>.3.!.k...]
[.p6.I.CCC4......]
[.'8.......d.A"..]
[AN... y.......x.]
[]:......2jfS...D]
[DD.w.......EEE$6]
[.A....iI.u'.p.O.]
[........o..G.a9.]
[!.'.FFF43<5.....]
[.].g.<!s.......G]
[....1F..3Yb..Y..]
[...2.!....6a.GGG]
[.X.HHH$...@(..3#]
[.C/..b.h.k...T#2]
[...R..M.....III4]
[."@23...f..27...]
[1...E...g5.r.0..]
[..%....H..L."b..]
[.....JJJ,...AAA.]
Unencrypted
DATE column
Encrypted DATE
without salt
Encrypted DATE
with salt
AAA,BBB,...,JJJ are filler column data
Carl Dudley University of Wolverhampton, UK
27
Encrypted Column Lengths - Examples
Datatype
Actual NO SALT
length AES192
SALT
AES192
NO SALT
3DES168
SALT
3DES168
NUMBER
any
36
52
28
36
DATE
7
36
52
28
36
VARCHAR2(1)
1
36
52
28
36
VARCHAR2(9)
9
36
52
36
44
VARCHAR2(100)
1-7
36
52
28
36
VARCHAR2(100)
8-15
36
52
36
44
VARCHAR2(100)
16-23
52
68
44
52
VARCHAR2(100)
24-31
52
68
52
60
VARCHAR2(100)
45
68
84
68
76
VARCHAR2(100)
63
84
100
84
92
VARCHAR2(100)
72
100
116
100
108
VARCHAR2(100)
81
116
132
108
116
Statistics from dbms_stats do not show the increase in storage
Carl Dudley University of Wolverhampton, UK
28
Column Encryption Padding and Storage
1. All data padded to nearest 16 bytes when using AES
2. 20-byte integrity checksum is added to give tamper detection for the
cipher text
3. SALT requires an additional 16 bytes
 NOMAC can be used to suppress the 20-byte integrity check
— 10.2.0.4, 11.1.0.7 and up
— Defined at table level and applies to all columns in a table when specified on
on any one column
ALTER TABLE emp MODIFY ename ENCRYPT 'NOMAC';
Carl Dudley University of Wolverhampton, UK
29
Presence of Histograms
 Encrypted values (tablespace or column) can be seen in histogram views
— Oracle automatically creates histograms by default
— VARCHAR2 columns are translated into large numerics that are then
truncated
CREATE• OR
REPLACE
FUNCTION
fn_hist_str(pi_str_hist
IN NUMBER)
User
written
functions
can reconstruct the number
and then decode it
RETURN VARCHAR2
—
Dates are shown as Julian dates
julian dates
AS
— vc_hist
Numbers
not translated
:= are
TO_CHAR(pi_str_hist,'fm'||RPAD('x',50,'x'));
vc_return
VARCHAR2(4000);
TABLE_NAME COLNAME
END_VAL
REAL_VAL MOD_REAL
BEGIN
----------------- ------------------------------------ -------- -------EMP_HISTO
ALLEM ´ ALLEN´
WHILE (ENAME
vc_hist 339046801723754000000000000000000000
is not null )
EMP_HISTO
ENAME
344238228918531000000000000000000000
BLAKD § BLAKE§
LOOP
EMP_HISTO
ENAME:= vc_return||CHR(TO_NUMBER(SUBSTR(vc_hist,1,2),'xx'));
365069608210433000000000000000000000 FORC ‗ FORD‗
vc_return
EMP_HISTO ENAME
385838479141748000000000000000000000 JONER ý JONESý
vc_hist
:= SUBSTR( vc_hist, 3 );
EMP_HISTO
HIREDATE
2444591 2444591 2444591
END
LOOP;
EMP_HISTO HIREDATE
2444656 2444656 2444656
RETURN vc_return;
EMP_HISTO
SAL
2500 2500
2500
EMP_HISTO
SAL
3000 3000
3000
END;
SELECT TO_DATE(2444591,'j') FROM dual;
TO_DATE(244
Encrypted data still available in Result Cache after wallet is closed
----------17-DEC-1980
3.8584E+35
Carl Dudley University of Wolverhampton, UK
30
Column Encryption – Available Datatypes
 Available Datatypes
CHAR
INTERVAL DAY TO SECOND
NCHAR
NVARCHAR2
TIMESTAMP
SECUREFILE/LOB (11g)
DATE
INTERVAL YEAR TO MONTH
NUMBER
RAW
VARCHAR2
 Candidate Columns
— Credit card Numbers
— First name
— Last name
— Driver Licence number
— Medical and health information (images that include PII)
Carl Dudley University of Wolverhampton, UK
31
Modifying Encrypted Columns
 Removing column encryption - has no effect on size of object
ALTER TABLE emp MODIFY job DECRYPT;
 Changing the table key and/or method of encryption
ALTER TABLE emp REKEY;
ALTER TABLE emp REKEY USING '3DES168';
— Only one algorithm allowed per table - column names cannot be specified
— Need correct version of master key
— Serious operation as wholesale updates occur
• Columns are decrypted and then re-encrypted
• Drop and recreate indexes to limit impact
– Use dbms_metadata to obtain index DDL statements
 Changing the salt situation
 ALTER TABLE emp MODIFY (ename ENCRYPT NO SALT);
Carl Dudley University of Wolverhampton, UK
32
Constraint Column Restrictions
ALTER TABLE emp ADD PRIMARY KEY(empno);
-- empno is already encrypted
ORA-28338: cannot encrypt indexed column(s) with salt
ALTER TABLE emp ADD CONSTRAINT emp_dept_fk
FOREIGN KEY (deptno) REFERENCES dept;
-- deptno is now a foreign key
ALTER TABLE dept MODIFY deptno ENCRYPT NO SALT;
*
ORA-28335: referenced or referencing FK constraint
column cannot be encrypted
ALTER TABLE emp ADD
FOREIGN KEY(empno) REFERENCES dept(deptno);
–- deptno is already encrypted
ORA-28335: referenced or referencing FK constraint
column cannot be encrypted
Carl Dudley University of Wolverhampton, UK
33
Controlling the Column Encryption
 Tables have different encryption keys to minimise risk
— Same values in encrypted columns in different tables
different ciphertexts
 IDENTIFIED BY can enable the same key to be used on different columns
— SALT must not be added
CREATE TABLE dept2cols (
dname VARCHAR2(20) ENCRYPT IDENTIFIED BY "abc123" NO SALT
,loc
VARCHAR2(20));
CREATE TABLE emp2cols (
dname VARCHAR2(20) ENCRYPT IDENTIFIED BY "abc123" NO SALT
,ename VARCHAR2(20));
— Same encryption is used for dname in both tables
Perhaps used in future to get around foreign key restrictions?
Carl Dudley University of Wolverhampton, UK
34
Controlling the Column Encryption (continued)
 Insert data into both tables
INSERT INTO dept2cols VALUES ('ACCOUNTING','LONDON');
INSERT INTO emp2cols VALUES ('ACCOUNTING','SMITH');
 Dump shows that the encrypted values are identical
block_row_dump:
tab 0, row 0, @0x1f69
dept2cols
tl: 47 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [36]
99 04 47 45 0a 72 c6 02 1c 4f e3 f8 15 23 6c 30 0d 23 2e 13 c1 44 97 17 87
41 04 ef 7c 63 f4 3b 0b 3d 1e 01
col 1: [ 6] 4c 4f 4e 44 4f 4e
block_row_dump:
tab 0, row 0, @0x1f69
emp2cols
tl: 47 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [36]
99 04 47 45 0a 72 c6 02 1c 4f e3 f8 15 23 6c 30 0d 23 2e 13 c1 44 97 17 87
41 04 ef 7c 63 f4 3b 0b 3d 1e 01
col 1: [ 6] 53 4d 49 54 48
Carl Dudley University of Wolverhampton, UK
35
Oracle 11g Transparent Data Encryption
TDE Architecture and Usage
Transparent Column Encryption
Transparent Tablespace Encryption
Encrypted Backups
Encryption Outside of the Database
Master Key Maintenance
Performance Tests
Carl Dudley University of Wolverhampton, UK
36
Tablespace Encryption
 Physical data files are encrypted but with NO storage overhead
— Uses cipher feedback mode (CFB) for space preservation
 Oracle will automatically decrypt data before it arrives in the SGA
— Does not apply to column-level TDE
 Only when data is written to the file system will it be encrypted
— True for all disk-based structures
 11g Release 2 – same master key for tablespace and wallet encryption
Carl Dudley University of Wolverhampton, UK
37
Indexes and Encrypted Tablespaces
 Indexes and foreign keys continue to work as normal
 Indexes on encrypted tables are in clear if not in encrypted tablespaces
— Return data even when wallet is closed if table is not in execution plan
• Likewise materialized views
 For unencrypted tables, indexes in encrypted tablespaces remain active
when wallet is closed
• Execution plans remain the same
Carl Dudley University of Wolverhampton, UK
38
Tablespace Encryption Support
 Supported
— Move table between encrypted tablespaces and non-encrypted tablespaces
— Transportable tablespace using datapump
— 3DES128, AES 128 (default), AES192, AES256
— All types of objects can be encrypted
 Not Supported
— Tablespace encryption for SYSTEM, SYSAUX, UNDO and TEMP tablespaces
— Encryption of existing tablespaces and REKEY operations
• The table(s) needs to be moved into a new encrypted tablespace
— Traditional export/import and block dumps
 Dictionary support
— DBA_TABLESPACES has new "ENCRYPTED" column
— V$ENCRYPTED_TABLESPACES
• Shows TS#, so needs to be joined with dba_tablespaces
Carl Dudley University of Wolverhampton, UK
39
When to use Tablespace Encryption
 Use TDE tablespace encryption if any of the following is true:
— You cannot find all columns with sensitive content
— Sensitive column is used as foreign key
— Applications perform range scans over indexed, encrypted columns
— You need index types other than B-tree over encrypted columns
— Need to encrypt lots of columns
 Tablespace encryption allows full benefit of compression
— Compression is applied before the data blocks are encrypted
 Column encryption allows compression on unencrypted columns only
— Encryption occurs in the SQL layer before the compression process
Carl Dudley University of Wolverhampton, UK
40
Oracle 11g Transparent Data Encryption
TDE Architecture and Usage
Transparent Column Encryption
Transparent Tablespace Encryption
Encrypted Backups
Encryption Outside of the Database
Master Key Maintenance
Performance Tests
Carl Dudley University of Wolverhampton, UK
41
Encryption with RMAN
 Selection of algorithms available
— AES 128-bit is the default
SELECT * FROM v$rman_encryption_algoritms;
ALGORITHM_ID ALGORITHM_
NAME
------------ ---------1 AES128
2 AES192
3 AES256
ALGORITHM_
DESCRIPTION
--------------AES 128-bit key
AES 192-bit key
AES 256-bit key
IS_DEFAULT RESTORE_ONLY
---------YES
NO
NO
-----------NO
NO
NO
Carl Dudley University of Wolverhampton, UK
42
Backup Encryption Modes
1. Transparent encryption (default and preferred method)
— Requires wallet configuration
— Entire data in backups is transparently encrypted and decrypted
— Resetting current master key should not affect decryption on restore
• Best suited for normal operations when backups are restored to
same database
 Encrypt all backups (can be done for a specific tablespace)
RMAN> CONFIGURE ENCRYPTION FOR DATABASE ON|OFF;
 Encrypt a specific backup
RMAN> SET ENCRYPTION ON|OFF;
RMAN> SET ENCRYPTION <tablespace_name> ON|OFF;
RMAN> SET ENCRYPTION ON|OFF FOR ALL TABLESPACES;
Carl Dudley University of Wolverhampton, UK
43
Backup Encryption Modes
2. Password encryption
RMAN> SET ENCRYPTION IDENTIFIED BY pwd ONLY ON FOR ALL TABLESPACES;
—
—
—
—
Loss of password renders backup useless
Encryption wallet not required
Cannot be CONFIGUREd within RMAN
Best for remote restores – password protected during transmission
3. Dual Mode
RMAN> SET ENCRYPTION IDENTIFIED BY pwd ON FOR ALL TABLESPACES;
— Either encryption wallet or password can be used to decrypt on restore
— Best for onsite restores with occasional remote restores
 Encrypted status shown in v$backup_piece
Carl Dudley University of Wolverhampton, UK
44
Restoring Encrypted Backups
RMAN> restore tablespace tiny;
Starting restore at 01-DEC-08
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00006 to C:\TINY.DBF
channel ORA_DISK_1: reading from backup piece C:\ORACLE\FLASH_RECOVERY_AREA\ORCL
\BACKUPSET\2008_12_01\O1_MF_NNNDF_TAG20081201T134746_1RXZR3CR_.BKP
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 12/01/2008 13:58:07
ORA-19870: error reading backup piece C:\ORACLE\FLASH_RECOVERY_AREA\ORCL\BACKUPS
ET\2008_12_01\O1_MF_NNNDF_TAG20081201T134746_1RXZR3CR_.BKP
ORA-19913: unable to decrypt backup
Carl Dudley University of Wolverhampton, UK
45
Backup Option Behaviour
Compression and Encryption
Application
data
Backup with
compression
Backup with
encryption
Backup with
compression and
encryption
Not encrypted
Data compressed
Data encrypted
Data compressed first,
then encrypted
Encrypted with
TDE column
encryption
Data compressed;
encrypted columns are
treated as if they were
not encrypted
Data encrypted;
double encryption of
encrypted columns
Data compressed, then
encrypted. Encrypted
columns treated as if not
encrypted; double
encryption of encrypted
columns
Encrypted with
TDE tablespace
encryption
Encrypted tablespaces
decrypted, compressed,
and re-encrypted
Encrypted
tablespaces passed
through to backup
unchanged
Encrypted tablespaces
decrypted, compressed,
and re-encrypted
Carl Dudley University of Wolverhampton, UK
46
Oracle 11g Transparent Data Encryption
TDE Architecture and Usage
Transparent Column Encryption
Transparent Tablespace Encryption
Encrypted Backups
Encryption Outside of the Database
Master Key Maintenance
Performance Tests
Carl Dudley University of Wolverhampton, UK
47
Exports of Encrypted Column Data
C:\>EXPDP SCOTT/TIGER DUMPFILE = ENC.DMP DIRECTORY = DP TABLES = EMPT
Export: Release 10.2.0.1.0 - Production on Sunday, 13 November, 2005 19:21:04
Copyright (c) 2003, 2005, Oracle.
All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "SCOTT"."SYS_EXPORT_TABLE_01": SCOTT/******** DUMPFILE = ENC.DMP DIREC
TORY = DP TABLES = EMPT
Estimate in progress using BLOCKS method...
Only Data Pump can
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
export encrypted data
Total estimation using BLOCKS method: 992 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."EMPT"
338.4 KB
7168 rows
ORA-39173: Encrypted data has been stored unencrypted in dump file set.
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
C:\ENC.DMP
Job "SCOTT"."SYS_EXPORT_TABLE_01" completed with 1 error(s) at 19:21:41
Carl Dudley University of Wolverhampton, UK
48
Encrypted Column Exports
C:\>EXPDP SCOTT/TIGER ENCRYPTION_PASSWORD = QQQQ DUMPFILE = ENCPASS.DMP DIRECTORY =
DP TABLES = EMPT
Export: Release 10.2.0.1.0 - Production on Sunday, 13 November, 2005 19:28:23
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "SCOTT"."SYS_EXPORT_TABLE_01": SCOTT/******** DUMPFILE = ENCPASS.DMP
DIRECTORY = DP TABLES = EMPT ENCRYPTION_PASSWORD = *********
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 992 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."EMPT"
970.4 KB
7168 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is: C:\ENCPASS.DMP
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at 19:28:54
 Only the columns already encrypted by TDE will be encrypted
— Place the password in a PARFILE to avoid display
— Backup the wallet file on every export
Carl Dudley University of Wolverhampton, UK
49
Importing Encrypted Column Data
C:\>IMPDP SCOTT/TIGER DUMPFILE = ENCPASS.DMP DIRECTORY = DP TABLES = EMPT
Import: Release 10.2.0.1.0 - Production on Sunday, 13 November, 2005 19:36:02
Copyright (c) 2003, 2005, Oracle.
All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 –
Production
With the Partitioning, OLAP and Data Mining options
ORA-39002: invalid operation
ORA-39174: Encryption password must be supplied.
Carl Dudley University of Wolverhampton, UK
50
Full Data Pump Encrypted Exports
 Requires COMPATIBLE = 11.1
 Set with ENCRYPTION parameter
ENCRYPTION value
Action
ENCRYPTED_COLUMNS_ONLY
10g behaviour
METADATA_ONLY
Only the metadata is encrypted
DATA_ONLY
All except Securefile columns
ALL
All including Securefile columns
NONE
No encryption performed
 If ENCRYPTION not specified there will be no encryption
— Unless ENCRYPTION_PASSWORD is specified which causes behaviour of
ENCRYPTION=ALL
Carl Dudley University of Wolverhampton, UK
51
Full Data Pump Encrypted Exports (continued)
 ENCRYPTION_MODE
— Can be DUAL, TRANSPARENT, PASSWORD as for RMAN
— TRANSPARENT prevents setting of ENCRYPTION_PASSWORD
 ENCRYPTION_ALGORITHM
— Can be AES128, AES2192, AES256
• Can be specified only if ENCRYPTION or ENCRYPTION_PASSWORD is set
 Tablespaces can be transported only if endianness is same on target
— Wallet must be copied to target
— Not possible if target already has wallet
Carl Dudley University of Wolverhampton, UK
52
Encryption in the Redo Logs
 Encrypted data remains encrypted in the redo logs
— LogMiner reports the values as ‘Unsupported Type’
SQL_REDO
--------------------------------------------------INSERT INTO emp VALUES(1234,'COX',Unsupported type,
7902,'01-JAN-2002',2500,500,30);
SQL_REDO
--------------------------------------------------------update "SCOTT"."ENCEMP" set "ENC_COL" = Unsupported Type,
"NOENC_COL" = 'DDDD' where "ENC_COL" = Unsupported Type
and "NOENC_COL" = 'CCCC' and ROWID = 'AAAOibAAEAAAIsvABe';
Carl Dudley University of Wolverhampton, UK
53
Encrypting External Tables
 The column encryption password is optional
— Must be used if the table needs to be moved
CREATE TABLE emp_ext (
empno
,ename ENCRYPT IDENTIFIED BY "A£4%77"
,job)
ORGANIZATION EXTERNAL
(TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY "DP"
LOCATION('emp_ext.dat'))
REJECT LIMIT UNLIMITED
AS SELECT empno,ename,job FROM scott.emp;
— Column password is used when creating a table based on the external data
Carl Dudley University of Wolverhampton, UK
54
Passwords for External Tables
 External table created from the encrypted file with incorrect or no password
CREATE TABLE emp_ext_new(
empno NUMBER,
ename VARCHAR2(20) [ENCRYPT IDENTIFIED BY “bad_pwd“],
job VARCHAR2(20))
ORGANIZATION EXTERNAL
(TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY "DP"
LOCATION('emp_ext.dat'))
REJECT LIMIT UNLIMITED
 Selecting ANY of the data causes this error
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-11010: unable to open at least one dump file for fetch
Carl Dudley University of Wolverhampton, UK
55
Use of External Table Encryption Password
 Use of the correct password allows access to the external table
— Even if the Master key has been changed
CREATE TABLE emp_pass(
empno NUMBER,
ename VARCHAR2(20) ENCRYPT IDENTIFIED BY "A£4%77",
job VARCHAR2(20))
ORGANIZATION EXTERNAL
(TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY "DP"
LOCATION('emp_ext.dat'))
REJECT LIMIT UNLIMITED
SELECT * FROM emp_pass;
EMPNO
----7369
7499
7521
:
ENAME
-----SMITH
ALLEN
WARD
:
JOB
--------CLERK
SALESMAN
SALESMAN
:
Carl Dudley University of Wolverhampton, UK
56
Moving Encrypted Data
 Column encrypted data is decrypted when it passes through the SQL layer
— If the data goes on the network, it is sent in the clear by default
 Data remains encrypted in Data Guard environments
— Only physical standbys supported in Oracle10g
 Streams unencrypts data during transfer in Oracle11g
— Unsupported type in Oracle10g
 The network encryption solution (ASO) can encrypt all data traveling to
and from a database over SQL*Net
Carl Dudley University of Wolverhampton, UK
57
Oracle 11g Transparent Data Encryption
TDE Architecture and Usage
Transparent Column Encryption
Transparent Tablespace Encryption
Encrypted Backups
Encryption Outside of the Database
Master Key Maintenance
Performance Tests
Carl Dudley University of Wolverhampton, UK
58
Maintaining the Master Key (1)
 Update the master key:
1. Rename the existing wallet file using the OS
— ewallet.p12 must not exist if new master key needs to be generated
• If it exists an error is generated and wallet is closed
2. Generate a new master key by creating a new wallet file
ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY "5678";
— File will have same original default name (ewallet.p12) with a new
password for the wallet
!Renaming or removing ewallet.p12 has no effect on accessibility to data
during a running application
 Prevent any encryption/decryption on a running system
ALTER SYSTEM SET WALLET CLOSE IDENTIFIED BY "5678";
Carl Dudley University of Wolverhampton, UK
59
Maintaining the Master Key (2)
 Previously encrypted data is no longer accessible with a new wallet
— Oracle uses a random number generator to create the master key
 System cannot revert to old wallet unless
1. Current wallet file is renamed (away from ewallet.p12) using the OS
2. The original wallet is restored and then opened using the original password
ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY "1234";
 Store wallet in protected location away from Oracle files
— Less chance of inclusion in Oracle backup
— However, recommended to backup wallet when backing up database
 Changing the master key re-encrypts it and also the table/column keys
— Actual data encryption is unaffected
 PCI-DSS requires/suggests that master key is changed annually
— Auditors may not require (expensive) re-key of tables
PCI-DSS Payment Card Industry Data Security Standard
Carl Dudley University of Wolverhampton, UK
60
Maintaining the Master Key (3)
 Always maintain a backup of the wallet file
 The loss of the wallet renders encrypted data inaccessible
— Building a new wallet with the wallet password in force at the time of the
encryption does not allow access
 Backup the wallet every time the password is changed
— Consider carefully before changing the password
— Could place backup on USB drive and put in safe
— Reflect it on any standby database for smooth role transition
 OWM or orapki can manage wallet
— Both have bug fixes in 10.2.05, 11.1.0.8 and 11.2
Carl Dudley University of Wolverhampton, UK
61
Master Key Features
 Can use an existing PKI Key as the master key
— Provides for Key recovery (Key Escrow)
— The Certificating Authority can keep a copy of the key
 Can use an auto-login wallet in a file called cwallet.sso
— Wallet is automatically opened on startup
...July 24 we learned that encrypted credit card numbers could have been accessed
• No need for Security DBA to be present
in our ecommerce system during the original incident. Although the credit card
• Useful for unattended Data Guard environments
numbers were encrypted, we found that the encryption key was not well protected
— Loss of separation of duties and password protection of wallet
in our application database. Data in our database up through June 19-20 could
— Do not delete the encryption wallet after creating an auto-open wallet
have been affected, representing approximately 4,500 US customers.
• Re-key operations on the master key would fail
We no longer store credit card n...
It’s all about the key!
Carl Dudley University of Wolverhampton, UK
62
The Wallet File
 Oracle 11g Release 1
— Tablespace master key is not changed when master key is rekeyed
• Data in encrypted tablespaces remains accessible across master rekeys
C:\oracle>orapki wallet display -wallet c:\encrypt
Enter wallet password:
Requested Certificates:
Subject:
CN=oracle
User Certificates:
Oracle Secret Store entries:
ORACLE.SECURITY.DB.ENCRYPTION.AUpVvAEkiU/5v/WOgL0O1zAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY
ORACLE.SECURITY.TS.ENCRYPTION.BTTSiSfHcQmKSfrVGIqtPnUCAwAAAAAAAAAAAAAAAAAAAAAAAAAA
Trusted Certificates:
 Oracle11g Release 2 has one master key
— Data in encrypted tablespaces becomes inaccessible across master rekeys
 Changing the wallet password does not change the master key (bugs?)
orapki wallet change_pwd -wallet c:\encrypt\ewallet.p12
-oldpwd CARL7777 -newpwd CARL8888
Carl Dudley University of Wolverhampton, UK
63
Protecting the Wallet Password
 Possible to split the wallet password
 One person knows first half and another the second half
— Method shown in
http://www.oracle.com/technology/deploy/security/databasesecurity/pdf/twp_transparent-dataencryption_bestpractices.pdf
 Oracle Advanced Security Transparent Data Encryption Best Practices
http://www.scribd.com/doc/19211743/Oracle-AdvancedSecuritytransparentdataencryptionbestpractices
 Managing the Wallet
— Bugs : 7582126, 9032843
• Various patches available
Carl Dudley University of Wolverhampton, UK
64
Oracle 11g Transparent Data Encryption
TDE Architecture and Usage
Transparent Column Encryption
Transparent Tablespace Encryption
Encrypted Backups
Encryption Outside of the Database
Master Key Maintenance
Performance Tests
Carl Dudley University of Wolverhampton, UK
65
Performance Tests – Scenario 1
 Create two tables of equal size containing 131072 rows
— A single byte encrypts to 52 bytes
CREATE TABLE noenc (x VARCHAR2(52));
CREATE TABLE enc (y VARCHAR2(1) ENCRYPT SALT);
-- Insert 131,072 identical rows into each table
INSERT INTO enc VALUES ('A');
52 characters
INSERT INTO noenc VALUES
('0123456789012345678901234567890123456789012345678912');
 Both tables occupy 1049 blocks
Carl Dudley University of Wolverhampton, UK
66
Some Query Times
SELECT COUNT(y)
FROM enc;
SELECT COUNT(SUBSTR(y,1,1))
FROM enc;
COUNT(Y)
---------131072
COUNT(SUBSTR(Y,1,1))
-------------------131072
Elapsed: 00:00:01.65
Elapsed: 00:00:01.91
SELECT COUNT(x)
FROM noenc;
SELECT COUNT(SUBSTR(x,1,1))
FROM noenc;
COUNT(X)
---------131072
COUNT(SUBSTR(X,1,1))
-------------------131072
Elapsed: 00:00:00.03
Elapsed: 00:00:00.09
Carl Dudley University of Wolverhampton, UK
67
Trace Results
SELECT COUNT(X) FROM NOENC
call
count
------- ----Parse
1
Execute
1
Fetch
2
------- ----total
4
Rows
------1
131072
cpu
elapsed
disk
query
current
-------- ---------- -------- ---------- ---------0.01
0.01
0
1
0
0.00
0.00
0
0
0
0.07
0.07
0
1067
0
-------- ---------- -------- ---------- ---------0.08
0.08
0
1068
0
rows
-------0
0
1
-------1
Row Source Operation
--------------------------------------------------SORT AGGREGATE (cr=1067 pr=0 pw=0 time=70085 us)
TABLE ACCESS FULL NOENC (cr=1067 pr=0 pw=0 time=655520 us)
***************************************************************************
SELECT COUNT(Y) FROM ENC
call
count
------- ----Parse
1
Execute
1
Fetch
2
------- ----total
4
Rows
------1
131072
cpu
elapsed
disk
query
current
-------- ---------- -------- ---------- ---------0.00
0.00
0
0
0
0.00
0.00
0
0
0
1.69
1.70
0
1067
0
-------- ---------- -------- ---------- ---------1.69
1.70
0
1067
0
rows
-------0
0
1
-------1
Row Source Operation
--------------------------------------------------SORT AGGREGATE (cr=1067 pr=0 pw=0 time=1702022 us)
TABLE ACCESS FULL ENC (cr=1067 pr=0 pw=0 time=524484 us)
Carl Dudley University of Wolverhampton, UK
68
Performance Test – Scenario 2
CREATE TABLE noenc1 (a varchar2(52));
SELECT * FROM noenc1 WHERE a =
'12345***********************************************';
52 characters
1 row selected
CREATE TABLE enc1 (b varchar2(1) ENCRYPT SALT);
SELECT * FROM enc1 WHERE b = '1';
1 row selected
 Both tables have been fully analysed
Carl Dudley University of Wolverhampton, UK
69
Performance Test – Scenario 2 (continued)
SELECT *
FROM NOENC1 WHERE A = '12345***********************************************'
call
count
------- ----Parse
1
Execute
1
Fetch
2
------- ----total
4
Rows
------1
cpu
elapsed
disk
query
current
-------- ---------- -------- ---------- ---------0.01
0.00
0
0
0
0.00
0.00
0
0
0
0.02
0.02
0
1064
0
-------- ---------- -------- ---------- ---------0.03
0.03
0
1064
0
rows
-------0
0
1
-------1
Row Source Operation
--------------------------------------------------TABLE ACCESS FULL NOENC1 (cr=1064 pr=0 pw=0 time=2584 us)
SELECT * FROM ENC1 WHERE B = '1'
call
count
------- ----Parse
1
Execute
1
Fetch
2
------- ----total
4
Rows
------1
cpu
elapsed
disk
query
current
-------- ---------- -------- ---------- ---------0.00
0.00
0
0
0
0.00
0.00
0
0
0
1.69
1.69
0
1068
0
-------- ---------- -------- ---------- ---------1.69
1.70
0
1068
0
rows
-------0
0
1
-------1
Row Source Operation
--------------------------------------------------TABLE ACCESS FULL ENC1 (cr=1068 pr=0 pw=0 time=1685294 us)
Carl Dudley University of Wolverhampton, UK
70
Column vs Tablespace Encryption
Space Usage and Performance
Table
Name
Column
Enc
Tablespace
Enc
Bytes
of data
per row
Storage
(Blocks)
CPU
Elapsed
CPU
Elapsed
n1
NO
NO
1
1664
0.82
1.77
0.03
0.35
n52
NO
NO
52
8192
1.26
7.52
0.05
1.81
etbs1
NO
YES
1
1664
1.81
2.04
0.43
1.04
etbs52 NO
YES
52
8192
2.12
14.35
1.18
2.01
ecol
NO
1
8192
11.53
14.26
5.45
5.73
YES
Insert Times
Scan Times
 Every table has one VARCHAR2 column and 1000000 (1M) rows
 Tablespace encryption incurs no storage overhead
— Encryption is at the block level
— Oracle claim performance overhead of around 5%
Carl Dudley University of Wolverhampton, UK
71
Oracle 11g Transparent Data Encryption
TDE Architecture and Usage
Transparent Column Encryption
Encryption Outside of the Database
Master Key Maintenance
Encrypted Backups
Performance Tests
Ensuring Full Encryption
Carl Dudley University of Wolverhampton, UK
72
Encrypting Existing Column data
 Clear data is ‘lost’ (overwritten)
only when block
defragmentation occurs
— Make sure blocks are cleaned
before audit
Block header
 Encryption mechanism can
cause large scale row migration
encrypted (lengthened) ‘new’ row
clear data
free space containing data in clear
(to be encrypted)
 Workaround:
1. perform column encryption
2. create a new tablespace
3. move ALL objects from the
old to the new tablespace
4. drop the old tablespace from
the database (without
removing the file)
5. use OS to shred the old file
6. remove the file
Carl Dudley University of Wolverhampton, UK
73
Clearing Up after Column Encryption
 Data may become fragmented, re-arranged, copied and moved within a
tablespace
— Leaves 'ghost copies' within datafiles
— Only the most recent 'valid' copy is encrypted
— Older clear-text versions may be left behind in ghost copies
— A hex – edit could show old clear text values until the blocks are overwritten
1. Create a new tablespace using a new data file
2. Encrypt clear text in original tablespace (ALTER TABLE ... ENCRYPT )
3. Move all tables into the new tablespace (ALTER TABLE ... MOVE ... )
4. Drop the original tablespace
— Do not use 'AND DATAFILES'
— Oracle recommends stronger methods for OS level operations, see 5
5. Use 'shred' or other OS commands to delete the old data file
— Reduces chance of finding ghost copies of the datafile, generated by either
the OS, or storage firmware
Carl Dudley University of Wolverhampton, UK
74
What TDE does not do
 Backup and recovery of wallet
 Manage the wallet password policies
 Ghost data management
 No access control
 No synchronisation of wallets across databases
Carl Dudley University of Wolverhampton, UK
75
Oracle11g Transparent Data Encryption
Carl Dudley
University of Wolverhampton, UK
UKOUG Director
Oracle ACE Director
[email protected]
Data Masking
 Majority of organisations use customer production data for testing
— Often over 10M records
— Around ¼ use credit card numbers
 Common practice to transfer live data to third parties
 Live data used as test may be lost
— Often no way of knowing due to lack of measures
— Non-production environments more susceptible to breaches
— Breaches must be disclosed - $240 per record
 Tension between requirements of test situations and data privacy (laws)
Carl Dudley University of Wolverhampton, UK
77
De – Identifying Information
 Irreversible process
— Options available to re-construct
 Data is scrambled but still appears realistic
 RI is maintained
— Implicitly – database enforced
— Explicitly – application enforced (correlated columns)
 Uses masking formats and templates
 Pre-built format libraries
— Randomising facilities
— Shuffles
— Substrings
— User-defined functions
Carl Dudley University of Wolverhampton, UK
78
Pre-Masking Validation
 Formats match column datatypes
 Selective masking
— SSNO, NINO
— Credit card numbers
• 4929......
• 3773......
 Maintenance of uniqueness
 Checks for available space
 Take care with partitioned tables
— Could suffer row migration
 Random masking of six columns in 100M row table
— 80 minutes
Carl Dudley University of Wolverhampton, UK
79
Data Masking Workflow
 Clone target and work off it
 Generate test directly from target
 Construct test data from scratch
Carl Dudley University of Wolverhampton, UK
80
Column masking
Differential column masking
Carl Dudley University of Wolverhampton, UK
81
Masking Definitions
Note performance related criteria
Carl Dudley University of Wolverhampton, UK
82
New Features
 Support for e-Business suite structures
 Heterogeneous database support
 Command line instructions
Carl Dudley University of Wolverhampton, UK
83
Reference Material for Oracle Data Masking
 Tutorial
http://www.oracle.com/technology/obe/11gr1_db/security/datamask/datam
ask.htm#o
 Encryption versus data masking video
http://www.youtube.com/watch?v=ZDKJysfyGUs
 Slide Show
http://www.slideshare.net/oracleil/data-masking-using-enterprise-manager4262943
 Oracle Data Masking Demo
http://download.oracle.com/technology/products/oem/screenwatches/data
_masking/index.html
Carl Dudley University of Wolverhampton, UK
84