Application Authorization - Expert Oracle and Java Security - page 211

Our new procedure, p_check_role_access looks very similar to our previous secure application role
procedure. We enter this procedure with a connection that is already proxying through our application
Oracle user, which we get into app_user variable. The new procedure takes the additional
application_id argument, which it in turn passes with the app_user identity to our new f_get_app_role
helper function in order to read the role name from v_application_registry . Also, instead of having the
code for SSO right in this method, we pass the app_user to our new f_is_sso function to get back the
validated user.
Listing 10-6. Dynamic Secure Application Role Procedure, p_check_role_access
DROP PROCEDURE appsec.p_check_hrview_access;
CREATE OR REPLACE PROCEDURE appsec.p_check_role_access(
-- m_two_factor_cd v_two_fact_cd_cache.two_factor_cd%TYPE,
m_application_id v_two_fact_cd_cache.application_id%TYPE,
m_err_no OUT NUMBER,
m_err_txt OUT VARCHAR2 )
AUTHID CURRENT_USER
AS
return_user VARCHAR2(40);
m_app_user v_application_registry.app_user%TYPE;
m_app_role v_application_registry.app_role%TYPE;
BEGIN
m_err_no := 0;
m_app_user := SYS_CONTEXT('USERENV','PROXY_USER');
m_app_role := appsec_only_pkg. f_get_app_role ( m_application_id, m_app_user );
return_user := f_is_sso( m_app_user );
IF( return_user IS NOT NULL )
THEN
-- Code for two-factor Auth moved to appver login process
-- IF( m_two_factor_cd IS NULL OR m_two_factor_cd = '' )
-- THEN
-- m_err_txt := appsec_only_pkg.f_send_2_factor( return_user, m_application_id );
-- ELSIF( appsec_only_pkg.f_is_cur_cached_cd( return_user, m_application_id,
-- m_two_factor_cd ) = 'Y' )
-- THEN
EXECUTE IMMEDIATE 'SET ROLE ' || m_app_role ;
-- ELSE
-- RAISE NO_DATA_FOUND;
-- END IF;
app_sec_pkg.p_log_error( 0, 'Success getting SSO and setting role, ' ||
SYS_CONTEXT( 'USERENV', 'OS_USER' ) );
ELSE
app_sec_pkg.p_log_error( 0, 'Problem getting SSO, ' ||
SYS_CONTEXT( 'USERENV', 'OS_USER' ) );
END IF;
EXCEPTION
WHEN OTHERS THEN
m_err_no := SQLCODE;
m_err_txt := SQLERRM;