Understanding Transparent Data Encryption

Transparent data encryption (TDE) enables encryption
of sensitive data in database columns as it is stored in the operating
system files. It provides for secure storage and management of encryption
keys in a security module located outside database, separating ordinary
program functions from those that pertain to security, such as encryption.

This separation enables you to divide administration
duties between DBAs and security administrators, which is a strategy
that enhances security because no administrator is granted comprehensive
access to data. For example, one administrator manages only the keys,
while another manages only the database.

TDE is a key-based access control system enforcing
authorization using these keys:

Key

Description

Table

For each database table that contains encrypted
columns, there is one encryption key used to encrypt all the columns,
regardless of the number of encrypted columns in a given table.

Master

Each table's column encryption key is, in turn,
encrypted with the database server's master key. The Master key is
stored in an Oracle wallet, which is part of the external security
module.

TDE is transparent to the application, and no views
or additional tables are required. The application logic associated
with SQL and table access will continue to work without modification.

To implement this feature within your PeopleSoft
application, you need to:

Determine the fields that are
candidates for TDE.

Set up the Oracle wallet.

Set the encryption algorithm.

Encrypt fields.

Note: This feature is available for Oracle databases running
10g R2 and later. Oracle did not provide this feature on any earlier
version.

Note: Depending on the type of business and country in
which you are running your PeopleSoft applications, there may be specific
types of information, PII, that needs to be encrypted to comply with
regulatory standards. For more information, see your PeopleSoft application
documentation.

Managing the Oracle Wallet

With TDE, each individual table has its own table
key, which is used to encrypt the selected columns in that table.
Each table key is, in turn, encrypted using the TDE master key. The
TDE master key is stored and protected outside the database in an
Oracle Wallet, which is a container that stores authentication and
signing credentials, including:

TDE master key.

PKI private keys.

Certificates.

Trusted certificates for SSL.

Encrypted table keys are placed in the data dictionary.
When a user enters data into the column defined as encrypted, the
Oracle database retrieves the master key from the wallet, decrypts
the encryption key for that table from the data dictionary, uses that
encryption key on the input value, and stores the encrypted data in
the database.

Setting up the Oracle Wallet

Before implementing TDE, creating an Oracle Wallet
is required.

Warning! After implementing TDE, the Oracle Wallet must be
opened each time a database instance starts (or has been restarted)
or else TDE will not work. If the wallet is not open, users will see
error messages if they attempt to access any data encrypted using
TDE.

To set up an Oracle Wallet for TDE:

Specify the wallet location.

By default, the wallet is created in the directory
$ORACLE_BASE/admin/$ORACLE_SID/wallet.

So, if $ORACLE_BASE is /ds1/product/oracle and $ORACLE_SID
is HRDMO, then the wallet will be stored in the directory /ds1/product/oracle/admin/HRDMO/wallet.

You can set a different directory by specifying
it in the sqlnet.ora file located in $ORACLE_HOME/network/admin. For
instance, if you want the wallet to be in /orawall directory, place
the following lines in the sqlnet.ora file:

Specify the desired algorithm by entering one of
the following values into the Database Encryption Algorithm edit box
exactly as it appears below:

AES128

AES192

AES256

3DES168

Note: You must specify an encryption algorithm to enable
the Encrypt option for a field definition in Application Designer.

Encrypting Fields

You encrypt fields in Application Designer by selecting
the Encrypt check box on a field definition, and then creating a table
or altering an existing table.

Note: The Encrypt check box is enabled only on Oracle
databases running version 10g R2 or later that also have an encryption
algorithm specified in the Database Encryption Algorithm edit box
on the PeopleTools Options page.

These PeopleSoft field types can be encrypted:

Character

Long Character (see note below)

Number

Signed number

Date

DateTime

Time

Note: Long Character field types may only take advantage
of TDE when the following conditions are true: the field length is
greater than 0 and less than 1334 and the Raw Binary field attribute is not set.

These PeopleSoft field types can not be encrypted:

Image

Image reference

Attachment

After you define the field to be encrypted, and
either create a table or alter an existing table containing that field
definition, the Build feature generates DDL SQL containing the ENCRYPT
clause in the following syntax:

Note: If you are using Oracle Database version 10.2.0.4
or higher, the syntax includes the NOMAC parameter. For example, ALTER TABLE PS_AM_BI_HDR MODIFY (CR_CARD_NBR ENCRYPT
using 'AES192' 'NOMAC' NO SALT);

When DDL SQL containing the ENCRYPT clause is run
against the database, Oracle:

creates a cryptographically secure
encryption key for the table containing the column.

encrypts the clear text data
in the column, using the specified encryption algorithm.

Managing Fields Encrypted for TDE

This section covers these topics related to the
ongoing maintenance of encrypted fields:

Decrypting fields.

Regenerating an encryption key.

Upgrading TDE encrypted fields.

Decrypting Fields

If you decide that you no longer want a field encrypted
for TDE, you can issue a SQL ALTER operation using the DECRYPT clause.
For example, assume you wanted to decrypt the SSN field on the ACCOUNT
table.

Regenerating An Encryption Key

You regenerate a table encryption key by issuing
a SQL ALTER operation using the REKEY clause. For example, assume
you wanted to rekey the PS_AM_BI_HDR table to take advantage of AES256.

ALTER TABLE PS_AM_BI_HDR REKEY using 'AES256';

This creates a new table key and recreates the encrypted
column values using the new table key.

Upgrading TDE Encrypted Fields

All metadata field definitions are delivered with
no-encryption attributes enabled. PeopleSoft applications will not
deliver any metadata indicating encryption enabled for any field for
an initial installation database file, project, or a PeopleTools or
PeopleSoft application patch.

If you customize the field by adding TDE encryption,
you need to keep track of the fields and associated record definitions
and ensure that you maintain the desired encryption status through
any upgrades that you perform.

See Your PeopleSoft upgrade documentation

Altering Tables With TDE Encrypted Fields

When altering tables with TDE encrypted fields using
the Alter in Place option, Application Designer automatically switches
the Index Creation Options selection to Recreate index only if modified even if you specifically
select Recreate index if it already exists in the Build Settings dialog box.