/*
|| 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