APPSEC/i — Multi-Tenant Application Security & Entitlements

SET PATH (unqualified routine resolution)

Two identically-named functions FN_ENTITLEMENT_SOURCE() live in schemas PATHA and PATHB, each returning its own schema tag. A qualified call always resolves to the named schema; an unqualified call resolves by the current PATH — SET PATH = PATHA, PATHB makes the unqualified call return PATHA, and re-ordering to SET PATH = PATHB, PATHA flips it to PATHB. The CURRENT PATH register reflects the reordered list. This is how per-tenant schema resolution order is expressed. See F.6.

C. Batch / Cycle Procedures ↑ top

APPSEC/i has no periodic accounting "close" — it is an operational security platform, so its "cycle" is the provisioning & entitlement lifecycle: deploy the schema and routines, provision tenants, seed roles/features, grant and revoke, check access, and (when a tenant leaves) deprovision. All of it is driven by CALL against the SQL-PL procedures and the security triggers that fire off the resulting table changes. There is no control row and no parameterless SBMJOB idiom — every action is an explicit call with its own parameters.

-- deploy (idempotent for OR-REPLACE objects; TYPE/TABLE/SEQ/VAR fail "already exists" on re-run)
RUNSQLSTM SRCFILE(APPSEC/SRC) SRCMBR(SCHEMA)     -- schema.sql
RUNSQLSTM SRCFILE(APPSEC/SRC) SRCMBR(ROUTINES)   -- routines.sql

-- provision a tenant (two-step: procedure, then host-issued CREATE SCHEMA)
CALL APPSEC.SP_PROVISION_TENANT('Acme Corp','TENACME',?,?,?);
CREATE SCHEMA TENACME
  CREATE TABLE TENDATA (TID VARCHAR(10) NOT NULL, TVAL VARCHAR(60))
  GRANT SELECT ON TENDATA TO PUBLIC;
GRANT CREATEIN, ALTERIN, DROPIN ON SCHEMA TENACME TO QPGMR WITH GRANT OPTION;

C.1 Full procedure / trigger table

C.2 Provisioning & grant/revoke detail

Provisioning — SP_PROVISION_TENANT

The procedure counts existing tenants of the given name. If one exists it returns that tenant's id with P_RC=1 ('tenant already provisioned'); otherwise it draws the next TENANT_SEQ value, formats a TEN… id ('TEN' || LPAD(CHAR(seq),7,'0'), e.g. TEN0000001), inserts the TENANT row and a PROVISION_OK SECAUDIT row, and returns P_RC=0. An EXIT HANDLER traps any SQLEXCEPTION, sets P_RC=9, captures the message via GET DIAGNOSTICS, and writes a PROVISION_FAIL SECAUDIT row. The per-tenant CREATE SCHEMA is then issued by the caller only when RC=0 (fresh), so a re-provision of the same name touches neither the catalog nor the schema.

Expected result (Acme, fresh then re-run):
  call 1:  P_TENANTID = TEN0000001,  P_RC = 0  ('provisioned')
  call 2:  P_TENANTID = TEN0000001,  P_RC = 1  ('tenant already provisioned')
  invariant: exactly one TENANT row for 'Acme Corp'

Grant / revoke — SP_GRANT_ROLE / SP_REVOKE_ROLE + the audit triggers

Both procedures guard their mutation with an existence check, so a re-grant (P_RC=1) and a re-revoke (P_RC=1) are clean no-ops. The audit is trigger-driven, not coded in the procedure: the AFTER-statement triggers use transition tables (NEW TABLE / OLD TABLE) to write one PRIVAUDIT row per affected assignment in a single set-based INSERT…SELECT. Because they are statement-level and only fire when a row actually changes, an idempotent no-op re-grant does not add a spurious audit row.

Expected (grant ROLADMIN to UACME01, then re-grant):
  grant 1:  P_RC = 0        1 USERROLE row, 1 PRIVAUDIT ROLE_GRANT row
  grant 2:  P_RC = 1        no new USERROLE row, no new audit row
  PRIVAUDIT ROLE_GRANT for UACME01/ROLADMIN carries EVTBY = 'SYSTEM' (from GRANTEDBY, via NT)

