Good Citizenship – Have Client Applications register themselves with the database

Years ago I have read about the package dbms_application_info for the first time. This is a standard supplied package, shipped with every Oracle Database. It can be used to set information about the client application currently using a certain database connection or session. It sounded nice, but I more or less ignored it, not being convinced of its practical value.

Things have changed for me. Perhaps I have matured as IT professional, perhaps I have thought this through more carefully or perhaps my recent experience on a project where we are migrating a WebForms 6.0 application – generated using Designer 6.0 and Headstart – to Designer 10g, WebForms 10g and Headstart 10g. We encountered at least two excellent reasons for having each of our modules register themselves with the database:

• Good citizenship – To enable DBAs and Application Administrators to properly manage and administer Databases and Applications, it is very useful for them to know what each database session is doing all the time (from V$SESSION), at least the name of the client application, ideally the name of the current module and optionally the action currently being performed
• To allow for what we duped ‘functional authorization’ – the application previously was riddled with client side authorization filters: if a certain form was meant for maintenance of certain records, the user could only see a subset of the data that was visible in another form that was used for read-only access. And sometimes the same form was used in Maintenance mode (restrictive filter in place) or Browse Mode (less restrictive filter). These authorization filters come on top of the base authorization that specifies what a user can or can not see. Period. We want all authorization to be defined inside the database server. For several reasons: more robust, enforced on all database access and not just when the user happens to use the Forms modules, easier maintenance – all authorization logic in a single location and better prepared for future migration of the application to Java/J2EE technology: all functionality that is in the database does not have to be migrated and can be reused in our Java application – Client Side logic in Form Block where clauses would have to be migrated.

Set Application Info

Clients can indicate to the database who they are and what they are doing in several ways of course. However, the most obvious way is using the supplied package dbms_application_info. One of the advantages of using this package is that the values set can be seen in the v$session view – across sessions!Here is how to set values through dbms_application_info:

begin
  DBMS_APPLICATION_INFO.SET_MODULE
  ( module_name => 'MODULE_NAME'
  , action_name => 'The Current Mode or State of the Module, something like ''Browsing'' '
  );
  DBMS_APPLICATION_INFO.SET_CLIENT_INFO
  ( client_info => 'extra information about the client module, such as the current Master Context'
  );
end;

Now that we know what code is required to perform this client registration, the question is how to implement this in our application. We should define a simple, straightforward way of doing so. And obviously we do not want to burden developers with having to write a lot of code manually to set up the application info all the time. And we do not want to give them the opportunity to forget doing so.

Implement Set Application Info in Oracle Forms applications

When it comes to Oracle (Web)Forms applications, it is best to implement this logic in an event-trigger. Initially I considered the pre-form trigger, until a colleague pointed out that when for example the form invokes a second form to display a list of values or so the pre-form trigger of the second for would set the application info – which is correct – but upon returning to my first form, the application info would not be restored for that first form and the database would assume from then on that the second form would still be active.

So a better choice is the when-window-activated trigger, that will fire whenever the form receives focus again, during initial start-up as well as after returning from an invoked form.The most straightforward implementation is now a WHEN-WINDOW-ACTIVATED trigger in each Form in my application:

begin
set_client_info('additional client info');
end;

and the Library used throughout my application contains the procedure:

PROCEDURE set_client_info
( p_client_info in varchar2
) IS
BEGIN
  dbms_application_info.SET_MODULE
  ( module_name => name_in('system.current_form') -- use the name of the FORM Module - in our case something like AGS4100F - as the MODULE name
, action_name => Get_Window_Property(name_in('SYSTEM.EVENT_WINDOW'),Title) -- use the title of the form as the action value  );
  pks_context_editor.set_module( p_module => name_in('system.current_form'));
  dbms_application_info.set_client_info( p_client_info); -- client info can be something like the MODE in which the module runs or the value of some parameter
END set_client_info;

Note: I make use of the package pks_context_editor to also set the value of the current module in my own application context PKS_CONTEXT. That is because in our 9.2.0.5 database, we cannot use sys_context(‘USERENV’,’MODULE’) in our VPD Policy predicates. Instead, I use sys_context(‘PKS_CONTEXT’,’MODULE’), and obviously I need to do something to set the MODULE in this PKS_CONTEXT.

When using Headstart

In our situation, we are not simply hand-building all forms. We generate the Forms out of Oracle Designer 10g and we also make use of Headstart, a template package that tremendously enhances the process of Form Generation. We have done a few things to make life easier still.
1. Copied procedure qms$event_form from qmsevh65.pll to our application library. This procedure is called for practically any event taking place in our forms, thanks to all event-triggers set up in the qmsolb65.olb object library from which virtually all generated components in the forms subclass.

