Whats this all about?
In a SaaS model, APEX provides an apex_session.set_tenant_id procedure to set a context. This context can be accessed by your VPD in determining the rows the user can access.
However with REST, I have some questions...
How do you securely instruct REST to use the VPD the credentials are associated with?
How do you know which client provided the OAuth2 Token?
How can you call a piece of PL/SQL to set the VPD when the REST Handler is a Collection Query which is effectively just a piece of SQL
Jon built a fantastic this blog on Building Multi-Tenant APEX Apps. However I want to take the example he provided a couple of steps further by implementing a VPD.
Once implemented, I then extend the REST service to identify the client credentials.
I do this by using an ORDS Pre-Hook. This is a piece of code that is ran before all REST Handlers. Once the Pre-Hook determines the tenant, I can implement a VPD
In this blog, I'll do a bit of setup, and then crack on with the VPD/REST Examples
Example Setup
I'm intentionally whizzing through the Example Setup steps as the detail will be in the subsequent sections
Go to SQL Workshop > SQL Scripts > Create
Paste the huge DB Script from the Appendix at the end of this Article and run it
Click Create Application button accepting all the defaults
Click Shared Components > Authentication Schemes > Create > Next
Click Stay on Page checkbox.
Create an Open Door Authentication Scheme (this is a really basic one to accept a username only)
Add this to the source section. This sets the tenant associated with the user.
PROCEDURE post_authentication IS BEGIN FOR x in ( SELECT * FROM cndemo_tenant_users WHERE user_name = :APP_USER) LOOP apex_session.set_tenant_id( x.tenant_id ); END LOOP; END;Add this to the Post-Authentication Procedure Name
post_authenticationIt should look like this
Click Apply Changes
Click Make Authentication Scheme Default
VPD Setup
Credits toOracle-Base
Create the package specification
CREATE OR REPLACE PACKAGE vpd_pkg AS FUNCTION tenant_insert_security(owner VARCHAR2, objname VARCHAR2) RETURN VARCHAR2; FUNCTION tenant_select_security(owner VARCHAR2, objname VARCHAR2) RETURN VARCHAR2; END vpd_pkg; /Create the package body.
create or replace PACKAGE BODY vpd_pkg IS FUNCTION tenant_select_security(owner VARCHAR2, objname VARCHAR2) RETURN VARCHAR2 IS predicate VARCHAR2(2000); BEGIN predicate := '1=2'; IF (SYS_CONTEXT('USERENV','SESSION_USER') = 'E3' AND SYS_CONTEXT('APEX$SESSION','APP_SESSION') IS NULL ) THEN predicate := NULL; ELSE predicate := 'TENANT_ID = SYS_CONTEXT(''APEX$SESSION'',''APP_TENANT_ID'')'; END IF; RETURN predicate; END tenant_select_security; FUNCTION tenant_insert_security(owner VARCHAR2, objname VARCHAR2) RETURN VARCHAR2 IS BEGIN RETURN tenant_select_security(owner, objname); END tenant_insert_security; END vpd_pkg; /Apply the polices to the knowledge table (change E3 to your schema name)
BEGIN DBMS_RLS.add_policy('E3', 'CNDEMO_KNOWLEDGE_ARTICLES', 'TENANT_INSERT_POLICY', 'E3', 'VPD_PKG.TENANT_INSERT_SECURITY', 'INSERT', TRUE); DBMS_RLS.add_policy('E3', 'CNDEMO_KNOWLEDGE_ARTICLES', 'TENANT_SELECT_POLICY', 'E3', 'VPD_PKG.TENANT_SELECT_SECURITY', 'SELECT'); END; /
Test the APEX App & VPD
Run the APEX Application
Log in as DFARKE (LUF Tenant)
Navigate to the Knowledge Articles
You should see 4 Articles for DFARKE
Logout / Login as SWEAVER (HGT Tenant)
You should see 6 Articles for SWEAVER
Create and Protect REST Services
Click on SQL Workshop > RESTful Services
Create a Module like this
Create a Template like this
Create a Handler like this
Here is the code
select * from CNDEMO_KNOWLEDGE_ARTICLESGive it a quick test and you'll see records
(Optional) Click on Roles and create two roles, COACH and MANAGER
Click on Privileges and create a privilege like this
Testing the endpoint again should now be Unauthorized because the module has now been protected.
Create two Clients, one for each Tenant, by running the below
BEGIN OAUTH.create_client( p_name => 'LUF_CLIENT', p_grant_type => 'client_credentials', p_owner => 'LUF Tenant', p_description => 'A client for LUF Tenant', p_support_email => 'dfarke@example.com', p_privilege_names => 'cndemo_priv' ); OAUTH.create_client( p_name => 'HGT_CLIENT', p_grant_type => 'client_credentials', p_owner => 'HGT Tenant', p_description => 'A client for HGT Tenant', p_support_email => 'sweaver@example.com', p_privilege_names => 'cndemo_priv' ); COMMIT; END; /Now fetch the clients and note down the credentials.
SELECT id, name, client_id, client_secret FROM user_ords_clients
Add a new column to the cndemo_tenants table to associate the tenant with the newly created Client ID
alter table CNDEMO_TENANTS add (CLIENT_ID VARCHAR2(32));Update the table
UPDATE cndemo_tenants t SET t.client_id = ( SELECT c.client_id FROM user_ords_clients c WHERE c.name = t.tenant_code || '_CLIENT' )Create an ORDS Pre-hook package spec
create or replace package ords_prehook_pkg as FUNCTION pre_hook RETURN BOOLEAN; END ords_prehook_pkg; /Create an ORDS Pre-hook package body
Important Note: As pointed out in the comments section... to mimic the APEX Application's method of setting the context used by the VPD (i.e by using
apex_session.set_tenant_id), REST must first create an APEX session first. This is a prerequisite ofapex_session.set_tenant_idwhich is not stated in the documentation. APEX differs, as it has already formed a session, that is why its ran in the post authentication section. However REST never forms an APEX session, so its performed specifically below. Creating an APEX session for each and every REST call adds a significant overhead to REST calls. This blog specifically adheres to the "standard approach" of usingapex_session.set_tenant_idconsistently across APEX and REST. Unless this prerequisite is dropped by a future version of APEX, I would recommend, for performance, when implementing a APEX & REST on SaaS approach, using a custom context instead ofapex_session.set_tenant_idthus avoiding the overhead of creating an APEX session for each and every REST call.create or replace package body ords_prehook_pkg as FUNCTION pre_hook RETURN BOOLEAN AS l_client_id user_ords_clients.client_id%TYPE DEFAULT NULL; l_tenant_id cndemo_tenants.id%TYPE DEFAULT NULL; BEGIN -- Fetch Remote Identity BEGIN l_client_id := OWA_UTIL.get_cgi_env('REMOTE_IDENT'); EXCEPTION WHEN OTHERS THEN -- Log No Client ID / Deal with Identless Calls per URL NULL; END; FOR x_client IN ( SELECT t.id tenant_id, c.id client_obj_id FROM user_ords_clients c, cndemo_tenants t WHERE c.client_id = t.client_id AND c.client_id = l_client_id ) LOOP l_tenant_id := x_client.tenant_id; IF l_tenant_id IS NOT NULL THEN apex_session.create_session ( p_app_id => 100, p_page_id => 1, p_username => 'EXAMPLE USER' ); apex_session.set_tenant_id( l_tenant_id ); END IF; END LOOP; RETURN TRUE; EXCEPTION WHEN OTHERS THEN -- Log RETURN FALSE; END pre_hook; end ords_prehook_pkg; /Run these grants
GRANT EXECUTE ON ords_prehook_pkg TO PUBLIC;The following steps will add the pre-hook to ORDS. But first we need to stop ORDS. The following ORDS instructions are based on my Oracle 23c Free Docker, APEX & ORDS all in one simple guide. If you're using something different then please adjust the commands accordingly
sh /home/oracle/scripts/stop_ords.shEdit the settings.xml of ORDS
nano /etc/ords/config/global/settings.xmlAdd the following entry (change E3 to your schema name)
<entry key="procedure.rest.preHook">e3.ords_prehook_pkg.pre_hook</entry>Ctrl+X to exit and save
Start ORDS
sh /home/oracle/scripts/start_ords.sh
Testing
Fetch a token with the LUF Client Credentials by concatenating the user and password together with a colon
TOKEN=$(echo -n "[user]:[password]" | base64) curl -X POST \ https://your-url/ords/leeds/oauth/token \ -H "Authorization: Basic $TOKEN" \ -H 'Content-Type: application/x-www-form-urlencoded' \ -d 'grant_type=client_credentials'Call it with the token
Success 4 records for LUF
Lets grab a token for the HGT client credentials and repeat the steps
Success 6 records for the HGT client credentials
Summary
Using a Pre-hook, we can determine the Client ID of the credentials used. We already associate the Client ID with the Tenant. Therefore we can set the Context based on the Tenant. Once the Context is set, the VPD does its magic.
Picture
Whats the Picture? It's the incredibly beautiful Thruscross Reservoir. Visit Yorkshire!
Appendix
DB Script
-- create tables
create table cndemo_tenants (
id number generated by default on null as identity
constraint cndemo_tenants_id_pk primary key,
tenant_code varchar2(4 char)
constraint tenants_tenant_code_unq unique not null,
tenant_name varchar2(50 char) not null,
created date not null,
created_by varchar2(255 char) not null,
updated date not null,
updated_by varchar2(255 char) not null
);
create table cndemo_tenant_users (
id number generated by default on null as identity
constraint cndemo_tenant_users_id_pk primary key,
tenant_id number
constraint cndemo_tenant_users_tenant_id_fk
references cndemo_tenants,
user_name varchar2(30 char)
constraint tenant_users_user_name_unq unique not null,
display_name varchar2(50 char) not null,
created date not null,
created_by varchar2(255 char) not null,
updated date not null,
updated_by varchar2(255 char) not null
);
-- table index
create index cndemo_tenant_users_i1 on cndemo_tenant_users (tenant_id);
create table cndemo_knowledge_articles (
id number generated by default on null as identity
constraint cndemo_knowledge_articles_id_pk primary key,
tenant_id number
constraint cndemo_knowledge_articles_tenant_id_fk
references cndemo_tenants,
title varchar2(90 char) not null,
content clob,
created date not null,
created_by varchar2(255 char) not null,
updated date not null,
updated_by varchar2(255 char) not null
);
-- table index
create index cndemo_knowledge_articles_i1 on cndemo_knowledge_articles (tenant_id);
-- triggers
create or replace trigger cndemo_tenants_biu
before insert or update
on cndemo_tenants
for each row
begin
if inserting then
:new.created := sysdate;
:new.created_by := coalesce(sys_context('APEX$SESSION','APP_USER'),user);
end if;
:new.updated := sysdate;
:new.updated_by := coalesce(sys_context('APEX$SESSION','APP_USER'),user);
end cndemo_tenants_biu;
/
create or replace trigger cndemo_tenant_users_biu
before insert or update
on cndemo_tenant_users
for each row
begin
if inserting then
:new.created := sysdate;
:new.created_by := coalesce(sys_context('APEX$SESSION','APP_USER'),user);
end if;
:new.updated := sysdate;
:new.updated_by := coalesce(sys_context('APEX$SESSION','APP_USER'),user);
end cndemo_tenant_users_biu;
/
create or replace trigger cndemo_knowledge_articles_biu
before insert or update
on cndemo_knowledge_articles
for each row
begin
if inserting then
:new.created := sysdate;
:new.created_by := coalesce(sys_context('APEX$SESSION','APP_USER'),user);
end if;
if :new.tenant_id is null then
:new.tenant_id := SYS_CONTEXT('APEX$SESSION', 'APP_TENANT_ID');
end if;
:new.updated := sysdate;
:new.updated_by := coalesce(sys_context('APEX$SESSION','APP_USER'),user);
end cndemo_knowledge_articles_biu;
/
-- load data
insert into cndemo_tenants (
id,
tenant_code,
tenant_name
) values (
1,
'LUF',
'Leeds United'
);
insert into cndemo_tenants (
id,
tenant_code,
tenant_name
) values (
2,
'HGT',
'Harrogate Town'
);
commit;
alter table cndemo_tenants
modify id generated always as identity restart start with 3;
insert into cndemo_tenant_users (
id,
tenant_id,
user_name,
display_name
) values (
1,
1,
'DFARKE',
'Daniel Farke'
);
insert into cndemo_tenant_users (
id,
tenant_id,
user_name,
display_name
) values (
2,
2,
'SWEAVER',
'Simon Weaver'
);
commit;
alter table cndemo_tenant_users
modify id generated always as identity restart start with 3;
INSERT INTO cndemo_knowledge_articles (tenant_id, title, content) VALUES (1, 'Inverting the Pyramid: The History of Football Tactics', 'This book by Jonathan Wilson explores the evolution of football tactics from its earliest days to the modern era, delving into the strategic shifts and innovations that have shaped the game.');
INSERT INTO cndemo_knowledge_articles (tenant_id, title, content) VALUES (1, 'Fever Pitch', 'Nick Hornby''s memoir captures the essence of being a football fan, reflecting on the emotional highs and lows experienced while supporting a club through the lens of his own lifelong devotion to Arsenal.');
INSERT INTO cndemo_knowledge_articles (tenant_id, title, content) VALUES (1, 'The Miracle of Castel di Sangro', 'Journalist Joe McGinniss recounts the remarkable true story of an Italian football team from a small town that defied the odds by reaching Serie B, providing a gripping narrative of passion, perseverance, and the power of sport.');
INSERT INTO cndemo_knowledge_articles (tenant_id, title, content) VALUES (1, 'Soccernomics', 'Simon Kuper and Stefan Szymanski explore the intersection of football and economics, using data analysis to uncover insights into the business, politics, and culture of the beautiful game.');
INSERT INTO cndemo_knowledge_articles (tenant_id, title, content) VALUES (2, 'The Ball is Round: A Global History of Football', 'David Goldblatt presents a comprehensive history of football, tracing its origins and development across continents and cultures, offering a rich tapestry of stories that illuminate the sport''s global impact.');
INSERT INTO cndemo_knowledge_articles (tenant_id, title, content) VALUES (2, 'Brave New World: Inside Pochettino''s Spurs', 'Journalist Guillem Balague provides an inside look into Mauricio Pochettino''s tenure as manager of Tottenham Hotspur, offering insights into his innovative tactics, leadership style, and the challenges of building a competitive team.');
INSERT INTO cndemo_knowledge_articles (tenant_id, title, content) VALUES (2, 'Barça: The Making of the Greatest Team in the World', 'Graham Hunter explores the rise of FC Barcelona under the management of Pep Guardiola, chronicling their dominance in both domestic and international football through the lens of tactical brilliance, youth development, and a distinct playing style.');
INSERT INTO cndemo_knowledge_articles (tenant_id, title, content) VALUES (2, 'Living on the Volcano: The Secrets of Surviving as a Football Manager', 'Michael Calvin interviews football managers across the English leagues, offering a revealing insight into the pressures, challenges, and strategies involved in one of the most demanding roles in the sport.');
INSERT INTO cndemo_knowledge_articles (tenant_id, title, content) VALUES (2, 'Das Reboot: How German Football Reinvented Itself and Conquered the World', 'Raphael Honigstein examines the transformation of German football from the depths of failure to World Cup success, tracing the reforms, innovations, and cultural shifts that revitalized the national team and domestic football.');
INSERT INTO cndemo_knowledge_articles (tenant_id, title, content) VALUES (2, 'The Nowhere Men', 'Michael Calvin delves into the world of football scouting, profiling the unsung heroes who travel far and wide in search of the next generation of talent, offering a fascinating glimpse into the intricate process of player recruitment.');
commit;
alter table cndemo_knowledge_articles
modify id generated always as identity restart start with 11;