Apex security and updatable views (2)

In my previous post, I indicated that the code for table API's and instead of triggers can easily be generated.

The code generator takes the Apex generated table API package as the basis, not the table definition. I don't know if Apex can generate code for every column data type. If Apex doesn't generate code for some column, the following code generator won't either.

Before generating the instead of triggers, you have to generate the package that lies on top of the apex API package.
There are some constants in the declaration section of this script that you have to modify. An important one is v_execute. When set to TRUE, the generated CREATE PACKAGE code will be executed in the database.

set scan off
declare
-- Create a custom package that call table API's that are generated using the Apex table API generator
-- (in SQL Workshop -> Methods on Tables).
-- Modify this package, instead of the Apex generated package, to add business rules.

   -- Change these constants before generating   
   
   -- Table on which view is based
   v_base_table    constant varchar2(30) := 'emp'; 
   -- Name of the Apex generated package 
   v_pkg           constant varchar2(30) := 'pkg_emp';
   -- Run execute immediate for generated code or not
   v_execute       constant boolean      := false;  

   -- Apex generated API prefixes
   v_upd_procedure constant varchar2(30) := 'UPD_';
   v_ins_procedure constant varchar2(30) := 'INS_';
   v_del_procedure constant varchar2(30) := 'DEL_';
   
   -- For code formatting 
   v_indent        constant integer      := 3;
   cr              constant varchar2(10) := chr(10);

   v_body                   varchar2(32767);

   cursor c_args(b_procedure  in varchar2) is
      select ua.sequence
            ,lpad(' ',3*v_indent)||rpad(lower(ua.argument_name),20)||'   '||
                lower(ua.in_out)||' '||lower(ua.data_type)||','||cr code
      from   user_arguments ua
      where  ua.object_name   = upper(b_procedure||v_base_table)
      and    ua.package_name  = upper(v_pkg)
      order by 1 
   ;   

   cursor c_bind(b_procedure  in varchar2) is
      select ua.sequence
            ,lpad(' ',3*v_indent)||rpad(lower(ua.argument_name),20)||' => '||
               lower(ua.argument_name)||','||cr code
      from   user_arguments ua
      where  ua.object_name   = upper(b_procedure||v_base_table)
      and    ua.package_name  = upper(v_pkg)
      order by 1 
   ;
   
   procedure create_procedure_spec (i_procedure in varchar2)
   is
   begin
      v_body := v_body || lpad(' ',v_indent)||'procedure '||
                   lower( i_procedure||v_base_table||' (' )||cr;

      -- Procedure arguments
      for r in c_args(i_procedure)
      loop
         v_body := v_body || r.code;
      end loop;   

      v_body := rtrim(v_body,cr||',')||cr||lpad(' ',v_indent)||');';
   
   end create_procedure_spec;
   
   
   procedure create_procedure_body (i_procedure in varchar2)
   is
   begin
      v_body := v_body || lpad(' ',v_indent)||'procedure '||
                  lower( i_procedure||v_base_table||' (' )||cr;

      -- Procedure arguments
      for r in c_args(i_procedure)
      loop
         v_body := v_body || r.code;
      end loop;   

      -- Procedure body
      v_body := rtrim(v_body,cr||',')||cr||lpad(' ',v_indent)||') is' || cr ||
         lpad(' ',v_indent) || 'begin'||cr ||
         lpad(' ',2*v_indent) || '-- Add business rules code here before calling table API.' || cr ||
         lpad(' ',2*v_indent) || 'null;' || cr || cr ||
         -- Call to Apex generated API
         lpad(' ',2*v_indent)||lower( v_pkg||'.'||i_procedure||v_base_table||' (' )||cr;

      -- Bind procedure arguments to Apex generated API arguments
      for r in c_bind(i_procedure)
      loop
         v_body := v_body || r.code;
      end loop;   

      v_body := rtrim(v_body,cr||',')||cr||lpad(' ',2*v_indent)||');'||cr;
      v_body := v_body || lpad(' ',v_indent) || 'end '||lower( i_procedure||v_base_table)||';';
   end create_procedure_body;
   
   
begin
   
   /*----  Package specification ----*/
   
   v_body := 'create or replace package '||lower(v_pkg)||'_br is' || cr ||
      '-- Package generated on: ' || to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') || cr ||
      '-- Manually add programmer defined code before call to table API.' || cr || 
      '-- Date         Author  Remarks'  || cr ||
      '-- ===========  ======  =======================================================' || cr ||
      '-- ' || to_char(sysdate,'dd-mon-yyyy') || '          Initially generated package'|| cr || cr
      ;
   
   -- Update procedure
   create_procedure_spec (v_upd_procedure);
  
   -- Insert procedure
   v_body := v_body || cr || cr;
   create_procedure_spec (v_ins_procedure);

   -- Delete procedure
   v_body := v_body || cr || cr;
   create_procedure_spec (v_del_procedure);

   -- End package specification
   v_body := v_body || cr || 'end;';

   dbms_output.put_line(v_body);
   
   if v_execute then
      execute immediate v_body;
   end if;   


   /*----  Package body ----*/

   v_body := null;
   v_body := 'create or replace package body '||lower(v_pkg)||'_br is'||cr; 

   -- Update procedure
   create_procedure_body (v_upd_procedure);
  
   -- Insert procedure
   v_body := v_body || cr || cr;
   create_procedure_body (v_ins_procedure);

   -- Delete procedure
   v_body := v_body || cr || cr;
   create_procedure_body (v_del_procedure);

   -- End package body
   v_body := v_body || cr || 'end;';

   dbms_output.put_line(v_body);
   
   if v_execute then
      execute immediate v_body;
   end if;   
