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

All application schemas that intend to implement our application security will need the CREATE
PROCEDURE system privilege.
Recall that to have access to the application security structures, we also need to grant each
application schema the object privilege to execute the app_sec_pkg package, like we did (already as
secadm ) for HR :
GRANT EXECUTE ON appsec.app_sec_pkg TO hr;
Creating the HR Security Package
HR will have a package of his own procedures and functions that provide access to HR tables, but return
any sensitive columns in encrypted form only. Let's examine the package and then create it at the end.
 Note You can find a script of the following commands in the file named Chapter7/HR.sql .
CREATE OR REPLACE PACKAGE hr.hr_sec_pkg IS
TYPE RESULTSET_TYPE IS REF CURSOR ;
Within the Specification of our package, we will define a TYPE . We will name it RESULTSET_TYPE , and it
will represent a CURSOR , also known as a ResultSet in Java. When we call procedures to get our encrypted
HR data, we are going to hand back from Oracle database, a number of OUT parameters. Many of the OUT
parameters will be artifacts of our secret password key, as we have seen, and one might also be a
RESULTSET_TYPE , which will hold multiple rows of encrypted data .
Selecting Sensitive Data Columns from EMPLOYEES
The code in Listing 7-10 is the body of an Oracle procedure named p_select_employees_sensitive . You
should be very familiar with this format. The list of parameters and the code used to set the secret
password key artifacts look like what we've seen before. We do have an OUT parameter named
resultset_out that will hold a RESULTSET_TYPE (rows of data):
Listing 7-10. Procedure to Select Sensitive Data from Employees Table, p_select_employees_sensitive
PROCEDURE p_select_employees_sensitive(
ext_modulus VARCHAR2,
ext_exponent VARCHAR2,
secret_pass_salt OUT RAW,
secret_pass_count OUT RAW,
secret_pass_algorithm OUT RAW,
secret_pass OUT RAW,
resultset_out OUT RESULTSET_TYPE ,
m_err_no OUT NUMBER,
m_err_txt OUT VARCHAR2 )
IS BEGIN
m_err_no := 0;
secret_pass_salt :=
appsec.app_sec_pkg.f_get_crypt_secret_salt( ext_modulus, ext_exponent );