Convert an APEX Application to Multi-Tenant

So you’ve built an APEX application to solve a problem for one client, or one department, or just yourself – and you think it might be useful for others as well. How do you make that application available for other users, departments, or companies to reuse, while ensuring each sees only their own data and cannot mess around with others’ data?

Architecting a Multi-Tenant Application

To make your application multi-tenant you have a few options.

Option #1. Copy the application to another workspace/schema, another Pluggable Database (in Oracle 12c+) or another database server entirely.

Option #2. Modify your data model to allow completely independant sets of data to co-exist in the same physical tables (e.g. a security_group_id column that allows the database to discriminate data for each tenant).

The desirable properties of a multi-tenant system are as follows:

a. Tenant isolation – no tenant sees data for another tenant; no tenant can affect the app’s behaviour for another tenant; protect against “noisy neighbours” (i.e. those which impact system capacity and performance).

“Hadlow’s first law of multi-tenancy: A multi-tenanted application should not look like a multi-tenanted application.”

b. Administration – ability to backup/recover all data for a single tenant; ability to give a degree of control to each tenant (self service).

c. Maintainability – simplicity of deploying enhancements and bug fixes for all tenants, or for one tenant at a time (e.g. rolling upgrades).

d. Scalability – ability to easily add more tenants, ability to add more capacity for more tenants.

Some of these properties are more easily and effectively achieved with option #1 (separate servers or schemas for each tenant), such as Isolation and Administration. Other properties are more easily and effectively achieved with option #2 (discriminator column) such as Maintainability and Scalability. This is a gross generalisation of course; there are many solutions to this design problem each with many pros and cons.

Some inspiration may be gained from examining how Oracle Application Express achieves this goal: multi-tenant has been baked into the product, via its concept of Workspaces. Each tenant can be given their own workspace in APEX and are able to build and deploy applications in isolation from other workspaces. Internally, APEX maintains a unique security_group_id for each workspace. This works very well – a single Oracle database instance can serve thousands or tens of thousands of workspaces.

It should be noted that a benefit of pursuing Option #2 is that it does not necessarily preclude using Option #1 as well, should the need arise later on – for example, to provide more capacity or better performance in the presence of more demanding tenants. For this reason, plus the fact that it’s much easier to maintain and enhance an application for all users at once if they’re colocated, I prefer Option #2.

Note: all sample code for this article may be found here [github.com/jeffreykemp/sample/tree/master/multitenant].

Security Data Model

The core element to implementing a single-schema multi-tenant application is the addition of a tenant identifier added to most tables in the data model. A common convention for this column is to be a surrogate key ID named security_group_id. This ID must refer to something so we need a simple data model for security groups:

First, we need a row to represent each tenant in a table that has security_group_id as its primary key:

create sequence security_group_id_seq;

create table security_groups
( security_group_id number not null
, name varchar2(100) not null
, active_ind varchar2(1) default 'Y'
);

Next, whenever someone logs into the application it must identify what their security group ID is; in order to allow a single user to access more than one tenant (i.e. so they don’t need to create new logins for each tenant, if they work for more than one client or department) we will design a table that will allow a user to be a member of any number of security groups:

create table security_group_members
( security_group_id number not null
, app_user varchar2(200) not null
, last_login date
, active_ind varchar2(1) default 'Y'
);

The last_login will be updated whenever the user logs in; we will also use it to automatically select their most recent group when they login. The active_ind attribute will be used if we want to temporarily (or permanently) restrict someone from a group, without having to actually delete the record.

After they login, we might want to assign different roles to different users – e.g. we might want an “Administrator” role who can see and update everything in the system; and we might want to give a “Readonly” or reporting role to someone who only needs to see the data but are not allowed to make any updates. To support this, we need two tables – one to represent each type of role, and one to indicate for each member what role(s) they have for a particular security group.

create table security_roles
( role_code varchar2(100) not null
, name varchar2(100) not null
, active_ind varchar2(1) default 'Y'
);

create table security_group_roles
( security_group_id number not null
, app_user varchar2(200) not null
, role_code varchar2(100) not null
, active_ind varchar2(1) default 'Y'
);

The full DDL for these tables, including all the constraints, may be viewed here: security_tables.sql.

We have an active_ind attribute on both these tables – this way we can deactivate a role for a single user, or for all users at one go.

Note: optimising this data model with appropriate indexes has been left as an exercise for the reader 🙂

We can prime the tables with some basic details like this:

insert into security_roles (role_code,name) values ('ADMIN','Admin');
insert into security_roles (role_code,name) values ('EDITOR','Editor');
insert into security_roles (role_code,name) values ('READONLY','Readonly');

insert into security_groups (security_group_id,name)
  values (security_group_id_seq.nextval,'Default');

insert into security_group_members (security_group_id,app_user)
  values (security_group_id_seq.currval,'JEFF');

insert into security_group_roles (security_group_id,app_user,role_code)
  values (security_group_id_seq.currval,'JEFF','ADMIN');

We need a database package to setup an APEX session using the data in these tables.

create or replace package security_pkg is

procedure init
  (app_user          in varchar2 := null
  ,security_group_id in number := null
  ,apex              in boolean := true
  );

end security_pkg;
/

The init procedure is called after a user is authenticated. If app_user is null it uses v('APP_USER'). It queries the relevant tables to determine if the user is active for the selected security group, or, if that parameter is left null, it gets the security group they most recently logged in to. The intention is that later we will give them the ability to “switch” to another security group within the app, if they have access to multiple tenants.

The full source code for the package may be viewed here: security_pkg.sql. The important procedure at this point is init.

create or replace package body security_pkg is

procedure sctx
  (attr in varchar2
  ,val  in varchar2
  ,apex in boolean) is
begin
  if apex then
    dbms_session.set_context
      (namespace => 'CTX'
      ,attribute => attr
      ,value     => val
      ,client_id => v('APP_USER') || ':' || v('SESSION'));
  else
    dbms_session.set_context
      (namespace => 'CTX'
      ,attribute => attr
      ,value     => val
      ,username  => user);
  end if;
end sctx;

procedure init
  (app_user          in varchar2 := null
  ,security_group_id in number := null
  ,apex              in boolean := true
  ) is
  cursor c
    (security_group_id in number
    ,app_user          in varchar2
    ) is
    select x.*
    from   security_group_members x
    join   security_groups g
    on     g.security_group_id = x.security_group_id
    where  x.app_user = c.app_user
    and    (x.security_group_id = c.security_group_id
            or c.security_group_id is null)
    and    x.active_ind = 'Y'
    and    g.active_ind = 'Y'
    order by x.last_login desc nulls last;
  r c%rowtype;
begin
  open c
    (security_group_id => security_group_id
    ,app_user          => coalesce(app_user, v('APP_USER'))
    );
  fetch c into r;
  close c;

  sctx('APP_USER', r.app_user, apex);
  sctx('SECURITY_GROUP_ID', r.security_group_id, apex);

  if apex
  and r.app_user is not null
  and r.security_group_id is not null then
    update security_group_members m
    set    last_login = sysdate
    where  m.security_group_id = r.security_group_id
    and    m.app_user = r.app_user;
  end if;

end init;

end security_pkg;
/

This package uses a globally accessible context (created by a user with CREATE ANY CONTEXT system privilege):

create context ctx using myschema.security_pkg accessed globally;

Since we set the client_id in the call to dbms_session.set_context that matches that set by APEX for each session, the context values will be automatically loaded for each database call from a valid APEX session. We can then use very efficient calls to sys_context('CTX','xyz') throughout the application’s PL/SQL and SQL, including views.

If we need to run code directly on the database, we can set up the security context for the session by calling security_pkg.init with apex=>false. This uses the username parameter of dbms_session.set_context instead of client_id (reference).

Side note: in APEX 5+ you might consider using APEX’s context to get APP_USER; however, this has the disadvantage that all database procedures that need to interact with the data must first set up an APEX session before they can work. The method I’ve used here allows batch jobs, for example, to simply call security_pkg.init with the user and security group ID directly.

APEX Authentication Scheme

We need to get APEX to set up the user’s context after they login; to do this we simply add the following call to the Post-Authentication Procedure Name attribute of the application’s current authentication scheme:

schemaname.security_pkg.init

For more details on how Oracle application contexts work within APEX, refer to my earlier article, “APEX and Application Contexts”.

APEX Authorization Schemes

This step is not strictly part of the multi-tenant feature; I’ve included this step here as part of the implementation of the role-based security system we have created.

We can allow/disallow access to features of the APEX application according to the user’s roles in our security model, by creating Authorization Schemes. For example, we can create one to restrict features to Administrators only with a scheme like this:

Scheme Type = PL/SQL Function Returning Boolean
PL/SQL Function Body = return security_pkg.user_has_role('ADMIN');

I will typically create authorization schemes for each role; sometimes I’ll create a scheme that combines conditions on multiple roles (e.g. if a feature needs to be visible to more than one role).

The source code for the user_has_role function may be found in security_pkg.sql.

Discriminator Column