end;

The code for generating the instead of triggers is similar, and so are the constants in the declaration section that you have to set.

set scan off
declare
-- Create instead of triggers on a view that is defined as "create view as select * from "
-- The instead of triggers call table API's that are generated using the Apex table API generator
-- (in SQL Workshop -> Methods on Tables)

   -- Change these constants before generating 

   -- View for which you are creating the instead of triggers
   v_view          constant varchar2(30) := 'emp_v';
   -- Table on which view is based
   v_base_table    constant varchar2(30) := 'emp'; 
   -- Name of the Apex generated package 
   v_pkg           constant varchar2(30) := 'pkg_emp';
   -- Run execute immediate for generated code or not
   v_execute       constant boolean      := false; 

   -- Argument in API that does not correspond to a column
   v_non_column    constant varchar2(30) := 'P_MD5'; 
   -- Apex generated API prefixes
   v_upd_procedure constant varchar2(30) := 'UPD_';
   v_ins_procedure constant varchar2(30) := 'INS_';
   v_del_procedure constant varchar2(30) := 'DEL_';
   
   -- For code formatting 
   v_indent        constant integer      := 3;
   cr              constant varchar2(10) := chr(10);
   
   v_trigger                varchar2(32767);
   v_body                   varchar2(32767);
   
   cursor c_args (b_procedure in varchar2) is
      select ua.sequence
            ,lpad(' ',3*v_indent)||rpad(lower(ua.argument_name),20)||' => '||
                 decode(b_procedure,v_del_procedure,':old.',':new.')||
                 substr(lower(ua.argument_name),3)||','||cr code
      from   user_arguments ua
      where  ua.object_name   = upper(b_procedure||v_base_table)
      and    ua.package_name  = upper(v_pkg)
      and    ua.argument_name <> v_non_column
      union
      select ua.sequence
            ,lpad(' ',3*v_indent)||rpad(lower(ua.argument_name),20)||' => '||'null'||','||cr code 
      from   user_arguments ua
      where  b_procedure      = v_upd_procedure  -- UPD procedure has non-column argument
      and    ua.object_name   = upper(b_procedure||v_base_table)
      and    ua.package_name  = upper(v_pkg)
      and    ua.argument_name = v_non_column
      order by 1 
   ;

   procedure create_procedure_body (i_procedure in varchar2)
   is
   begin
      v_body := lpad(' ',v_indent)||
                lower( v_pkg||'_br'||'.'||i_procedure||v_base_table||' (' )||cr;

      for r in c_args(i_procedure)
      loop
         v_body := v_body || r.code;
      end loop;   

      v_body := rtrim(v_body,cr||',')||cr||lpad(' ',v_indent)||');';
   end  create_procedure_body;
        
begin
   -- Instead of update
   create_procedure_body(v_upd_procedure);
   
   v_trigger := 'create or replace trigger iur_'||lower(v_view)||cr
              ||'instead of update on '||lower(v_view)||cr
              ||'for each row'||cr
              ||'begin'||cr
              ||v_body||cr
              ||'end;'; 

   dbms_output.put_line(v_trigger);
   
   if v_execute then
      execute immediate v_trigger;
   end if;   

   -- Instead of insert
   create_procedure_body(v_ins_procedure);
   
   v_trigger := 'create or replace trigger iir_'||lower(v_view)||cr
              ||'instead of insert on '||lower(v_view)||cr
              ||'for each row'||cr
              ||'begin'||cr
              ||v_body||cr
              ||'end;'; 

   dbms_output.put_line(v_trigger);
   
   if v_execute then
      execute immediate v_trigger;
   end if;   

   --
   -- Instead of delete
   
   create_procedure_body(v_del_procedure);
   
   v_trigger := 'create or replace trigger idr_'||lower(v_view)||cr
              ||'instead of delete on '||lower(v_view)||cr
              ||'for each row'||cr
              ||'begin'||cr
              ||v_body||cr
              ||'end;'; 

   dbms_output.put_line(v_trigger);
   
   if v_execute then
      execute immediate v_trigger;
   end if;   

end;

Apex security and updatable views (1)

Jeez, no post in 2010 at all! Time for a new one then.

A lot of the basics in this post are derived from this very good presentation by Scott Spendolini (Secure Apex Development).

In this presentation Scott basically shows how to use a separate schema, i.e. separate from your actual data schema, for building Apex applications. This shadow schema, as he calls it, contains just read only views and synonyms to table API packages in the data schema.

