Data Encryption in Transit - Expert Oracle and Java Security - page 123

Selecting Sensitive Data for an Employee ID
We will explore one more example of procedures to select encrypted data from HR . This Oracle
procedure, p_select_employee_by_id_sens , is almost identical to the previous two procedures, except
that it also takes a parameter representing a single EMPLOYEE_ID . That is shown in Listing 7-12.
Listing 7-12. Portion of Procedure to Select Sensitive Data by ID, p_select_employee_by_id_sens
m_employee_id employees.employee_id%TYPE
...
OPEN resultset_out FOR SELECT
employee_id,
...
FROM employees
WHERE employee_id = m_employee_id ;
The query for the resultset_out parameter selects data where the EMPLOYEE_ID equals that input
parameter.
This procedure should only return one row of data.
Revising Procedure to Get Shared Passphrase
We saw the p_get_shared_passphrase procedure in Chapter 6. We are embellishing it a bit in this chapter
with error logging. Error logging can assist application security in supporting the application developer.
The biggest change is that we are pulling p_get_shared_passphrase from the app_sec_pkg package
into our individual application package, hr_sec_pkg . We have it in hr_sec_pkg now so that our client
application, which may be running with hr_view role, can execute the procedure. We allow HR to execute
the app_sec_pkg structures, but we don't allow hr_view to do so. So, hr_view executes HR structures and
HR structures execute appsec structures.
We call p_get_shared_passphrase and follow up with a call to our new
OracleJavaSecure.makeDESKey() method to complete a key exchange and build the shared secret
password key. We must do this before attempting data updates.
Updating Sensitive Data Columns in EMPLOYEES
We are at the point now where we can implement encrypted updates to our data. We will define a
procedure p_update_employees_sensitive shown in Listing 7-13, in the package hr_sec_pkg to take data
for all the columns of the EMPLOYEES table. For the sensitive columns, we will be submitting RAW types that
encapsulate the encrypted data. The only IN parameters are the table column data, and the only OUT
parameters are the error number and text. Notice what is missing here—there are no parameters
representing our encryption keys. We have to assume that key exchanges have already taken place. If we
have not already exchanged keys in the current Oracle session, then the user application is trying to
submit unencrypted data in the fields that require encryption, or they are encrypting the data with keys
from a different session; and the Oracle database will not be able to decrypt the data.
We are defining our parameter types using an anchored datatype form that refers to the definition of
the original data. We anchor this datatype declaration to a previous definition. For example, in this
declaration:
m_employee_id employees.employee_id%TYPE,