2. Added a small piece of logic – a call to procedure set_client_info – in qms$event_form, in the before when-window-activated handling section:

  elsif p_event = 'WHEN-WINDOW-ACTIVATED'
  then
    if p_exec_sequence = 'BEFORE'
    then
      qms$block.init_blocks_in_window(  name_in('system.trigger_block'));
      qms$application.chk_closing_state;
      if name_in('system.event_window') = 'QMS$TRANS_ERRORS'
         and
         name_in('system.cursor_block') <> 'QMS$TRANS_ERRORS'
      then
        copy(name_in('system.cursor_block'),'qms$trans_errors.return_to_block');
        go_block('QMS$TRANS_ERRORS');
      end if;
      -- Lucas (20-sep-2005) add call to set client info
      set_client_info;
      elsif p_exec_sequence = 'AFTER'
      then
        qms$find_block.raise_on_entry('WHEN-WINDOW-ACTIVATED');
      end if;
      elsif p_event = 'WHEN-FORM-NAVIGATE'
      ...

Note that we do not provide the parameter p_client_info in this call. That is because this is generic code while the actual value of client-info is set on a per forms base, in the pre-form trigger of each indivual module that needs to set client-info

3. We added a package to the Application Library

PACKAGE pks_application_info IS
  function get_client_info
  return varchar2
  ;
  procedure set_client_info(p_client_info in varchar2)
  ;
END;
PACKAGE BODY pks_application_info IS
  g_client_info varchar2(4000);
  function get_client_info
  return varchar2
  is
  begin
    return g_client_info;
  end get_client_info;
    procedure set_client_info(p_client_info in varchar2)
  is
  begin
    g_client_info:= p_client_info;
  end set_client_info;
 END;

Note that this procedure makes use of the package variable pks_application_info.get_client_info to set up client-info. This means that all code so far is self-contained. It is up to individual forms to set this package variable if they want the generic code to set the client info.

In each form that has special client-info to set, beyond the name and title of the module that are set automatically through the above code, we add a PRE-FORM trigger, something like:

begin
  pks_application_info.set_client_info( name_in('parameter.p_form_mode')); -- with p_form_mode a parameter set from the Menu Command
end;

Retrieve Application Info

With sys_context('USERENV','CLIENT_INFO') in SQL or PL/SQL we can easily retrieve the value of Client Info as set with dbms_application_info.set_client_info.

select sys_context('USERENV','CLIENT_INFO')
from   dual
/

Note: sys_context(‘USERENV’,’MODULE’) and sys_context(‘USERENV’,’ACTION’) can be used in 10gR1 and beyond but are not supported pre 10g.

Other ways to retrieve the values set by calls to dbms_application_info:

declare
l_module varchar2(2000);
l_action  varchar2(2000);
begin
  dbms_application_info.get_module( l_module, l_action);  -- get_module unfortunately is a PL/SQL procedure that cannot directly be invoked from SQL queriesend;

and

select dbms_application_info.get_client_info from
dual
/

More powerful is:

select module
,      action
,      client_info
from   v$session
/

Interestingly enough, this query acts across sessions: you can find the values of module, action and client_info for other sessions than your own. This is an ideal way for DBAs and Application Administrators to find out what database sessions are doing.

Example: Moving authorization code and filter logic from Client to Server

Now that the database is aware of the Module making use of the database connection, we can relocate some of the client side logic in the database server. For example, many of our Oracle Forms modules contain Where Clauses at Block Level – generated by and from Oracle Designer where we have where-clauses on the base table usages in the Module Component. Frequently, these where clauses restrict the records based on which user is connected and what he or she is trying to do. It is what I have duped ‘Functional Authorization’, as opposed to ‘Absolute Authorization’ that specifies which records a user can see period. Functional Authorization states something like ‘when you are doing this operation, you may see these records’. A simple example:

• Absolute Authorization (independent of the operation or module) – users can see all records from the PLANS table that are not set to ‘PRIVATE’ – they can see ‘PRIVATE’ PLANS only if they are the owner of those plans.
• Functional Authorization – When the user is in the Module "Copy Plans", he should only have access to PLANS of which he is either the owner or PLANS with a STATUS equals DEFINITIVE. In the Module "Query Plans" – which happens to be the same FMB, run in a different mode, the user can see all plans (after applying Absolute Authorization of course)

Currently Absolute Authorization is implemented in the WHERE clause of the View that is used from the Form module. The Functional Authorization is implemented in the Form itself, in the Where Clause on the Block. The latter looks like this:

