15 Database Security Copyright 2006 Oracle All

Objectives TDE DP RMAN VPD After completing this lesson, you should be able to do the following: • Implement Transparent Data Encryption (TDE) • Use TDE with encrypted columns • Describe Data Pump (DP) encryption • Identify components of Recovery Manager (RMAN) –encrypted backups • Define basic concepts of a Virtual Private Database (VPD) • Apply a column-level VPD policy 15 -2 Copyright © 2006, Oracle. All rights reserved.

Oracle Transparent Data Encryption (TDE): Overview • • Need for secure information Automatic encryption of sensitive information: – Embedded in the Oracle database – No need to change application logic – Encrypts data and index values • Using an encryption key: – Master key for the entire database – Stored in Oracle Wallet Encryption/Decryption Column and index data 15 -3 Copyright © 2006, Oracle. All rights reserved. Wallet

TDE Process Name Sal Card JFV 10000 20000 10000 30000 20000 40000 Address A 0023 Rognes B 1524 C 2568 D 1483 E 0732 F 3456 Clear data Wallet Name Sal JFV Master key Column keys External Security Module É&à{ ])°=# &(è`$ @”#|} µ£*°{ ~{([ç^ 15 -5 Copyright © 2006, Oracle. All rights reserved. Address +”~é[ Rognes §!? &} }{|ç{ #{[|è` }|_@} “&²#è Encrypted data SELECT|INSERT|UPDATE| CREATE TABLE ALTER TABLE Card

Implementing Transparent Data Encryption 1. Create a wallet: automatically or by using Oracle Wallet Manager. Example sqlnet. ora entry: ENCRYPTION_WALLET_LOCATION= (SOURCE=(METHOD=FILE)(METHOD_DATA= (DIRECTORY=/opt/oracle/product/10. 2. 0/db_1/))) 15 -6 Copyright © 2006, Oracle. All rights reserved.

Implementing Transparent Data Encryption 2. Set the master key from within your instance: ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY <password>; 3. Open the wallet from within your instance (future): ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY <password>; 4. Create tables that contain encrypted columns: CREATE TABLE emp ( first_name VARCHAR 2(128), last_name VARCHAR 2(128), emp. ID NUMBER ENCRYPT NO SALT, salary NUMBER(6) ENCRYPT USING '3 DES 168', comm NUMBER(6) ENCRYPT ); 15 -7 Copyright © 2006, Oracle. All rights reserved.

Existing Tables and TDE • Add encrypted columns: ALTER TABLE emp ADD (ssn VARCHAR 2(11) ENCRYPT); • Encrypt unencrypted columns: ALTER TABLE emp MODIFY (first_name ENCRYPT); • Disable column encryption: ALTER TABLE emp MODIFY (first_name DECRYPT); • Add or remove salt: ALTER TABLE emp MODIFY (first_name ENCRYPT [NO] SALT); • Change keys and the encryption algorithm: ALTER TABLE emp REKEY USING '3 DES 168'; 15 -9 Copyright © 2006, Oracle. All rights reserved.

Transparent Data Encryption: Considerations • • 15 -10 You cannot encrypt tables owned by SYS. LONG and LOB data types are not supported. The supported encryption algorithms are: – – 3 DES 168 AES 128 AES 192 AES 256 NO SALT must be used to encrypt index columns. TDE works with indexes for equality searches. Encrypted data must be decrypted before expressions evaluation. Best practice tip: Back up the wallet. Copyright © 2006, Oracle. All rights reserved.

Wallet Support for Usernames and Passwords • Wallets can now hold more than just a certificate: – You can store usernames and passwords in a wallet rather than providing them on the command line. • Batch job processing: – Protects exposure of usernames and passwords when listing processes on the OS • Set up using: – WALLET_LOCATION in sqlnet. ora – mkstore utility connect /@db_connect_string 15 -11 Copyright © 2006, Oracle. All rights reserved.

Data Pump and Transparent Data Encryption • TDE > DP RMAN VPD Use your own provided column key during export and import: ENCRYPTION_PASSWORD = <password> • Also true for external tables: CREATE TABLE emp_ext ( first_name, last_name, emp. ID, salary ENCRYPT IDENTIFIED BY "x. Icf 3 T 9 u" ) ORGANIZATION EXTERNAL ( TYPE ORACLE_DATAPUMP DEFAULT DIRECTORY "D_DIR" LOCATION('emp_ext. dat') ) REJECT LIMIT UNLIMITED as select * from employees; 15 -12 Copyright © 2006, Oracle. All rights reserved.

RMAN Encrypted Backups: Overview TDE DP > RMAN VPD Three possible encryption modes for your backups: • Transparent mode: – Requires Oracle Wallet – Is best suited for day-to-day backup and restore operations at the same location – Is the default encryption mode • Password mode: – Requires you to provide a password – Is best suited for backups restored at remote locations • Dual mode: – Can use either Oracle Wallets or passwords – Is best suited for backups restored locally and remotely 15 -13 Copyright © 2006, Oracle. All rights reserved.

