/*
|| Oracle 10g Security Listing 3
||
|| Demonstrates new Oracle 10gR2 Security features, including:
|| - Setting up Transparent Data Encryption (TDE)
|| - Opening and closing the Oracle Wallet from within SQL*Plus
|| - Adding encryption to columns in existing tables
|| - Verifying that encryption is indeed in place
||
|| Author: Jim Czuprynski
||
|| Usage Notes:
|| This script is provided to demonstrate various features of Oracle 10g
|| security enhancements, and it should be carefully proofread before
|| executing it against any existing Oracle database to insure that no
|| potential damage can occur.
*/

/* 
|| Listing 3.1: Setting up Transparent Data Encryption
*/

-----
-- Add these lines to SQLNET.ORA to enable Oracle 10g to
-- read the encryption data from the Wallet.
-----
WALLET_LOCATION =
    (SOURCE = 
        (METHOD=file)
        (METHOD_DATA=(DIRECTORY=/home/oracle/_dba))
    )

-----
-- First, open the security wallet ...
-----
ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "Check_M8";

-----
-- ... and then set the master encryption key for the database.
-----
ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY "Check_M8";

/* 
|| Listing 3.2: Preparations For TDE Demonstrations
*/

-----
-- Create a new tablespace for tables with encrypted data. This allows
-- browsing of the tablespace's datafile later to verify that the data
-- stored within is truly encrypted.
-----
DROP TABLESPACE tbs_encrypted INCLUDING CONTENTS AND DATAFILES;
CREATE TABLESPACE tbs_encrypted
    DATAFILE '/u01/app/oracle/oradata/orcl/tbs_encrypted01.dbf'
    SIZE 1M REUSE
    EXTENT MANAGEMENT LOCAL
    SEGMENT SPACE MANAGEMENT AUTO;
    
-----
-- Create a new table in this new tablespace
-----

DROP TABLE hr.employee_secured PURGE;
CREATE TABLE hr.employee_secured (
     employee_id        NUMBER(5)   PRIMARY KEY
    ,socsecnbr          VARCHAR2(9)
    ,ethnicity          VARCHAR2(1)
    ,termination_rsn    VARCHAR2(1)
    ,termination_dt     DATE
    ,marital_status     VARCHAR2(1)
    )
    TABLESPACE tbs_encrypted;
        
-----
-- Add some rows to the table
-----

INSERT INTO hr.employee_secured
VALUES(101, '112521234', 'W', NULL, NULL, 'M');
INSERT INTO hr.employee_secured
VALUES(102, '212521234', 'A', 'S', TO_DATE('07/06/2005', 'MM/DD/YYYY'), 'M');
INSERT INTO hr.employee_secured
VALUES(103, '312521234', 'S', NULL, NULL, 'S');
INSERT INTO hr.employee_secured
VALUES(104, '412521234', 'B', NULL, NULL, 'M');
INSERT INTO hr.employee_secured
VALUES(105, '512521234', 'I', 'G', TO_DATE('12/31/2004', 'MM/DD/YYYY'), 'D');

COMMIT;


/* 
|| Listing 3.3: Breaching the Trusty Datafile
*/
-----
-- Browsing an existing datafile for character strings. This is 
-- one method a hacker or unauthorized personnel might use to 
-- view data directly without invoking SQL, thus bypassing standard
-- or fine-grained auditing. To simulate, the tablespace will be 
-- taken offline, and then the Linux STRINGS command will be used 
-- to look for character strings within the tablespace's datafile.
-----

SQL> ALTER TABLESPACE tbs_encrypted OFFLINE;

$> strings /u01/app/oracle/oradata/orcl/tbs_encrypted01.dbf | more