( parameter.p_copy = 'N' -- the form is in Query mode
  or
  ( parameter.p_copy = 'J'  -- form is in Copy mode
    and
    (  PLANS.USR_ID = ( select usr.id from app_users usr where usr.username= USER)  -- PLAN is owned by current user
       OR
       PLANS.PLAN_STATUS ='DEFINITIVE'
     )
  )

In a previous post I have argued that USER should not be used anymore in Client or Server side SQL or PLSQL. Instead, the application or database should register the current user – that is the End User, not necessarily the Database User – in an Application Context. The above filter logic would then change to:

( parameter.p_copy = 'N' -- the form is in Query mode
  or
  ( parameter.p_copy = 'J'  -- form is in Copy mode
    and
    PLANS.USR_ID = sys_context('APP_CONTEXT', 'USERID')
    )
  )

Now we can take this one step further. If we know in the server what the current module is and we also have knowledge about the mode in which the module is running – copy or query mode – we can implement the Functional Authorization using either a Where clause on a Server Side View or – even better – using a Predicate produced by a Policy Function registered with the VPD (Virtual Private Database) framework in the database. In this example, the policy function looks like this:

function PLAN_FUN_AUT
(  p_schema_name   in   varchar2
,  p_table_name    in   varchar2
) return varchar2
is
  l_predicate varchar2(2000):='';
begin
  -- predicate for Copying Plans
  l_predicate = '(PRIVATE_YN = ''N'' OR USR_ID = sys_context(''PKS_CONTEXT'',''USERID''));
  if sys_context('APP_CONTEXT','MODULE') = 'COPY_PLANS'
     and
     sys_context('USERENV','CLIENT_INFO') = 'C' -- COPY MODE; note: if this same module is used for Querying, the CLIENT_INFO is set to Q
  then
    l_predicate := l_predicate
                 ||' AND
                 ||  ('
                 ||'   USR_ID = sys_context(''PKS_CONTEXT'',''USERID'')
                       OR
                       PLAN_STATUS =''DEFINITIVE''
                   '
                 ||' )' ;
  end if;
  return l_predicate;
end PLAN_FUN_AUT;
/

Note: in Oracle 9i, a static Policy Function is called only once per session. That is: the predicate applied to queries on the Table or View on which the policy is defined is the same for every query in the entire session. If the first query was done with the module running in Query Mode, the more lenient predicate (not checking status = definitive) would be applied, even if the form was in Copy Mode. The one exception is when the Policy Function relies on the SYS_CONTEXT function: if – as is the case in this policy function – the PL/SQL logic depends on SYS_CONTEXT, then the policy function is called for every query against the governed Table or View.

This is the reason for using APP_CONTEXT for storing the Module, instead of simply using select MODULE from V$SESSION where audsid = sys_context('USERENV', 'SESSIONID') or

declare
  l_module varchar2(2000);
  l_action  varchar2(2000);
begin
  dbms_application_info.get_module( l_module, l_action);
end;

This policy function is registered as follows:

begin
  dbms_rls.add_policy
  ( object_schema   => 'APP'
  , object_name     => 'APP_PLANS'
  , policy_name     => 'APP_PLAN_FUN_AUT'
  , policy_function => 'APP_AUTHORIZATION_CENTER.PLAN_FUN_AUT'
  , function_schema => 'APP'
  , statement_types => 'SELECT'
  , update_check    => false
  , enable          => true);
end;
/

It can easily be tested, without even running the Form, from SQL*Plus:

begin
  dbms_application_info.set_module('COPY_PLANS','');
  pks_context_editor.set_module('COPY_PLANS');
  dbms_application_info.set_client_info('C');  -- Copy mode
end;
/
-- this query applies Absolute Authorization: Only non-private plans complemented with the user's Private Plans
-- as well as Functional Authorization: Only plans that are owned by the User or have the status Definitive
select plan_label
,      plan_status
,      usr_id
,      private_yn
,      sys_context('PKS_CONTEXT','MODULE')
,      sys_context('USERENV','CLIENT_INFO')
from   plans
/
-- the second query applies only Absolute Authorization: All non-private plans and the user's Private Plans
begin
  dbms_application_info.set_module('COPY_PLANS','');
  pks_context_editor.set_module('COPY_PLANS');
  dbms_application_info.set_client_info('Q'); -- Query mode
end;
/
select plan_label
,      plan_status
,      usr_id
,      private_yn
,      sys_context('PKS_CONTEXT','MODULE')
,      sys_context('USERENV','CLIENT_INFO')
from   plans
/