Bring back `TENANT_ID` and add first-class multi-tenant scaffolding to QuickSQL (incl. secure views DSL)

[pallasinfotech]

Summary Please restore the ability to generate a TENANT_ID column and add a simple, opinionated way for QuickSQL to scaffold multi-tenant table structures (columns, FKs, indexes, optional RLS helpers), plus a DSL to generate secure views with per-tenant filters.

Why this is needed Multi-tenant apps are a very common APEX pattern. Today, authors hand-add TENANT_ID, wire FKs, build composite uniques, write VPD/RLS boilerplate, and create secure views. This is repetitive, error-prone, and inconsistent across projects. QuickSQL is the perfect place to standardize and accelerate this.

Proposed design (DSL additions)

1. Global toggle

/tenancy on

2. Options (all optional, with sensible defaults)

/tenancy
  column tenant_id number not null
  master_table tenants
  master_pk name id
  fk_mode strict             -- strict = FK to TENANTS on every table (except TENANTS)
/unique_mode per_tenant      -- unique indexes include TENANT_ID
/pk_mode single              -- keep surrogate ID PKs; or "composite" for (TENANT_ID, ID)
/rls off                     -- off | vpd | ras (emit helpers only; don’t enable by default)

3. Mark tables as global (omit TENANT_ID) when needed

/global settings, plans

4. NEW: Quick secure views DSL Add a simple line to define a secure view with a developer-supplied predicate:

view <view_name> <base_table> /where <predicate>

Example

view Knowledge_articles_v Knowledge_articles /where tenant_id = SYS_CONTEXT('APEX$SESSION','APP_TENANT_ID');

This generates:

create or replace view knowledge_articles_v as
select *
from knowledge_articles
where tenant_id = SYS_CONTEXT('APEX$SESSION','APP_TENANT_ID');

Notes:

• Works independently of /rls (useful when projects prefer view-based security).

• If /tenancy on and no /where is provided, QuickSQL could optionally offer a default predicate template:

  /secure_views default where tenant_id = to_number(sys_context('APP_TENANCY_CTX','TENANT_ID'))
  

  (Only if a tenancy context helper is generated.)

Example QuickSQL input

/app Budget Tracker
/tenancy on
/tenancy
  column tenant_id number not null
  master_table tenants
  master_pk name id
  fk_mode strict
/unique_mode per_tenant
/pk_mode single
/rls vpd

tenants:
  name vc100 not null /unique
  status vc10 default 'ACTIVE'

accounts:
  name vc100 not null /unique
  type vc30
  balance number

transactions:
  account_id fk accounts
  amount number not null
  posted_on date not null

-- Secure views
view accounts_v accounts /where tenant_id = SYS_CONTEXT('APEX$SESSION','APP_TENANT_ID');
view transactions_v transactions /where tenant_id = SYS_CONTEXT('APEX$SESSION','APP_TENANT_ID');

Generated SQL (excerpt, illustrative)

• Master table

create table tenants (
  id          number generated always as identity primary key,
  name        varchar2(100 char) not null,
  status      varchar2(10 char) default 'ACTIVE' not null,
  created     date default sysdate not null,
  created_by  varchar2(255 char),
  updated     date,
  updated_by  varchar2(255 char),
  constraint tenants_u1 unique (name)
);

• Regular table with TENANT_ID, FK, and per-tenant unique

create table accounts (
  id          number generated always as identity primary key,
  tenant_id   number not null,
  name        varchar2(100 char) not null,
  type        varchar2(30 char),
  balance     number,
  created     date default sysdate not null,
  created_by  varchar2(255 char),
  updated     date,
  updated_by  varchar2(255 char),
  constraint accounts_tenant_fk foreign key (tenant_id) references tenants(id)
);

create index accounts_tenant_ix on accounts (tenant_id);
create unique index accounts_u1 on accounts (tenant_id, name);

• Child table includes TENANT_ID and same-tenant check

create table transactions (
  id          number generated always as identity primary key,
  tenant_id   number not null,
  account_id  number not null,
  amount      number not null,
  posted_on   date not null,
  constraint transactions_tenant_fk foreign key (tenant_id) references tenants(id),
  constraint transactions_account_fk foreign key (account_id) references accounts(id),
  constraint transactions_tenant_account_chk
    check (tenant_id = (select tenant_id from accounts where accounts.id = account_id))
);

create index transactions_tenant_ix on transactions (tenant_id);
create index transactions_account_ix on transactions (account_id);

• Optional RLS helpers when /rls vpd (emit but do not enable by default)

create context app_tenancy_ctx using app_tenancy_sec;

create or replace package app_tenancy_sec as
  procedure set_tenant_id(p_tenant_id number);
  function tenant_predicate(p_schema varchar2, p_object varchar2) return varchar2;
end;
/

create or replace package body app_tenancy_sec as
  procedure set_tenant_id(p_tenant_id number) is
  begin
    dbms_session.set_context('APP_TENANCY_CTX','TENANT_ID', to_char(p_tenant_id));
  end;
  function tenant_predicate(p_schema varchar2, p_object varchar2) return varchar2 is
  begin
    return 'tenant_id = to_number(sys_context(''APP_TENANCY_CTX'',''TENANT_ID''))';
  end;
end;
/

APEX integration note (docs snippet to generate) Post-Authentication process to set context:

begin
  app_tenancy_sec.set_tenant_id(:G_TENANT_ID);
end;

Defaults & backward compatibility

• /tenancy off by default (no changes for existing QuickSQL scripts).

• When on:

  • Add TENANT_ID to non-global tables, FK to TENANTS(ID), and per-tenant uniques.
  • Generate indexes on TENANT_ID.
  • Optionally emit RLS helper code (/rls vpd|ras).

• Secure views DSL is optional; used only when view ... /where ... lines appear.

Acceptance criteria

• A single directive enables consistent multi-tenant scaffolding across all generated tables.
• Ability to mark global tables that omit TENANT_ID.
• Tenant-aware FKs, unique indexes, and validation checks.
• Optional RLS helper code emitted (but not auto-enabled).
• New: DSL line view <view_name> <base_table> /where <predicate> generating secure views.
• Clear, generated README comments showing how to wire APEX session/tenant to context.

Environment

• Oracle APEX 24.2 on Oracle Cloud (Autonomous Database 23ai).

Labels enhancement feature-request quicksql multitenancy views

---

Want me to also add a short “Secure Views” section to the QuickSQL docs snippet in the issue (with a couple more examples like column-list views or joins)?