Database Reference
In-Depth Information
Class.forName( "oracle.jdbc.driver.OracleDriver" );
conn = DriverManager.getConnection( appusrConnString );
Getting Ready for Encryption
We do not need the
Connection
in
OracleJavaSecure
because we won't be calling Oracle database
directly from that class. The only function of
OracleJavaSecure
on the client (in this chapter) is to build
keys and encrypt/decrypt data. See Listing 7-19.
Listing 7-19.
Get Ready for Encryption
//OracleJavaSecure.setConnection( conn );
String locModulus = OracleJavaSecure.getLocRSAPubMod();
String locExponent = OracleJavaSecure.getLocRSAPubExp();
We get our RSA key pair, and get the public key exponent and modulus to pass to Oracle database.
Setting Non-Default Role
The
appusr
user has the privilege to execute the
appsec.p_check_hrview_access
procedure (refer back to
Chapter 2), which will set the Secure Application Role,
hrview_role
. We execute the procedure as shown
in Listing 7-20.
Listing 7-20.
Set Non-Default Role
stmt = ( OracleCallableStatement )conn.prepareCall(
"CALL appsec.
p_check_hrview_access()
" );
// Comment next line to see Exception when non-default role not set
stmt.executeUpdate();
We need to execute the statement in order to acquire the role. If you want to assure yourself that
access without the role will fail, comment the line to
executeUpdate()
and run
TestOracleJavaSecure
. Be
sure to uncomment that line after you run that test, so you can run our primary tests.
Reusing a Callable Statement
Inasmuch as an
OracleCallableStatement
is an interface that implements
Statement
, we can use it like a
regular
Statement
. A regular
Statement
can be used over and over again to execute queries and updates.
In my experience, though, if you have
OUT
parameters from a procedure called from your
OracleCallableStatement
, then you should not reuse it—just get a new
OracleCallableStatement
.
That first call to set our role in Listing 7-20, leaves us with an
OracleCallableStatement
that we can
reuse for getting a count of the rows in our non-sensitive view of
EMPLOYEES
. We will count the rows two
ways, both shown in Listing 7-21: once by iterating through the
ResultSet
of all the rows, incrementing
our count,
cnt
as we go; and once by selecting the
count(*)
of all rows. Selecting
count(*)
is a much
more efficient way: