How do I turn a single-user app into a multi-tenant app?

yaitcon – YET ANOTHER IT CONSULTANT

Task:

Tom, I want to use your app, but only with my data!

MICHAEL

There are a few ways to achieve this (here only 2):

  1. Clone the app in a workspace and mount it on its own Oracle schema, create all the necessary objects (which you can now define right inside Oracle Apex with the install scripts).

    not all sideeffects:
  • Update of the app means more complex app lifecycle management.
  • Maybe other processes have to be adapted which also write to the new database or schema.
  • There might be common data that should be used by all

2. Don’t clone the app and change the data model and customize the APP

not all side effects:

  • This means you have to filter in the Apex App depending on APP_USER or client of a user.
  • Depending on how the data is connected to Regions, IG, IR etc., you might have to make an adjustment in each page.

to point 2:

Here we can proceed very effectively because we can use the option of Oracle VPD-Virtual Private Database. We do not need to customize a single view or Apex SQL + PL/SQL query and that is really a huge advantage.

What was to be done?

In my case I had to extend the APP_USER in the relevant tables (of course not in attached views), I call the column MANDANT:

ALTER TABLE CARLOG_DATA add MANDANT VARCHAR2(100);

Next I had to tell the asynchronous load process to assign the data to the user. That means the DML (Insert , Update, Delete) in this table had to be extended by the column MANDANT. It would also have been possible to use a DML trigger that could assign the data correctly. But this was not an „easier“ option for me so I decided to customize the loading process.

As soon as the data of the other client was in the database, I saw my data aggregated with his in the Apex APP. For example, in this specific case, my car drove 133km instead of 100km that day.

Now the Oracle VPD comes into play:

First, create a function that allows to manage more complex criteria in one place: Here I restrict that the check only takes place if the session is in the context of the Apex application 100.

The APP_USER is my client. Oracle Apex automatically sets the APP_USER to the session context after login. Nice…

create or replace function get_mandant_criteria 
(
 object_schema IN VARCHAR2, 
 object_name VARCHAR2
) 
return varchar2 DETERMINISTIC as
l_criteria varchar2(4000);
begin
  -- Only apply VPD to specific APEX applications
  if sys_context('APEX$SESSION', 'APP_ID') in (100)
  then
   l_criteria:=q'#(MANDANT=sys_context('APEX$SESSION', 'APP_USER'))#';
  end if;

  return l_criteria;
end get_mandant_criteria;
/

The function must of course also be used in the VPD therefore.
Using Oracle Virtual Private Database to Control Data Access

begin
  dbms_rls.add_policy
    (object_schema=>'CARLOG'  
    ,object_name=>'CARLOG_DATA' -- für jede Tabelle anlegen wo gebr.
    ,policy_name=>'CARE_APP_100'
    ,function_schema=>'ADMIN' 
    ,policy_function=>'get_mandant_criteria'
    ,statement_Types=>'SELECT'
    );
end;
/

And already APEX App 100 is filtered to APP_USER client from login time.

Yes! YAITCON

On the subject of multi-tenant and more complex security concepts such as row-level and column-level security, we recommend our SCURTY!

SPHINX.at