Access check — SP_CHECK_ACCESS

The gate short-circuits on the first failing condition (see B.2). A denial is a SIGNAL of condition C_DENIED (75040), which surfaces to the caller as a non-zero call code plus the denial message — never a silent grant. Only when unknown-user, disabled-user, suspended-tenant, missing-role and un-entitled-feature all pass does it set P_RC=0 / 'access granted'.

Deprovisioning — SP_DEPROVISION_TENANT

Probes QSYS2.SYSSCHEMAS for the schema and, if present, issues DROP SCHEMA … CASCADE via EXECUTE IMMEDIATE (which, unlike CREATE SCHEMA, does run from inside a routine body). A DROP SCHEMA … RESTRICT against a non-empty per-tenant schema is refused ("not empty"); CASCADE removes the schema and its contents. Note this drops only the tenant's schema — it deliberately does not delete the APPSEC.TENANT registry row (a real platform would archive that separately).

C.3 Ordering & dependencies

• schema.sql before routines.sql. The routines reference the tables, distinct types, sequences, the global variable and SESSION_CTX declared in schema.sql; deploy the schema first.
• PATHA/PATHB schemas before routines.sql. The two FN_ENTITLEMENT_SOURCE functions target schemas PATHA/PATHB, which the driver creates once before loading the routines (they cannot be created idempotently inside the reloadable routines file).
• Seed roles/features before grants and access checks. SP_GRANT_ROLE references a ROLE; SP_CHECK_ACCESS references a FEATURE entitlement. Load seed.mjs (3 roles, 3 features, the empty SESSION_CTX row) first.
• Provision before grant. A user is scoped to a tenant (APPUSER.TENANT_ID), so the tenant must exist before its users and their role grants.
• Set the session context before a tenant-scoped write. The veto only fires when SESSION_CTX.CUR_TENANT is set; keep CURRENT_TENANT and SESSION_CTX in lockstep (the driver always updates both together).

D. Data Files (data dictionary) ↑ top

All core objects are in schema/library APPSEC, grounded in schema.sql. Dates are stored as INT in YYYYMMDD form; the tenant/role id columns use the distinct types APPSEC.TENANTID / APPSEC.ROLEID (both over VARCHAR(10)). Per-tenant data lives in each tenant's own TEN… schema, not in APPSEC.

TENANT — Tenant registry (PK TENANT_ID)

ROLE — Role catalog (PK ROLE_ID)

Seeded roles: ROLADMIN (Administrator), ROLVIEWER (Viewer), ROLEDITOR (Editor).

APPUSER — Users (PK USERID; scoped to a tenant)

USERROLE — User–role assignments (PK USERID, ROLE_ID) — the grant log

INSERT here fires TR_USERROLE_AUDIT_INS; DELETE fires TR_USERROLE_AUDIT_DEL.

FEATURE — Feature catalog (PK FEATCD)

Because FEATCD is CHAR(8), a shorter code is space-padded — the seed uses 'REPORTS ' (trailing blank).

TENANT_ENTITLE — Per-tenant entitlements (PK TENANT_ID, FEATCD)

RESOURCE — Tenant-scoped business object (PK RESID) — the veto target

PRIVAUDIT — Privilege-change audit (PK AUDID identity)

SESSION_CTX — Session current-tenant context (one row, PK CTXKEY='X')

SECAUDIT — General security-event audit (PK AUDID identity)

V_ACTIVE_ENTITLEMENTS / V_USER_ROLES — security views

V_ACTIVE_ENTITLEMENTS joins TENANT ⨯ TENANT_ENTITLE ⨯ FEATURE, filtered to TSTAT='A' and ENSTAT='Y' — the effective entitlement gate list. V_USER_ROLES joins APPUSER ⨯ USERROLE ⨯ ROLE for active users. Both are deployed via CREATE OR REPLACE VIEW, so the daily deploy can redeploy them idempotently without dropping dependents.

TENANTID / ROLEID — distinct types; TENANT_SEQ / USER_SEQ — sequences; CURRENT_TENANT — global variable

