Apex and client server applications

When you have situations that same Oracle database is attacked from several client-server applications (all kinds of Oracle forms for instance) and Apex WEB application in the same time, some problematic parts might arise. Today I'll focus on one small hint which might help in many cases when you deal with authenticated users and their actions.

The problem

Problem is that in classical client-server apps, you probably deal with real Oracle users, who logged in to database with their accounts. In Apex, the situation is rather different and in that situation only one Oracle user (attached Oracle user which is assigned to Apex workspace where application is developed) is contacting the database.

Here is one live example of topical problem.

In a case that you have some simple monitoring part implemented in a classic way through 4 fields in tables, declared as:

  CREATE_USER       VARCHAR2(30 BYTE),
  CREATE_DATE       DATE,
  EDIT_USER         VARCHAR2(30 BYTE),
  EDIT_DATE         DATE

whose values are filled through two triggers, BEFORE INSERT:

CREATE OR REPLACE TRIGGER "SOME_TABLE_TGBI" 
BEFORE INSERT
ON SOME_TABLE REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
begin
  :new.create_date:= sysdate;
  :new.create_user:= user;
end;
/

and BEFORE UPDATE:

CREATE OR REPLACE TRIGGER "SOME_TABLE_TGBU" 
BEFORE INSERT
ON SOME_TABLE REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
begin
  :new.edit_date:= sysdate;
  :new.edit_user:= user;
end;
/

then you'll get correct values from all client-server applications and wrong values for all Apex based applications. In those cases you'll always get just one user explained previously. Let me show exactly what I'm pointing to. Here is an example of one table edited by real Oracle users (4 letters in name) and many Apex application users represented by APEX_USER.

ID CREATE_DATE         CREATE_USER   EDIT_DATE           EDIT_USER
----- ------------------- ------------- ------------------- ----------------
26384 11.07.2010 10:22:15 NAMR          19.04.2011 09:01:16 UACA
26386 19.04.2009 11:17:15 UACA          19.04.2011 19:10:15 APEX_USER
26385 12.01.2011 08:11:15 RAPO          19.04.2011 03:33:16 APEX_USER
26387 19.02.2011 12:24:15 RAPO          19.04.2011 19:04:13 APEX_USER
26388 22.02.2011 17:23:15 APEX_USER     19.04.2011 22:22:05 RAPO
26390 30.03.2011 13:19:15 IGAP          19.04.2011 21:47:36 APEX_USER
26391 06.01.2011 13:18:15 APEX_USER     19.04.2011 19:56:27 APEX_USER
26389 02.01.2011 11:08:15 APEX_USER     19.04.2011 20:23:33 APEX_USER
26392 02.04.2011 11:01:15 APEX_USER     19.04.2011 10:09:56 IGAP
26394 18.04.2011 09:10:15 NAMR          19.04.2011 16:11:01 APEX_USER

For some application t is vital to follow which user has done some actions, so it is more then obvious to implement a solution for that situations.

The solution

But regardless that only one real Oracle user is connected to database through Apex application, Apex leave plenty of information about "application" based user ... authenticated user in Apex application. This value can always be retrieved Apex "Built-in Substitution String" v('APP_USER') value in any PL/SQL code. More on Apex "Built-in Substitution Strings" can be found here.

So, when this is true, let me show you how to write trigger which will server both environments in the same time.

For that create a procedure in one separate schema (in mine case this is mine famous TOOLS schema). I'll call that procedure TGBIU_APEX:

CREATE OR REPLACE PROCEDURE "TGBIU_APEX" 
/******************************************************************************

 NAME : TGBIU_APEX.prc
 PURPOSE : Comon part for insert/update trigger in Apex and NON Apex applications

 Date : 15.02.2008.
 Author : Damir Vadas

 Remarks : Create procedure in separate schema and grant execute to public. 
           Recommendation is to not create PUBLIC SYNONYM.

 Changes (DD.MM.YYYY, Name, CR/TR#):
******************************************************************************/
                                       (o_date out date,
                                        o_user out VARCHAR2
                                       )
AUTHID CURRENT_USER
AS
BEGIN
  o_date:=sysdate;
  o_user:=nvl(v('APP_USER'),USER);
END TGBIU_APEX;
/

As you see, procedure has two "out" parameters where interesting one is "o_user" value which would firstly be assigned from Apex application and if this value is null then real Oracle user value would be assigned. So in that case problem is solved.

Do not forget to place AUTHID CURRENT_USER which will ensure proper usage all around the database. Grant execute to PUBLIC but do not create PUBLIC SYNONYM.

Usage for previously two triggers would be like:

CREATE OR REPLACE TRIGGER "SOME_TABLE_TGBI" 
BEFORE INSERT
ON SOME_TABLE REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
begin
  TOOLS.TGBIU_APEX (:new.create_date,:new.create_user);
end;
/

and

CREATE OR REPLACE TRIGGER "SOME_TABLE_TGBU" 
BEFORE INSERT
ON SOME_TABLE REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
begin
  TOOLS.TGBIU_APEX (:new.edit_date,:new.edit_user);
end;
/

The End

Another usage of approach like this might be in some other actions where you need to monitor other actions based on authenticated users.

Beside you achieve the main purpose, clearness and centralizing code is another benefit, which would certainly help in future maintains/upgrades to some other environment.

Cheers!