Database Reference
In-Depth Information
m_schema_nm VARCHAR2,
m_table_nm VARCHAR2 )
RETURN VARCHAR2
IS
rtrn_clause VARCHAR2(400);
BEGIN
-- appsec.app_sec_pkg.p_log_error( 122, 'dave',
-- 'appsec.apps_for_user: ' || SYS_CONTEXT( 'USERENV', 'CLIENT_IDENTIFIER' ) );
rtrn_clause :=
'class_name IN ( SELECT class_name FROM appsec.t_app_class_id '
|| 'WHERE application_id IN ( '
|| 'SELECT application_id FROM appsec.t_application_registry '
|| 'WHERE app_user IN ( '
|| 'SELECT proxy FROM ojsaadm.instance_proxy_users@orcl_link '
|| 'WHERE client = SYS_CONTEXT( ''USERENV'', ''CLIENT_IDENTIFIER'' ))) '
|| 'UNION SELECT class_name FROM appsec.t_application_admins '
|| 'WHERE user_id = SYS_CONTEXT( ''USERENV'', ''CLIENT_IDENTIFIER'' ) '
|| 'OR SYS_CONTEXT( ''USERENV'', ''CLIENT_IDENTIFIER'' ) = ( '
|| 'SELECT GRANTEE FROM SYS.DBA_TAB_PRIVS '
|| 'WHERE TABLE_NAME=''V_APPLICATION_ADMINS'' '
|| 'AND OWNER=''APPSEC'' '
|| 'AND PRIVILEGE=''UPDATE'' '
|| 'AND GRANTEE=SYS_CONTEXT( ''USERENV'', ''CLIENT_IDENTIFIER'' )))';
RETURN rtrn_clause;
END apps_for_user;
/
BEGIN
DBMS_RLS.ADD_POLICY (
object_schema => 'appsec',
object_name => 't_app_conn_registry',
policy_name => 'apps_for_user_policy',
function_schema => 'appsec',
policy_function => 'apps_for_user',
statement_types => 'SELECT'
,
policy_type => DBMS_RLS.STATIC );
END;
/
One of the biggest benefits of VPD is also one of its biggest problems. The problem is that it can be
hard to deal with. It is a hidden restriction, not listed in the grants or in the application code. The
dynamic where clause gets applied behind the scenes, transparent to the user. That difficulty is a feature,
a feature worth paying for in some secure computing environments. If you need VPD on steroids, you
can acquire Oracle Label Security.
To disable the VPD policies we configured, you would execute the statements in Listing 12-67. I am
going to do that, because for our efforts we can take a different, more manageable approach to achieve
the same effect. Also disable VPD because certain procedures in
appsec
packages running as “definer's
rights” could not select from
v_app_conn_registry
when
appsec
cannot.