Database Reference
In-Depth Information
Listing 12-67.
Disable VPD Policies
BEGIN
DBMS_RLS.DROP_POLICY (
object_schema => 'appsec',
object_name => 't_app_conn_registry',
policy_name => 'apps_for_admin_policy' );
END;
/
BEGIN
DBMS_RLS.DROP_POLICY (
object_schema => 'appsec',
object_name => 't_app_conn_registry',
policy_name => 'apps_for_user_policy' );
END;
/
Adding a Dynamic Where Clause to Procedures
There are only two procedures that access the
t_app_conn_registry
table in a manner that concerns us.
Both of these procedures are in the
appsec.appsec_only_pkg
package:
p_get_class_conns
and
p_set_class_conns
. Outside of those procedures, only
SYS
, the
appsec
schema user account and users
who have been granted proxy through
avadmin
(with
appver_admin
role) have access to the table or a view
of it.
I propose that instead of using VPD to restrict data access, we modify the specific procedures of
concern with the same dynamic where clauses that we proposed for VPD. The resulting procedures are
shown in Listing 12-68.
Listing 12-68.
Procedures Protected by Dynamic Where Clause
PROCEDURE
p_get_class_conns
(
m_class_name v_app_conn_registry.class_name%TYPE,
m_class_version v_app_conn_registry.class_version%TYPE,
m_class_instance OUT v_app_conn_registry.class_instance%TYPE,
m_connections OUT v_app_conn_registry.connections%TYPE )
IS BEGIN
SELECT class_instance, connections
INTO m_class_instance, m_connections
FROM appsec.v_app_conn_registry
WHERE class_name = m_class_name
AND class_version = m_class_version
AND class_name IN ( SELECT class_name FROM appsec.v_app_class_id
WHERE application_id IN (
SELECT application_id FROM appsec.v_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.v_application_admins
WHERE user_id = SYS_CONTEXT( 'USERENV', 'CLIENT_IDENTIFIER' )