>>> Results of STRINGS command:

}|{z
=@CORCL
TBS_ENCRYPTED
	512521234
	412521234
	312521234
	212521234
	112521234

SQL> ALTER TABLESPACE tbs_encrypted ONLINE;


/* 
|| Listing 3.4: Adding encryption to columns in an existing table
||              with Transparent Data Encryption
*/

-----
-- Establishing data encryption after a table's creation:
-- 1.) Column SOCSECNBR uses the standard salting method for encryption.
-- 2.) No encryption method is specified for columns ETHNICITY and 
--     TERMINATION_RSN, so they will use the default method (SALT).
-- 3.) Column TERMINATION_DATE is encrypted using the lower-security
--     NO SALT method.
-- 4.) Columns EMPLOYEE_ID and MARITAL_STATUS are not encrypted (yet). 
-----

ALTER TABLE hr.employee_secured
    MODIFY (socsecnbr ENCRYPT SALT);
ALTER TABLE hr.employee_secured
    MODIFY (ethnicity ENCRYPT);
ALTER TABLE hr.employee_secured
    MODIFY (termination_rsn ENCRYPT);
ALTER TABLE hr.employee_secured
    MODIFY (termination_dt ENCRYPT NO SALT);

-----
-- Create a new index on AP.VENDORS.CREDIT CARD, and then 
-- then attempt to apply standard encryption on the column. 
-- This will fail because indexed columns cannot be "salted" ...
-----

SQL> CREATE INDEX ap.vendors_ccnbr_idx
  2      ON ap.vendors (credit_card)
  3      TABLESPACE example;

Index created.

SQL> ALTER TABLE ap.vendors
  2      MODIFY (credit_card ENCRYPT);
    MODIFY (credit_card ENCRYPT)
            *
ERROR at line 2:
ORA-28338: cannot encrypt indexed column(s) with salt

-----
-- However, even if the column is indexed, it can be encrypted 
-- without "salting."
-----
SQL> ALTER TABLE ap.vendors
  2      MODIFY (credit_card ENCRYPT NO SALT);

Table altered.

/* 
|| Listing 3.5: Viewing metadata about columns encrypted via
||              Transparent Data Encryption
*/

-----
-- Which columns are currently encrypted in the database?
-----
SET PAGESIZE 50
SET LINESIZE 110
TTITLE 'Currently Encrypted Columns|(From DBA_ENCRYPTED_COLUMNS)'
COL owner               FORMAT A08          HEADING 'Owner'
COL table_name          FORMAT A16          HEADING 'Table Name'
COL column_name         FORMAT A24          HEADING 'Column Name'
COL encryption_alg      FORMAT A32          HEADING 'Encryption|Algorithm'
COL salt                FORMAT A5           HEADING 'Salt?'
SELECT
     owner
    ,table_name
    ,column_name
    ,encryption_alg
    ,salt
  FROM dba_encrypted_columns
 ORDER BY owner, table_name, column_name
;
TTITLE OFF

>>> Encryption status after initial encryption efforts:

                              Currently Encrypted Columns
                              (From DBA_ENCRYPTED_COLUMNS)
 
                                                   Encryption
Owner    Table Name       Column Name              Algorithm                        Salt?
-------- ---------------- ------------------------ -------------------------------- -----
AP       VENDORS          CREDIT_CARD              AES 192 bits key                 NO
HR       EMPLOYEE_SECURED ETHNICITY                AES 192 bits key                 YES
HR       EMPLOYEE_SECURED SOCSECNBR                AES 192 bits key                 YES
HR       EMPLOYEE_SECURED TERMINATION_DT           AES 192 bits key                 YES
HR       EMPLOYEE_SECURED TERMINATION_RSN          AES 192 bits key                 YES

/* 
|| Listing 3.6: Adding additional encryption to an existing table
||              with Transparent Data Encryption
*/

-----
-- Add a new column and activate encryption
-----
ALTER TABLE hr.employee_secured
    ADD (blood_type VARCHAR2(1) ENCRYPT NO SALT);
    