CREATE TYPE APPSEC.TENANTID AS VARCHAR(10) WITH COMPARISONS and APPSEC.ROLEID similarly — two unrelated distinct types over the identical source, used to probe whether cross-type assignment is rejected (see F.1). TENANT_SEQ / USER_SEQ are AS INT START WITH 1 INCREMENT BY 1 NO CYCLE generators. CURRENT_TENANT is a CREATE VARIABLE APPSEC.CURRENT_TENANT TENANTID DEFAULT NULL global (the type is spelled unqualified as an app-level workaround for SQLPL-PLAT-APPSEC-01).

Per-tenant schema — TEN….TENDATA

Each provisioned tenant gets its own SQL schema named by SCHEMANM, created (by the driver) with a contained TENDATA (TID VARCHAR(10), TVAL VARCHAR(60)) table and a GRANT SELECT ON TENDATA TO PUBLIC, plus a schema-level GRANT CREATEIN, ALTERIN, DROPIN … TO QPGMR WITH GRANT OPTION. This is where a tenant's own data would live, isolated from every other tenant's schema.

Relationships

• APPUSER.TENANT_ID → TENANT.TENANT_ID (users are tenant-scoped).
• USERROLE.(USERID, ROLE_ID) → APPUSER.USERID / ROLE.ROLE_ID (assignments).
• TENANT_ENTITLE.(TENANT_ID, FEATCD) → TENANT / FEATURE (entitlements).
• RESOURCE.TENANT_ID → TENANT.TENANT_ID (tenant-scoped object, veto-guarded).
• PRIVAUDIT / SECAUDIT are append-only audit logs (identity PK, no per-row FK).
• SESSION_CTX is a standalone one-row context; CURRENT_TENANT is the parallel global variable.
• TENANT.SCHEMANM names the per-tenant schema holding TENDATA.

E. Operations Runbook ↑ top

E.1 Provision a tenant

1. Ensure the platform is deployed: schema.sql then routines.sql loaded, the PATHA/PATHB probe schemas created, and the seed run (3 roles, 3 features, the empty SESSION_CTX row).
2. Call the procedure (two-step provisioning — the CREATE SCHEMA is host-issued only on a fresh provision):

CALL APPSEC.SP_PROVISION_TENANT('Acme Corp','TENACME',?,?,?);
-- if the OUT P_RC came back 0 (fresh), issue the per-tenant schema:
CREATE SCHEMA TENACME
  CREATE TABLE TENDATA (TID VARCHAR(10) NOT NULL, TVAL VARCHAR(60))
  GRANT SELECT ON TENDATA TO PUBLIC;
GRANT CREATEIN, ALTERIN, DROPIN ON SCHEMA TENACME TO QPGMR WITH GRANT OPTION;

Verify:

• A tenant id came back, e.g. TEN0000001; P_RC=0 ('provisioned') on a fresh name, P_RC=1 ('tenant already provisioned') on a repeat.
• Exactly one registry row: SELECT COUNT(*) FROM APPSEC.TENANT WHERE TENANTNM='Acme Corp' → 1.
• The schema exists: SELECT COUNT(*) FROM QSYS2.SYSSCHEMAS WHERE SCHEMA_NAME='TENACME' → 1, and SELECT COUNT(*) FROM TENACME.TENDATA answers (0 rows).
• The grants are catalog-visible: the PUBLIC SELECT on TENDATA in SYSTABAUTH, and the schema-level CREATEIN/ALTERIN/DROPIN for QPGMR with IS_GRANTABLE='YES' (for a schema grant, the schema name lands in SYSTABAUTH.TABLE_NAME with OBJECT_TYPE='SCHEMA').

E.2 Grant / verify access

1. Add the user (scoped to the tenant): INSERT INTO APPSEC.APPUSER (USERID, TENANT_ID, USERNM, USTAT) VALUES ('UACME01','TEN0000001','Alice','A').
2. Grant a role: CALL APPSEC.SP_GRANT_ROLE('UACME01','ROLADMIN','SYSTEM',?) → expect P_RC=0.
3. Entitle the tenant to a feature: INSERT INTO APPSEC.TENANT_ENTITLE (TENANT_ID, FEATCD, ENSTAT) VALUES ('TEN0000001','RESOURCE','Y').
4. Make the access decision: CALL APPSEC.SP_CHECK_ACCESS('UACME01','ROLADMIN','RESOURCE',?,?) → expect P_RC=0, 'access granted'.