Transparent Mode Setup 1. Create a wallet: automatically or by using Oracle Wallet Manager. 2. Open the wallet from within your instance: ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY <password>; 3. Set the master key from within your instance: ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY <password>; 4. Configure RMAN to use transparent encryption: CONFIGURE ENCRYPTION FOR DATABASE ON 5. There are no changes to your backup or recover commands. 6. Permanent configuration can be temporarily overwritten: SET ENCRYPTION OFF 15 -14 Copyright © 2006, Oracle. All rights reserved.

Password Mode Setup 1. Set your RMAN session to use password encryption: SET ENCRYPTION ON IDENTIFIED BY password ONLY 2. There are no changes to your backup commands. 3. Set your RMAN session to decrypt passwordencrypted backups: SET DECRYPTION IDENTIFIED BY password 1 {, password 2, …, passwordn} 4. There are no changes to your recover commands. 15 -15 Copyright © 2006, Oracle. All rights reserved.

Dual Mode Setup 1. Create a wallet: automatically or by using Oracle Wallet Manager. 2. Open the wallet from within your instance: ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY <password>; 3. Set your RMAN session to use dual encryption: SET ENCRYPTION ON IDENTIFIED BY password 4. There are no changes to your backup commands. 5. If necessary, set your RMAN session to decrypt your backups by using the password: SET DECRYPTION IDENTIFIED BY password 1 {, password 2, …, passwordn} 6. There are no changes to your recover commands. 15 -16 Copyright © 2006, Oracle. All rights reserved.

RMAN-Encrypted Backups: Considerations • • • Image copy backups cannot be encrypted. COMPATIBLE must be set to at least 10. 2. 0. V$RMAN_ENCRYPTION_ALGORITHMS contains the list of possible encryption algorithms. CONFIGURE ENCRYPTION ALGORITHM 'algorithmname' SET ENCRYPTION ALGORITHM 'algorithmname' • • 15 -17 Backup encryption is available only with Oracle Database Enterprise Edition. One new encryption key is used for each new encrypted backup. You can increase disk performance by using multiple channels. You can change the master key anytime without affecting your transparent encrypted backups. Copyright © 2006, Oracle. All rights reserved.

Need for Data Privacy TDE DP RMAN > VPD Examples: • Employees: Protect salary and commission percent (used in the remainder of this lesson) • Online banking: Protect access to accounts • Web store: Supply individual shopping baskets • Web host: Allow each customer to see only their own data • Used in Oracle Sales. Online. com and Oracle Portal 15 -18 Copyright © 2006, Oracle. All rights reserved.

Definition and Usage of Terms • • • 15 -19 Fine-grained access control (FGAC): Use of functions Application context: To preserve user identity and serve as a secure data cache for application attributes and values Application attributes: Used by fine-grained access policies Copyright © 2006, Oracle. All rights reserved.

Virtual Private Database: Overview • Virtual Private Database (VPD) consists of: – Fine-grained access control (FGAC) – Secure application context • • • 15 -20 VPD uses policies to add conditions to SQL statements that protect sensitive data. VPD provides row-level access control. Application attributes defined inside an application context are used by fine-grained access policies. Copyright © 2006, Oracle. All rights reserved.

Virtual Private Database: Features • • • 15 -21 Column-level VPD enforces row-level access control based on accessed security columns. With customization, you can define static and nonstatic policies. Using shared policies, you can associate one policy with multiple objects. Policy type can be INDEX. Policy predicate text string can be of size 32 KB. Copyright © 2006, Oracle. All rights reserved.

Column-Level VPD: Example • • Statements are not always rewritten. Consider a policy protecting the SALARY and COMMISSION_PCT columns of the EMPLOYEES table. Fine-grained access control is: – Not needed for this query: SQL> SELECT last_name FROM employees; – Enforced for these queries: SQL> SELECT last_name, salary 2 FROM employees; SQL> SELECT * FROM employees; 15 -22 Copyright © 2006, Oracle. All rights reserved.

Creating a Column-Level Policy 1. Grant the privilege. 2. Create the function. 3. Apply the policy to the object. BEGIN dbms_rls. add_policy(object_schema => 'hr', object_name => 'employees', policy_name => 'hr_policy', function_schema =>'hr', policy_function => 'hrsec', statement_types =>'select, insert', sec_relevant_cols=>'salary, commission_pct'); END; / 15 -23 Copyright © 2006, Oracle. All rights reserved.

Summary In this lesson, you should have learned how to: • Implement Transparent Data Encryption • Use TDE with encrypted columns • Describe Data Pump encryption • Identify components of RMAN-encrypted backups • Define basic concepts of a Virtual Private Database • Apply a column-level VPD policy 15 -24 Copyright © 2006, Oracle. All rights reserved.

Practice Overview: Using Oracle Database Security This practice covers the following topics: • Implementing TDE by creating an encrypted wallet and encryption keys • Using TDE with encrypted columns 15 -25 Copyright © 2006, Oracle. All rights reserved.