Database Reference
In-Depth Information
m_application_id v_two_fact_cd_cache.application_id%TYPE )
RETURN VARCHAR2;
Add Helper Function to Get APP_ROLE
We are going to need one piece of data from the
t_application_registry
table, the secure application
role name, when we run the procedure that sets those roles. We have already discussed the need to run
our secure application role procedure as
AUTHID CURRENT_USER
. We do this so we can assure the validity
of the current user, not the schema owner,
appsec
. This is called invoker's rights. Also, this is the only
way that roles may be set, by the
CURRENT_USER
.
In order to execute the secure application role procedure, we need to grant execute on the
procedure to
PUBLIC
. However, we do not want to grant data privileges to
PUBLIC
on the
v_application_registry
view. If we have a helper function that belongs to the same schema as the
v_application_registry
and the secure application role procedure, then the helper function can be
executed by the procedure and read the data in the table on its behalf. PUBLIC has execute on the
procedure, but no grants on the view or the functions; yet the procedure has access to the functions in
order to select on the view. The result of this is that we can run a procedure to grant the role, but not
expose the data used to evaluate access.
We read the application role name, based on the
application_id
and
app_user
from the
f_get_app_role
function. We will add this function to our
appsec_only_pkg
package. See Listing 10-5.
Listing 10-5.
Helper Function to get Application Role Name
FUNCTION f_get_app_role(
m_application_id v_two_fact_cd_cache.application_id%TYPE,
m_app_user v_application_registry.app_user%TYPE )
RETURN VARCHAR2
AS
m_app_role v_application_registry.app_role%TYPE;
BEGIN
SELECT app_role INTO m_app_role
FROM v_application_registry
WHERE application_id = m_application_id
AND app_user = m_app_user;
RETURN m_app_role;
END f_get_app_role;
Replace Procedure for hrview_role Access with Dynamic Procedure
We will be replacing the application-specific procedure,
p_check_hrview_access
, which set the
hrview_role
with a generic procedure,
p_check_role_access
, which will set the secure application role
for any application. Each application will require an entry (one or more) in the
t_application_registry
table. With this new procedure, we can easily apply our SSO and two-factor authentication to multiple
applications.
Code for the New Procedure
By way of cleaning up, we will drop the old
p_check_hrview_access
procedure. That will assure us that
we are using the new procedure, even for setting
hrview_role
. Refer to Listing 10-6.