Negative paths to confirm the gate really gates (each returns a failed CALL with the named message, never a silent grant):

Cross-tenant write test — set the session context and prove isolation:

SET APPSEC.CURRENT_TENANT = 'TEN0000001';
UPDATE APPSEC.SESSION_CTX SET CUR_TENANT = 'TEN0000001' WHERE CTXKEY='X';
-- same-tenant INSERT is allowed:
INSERT INTO APPSEC.RESOURCE (RESID,TENANT_ID,RESNM,SENSITIVE) VALUES ('RES00001','TEN0000001','Acme Widget','secret-a');
-- cross-tenant INSERT is VETOED (SQLSTATE 75030); the row never lands:
INSERT INTO APPSEC.RESOURCE (RESID,TENANT_ID,RESNM,SENSITIVE) VALUES ('RES00002','TEN0000002','Globex Widget','secret-b');

Audit verification: after N grants and M revokes, SELECT COUNT(*) FROM APPSEC.PRIVAUDIT WHERE EVTTYPE='ROLE_GRANT' = N and ...='ROLE_REVOKE' = M — an idempotent no-op grant/revoke adds nothing. The grant's EVTBY equals the GRANTEDBY supplied to SP_GRANT_ROLE.

E.3 Reconciling & re-run rules

Every procedure returns an OUT return code; a healthy call ends P_RC=0 (acted) or P_RC=1 (idempotent no-op). A denial or error surfaces as a non-zero call code with a message (section F.7).

F. Developer Reference ↑ top

The complete SQL-PL and privilege surface, from schema.sql and routines.sql. Core objects are in schema APPSEC; the resolution-probe functions are in PATHA / PATHB.

F.1 Distinct types, sequences, global variable

CREATE TYPE APPSEC.TENANTID AS VARCHAR(10) WITH COMPARISONS

Distinct type for a tenant id, so cross-tenant assignment is at least nominally type-checked. Used as the column type of TENANT.TENANT_ID, APPUSER.TENANT_ID, RESOURCE.TENANT_ID, etc.

CREATE TYPE APPSEC.ROLEID AS VARCHAR(10) WITH COMPARISONS

A second, unrelated distinct type over the identical source. It exists so a probe can attempt to flow a ROLEID value into a TENANTID position and observe whether the engine rejects the cross-type assignment. Documented deviation: strong typing / CAST identity is not modeled by the platform (SQLPL-COVERAGE.md section E), so the assignment is accepted rather than rejected — the app proves that boundary rather than assuming it.

CREATE SEQUENCE APPSEC.TENANT_SEQ / USER_SEQ AS INT START WITH 1 INCREMENT BY 1 NO CYCLE

Id generators. SP_PROVISION_TENANT draws NEXT VALUE FOR APPSEC.TENANT_SEQ and formats 'TEN' || LPAD(CHAR(seq),7,'0'). Sequence-generation is what makes repeated provisioning collision-free.

CREATE VARIABLE APPSEC.CURRENT_TENANT TENANTID DEFAULT NULL

The session current-tenant context. Source-documented workaround (SQLPL-PLAT-APPSEC-01): the CREATE VARIABLE parser does not accept a schema-qualified distinct-type name in the type position, so the type is spelled unqualified (TENANTID, not APPSEC.TENANTID). The variable reads/writes correctly through the top-level batch path (SET / VALUES APPSEC.CURRENT_TENANT); the veto triggers read the parallel SESSION_CTX table instead (see F.4).

F.2 Functions (2 — the SET PATH probe)

PATHA.FN_ENTITLEMENT_SOURCE () RETURNS CHAR(5) LANGUAGE SQL RETURN 'PATHA'

Returns the literal tag 'PATHA'.

PATHB.FN_ENTITLEMENT_SOURCE () RETURNS CHAR(5) LANGUAGE SQL RETURN 'PATHB'

Returns the literal tag 'PATHB'. Two identically-named functions in two schemas, used to prove unqualified-call resolution follows SET PATH (F.6). Both are CREATE OR REPLACE FUNCTION, so they reload idempotently; only their containing schemas are non-idempotent (driver-created once).

