Introduction

In this post, I will describe how you can leverage the apex_session.set_tenant_id PL/SQL API and the APEX$SESSION Application Context to build Multi-Tenenat APEX Applications.

Note: If you are using the Enterprise Edition of the Oracle Database, you can achieve the same goal using a Virtual Private Database (VPD).

Multi-Tenant Architecture

Multi-tenant architecture is a design approach that enables multiple user groups, referred to as tenants, to access one instance of an application or system. Each tenant is logically separated, meaning that a single instance of the software will run on one server and serve multiple tenants.

Example Application

For this post, I will use the example of a simple knowledge-sharing application. Companies can sign up for the App to facilitate sharing knowledge throughout their organization. To keep costs low, I have decided to implement the solution as a multi-tenant Application. This means one APEX Application is used, and all data will be stored in one set of tables. Logged-in users should only be able to access knowledge articles that belong to their company or tenant. I will use the terms company and tenant interchangeably for the remainder of this post.

Data Model

The core of the application is a table called knowledge_articles. You will notice the column tenant_id, which will be used to separate records by tenant.

create table knowlege_articles (
    id         number generated by default on null as identity 
               constraint knowlege_articles_id_pk primary key,
    tenant_id  number
               constraint knowlege_articles_tenant_id_fk
               references tenants on delete cascade,
    title      varchar2(50 char) not null,
    content    clob,
    created    timestamp with local time zone not null,
    created_by varchar2(255 char) not null,
    updated    timestamp with local time zone not null,
    updated_by varchar2(255 char) not null);

create or replace trigger knowlege_articles_biu
    before insert or update 
    on knowlege_articles
    for each row
begin
    if :new.tenant_id is null then
        :new.tenant_id := 0;
    end if;
    if inserting then
        :new.created := localtimestamp;
        :new.created_by := coalesce(sys_context('APEX$SESSION','APP_USER'),user);
    end if;
    :new.updated := localtimestamp;
    :new.updated_by := coalesce(sys_context('APEX$SESSION','APP_USER'),user);
end knowlege_articles_biu;

Note: We will come back to the line of code :new.tenant_id := 0; in the trigger later in the post.

The above scripts were generated using Quick SQL. Quick SQL has a setting that automates the addition of a tenant_id column in table definitions and includes a stub to set the tenant_id in the table trigger. The screenshot below shows this setting, as well as the Quick SQL representing all the tables in the example application.

Setting APP_TENANT_ID

To be able to only query records for the tenant the currently logged-in user belongs to, we need to set the current tenant as soon as the user logs in. This is done by calling the PL/SQL API apex_session.set_tenant_id.

A good place to call this API is from the Post-Authentication Procedure in the Authentication Scheme:

• After the user has logged in, APEX calls the procedure identified in the Post-Authentication Procedure Name property of the Authentication Scheme. In the above example, the Post-Authentication Procedure Name is called post_authentication.

• The source code for the procedure can be a PL/SQL package.procedure in the parsing schema or a procedure defined in the Source > PL/SQL Code property.

  • Note: I recommend that you include this code in a package.procedure. It was easier for me to include the code in the PL/SQL Code property to illustrate the example.

• In the above example, the code in the post_authentication procedure fetches the tenant_id for the logged-in user (identified by the Substitution String :APP_USER), and then calls APEX_SESSION.SET_TENANT_ID to set the built-in APP_TENANT_ID with that tenant_id.

• Once set for a session, you can not set the tenant ID again. If you call a APEX_SESSION.SET_TENANT_ID a second time in the same session, the exception WWV_FLOW_SESSION_API.TENANT_ID_EXISTS will be raised.

Getting APP_TENANT_ID

One set, for the remainder of the session, the value in APP_TENANT_ID can be obtained from the Application Context APEX$SESSION as follows:

-- Get the Current Tenant ID from a SQL Query
SELECT SYS_CONTEXT('APEX$SESSION', 'APP_TENANT_ID')
FROM   sys.dual;

-- Filter a SQL Query using the Tenant ID
SELECT title, content
FROM   cndemo_knowlege_articles
WHERE  tenant_id = SYS_CONTEXT('APEX$SESSION', 'APP_TENANT_ID');

-- Reference the Current Tenant ID from PL/SQL
DECLARE
  l_tenant_name  cndemo_tenants.tenant_name%TYPE;
BEGIN
  SELECT tenant_name
  FROM   cndemo_tenants
  WHERE  id = SYS_CONTEXT('APEX$SESSION', 'APP_TENANT_ID');
END;

See Jeff Kemp's post for more on the Application Context APEX$SESSION.

See this post from Hari for more details on why using SYS_CONTEXT is more performant than using the V function.

Let's go back to the trigger for the trigger knowlege_articles_biu specified earlier in the post. We can now change the trigger to reference the current tenant ID as follows:

-- Change:
if :new.tenant_id is null then
  :new.tenant_id := 0;
end if;
-- To:
if :new.tenant_id is null then
  :new.tenant_id := SYS_CONTEXT('APEX$SESSION', 'APP_TENANT_ID');
end if;

Whenever records are added to these multi-tenant tables, they will now automatically get tagged with the Tenant of the currently logged-in user.

Secured Views

The primary use of the APP_TENANT_ID Application Context variable is to ensure that users that belong to one Tenant cannot see records that belong to another tenant.

Instead of referencing SYS_CONTEXT('APEX$SESSION', 'APP_TENANT_ID') in the WHERE clause of all of the SQL in your reports, charts, list of values, etc. (and risk missing it somewhere), you can create secured views like the following.

CREATE OR REPLACE VIEW cndemo_knowlege_articles_v AS
SELECT id
,      title
,      content
FROM   cndemo_knowlege_articles
WHERE  tenant_id = SYS_CONTEXT('APEX$SESSION', 'APP_TENANT_ID');

These views can then be used throughout your APEX Application and PL/SQL code. If you always use the secured view, there is no chance users from one tenant can see records from another tenant.

Debugging

What if you need to run queries against these secured views from SQL Developer to debug an issue? Without setting the application context, you would never get any records returned. There are a couple of options:

Option 1 - Attach to an APEX Session

The best option is to sign in to the APEX Application as a user in the same tenant and then use the apex_session.attach PL/SQL API to attach to the session. This procedure sets the environment and runs the Initialization PL/SQL Code based on the given application and current session. You can now query the secured views and see the same results the logged-in user would see.

SELECT COUNT(1)
FROM   cndemo_knowlege_articles_v;
-- 0 Rows Returned

-- Attach to an APEX Session
EXEC apex_session.attach(p_app_id => 101, p_page_id => 1, p_session_id => 115310927172254);

SELECT COUNT(1)
FROM   cndemo_knowlege_articles_v;
-- Rows now returned from view.

Option 2 - Set the Application Context

A less preferred option is to call apex_session.set_tenant_id directly:

SELECT COUNT(1)
FROM   cndemo_knowlege_articles_v;
-- 0 Rows Returned

-- Set Application Context.
EXEC apex_session.set_tenant_id (p_tenant_id => 2);

SELECT COUNT(1)
FROM   cndemo_knowlege_articles_v;
-- Rows now returned from view.

Conclusion

In this post, I showed you how you can leverage the apex_session.set_tenant_id PL/SQL API and the APEX$SESSION Application Context to stripe your APEX applications by tenant.

Of course, you don't have to use this API to set a tenant ID. You could populate it with any value that you need to access for the duration of the session. This is particularly useful when you need to access the value in a performant way from within views.

🔗 Read More

- 🩳 APEX Shorts

- ✍️ APEX Posts