Now that the security data model is in place, we shall add the security_group_id column to all the tables in the application’s data model. In some cases you might decide to just add the column to the core tables of the data model; however, it’s more convenient to standardise and add it to all tables. You will find, however, that it is a requirement for any tables with any natural keys (i.e. non-surrogate primary keys, or unique constraints on user-entered data) because these will need to have the discriminator column added later on.

1. Set up a session

exec security_pkg.init('JEFF',apex=>false);

2. Add security_group_id column to the table

alter table mytable
  add security_group_id integer
  default sys_context('CTX','SECURITY_GROUP_ID') not null;

alter table mytable
  add constraint mytable$secfk
  foreign key (security_group_id)
  references security_group (security_group_id);

In my security package I have defined a simple VPD policy that will apply to all my tables. It returns a single, static string which will be added to any queries on the tables, to ensure that each user will only see rows for their security group.

function vpd_policy
  (object_schema in varchar2
  ,object_name in varchar2
  ) return varchar2 is
begin
  return q'[
    security_group_id = sys_context('CTX','SECURITY_GROUP_ID')
    ]';
end vpd_policy;

Call dbms_rls.add_policy for each table:

begin
  dbms_rls.add_policy
    (object_name     => 'MYTABLE'
    ,policy_name     => 'vpd_policy'
    ,policy_function => 'security_pkg.vpd_policy'
    ,update_check    => true
    ,static_policy   => true);
end;

I set update_check to true to ensure that users cannot try to override the security group ID with any insert or update. Since the policy function returns the same string regardless of which user or session is executing it, I’ve also set static_policy to true which may give a small performance benefit.

Poor Man’s VPD

If you don’t have the Enterprise Edition of Oracle you might need to consider an alternative method – “Poor Man’s VPD”. This generally entails restricting access to all base tables (e.g. moving them to another schema) and only allowing queries and DML via a view for each table which incorporates the necessary predicate on security_group_id.

I’ve used the following procedure to convert a table to a view; it renames the table to add a suffix ($B) and creates a view (minus the security_group_id) on the same table, along with the necessary predicate, plus the with check option for good measure.

procedure table2view (table_name in varchar2) is
  l_table_name varchar2(30);
  l_columns varchar2(32767);
begin
  l_table_name := upper(substr(table_name,1,30-length('$B')) || '$B');
  begin
    execute immediate 'alter table ' || table_name
      || ' rename to ' || l_table_name;
  exception
    when others then
      -- if the table doesn't exist, it was probably
      -- already converted; so we don't error out
      -- and recreate the view
      if sqlcode!=-942 then
        raise;
      end if;
  end;
  for r in (select column_name 
            from   user_tab_columns 
            where  table_name = table2view.l_table_name
            and    column_name != 'SECURITY_GROUP_ID'
            order by column_id) loop
    if l_columns is not null then
      l_columns := l_columns || ',';
    end if;
    l_columns := l_columns || 'x.' || lower(r.column_name); 
  end loop;
  execute immediate replace(replace(replace(q'[
    create or replace force view #VIEW#
    as select #COLUMNS#
       from #TABLE# x
       where x.security_group_id = sys_context('CTX','SECURITY_GROUP_ID')
       with check option
    ]','#VIEW#',table_name)
      ,'#COLUMNS#',l_columns)
      ,'#TABLE#',l_table_name);
end table2view;

Source: table2view.sql

To stop users from inserting or updating the security_group_id column, I’ve used a trigger that checks the before and after values on each insert, update and delete:

create trigger MYTABLE$TRG
before insert or update or delete on MYTABLE
for each row
begin
  if updating or deleting then
    security_pkg.validate
      (security_group_id => :old.security_group_id);
  end if;
  if inserting then
    :new.security_group_id := sys_context('CTX','SECURITY_GROUP_ID');
  end if;
  if inserting or updating then
    security_pkg.validate
      (security_group_id => :new.security_group_id);
  end if;
end MYTABLE$TRG;

I wrote a simple procedure to generate this trigger for each table, after adding the security_group_id column. Source: convert2multitenant.sql

Unique Constraints

There is one more hurdle to jump for this solution to work: uniqueness constraints. If one tenant tries to insert or update a row that collides with a value already committed for a different tenant, the uniqueness constraint will trap them. Therefore, we need to add security_group_id to all unique constraints (and any unique indexes as well, if any were created independently of any constraint). We would probably make an exception for surrogate keys, since these should already be generated by a globally unique method and should not be updatable by users. These could still cause a problem in the case that a tenant needs to import data from a different database instance, if any keys happen to collide; this may be a good argument for using GUIDs for generating surrogate keys instead of a sequence.