F.3 Procedures (5)

SP_PROVISION_TENANT (IN P_TENANTNM VARCHAR(60), IN P_SCHEMANM VARCHAR(18); OUT P_TENANTID TENANTID, OUT P_RC INT, OUT P_MSG VARCHAR(100))

Idempotent tenant registration. If a tenant of that name exists → returns its id, P_RC=1. Otherwise draws TENANT_SEQ, inserts the TENANT + a PROVISION_OK SECAUDIT row, P_RC=0. An EXIT HANDLER FOR SQLEXCEPTION sets P_RC=9, captures the message via GET DIAGNOSTICS CONDITION 1 ... = MESSAGE_TEXT, and writes a PROVISION_FAIL SECAUDIT row. Does not itself issue CREATE SCHEMA (SQLPL-PLAT-APPSEC-03: CREATE SCHEMA is not recognized by the routine dispatcher; the caller issues it top-level on a fresh provision).

SP_DEPROVISION_TENANT (IN P_SCHEMANM VARCHAR(18))

Probes QSYS2.SYSSCHEMAS; if the schema exists, EXECUTE IMMEDIATE 'DROP SCHEMA ' || P_SCHEMANM || ' CASCADE'. DROP SCHEMA does run from inside a routine (unlike CREATE SCHEMA). Idempotent via the existence probe.

SP_GRANT_ROLE (IN P_USERID VARCHAR(10), IN P_ROLEID VARCHAR(10), IN P_GRANTEDBY VARCHAR(10); OUT P_RC INT)