The problem with his approach, in my opinion, is that you miss a lot of the default (multi) row processing of Apex.
So, I modified the model slightly and use updatable views in combination with instead of triggers.

Some people are very opposed to instead of triggers. In my case, the triggers still use the Apex generated table API package (accessable via Create PL/SQL - Methods on Tables in SQL Workshop). There doesn't have to be any other code in the triggers than this.

For this to work properly, the view has to have all the columns of the base table. If you select only a few columns of the base table, it will get a bit more complicated. The reason is that the Apex generated API will nullify a column when a no value is passed to the update procedure.

So, the view on the EMP table will be created as:

create or replace view emp_v as
select *
from emp;

The Apex generated API package for table EMP is (I only display the insert procedure here):

CREATE OR REPLACE package "PKG_EMP" is
--------------------------------------------------------------
-- create procedure for table "EMP"
procedure "INS_EMP" (
"P_EMPNO"    in number,
"P_ENAME"    in varchar2  default null,
"P_JOB"      in varchar2  default null,
"P_MGR"      in number    default null,
"P_HIREDATE" in date      default null,
"P_SAL"      in number    default null,
"P_COMM"     in number    default null,
"P_DEPTNO"   in number    default null
);
(...)

The instead of insert trigger on the view emp_v will then be:

CREATE OR REPLACE TRIGGER iir_emp_v
instead of insert ON EMP_V for each row
begin
pkg_emp.ins_emp (
   p_empno              => :new.empno,
   p_ename              => :new.ename,
   p_job                => :new.job,
   p_mgr                => :new.mgr,
   p_hiredate           => :new.hiredate,
   p_sal                => :new.sal,
   p_comm               => :new.comm,
   p_deptno             => :new.deptno
);
end;

This is all very structured, and therefore easily generated with another code generator. But more about that in my next post.

One of the main reasons to use table API's is to centralize business rules in one place. You can modify the generated Apex package to add you business rules.
Now, I don't like the idea of modifying any Apex generated code.

The solution is to create a second package on top of the Apex generated package. This custom package calls the procedures in the API package. Since this is your own code, you can program business rules in this second package.

Suppose you call your package pkg_emp_br. The insert procedure in this package body will look like

CREATE OR REPLACE package body pkg_emp_br is
procedure ins_emp (
    p_empno                in number,
    p_ename                in varchar2,
    p_job                  in varchar2,
    p_mgr                  in number,
    p_hiredate             in date,
    p_sal                  in number,
    p_comm                 in number,
    p_deptno               in number
) is
begin
 -- Add business rules code here before calling table API.
 null;

 pkg_emp.ins_emp (
    p_empno              => p_empno,
    p_ename              => p_ename,
    p_job                => p_job,
    p_mgr                => p_mgr,
    p_hiredate           => p_hiredate,
    p_sal                => p_sal,
    p_comm               => p_comm,
    p_deptno             => p_deptno
 );
end ins_emp;
(...)

If there are no insert business rules, you can leave the package like it is now.

The instead of triggers will have to be modified to call procedures in this custom package, instead of calling the table API's directly.

CREATE OR REPLACE TRIGGER iir_emp_v
instead of insert ON EMP_V for each row
begin
pkg_emp_br.ins_emp (
(...)

Now, let's add a business rule to the custom package:
there can only be one PRESIDENT

Modify the insert procedure:

procedure ins_emp (
    p_empno                in number,
    p_ename                in varchar2,
    p_job                  in varchar2,
    p_mgr                  in number,
    p_hiredate             in date,
    p_sal                  in number,
    p_comm                 in number,
    p_deptno               in number
) is
 v_count                   integer;
 v_president     constant  emp.job%type := 'PRESIDENT';
begin
 -- Add business rules code here before calling table API.

 -- There can only be one PRESIDENT
 if p_job = v_president then
    select count(1)
    into   v_count
    from   emp
    where  job = v_president;

    if v_count > 0 then
       raise_application_error(-20000,'There can only be one president');
    end if;
 end if;

 pkg_emp.ins_emp (
    p_empno              => p_empno,
    p_ename              => p_ename,
    p_job                => p_job,
    p_mgr                => p_mgr,
    p_hiredate           => p_hiredate,
    p_sal                => p_sal,
    p_comm               => p_comm,
    p_deptno             => p_deptno
 );
end ins_emp;

If you try to add a new employee with job PRESIDENT, you will get an error:

Error in mru internal routine: ORA-20001: Error in MRU: row= 1, ORA-20000: There can only be one president ORA-06512: at "HR.PKG_EMP_BR", line 53 ORA-06512: at "HR.IIR_EMP_V", line 2 ORA-04088: error during execution of trigger 'HR.IIR_EMP_V', insert into "HR"."EMP_V" ( "EMPNO", "ENAME", "JOB", "MGR", "SAL", "COMM", "DEPTNO") values ( :b1, :b2, :b3, :b4, :b5, :b6, :b7)

If the EMP table changes, the Apex package can be regenerated without problems. Your custom package will have to be modified accordingly.