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.
 
Search WWH ::




Custom Search