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

secret_pass_count :=
appsec.app_sec_pkg.f_get_crypt_secret_count( ext_modulus, ext_exponent );
secret_pass :=
appsec.app_sec_pkg.f_get_crypt_secret_pass( ext_modulus, ext_exponent );
secret_pass_algorithm :=
appsec.app_sec_pkg.f_get_crypt_secret_algorithm(ext_modulus, ext_exponent);
OPEN resultset_out FOR SELECT
employee_id,
first_name,
last_name,
email,
phone_number,
hire_date,
job_id,
appsec.app_sec_pkg. f_get_crypt_data ( TO_CHAR( salary ) ),
appsec.app_sec_pkg. f_get_crypt_data ( TO_CHAR( commission_pct ) ),
manager_id,
department_id
FROM employees;
EXCEPTION
WHEN OTHERS THEN
m_err_no := SQLCODE;
m_err_txt := SQLERRM;
appsec.app_sec_pkg.p_log_error( m_err_no, m_err_txt,
'HR p_select_employees_sensitive' );
END p_select_employees_sensitive;
Filling the RESULTSET_TYPE
In the middle of the p_select_employees_sensitive procedure, we open the RESULTSET_TYPE to gather a
CURSOR from a query. Note that we don't actually transmit all the data when we return to the client;
rather, we provide the client with a handle for the CURSOR so that the client can gather and process rows
of data, one at a time.
The query we are using selects all the columns from the EMPLOYEES table. Notice in Listing 7-10 that
we encrypt the SALARY and COMMISSION_PCT with these calls:
appsec.app_sec_pkg.f_get_crypt_data( TO_CHAR( salary ) ),
appsec.app_sec_pkg.f_get_crypt_data( TO_CHAR( commission_pct ) ),
Our encryption methods require that we pass the data in for encryption using a String . Both SALARY
and COMMISSION_PCT are number columns, so we first convert them to VARCHAR2 and then pass them to
our Application Security Java Stored Procedure (function), appsec.app_sec_pkg.f_get_crypt_data .
That function returns a RAW type that holds the encrypted data. The client will decrypt the data back
to a clear-text String . And we will convert the data back to its original type ( Date , number, etc.), as
needed at the client.
You're probably asking, “But can't we encrypt non- String data?” The answer is yes. Actually, we
can encrypt anything that can be represented as a byte array, which is really anything, after some
conversion. However, if you can see the data on a screen or print it out, then you can also represent the
data as a String , and it's often clearer when we convert to / from Strings and often the case that we
eventually want a String anyway.