Administration of Security - Expert Oracle and Java Security - page 389

For all our application connections, we are setting the SYS_CONTEXT parameter CLIENT_IDENTIFIER to
the user ID from our SSO process. We would like to restrict access to the appsec.t_app_conn_registry
table to just those users whose SSO user IDs are listed in appsec.v_application_admins as administrators
for the specific application. That is the easy part—our dynamic where clause will be:
WHERE class_name IN ( SELECT class_name FROM appsec.t_application_admins
WHERE user_id = SYS_CONTEXT( 'USERENV', 'CLIENT_IDENTIFIER' ) )
But we also want to allow any administrators of administrators to see all rows. We can find out who
those individuals are by finding who has been granted extra privileges, like UPDATE on the
appsec.v_application_admins view. We will add this to our dynamic where clause:
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' ) )
That addition will also allow the current user to select rows from appsec.v_app_conn_registry where
it is true that the current user is found in the SYS.DBA_TAB_PRIVS data dictionary view with the privilege to
UPDATE the appsec.v_application_admins view. If this is true, our dynamic where clause will return all
rows. This requires that we grant select on the data dictionary view, SYS.DBA_TAB_PRIVS to the appsec
user who will execute the VPD code:
GRANT SELECT ON SYS.DBA_TAB_PRIVS TO APPSEC WITH GRANT OPTION;
In a couple sections form here, we are going to use a similar dynamic where clause in a view,
v_app_conn_registry_filtered . Then we will let other users select from that view. This reveals a
potential limitation to the grant we issued earlier. In this case we specified WITH GRANT OPTION , as if
appsec was interested in granting select on the sys.dba_tab_privs view to others. Appsec will not be
doing that, but she wants to permit other users to view data that is selected from that data dictionary
view. If we had not specified WITH GRANT OPTION , then appsec would not be able to let others see what she
sees in the dba_tab_privs view. Grants cannot be transferred to others without specifying WITH GRANT
OPTION .
The first step in using VPD is to create a function using a specific template form, that will return the
dynamic where clause as a VARCHAR2 . Listing 12-64 shows the function that encapsulates the dynamic
where clause I just described, appsec.apps_for_admin .
Listing 12-64. VPD Function for Administrators
CREATE OR REPLACE FUNCTION appsec.apps_for_admin(
m_schema_nm VARCHAR2,
m_table_nm VARCHAR2 )
RETURN VARCHAR2
IS
rtrn_clause VARCHAR2(400);
BEGIN
rtrn_clause :=
'class_name IN ( 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 '