1 / 22

Oracle TDE -11gR2

Oracle TDE -11gR2. Objectives. What and Why to Encrypt How to Encrypt Data Encryption Storage Level Salt Network Level Integrity Check Oracle and Cryptography Transparent Data Encryption TDE Column Encryption Configuring TDE Creation of tables with encrypted columns

amroz
Download Presentation

Oracle TDE -11gR2

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. Oracle TDE -11gR2

  2. Objectives • What and Why to Encrypt • How to Encrypt • Data Encryption • Storage Level • Salt • Network Level • Integrity Check • Oracle and Cryptography • Transparent Data Encryption • TDE Column Encryption • Configuring TDE • Creation of tables with encrypted columns • Comparison of table sizes • Indexing of encrypted columns • TDE Tablespace Encryption • Creation of encrypted tablespaces • Export/Import of tables with encrypted columns • Re-Keying of encryption keys • TDE and Standby DBs • TDE and RMAN • Backup of Encryption Wallet • TDE Data Dictionary Views John Jacob http://boomslaang.wordpress.com

  3. 1)What and Why to Encrypt • Credit Card Numbers • Contact Details • Business Details (like bidding rate etc) • Military Data • Or, anything you think is a secret • 2)How to Encrypt • Using Algorithms • Mr.Tom sends a message to Miss.Jerry • "let us go for a movie" =>Plain Text • TIGER =>Key • "uovtfoeomsrilgae" =>cipher text (or encrypted data) • This is called Columnar Transposition Cipher. John Jacob http://boomslaang.wordpress.com

  4. 3)Data Encryption Encryption at Storage level: Confidential data is encrypted using Algorithms and stored The algorithms widely used are :RC4 -Uses the modules from RSA Inc. :DES -Data Encryption Standard :3DES -Triple DES :AES -Advanced Encryption Standard Encryption over the network: The above algorithms make sure that your data is encrypted on the disk. What if someone modifies the data packet before it reaches the client or server. Data Integrity algorithms are used to ensure the integrity of the data. The algorithms widely used are :MD5 -Message Digest 5 :SHA-1 hashing algorithms Both these algorithms create checksums that would change if the data is altered. John Jacob http://boomslaang.wordpress.com

  5. 4)What is a SALT SALT is a random string appended to plain text to disguise cryptanalysts. If salt is not used, then the cipher text of similar plain text would remain the same for a KEY. 5)What is Integrity Check Integrity check is necessary to thwart Data Modification attack and Data Replay Attack. Algorithms create checksums that would change if the data is altered John Jacob http://boomslaang.wordpress.com

  6. 6) Oracle and Cryptography Oracle supports storage level encryption since 8i. 1)DBMS_OBFUSCATION_TOOLKIT -8i The application should be re-written to include this. The key is hard coded in the code and is accessible to developers/dbas. 2)DBMS_CRYPTO -10gR1 Supports more algorithms than dbms_obsfucation_toolkit. The application should be re-written to include this. The key is hard coded in the code and is accessible to developers/dbas. 3)Transparent Data Encryption -10gR2 John Jacob http://boomslaang.wordpress.com

  7. 7) TDE- Transparent data encryption • Data is safe even if media is stolen • Need not modify the application • Data is encrypted transparently without the knowledge of the application and users • Key management is secure • 5-8% overhead in CPU consumption • Two Types: • --]TDE Column Encryption -10gR2 • --]TDE Tablespace Encryption -11gR1 • Table/Tablespace Keys: • Used to encrypt the table columns/tablespace. • Stored inside the data dictionary/datafile headers respectively; but encrypted using the Master Key. • Master Key: • Master key is used to encrypt the table keys and tablespace keys. • Stored inside the wallet. • Wallet : • A wallet is a password-protected container that is used to store authentication. • Resides at the OS level. John Jacob http://boomslaang.wordpress.com

  8. 7.1)TDE Column Encryption • Used to protect confidential data in table columns. • Master Key, Table Key and Wallet Password • The data is encrypted/decrypted using Table Key and this table key is again encrypted using Master Key. • The table key is unique for a table (means, all columns in a table will use the same table key) and is stored in the data dictionary. • The master key is stored in Oracle Wallet • Encryption happens at SQL Layer John Jacob http://boomslaang.wordpress.com to be continued…….

  9. 7.1 continued….. The algorithms supported are 3DES168, AES128, AES192(default) and AES256 Storage Overhead of max 52 bytes: 16 bytes of SALT 20 bytes for Integrity Check Encrypted value is padded to multiple of 16bytes The below data types can be encrypted using column encryption provided the size after encryption doesn't exceed the maximum allowable size for each data type BINARY_DOUBLE BINARY_FLOAT CHAR DATE INTERVAL DAY TO SECOND INTERVAL YEAR TO MONTH LOBs (Internal LOBs and SECUREFILE LOBs Only) NCHAR NUMBER NVARCHAR2 RAW TIMESTAMP (includes TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE) VARCHAR2 John Jacob http://boomslaang.wordpress.com

  10. 7.1 continued….. Restrictions for TDE Column Encryption: TDE column encryption encrypts and decrypts data at the SQL layer. Oracle Database utilities and features that bypass the SQL layer cannot leverage the services provided by TDE column encryption. Do not use TDE column encryption with the following database features: Index types other than B-tree Range scan search through an index External large objects (BFILE) Synchronous Change Data Capture Transportable Tablespaces Original import/export utilities In addition, you cannot use TDE column encryption to encrypt columns used in foreign key constraints. John Jacob http://boomslaang.wordpress.com

  11. 7.1.1)Configuring TDE 1)Create the wallet directory and edit the sqlnet.ora file with below Default path= ORACLE_BASE/admin/DB_UNIQUE_NAME/wallet/ ENCRYPTION_WALLET_LOCATION= (SOURCE=(METHOD=FILE)(METHOD_DATA= (DIRECTORY=/u01/app/oracle/admin/insys/wallet))) 2)Create master encryption key ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY "Spad3JacK"; The wallet ewallet.p12 will be created and the generatedmaster key and would be stored inside this. [oracle@localhost wallet]$ ls -ltr /u01/app/oracle/admin/insys/wallet total 4 -rw-r--r-- 1 oracle dba 1573 Oct 11 23:14 ewallet.p12 -rw-r--r-- 1 oracle dba 1521 Oct 11 23:47 cwallet.sso [oracle@localhost wallet]$ After creation of the master key, the wallet will be open by default. select * from v$encryption_wallet; WRL_TYPE WRL_PARAMETER STATUS -------------------- -------------------------------------------------- ------- file /u01/app/oracle/admin/insys/wallet OPEN The wallet should be manually opened each time the server is bounced. ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "Spad3JacK"; 3)Auto-Open Wallet Wallet can be set to open automatically using auto open wallet. $orapki wallet create –wallet <wallet_location> -auto_login John Jacob http://boomslaang.wordpress.com

  12. 7.1.2)Create tables with encrypted columns CREATE TABLE cust_with_salt_integrity (first_name VARCHAR2(20), last_name VARCHAR2(20), order_number NUMBER(16), credit_card_number VARCHAR2(16) ENCRYPT, active_card VARCHAR2(3)); CREATE TABLE cust_without_salt (first_name VARCHAR2(20), last_name VARCHAR2(20), order_number NUMBER(16), credit_card_number VARCHAR2(16) ENCRYPT NO SALT, active_card VARCHAR2(3)); CREATE TABLE cust_without_salt_integrity (first_name VARCHAR2(20), last_name VARCHAR2(20), order_number NUMBER(16), credit_card_number VARCHAR2(16) ENCRYPT NO SALT 'NOMAC', active_card VARCHAR2(3)); JOHNJ@insys > select * from user_encrypted_columns; TABLE_NAME COLUMN_NAME ENCRYPTION_ALG SAL INTEGRITY_AL ------------------------------ ------------------------------ ----------------------------- --- ------------ CUST_WITHOUT_SALT CREDIT_CARD_NUMBER AES 192 bits key NO SHA-1 CUST_WITHOUT_SALT_INTEGRITY CREDIT_CARD_NUMBER AES 192 bits key NO NOMAC CUST_WITH_SALT_INTEGRITY CREDIT_CARD_NUMBER AES 192 bits key YES SHA-1 John Jacob http://boomslaang.wordpress.com

  13. 7.1.3)Comparison of encrypted table’s storage size 7.1.4)Altering Tables ALTER TABLE table_name ADD (column_name datatype ENCRYPT); ALTER TABLE table_name MODIFY (column_name ENCRYPT); 7.1.5)Indexing of encrypted columns Only B-Tree indexing is allowed on encrypted columns provided SALT is not used. 1)create index credit_card_no_idx on cust_with_salt_integrity(credit_card_number); JOHNJ@insys > create index credit_card_no_idx on cust_with_salt_integrity(credit_card_number); create index credit_card_no_idx on cust_with_salt_integrity(credit_card_number) * ERROR at line 1: ORA-28338: Column(s) cannot be both indexed and encrypted with salt 2)create index credit_card_no_idx on cust_without_salt(credit_card_number); JOHNJ@insys > create index credit_card_no_idx on cust_without_salt(credit_card_number); Index created. John Jacob http://boomslaang.wordpress.com

  14. 7.2)TDE Tablespace Encryption • Used to encrypt an entire tablespace • All objects are automatically encrypted • The datafiles associated with the tablespace is encrypted using using Tablespace Key and this key is again encrypted using Master Key. • The tablespace key is stored in the Tablespace Header. John Jacob http://boomslaang.wordpress.com

  15. 7.2 continued….. • Cannot encrypt an existing tablespace, but can move table using impdp or CTAS or move to a encrypted tablespace • Has no storage overhead • TDE tablespace encryption encrypts/decrypts data during read/write operations, as opposed to TDE column encryption, which encrypts/decrypts data at the SQL layer. This means that most restrictions that apply to TDE column encryption, such as data type restrictions and index type restrictions, are not applicable to TDE tablespace encryption. • The algorithms supported are • 3DES168, • AES128 (default), • AES192 and • AES256 • Restrictions for TDE Tablespace Encryption: • External Large Objects (BFILEs) cannot be encrypted using TDE tablespace encryption. -This is because these files reside outside the database. • Original import/export utilities are not supported. Use the Oracle Data Pump instead. John Jacob http://boomslaang.wordpress.com

  16. 7.2 continued….. CREATE TABLESPACE users_secure DATAFILE '/u02/app/oradata/insys/users_secure01.dbf' SIZE 10M ENCRYPTION DEFAULT STORAGE(ENCRYPT); CREATE TABLESPACE users_secure DATAFILE '/u02/app/oradata/insys/users_secure01.dbf' SIZE 10M ENCRYPTION USING '3DES168' DEFAULT STORAGE(ENCRYPT); CREATE TABLE cust_on_secure_tblspce (first_name VARCHAR2(20), last_name VARCHAR2(20), order_number NUMBER(16), credit_card_number VARCHAR2(16), active_card VARCHAR2(3)) tablespace users_secure; JOHNJ@insys > create index credit_card_no_tblspce_idx on cust_on_secure_tblspce(credit_card_number); Index created. John Jacob http://boomslaang.wordpress.com

  17. 7.3 Exporting encrypted tables Use Oracle Data Pump to export/import tables containing encrypted columns 1) The encryption wallet should be open before export 2) The data is first decrypted and is moved to the dump file expdp directory=DATA_PUMP_DIR tables=JOHNJ.CUST_WITH_SALT_INTEGRITY dumpfile=CUST_WITH_SALT_INTEGRITY.dmp logfile=CUST_WITH_SALT_INTEGRITY.log Once exported the encrypted data is visible if open using a text editor John Jacob http://boomslaang.wordpress.com

  18. 7.3 continued….. Use Data Pump with the ENCRYPTION parameter to encrypt the dump file. expdp directory=DATA_PUMP_DIR tables=JOHNJ.CUST_WITH_SALT_INTEGRITY dumpfile=CUST_WITH_SALT_INTEGRITY_enc.dmp logfile=CUST_WITH_SALT_INTEGRITY_enc.log ENCRYPTION=ALLENCRYPTION_PASSWORD=Kla2Kli John Jacob http://boomslaang.wordpress.com

  19. 7.4 Importing encrypted tables The encryption wallet should be open before the import impdp directory=DATA_PUMP_DIR dumpfile=CUST_WITH_SALT_INTEGRITY_enc.dmp logfile=CUST_WITH_SALT_INTEGRITY_enc_imp.log remap_schema=JOHNJ:TOMJ ENCRYPTION_PASSWORD=Kla2Kli 7.5 Re-Keying the encryption keys Master Key- Re-keying of master key doesn't impact the performance Re-keying of master key decrypts and re-encrypts the table and tablespace keys Table Key- Requires planning It is equivalent to performing a full upgrade Eg:ALTER TABLE CUST_WITHOUT_SALT REKEY; Tablespace Key- Not possible to re-key this. Before any re-keying operation both the original encryption wallet and auto-login wallet(if created) should be available. John Jacob http://boomslaang.wordpress.com

  20. 7.6 TDE and Standby DBs • Copy the encryption wallet from the Primary to the Standby • Re-Copy the encryption wallet to Standby if the master re-key operation is done. • The redo log entries will remain encrypted even during the transit. 7.7 TDE and RMAN • RMAN works seamlessly with TDE encrypted data. John Jacob http://boomslaang.wordpress.com

  21. 7.8 Backup of Encryption Wallet • Periodically backup the wallet. • Backup the wallet immediately if the master key is re-keyed. • Do not backup the wallet along with the encrypted data. • If the wallet is lost data is lost. 7.9 TDE Data Dictionary Views • DBA_ENCRYPTED_COLUMNS • V$ENCRYPTED_TABLESPACES • V$WALLET John Jacob http://boomslaang.wordpress.com

  22. Thank You John Jacob http://boomslaang.wordpress.com

More Related