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.