Application Authorization - Expert Oracle and Java Security - page 207

Note that each new connection established for an application may require a new and distinct set of
encryption keys. There are limitations to how many sets of keys we will maintain for concurrent use in
the code provided in this topic. We will retain the encryption keys for the appver connection, in order to
continue decrypting the connection strings for this application, and we will allow each application user
to use one additional set of encryption keys at a time. Additional Oracle connections without encryption
can be used concurrently. However, a diligent programmer can overcome those limitations.
Secure Application Role Procedure for Multiple Applications
Thinking back on our secure application role— hrview_role and the procedure that sets the role—what
application-specific features did it implement? It tested for a number of things that are not application
specific: IP address, time of day, two-factor authentication, and most important, SSO identity. However,
a couple things were application-specific: the user who was seeking the role ( appusr ) and the role itself
( hrview_role ).
Our goal at this juncture is to build a single secure application role procedure that will work for any
application, enforcing all our connection security requirements, but granting the specific role required
to the specific application user. We can build a procedure to handle that, but first we need a registry of
those application-specific features. We will create the t_application_registry table in the appsec
schema to hold the data. The code for creating this table is in Listing 10-1.
 Note You can find the script in Listing 10-1 in the file named Chapter10/AppSec.sql .
Listing 10-1. Application Registry Table of Salient Features
CREATE TABLE appsec.t_application_registry
(
application_id VARCHAR2(24 BYTE) NOT NULL,
app_user VARCHAR2(20 BYTE) NOT NULL,
app_role VARCHAR2(20 BYTE) NOT NULL
);
We will also create a view of the table for general use. And, though not shown here, we will make the
application_id and app_user columns a unique index and our primary key. We will not depend on that
key until we get to Chapter 12. For now, suffice it to say that each application may use multiple secure
application roles. We will acquire these roles by proxying through a variety of application users. So a pair
of application_id and app_user is a unique key to acquire an app_role .
While we're at it, let's insert a data record with the labels we already know: user APPUSR and role
HRVIEW_ROLE . We give those settings to the application_id of HRVIEW , as shown here:
INSERT INTO appsec.v_application_registry ( application_id, app_user, app_role )
VALUES ( 'HRVIEW', 'APPUSR', 'HRVIEW_ROLE' );
We are introducing the application_id column here as a handle to acquire the required role. Each
application will need a unique application_id , which, with a couple more additions, will allow our
existing code to provide two-factor authentication, SSO, and secure application roles to multiple
applications.