-- Data such as PII and similar medical historic details that are stored in -- a database has to be encrypted. The encryption is implemented in Oracle -- by Transparent Data Encryption (TDE). The TDE has "key" management -- features that provides transparency for encrypting secure data. -- The encryption is implemented by DDL commands that avoid application -- code changes. Also programatic encryption management can also be -- performed. -- New Feature in 11g -- 1. Transparent encryption of tablespaces -- 2. Transparent encryption of secure files/large objects (RAW, CLOB, BLOB) -- 3. TDE with Hardware Security Module (HSM) integration -- Initialization/resetting of master key -- This creates a wallet and encrypts it using the password based on -- PKCS#5. The wallet keeps a history of encryption keys so that encrypted -- data can be read from backup that could be using an older key. -- Wallet location to be specified in init.ora file (sameple shown below) ENCRYPTION_WALLET_LOCATION = (SOURCE= (METHOD=file) (METHOD_DATA=(DIRECTORY=ORACLE_HOME\orawallet) ) ) WALLET_LOCATION = (SOURCE= (METHOD=file) (METHOD_DATA=(DIRECTORY=c:\oracle\product\11.2.0\dbhome_1\orawallet) ) ) ALTER SYSTEM SET KEY IDENTIFIED BY "some_password"; ALTER SYSTEM SET wallet OPEN IDENTIFIED BY "some_password"; ALTER SYSTEM SET encryption wallet CLOSE IDENTIFIED BY "some_password"; -- Encrypting an existing column using TDE can be done as shown below ALTER TABLE sales_transactions MODIFY (tax_id ENCRYPT); -- The key (primary/foreign) columns cannot be encrypted using TDE. -- Creating a table with encrypted columns using AES CREATE TABLE medical_transactions (transaction_id NUMBER(16) NOT NULL, tranaction_date DATE NOT NULL, provider_tax_id VARCHAR2(20) ENCRYPT USING 'AES256', customer_id VARCHAR2(20) ENCRYPT USING 'AES256', icd_code VARCHAR2(30), hcpcs_code VARCHAR2(10), transaction_desc VARCHAR2(250), created_date DATE DEFAULT SYSDATE NOT NULL, updated_date DATE ); -- Encrypted Tablespace Details SELECT ts.name, es.encryptedts, es.encryptionalg FROM v$tablespace ts INNER JOIN v$encrypted_tablespaces es ON es.ts# = ts.ts#;
17660