-- 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#;