-----
-- Activating encryption for an existing, unencrypted column. 
-- Note that Oracle will not allow the existing encryption 
-- algorithm to be changed to Triple DES 168-bit (3DES168)
-- unless the entire table is rekeyed to that algorithm!
-----
SQL> ALTER TABLE hr.employee_secured
    MODIFY (marital_status ENCRYPT USING '3DES168' NO SALT); 
    MODIFY (marital_status ENCRYPT USING '3DES168' NO SALT)
            *
ERROR at line 2:
ORA-28340: a different encryption algorithm has been chosen for the table

-----
-- Changing the encryption algorithm to Triple DES 168-bit 
-----
ALTER TABLE hr.employee_secured REKEY USING '3DES168';

-----
-- Changing encryption from unSALTed to SALTed
-----
ALTER TABLE hr.employee_secured
    MODIFY (marital_status ENCRYPT SALT);

-----
-- Removing encryption on existing encrypted column
-----
ALTER TABLE hr.employee_secured
    MODIFY (marital_status DECRYPT);

>>> Encryption status after the latest encryption operations:

                              Currently Encrypted Columns
                              (From DBA_ENCRYPTED_COLUMNS)
 
                                                   Encryption
Owner    Table Name       Column Name              Algorithm                        Salt?
-------- ---------------- ------------------------ -------------------------------- -----
AP       VENDORS          CREDIT_CARD              AES 192 bits key                 NO
HR       EMPLOYEE_SECURED BLOOD_TYPE               3 Key Triple DES 168 bits key    NO
HR       EMPLOYEE_SECURED ETHNICITY                3 Key Triple DES 168 bits key    YES
HR       EMPLOYEE_SECURED SOCSECNBR                3 Key Triple DES 168 bits key    YES
HR       EMPLOYEE_SECURED TERMINATION_DT           3 Key Triple DES 168 bits key    YES
HR       EMPLOYEE_SECURED TERMINATION_RSN          3 Key Triple DES 168 bits key    YES

/* 
|| Listing 3.7: Proving Transparent Data Encryption is in place
*/

SQL> ALTER TABLESPACE tbs_encrypted OFFLINE;

$> strings /u01/app/oracle/oradata/orcl/tbs_encrypted01.dbf | more

>>> Results of STRINGS command:

}|{z
=@CORCL
TBS_ENCRYPTED
eE9Uc
Kzi;
oYgK
8)Yy
eE9Uc
Kzi;
oYgK
eE9Uc
Kzi;
Bw-96
Y|]d
p|ac&v
R4J.
	7{;
oYgK
5rpd~
eE9Uc
Kzi;
Bw-96
Y|]d
p|ac&v
oYgK

SQL> ALTER TABLESPACE tbs_encrypted ONLINE;

/* 
|| Listing 3.8: Closing the Wallet: Turning off encryption capabilities
*/

-----
-- Close the encryption wallet. This should prohibit access to any 
-- table with encrypted data ...
-----

ALTER SYSTEM SET ENCRYPTION WALLET CLOSE;

-----
-- ... and now attempt to query selected AP.VENDOR columns.
-----

COL vendor_id           FORMAT 999999       HEADING 'Vendor|ID'
COL name                FORMAT A40          HEADING 'Vendor Name'
COL credit_card         FORMAT A16          HEADING 'Credit Card #'
TTITLE 'Vendor ID and Name|(From AP.VENDORS)'
SELECT 
    vendor_id, name
  FROM ap.vendors;
TTITLE 'Vendor ID and Credit Card|(From AP.VENDORS)'
SELECT 
    vendor_id, credit_card
  FROM ap.vendors;
TTITLE OFF

>>> Results from query:

                     Vendor ID and Name
                     (From AP.VENDORS)
 
 Vendor
     ID Vendor Name
------- ----------------------------------------
    101 FacesRUs, Inc.
    102 Scared Silly Makeup Gmbh
    103 Blood Imitation Products, LLC
    104 Tri-Color Corn NA
    105 Superhero Costumes Corporation
 
SQL> SQL>   2    3  SELECT
*
ERROR at line 1:
ORA-28365: wallet is not open