Administration of Security - Expert Oracle and Java Security - page 377

v_app_conn_registry . These queries run in functional screens where the Oracle application user is
proxying through avadmin . So, we grant SELECT on those views to the appver_admin role. Additionally, in
the Register New Application screen, that we are examining now, we insert data into three views:
v_app_class_id , application_registry and application_admin . We have currently set the appver_admin
role, so we grant INSERT on those views to appver_admin .
There will come a time when we need to select, update and delete records in the
v_application_admin view. We need to identify people, in addition to or in place of the person who
initially registered the application, to manage connection strings for each application. We delegate that
job of setting application administrators to specific users. We will grant SELECT , UPDATE , and DELETE on
v_application_admin to the osadmin user (you, perhaps.)
On the Assign Applications functional screen, we need to select records from the
v_application_registry view. That screen is operating with the ojs_adm_admin role by user proxying
through ojsaadm . Recall that ojsaadm has a whole set of grants and package of procedures all of his own—
look up at the description of the Admin Users screen for that discussion. We grant SELECT on
v_application_registry to the ojs_adm_admin role.
Listing 12-48 shows the script used to make the remaining grants needed on the apver instance.
Those grants take care of all the data requirements I mentioned earlier.
Listing 12-48. Grants to Appsec Data on Apver
GRANT SELECT ON appsec.v_app_class_id TO appver_admin ;
GRANT SELECT ON appsec.v_application_registry TO appver_admin;
GRANT SELECT ON appsec.v_app_conn_registry TO appver_admin;
GRANT INSERT ON appsec.v_app_class_id TO appver_admin ;
GRANT INSERT ON appsec.v_application_registry TO appver_admin ;
GRANT INSERT ON appsec.v_application_admins TO appver_admin;
GRANT UPDATE, SELECT, DELETE ON appsec.v_application_admins TO osadmin;
We still have one remaining security data access hurdle to deal with. Typically (that is up through
Chapter 11), we have just embedded our passwords in the application and used the first pass through
the application to set the connection strings. We don't want to have to do that! There was a reason for
that which we have discussed. We need to be proxied through the appver user, as the application, in
order to set the connection strings for the application. Once we call the getAAConnRole() method to get
one of the application connections from the list, we can no longer update the connection strings
associated with the application. I'm sure that OracleJavaSecure could be re-written to operate
differently.
The issue arose when we moved some of our procedures into the appsec_admin_pkg package and
required a secure role to get access. In effect, we have made the procedure to update our connection
strings impossible for us to call. When we are connected by proxying through the appver user, we have
not set a secure application role by executing the p_check_role_access procedure, or something similar.
Individual Oracle users, proxied through appver , cannot execute appsec_admin_pkg , even if they are
granted the privilege to proxy through avadmin to acquire the appver_admin role.
The solution we have is to create another role on apver instance that will be a default role for all
users who need to update the connection strings for an application. Our application administrators will
have both the grant to proxy through avadmin , and the grant to this new role, which we will call
appver_conns . The code for creating the appver_conns role is shown in Listing 12-49.