Application Authorization - Expert Oracle and Java Security - page 212

app_sec_pkg.p_log_error( m_err_no, m_err_txt,
'APPSEC p_check_role_access' );
END p_check_role_access;
/
Notice that the two-factor code is no longer required, and all the logic related to processing two-
factor authentication has been commented. We are moving that logic to take place immediately after the
application verification, the appver user, connects. The job of appver , after controlling initial application
access, is to provide a list of connection strings to the application. It is in this process, a call to a new
procedure, P_GET_APP_CONNS that we will do two-factor authentication.
If we pass SSO, then we proceed to set the role to the value we looked up in v_application_registry .
If the user's connection/session fails to pass our SSO requirements, then we log the error “Problem
getting SSO,” and return without setting the role. Something is seriously wrong with that user, and we
don't want to deal with him at all.
Put Dynamic Secure Application Role Procedure to Work
As a one-stop-shop for all applications to gain the privileges needed to operate, p_check_role_access
needs to be executable by all. We will grant execute on p_check_role_access to PUBLIC . The procedure
runs with AUTHID CURRENT_USER , so it doesn't have direct access to data in the appsec schema; however,
this procedure, by virtue of its definition in the appsec schema, can execute other functions and
procedures in the appsec schema, like the f_get_app_role helper function, which provides the required
data. Following is our grant of execute privileges:
GRANT EXECUTE ON appsec.p_check_role_access TO PUBLIC;
We will add some additional auditing, because we want to see if there is a trend of errors in this
procedure. We can combine this audit information with information in the v_appsec_errors log view.
For example:
AUDIT EXECUTE ON appsec.p_check_role_access
BY ACCESS WHENEVER NOT SUCCESSFUL;
 Note You can find a script with the preceding statements in the file named Chapter10/SecAdm.sql .
As you may recall, when we initially created the hrview_role back in Chapter 2, we specified that it
would be IDENTIFIED USING appsec.p_check_hrview_access . We are going to have to redirect it to be
identified by our new procedure. We will drop the role and recreate it. Also, we need to repeat the grants
that we had made to that role as shown in Listing 10-7.
Listing 10-7. Recreate HR View Role Identified by New Procedure
DROP ROLE hrview_role;
CREATE ROLE hrview_role IDENTIFIED USING appsec.p_check_role_access;
GRANT EXECUTE ON hr.hr_sec_pkg TO hrview_role;