1 / 75

Transparent Data Encryption Explained

Transparent Data Encryption Explained. By: Cheryl Lei Bryll, OCP Senior Oracle DBA Mobile: 214-733-3869 cbryll@onshoresolutions.net http://www.onshoresolutions.ne t. Transparent Data Encryption. Why there is a need for Transparent Data Encryption

Download Presentation

Transparent Data Encryption Explained

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. Transparent Data Encryption Explained

  2. By: Cheryl Lei Bryll, OCP Senior Oracle DBA Mobile: 214-733-3869 cbryll@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 don’t exist? Think again …

  7. 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

  8. 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)

  9. 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

  10. 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

  11. Oracle Transparent Data Encryption addresses data protection and privacy standards such as PCI DSS

  12. 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

  13. Transparent Data Encryption addresses strong encryption The need for stronger data security standards with strong encryption is a growing concern …

  14. 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

  15. 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

  16. 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

  17. 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

  18. 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

  19. How to use Transparent Data Encryption • Oracle 10g column level encryption • Oracle 11g tablespace level encryption • Key management • Backups & Exports • Replication • Troubleshooting

  20. 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)

  21. 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

  22. Steps for using column-level Transparent Data Encryption: • Set compatibility parameter • Compatibility level of 10.2 or higher

  23. 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

  24. 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 server’s master encryption key

  25. 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 ‘<AES192>‘ • 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;

  26. Demo –10g Column level

  27. Demo –10g Column level

  28. Demo –10g Column level car Card_num ------------------- X!wp#!9r4@ee car Card_num -------------------715088606525

  29. 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

  30. 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

  31. 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); 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 COLUMN_NAME SALT --------------- --------------- --------- TDE_TEST DATA YES TDE_DOCTOR DOC_FIRST YES TDE_DOCTOR DOC_LAST YES TDE_TEST_2 DATA YES TDE_PRIVATE INFO YES CUST_INFO SSN YES CUST_INFO DOB YES CUST_INFO CUST_LAST NO

  32. 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 “<password>“;

  33. How to prove encryption is working? SQL> conncust_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.

  34. 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

  35. 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

  36. 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 !

  37. 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

  38. 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);

  39. Demo –11g tablespace encryption

  40. Demo –11g tablespace encryption

  41. Demo –11g tablespace encryption

  42. Steps for SecureFileLOBsTransparent 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';

  43. Demo –Lobs

  44. 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

  45. 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.

  46. 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

  47. Transparent Data Encryption –Key Management • Two-tier key architecture • Resetting keys • Backup and recovery of keys • Autologin • External security module • Hardware security module • Wallet

  48. 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

More Related