Existence-guarded INSERT into USERROLE; P_RC=0 granted, 1 already granted. The INSERT fires TR_USERROLE_AUDIT_INS. Workaround (SQLPL-PLAT-APPSEC-04): P_ROLEID is declared VARCHAR(10), not the ROLEID distinct type, because a CALL argument bound to a character-sourced distinct-type IN parameter is spuriously rejected with SQL0302 (callProc0's isCharType() inspects the raw declared-type text rather than the resolved source type).

SP_REVOKE_ROLE (IN P_USERID VARCHAR(10), IN P_ROLEID VARCHAR(10); OUT P_RC INT)

Existence-guarded DELETE from USERROLE; P_RC=0 revoked, 1 nothing to revoke. Fires TR_USERROLE_AUDIT_DEL. Same VARCHAR(10) workaround on P_ROLEID.

SP_CHECK_ACCESS (IN P_USERID VARCHAR(10), IN P_ROLEID VARCHAR(10), IN P_FEATCD CHAR(8); OUT P_RC INT, OUT P_MSG VARCHAR(100))

The entitlement gate. Declares condition C_DENIED FOR SQLSTATE '75040' and SELECT INTOs the user (tenant + status), tenant status, role count and entitlement status, SIGNAL C_DENIEDing with a specific MESSAGE_TEXT at the first failure: unknown user → disabled user → suspended tenant → missing role → un-entitled feature. Clean pass → P_RC=0, 'access granted'. Same VARCHAR(10) workaround on P_ROLEID.

F.4 Triggers (4) & views

TR_USERROLE_AUDIT_INS — AFTER INSERT ON USERROLE, FOR EACH STATEMENT, REFERENCING NEW TABLE AS NT

BEGIN ATOMIC INSERT INTO PRIVAUDIT (...) SELECT 'ROLE_GRANT', NT.USERID, NT.ROLE_ID, NT.GRANTEDBY, NT.GRANTDATE FROM NT; — one audit row per inserted assignment in a single set-based statement (proves multi-row transition-table batching, not FOR EACH ROW).

TR_USERROLE_AUDIT_DEL — AFTER DELETE ON USERROLE, FOR EACH STATEMENT, REFERENCING OLD TABLE AS OT

INSERT ... SELECT 'ROLE_REVOKE', OT.USERID, OT.ROLE_ID, NULL, 20260807 FROM OT; — one ROLE_REVOKE row per deleted assignment (EVTBY NULL on revoke).

TR_RESOURCE_TENANT_VETO — BEFORE INSERT ON RESOURCE, FOR EACH ROW

WHEN a current tenant is set in SESSION_CTX AND the new row's TENANT_ID does not match → SIGNAL SQLSTATE '75030' ("cross-tenant write refused"). Reads SESSION_CTX, not the global variable (below).

TR_RESOURCE_TENANT_VETO_UPD — BEFORE UPDATE ON RESOURCE, FOR EACH ROW

WHEN the existing row (OLD.TENANT_ID) is not owned by the current tenant → SIGNAL SQLSTATE '75031'.

V_ACTIVE_ENTITLEMENTS / V_USER_ROLES — CREATE OR REPLACE VIEW

The active-entitlement gate list and the active-user role list (see D). Redeployable idempotently.

F.5 GRANT/REVOKE granularity surface

Every DB2 privilege granularity the application exercises, with the catalog it lands in and the behaviour the driver verifies. All of this is catalog bookkeeping (A.2 enforcement caveat); the runtime authorization gate is the data-enforced model in F.3–F.4.

The revoke rules proven, in words: a column-scoped revoke removes only the named columns; a bare REVOKE UPDATE removes all remaining UPDATE columns for the grantee; a different privilege type (REFERENCES) and the table-level grant survive an UPDATE revoke; and re-granting the identical privilege to the identical grantee is a no-op in the catalog (no duplicate row).

F.6 SET PATH resolution

This is the mechanism per-tenant schema resolution order would ride on: an unqualified routine name binds to the first schema on the PATH that defines it, and SET PATH re-orders which wins.

F.7 Application SQLSTATE table

G. Glossary ↑ top

Distinct type

A user-defined type over a built-in source type (here TENANTID / ROLEID over VARCHAR(10)). Nominally scopes a domain; note the platform does not model strong CAST identity, so cross-type assignment is accepted, not rejected.

Entitlement

A per-tenant feature grant (TENANT_ENTITLE, ENSTAT='Y'). A tenant can act on a feature only when entitled, checked by SP_CHECK_ACCESS.

Global variable (CREATE VARIABLE)

A session-scoped SQL variable (APPSEC.CURRENT_TENANT) holding the current-tenant context. Kept in lockstep with the SESSION_CTX table the veto triggers read.

GRANT / REVOKE

The DB2 statements that record privileges in the authorization catalog. In APPSEC/i they are proved for catalog correctness at every granularity (table, column, EXECUTE, schema, PUBLIC, WITH GRANT OPTION); they are the auditable record of who was granted what, not the runtime authorization gate.

Idempotent

Safe to run again with the same result. Provisioning (by name), grant, revoke and deprovision are all idempotent; the whole platform re-deploy is safe end to end.

Multi-tenant / tenant

One customer of the SaaS platform. Each tenant has a registry row (TENANT) and a private SQL schema (SCHEMANM); cross-tenant writes are vetoed.

Role & user–role assignment

A named privilege bundle (ROLE) and its assignment to a user (USERROLE), granted/revoked through procedures and audited by triggers.

SET PATH

The SQL statement that orders which schemas resolve an unqualified routine name; the first schema on the path that defines the name wins.

SIGNAL / RESIGNAL

SQL-PL statements that raise an SQLSTATE condition. SP_CHECK_ACCESS SIGNALs 75040 on denial; the veto triggers SIGNAL 75030/75031.

SQLSTATE / SQLCODE

SQL return-status fields. A clean call returns normally with P_RC 0 or 1; a denial or veto surfaces as a failed call carrying an application SQLSTATE (F.7).

STRSQL

Start SQL Interactive Session — the IBM i interactive SQL entry point. APPSEC/i has no 5250 screen; it is driven by CALL from STRSQL or any SQL client.

SYSTABAUTH / SYSCOLAUTH

The DB2 for i catalog views recording table/routine/schema and column privileges. APPSEC/i reads them back to prove GRANT/REVOKE bookkeeping.

Transition table (NEW TABLE / OLD TABLE)

The set of rows an INSERT/DELETE affected, referenced by a FOR-EACH-STATEMENT trigger. The audit triggers use them to write one audit row per affected assignment in a single set-based statement.

Cross-tenant veto

The BEFORE-trigger rule that refuses a write to a resource whose tenant does not match the session's current tenant (SQLSTATE 75030/75031).