To convert the unique constraints to include security_group_id I’ve used the following procedure. If it is called with just the table name, it will attempt to convert all Unique constraints on the table. If it is called with the name of a particular constraint, it will attempt to convert just that one constraint (I use this for primary key constraints that need to be converted).

procedure fix_unique_constraint
  (table_name in varchar2
  ,constraint_name in varchar2 := null) is
  l_ddl varchar2(32767);
begin
  for r in (
    select c.table_name, c.constraint_name
          ,decode(c.constraint_type
                 ,'U','unique'
                 ,'P','primary key') as constraint_type
          ,(select listagg('"'||cc.column_name||'"',',')
                   within group (order by position)
            from   user_cons_columns cc
            where  cc.constraint_name = c.constraint_name
            and    cc.table_name = c.table_name
            and    cc.column_name != 'SECURITY_GROUP_ID'
           ) as column_list
    from user_constraints c
    where c.table_name = fix_unique_constraint.table_name
    and ((fix_unique_constraint.constraint_name is null
           and c.constraint_type = 'U')
         or c.constraint_name = fix_unique_constraint.constraint_name)
    ) loop

    -- drop the constraint

    l_ddl := 'alter table "' || r.table_name
      || '" drop constraint "' || r.constraint_name || '"';

    dbms_output.put_line(l_ddl);
    execute immediate l_ddl;

    -- recreate the constraint

    l_ddl := 'alter table "' || r.table_name
      || '" add constraint "' || r.constraint_name
      || '" ' || r.constraint_type
      || ' (security_group_id,' || r.column_list || ')';

    dbms_output.put_line(l_ddl);
    execute immediate l_ddl;

  end loop;
end fix_unique_constraint;

Source: fix_unique_constraint.sql

Note: one scenario this procedure fails to cover is where the constraint is based on an index of the same name that was created prior to the constraint; in that case, the constraint will not drop the index, causing the add constraint to fail when it tries to recreate the index.

Referential Constraints

In my case, the data model is replete with surrogate keys based on sequences, so we don’t need to worry about adding security_group_id to the primary key constraints. In cases where we do, we would also need to consider referential constraints. If a foreign key refers to a constraint to which we have just added security_group_id, we need to update the foreign key constraint to include it as well.

Writing this as an automated procedure is left as an exercise for the reader. This process would entail dropping all foreign key constraints that refer to the table, dropping the primary key constraint, recreating the primary key constraint with the addition of security_group_id, and then recreating the foreign keys, adding security_group_id to each.

Summary

This article describes a simple method to take an existing data model in an Oracle schema supporting an Oracle Application Express application and convert it to one that supports multiple tenants. It does this by adding a discriminator column (security_group_id) to each table in the data model, with a referential constraint to a simple user security data model.

We use triggers and Virtual Private Database (either Oracle’s builtin Row Level Security feature, or by rolling our own by hiding the tables behind views) to ensure tenant isolation for queries, inserts, updates and deletes. Only minor changes to the existing codebase and APEX application were required for all of this to work.

I hope this was an interesting read. Your comments or constructive criticism are (as always) welcome.

Further Reading

• “APEX and Application Contexts”
• Oracle 12c Database Security Guide: Virtual Private Database
• Oracle 12c Documentation: DBMS_RLS
• Oracle 12c Documentation: DBMS_SESSION
• Oracle 12.2 Documentation: “Overview of the Multitenant Architecture”
• Tim Hall, Oracle-Base: “Multitenant: Overview of Container Databases (CDB) and Pluggable Databases (PDB)”
• Martin Giffy D’Souza: “Poor Man’s VPD in APEX and Oracle XE”
• Microsoft: “Design Patterns for Multi-tenant SaaS Applications and Azure SQL Database”
• Software Engineering Stack Exchange: “Do multi-tenant DBs have multiple databases or shared tables?”
• Software Engineering Stack Exchange: “Multi-tenancy – single database vs multiple database”
• Mike Hadlow: “Multi-tenancy part 1: Strategy”
• Mike Hadlow: “Multi-tenancy part 2: Components and Context”

Related Slide Deck

One App, Many Clients: Converting an APEX Application to Multi-Tenant (presented at the AUSOUG Connect conference, November 2017)

Jeffrey Kemp 29 November 2017

APEX / apex-5.0 / data-modelling / DBMS_RLS / DBMS_SESSION / multi-tenant / oracle-11g / oracle-12c / oracle-xe / security / SYS_CONTEXT /

About Jeffrey Kemp

Application Designer & Developer at Oracle specialising in Oracle APEX (Application Express), Oracle SQL and PL/SQL. Oracle ACE Alumni ♠️. Piano player, father, husband, Christian. The views expressed on this blog are my own and do not necessarily reflect the views of Oracle.