Database Reference
In-Depth Information
Listing 12-54.
Insert Application Class to ID Relationship
String updateString =
"insert into appsec.
v_app_class_id
" +
"( class_name, application_id ) values ( ?, ? )";
PreparedStatement pstmt = conn.prepareStatement(updateString);
pstmt.setString(1,
innerClassName
);
pstmt.setString(2,
applicationIDTextField
.getText().toUpperCase());
pstmt.executeUpdate();
The last of the three insert statements is our application registration entry into the
appsec.v_application_registry
view. This data entry correlates the proxy user for this application with a
secure application role. The code for the insert command is given in Listing 12-55.
Listing 12-55.
Application Registration Entry
String updateString =
"insert into appsec.
v_application_registry
" +
"( application_id, app_user, app_role ) values ( ?, ?, ? )";
PreparedStatement pstmt = conn.prepareStatement(updateString);
pstmt.setString(1,
applicationIDTextField
.getText().toUpperCase());
pstmt.setString(2,
applicationUserTextField
.getText().toUpperCase());
pstmt.setString(3,
applicationRoleTextField
.getText().toUpperCase());
pstmt.executeUpdate();
When we make an entry into the
v_application_registry
view on the
apver
database instance, we
need to have an identical entry in the
v_application_registry
view on the
orcl
instance. That is pretty
easy to arrange with an insert trigger across a database link. (There are also more robust ways to do this
with message queues or streams.) Because we already have a private database link from
appsec
on the
apver
instance to
apsec
on the
orcl
instance, all we need is the trigger. (Previously we had used the
orcl_link
to access data in the
HR
schema on the
orcl
instance from procedures running on the
apver
instance.) Listing 12-56 shows the trigger we will use to insert an identical record in the
v_application_registry
view on the
orcl
instance to the one we inserted on the
apver
instance.
Listing 12-56.
Insert Trigger on Application Registry
CREATE OR REPLACE TRIGGER appsec.t_application_registry_iar
AFTER INSERT ON appsec.t_application_registry FOR EACH ROW
BEGIN
INSERT INTO appsec.v_application_registry@
orcl_link
( application_id, app_user, app_role ) VALUES
( :new.application_id, :new.app_user, :new.app_role );
-- OJDBC will auto-commit this insert on orcl instance
END;
/
ALTER TRIGGER appsec.t_application_registry_iar ENABLE;
We want to give the current user access to edit the connection strings for this application they just
registered. To do that task, the user needs to be granted the
appver_conns
role on the
apver
instance. This is
in addition to being able to proxy through the
avadmin
user. We create a prepared statement to call the
stored procedure
sys.appver_conns_role_pkg.p_grant_appver_conns_role
. This is shown in Listing 12-57.