Database Reference
In-Depth Information
|| ' 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_admin;
/
We create this procedure on the
apver
instance, where it is applied to the
t_app_class_registry
table. Listing 12-65 shows the command that creates the policy to use this function. Notice that we only
apply this policy to the statement types:
INSERT
,
UPDATE
, and
DELETE
. We consider this a restriction on
administrators. Notice also that the policy is declared to be
STATIC
. That lends it great speed since it will
reside in and execute from the system global area, SGA memory. See the Oracle Database Security Guide
document for more information on VPD.
Listing 12-65.
VPD Policy for Administrators
BEGIN
DBMS_RLS.ADD_POLICY (
object_schema => 'appsec',
object_name => 't_app_conn_registry',
policy_name => 'apps_for_admin_policy',
function_schema => 'appsec',
policy_function => 'apps_for_admin',
statement_types => 'INSERT,UPDATE,DELETE'
,
policy_type => DBMS_RLS.STATIC
);
END;
/
If we can do that, then surely we can restrict what application connection string lists our application
users can
SELECT
. We want to apply the same
where
clause as before, with an additional allowance. If the
user has been granted the privilege of proxying through the application user associated with an
application, then the user should be able to select the list of connection strings. That addition to the
where
clause looks like this:
WHERE 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'' ))) '
Reading from the inside out, that where clause looks at the cross-instance view of proxy users that
we configured in Listing 12-29, getting all the proxy users that have been granted to the current user,
then selects all applications from the
t_application_registry
table that are associated with that proxy
user, then selects all application inner class names for those applications. The current user will be able
to select application connection string lists from
v_app_conn_registry
that are associated with those
application inner classes. Listing 12-66 shows both the function that returns the dynamic where clause
and the policy that applies it to
SELECT
statements on the
appsec.t_app_conn_registry
table.
Listing 12-66.
VPD for Users
CREATE OR REPLACE FUNCTION appsec.apps_for_user(
