T09 · Insecure Skill Coding Practices
- Location
references/business-metrics.md:202- Finding
Missing Tenant Isolation in Defect-Density SQL Template
- Content
View full analysis
Vulnerability Details
File Location:
references/business-metrics.md, lines 202-230
Vulnerability Type: Cross-tenant data association caused by incomplete join predicates
Risk Level: HighVulnerable Code
sql item_case AS ( -- Items associated with test cases SELECT DISTINCT b."tenantkey", b."objectid" AS item_id, fs."actual_finish_ts", rel."{case_id_field}" FROM base_items b INNER JOIN finished_status fs ON b."tenantkey" = fs."tenantkey" AND b."objectid" = fs."objectid" INNER JOIN {schema}."{case_item_rel_table}" rel ON b."{key_field}" = rel."{item_fk_field}" AND rel."del_flag" = 0 ), case_defect AS ( -- Test cases associated with defects through execution records SELECT DISTINCT ic."tenantkey", ic."item_id", ic."actual_finish_ts", dcr."{defect_id_field}" FROM item_case ic INNER JOIN {schema}."{exec_table}" ce ON ce."{case_id_field}" = ic."{case_id_field}" INNER JOIN {schema}."{defect_case_rel_table}" dcr ON ce."{exec_id_field}" = dcr."{case_id_field}" AND dcr."del_flag" = 0 ),Technical Analysis
The defect-density template carries
b."tenantkey"into theitem_caseCTE, but three subsequent joins rely only on item, case, or execution identifiers:b."{key_field}" = rel."{item_fk_field}"ce."{case_id_field}" = ic."{case_id_field}"ce."{exec_id_field}" = dcr."{case_id_field}"
No tenant predicate constrains these joins. If the identifiers are unique only within a tenant rather than globally, rows belonging to another tenant can match the current tenant's records.
The resulting foreign records inherit
ic."tenantkey"from the current tenant rather than retaining or validating their actual tenant ownership. This can make cross-tenant records appear to belong to the current tenant in the final aggregation.This behavior directly conflicts with the ...[truncated 1870 chars]
- Remediation
View remediation
Remediation Suggestions
- Add tenant predicates to every join where the joined table contains a tenant identifier:
sql INNER JOIN {schema}."{exec_table}" ce ON ce."{case_id_field}" = ic."{case_id_field}" AND ce."tenantkey" = ic."tenantkey" INNER JOIN {schema}."{defect_case_rel_table}" dcr ON ce."{exec_id_field}" = dcr."{case_id_field}" AND dcr."tenantkey" = ic."tenantkey" AND dcr."del_flag" = 0- If
{case_item_rel_table}has a tenant column, enforce it directly:
sql INNER JOIN {schema}."{case_item_rel_table}" rel ON b."{key_field}" = rel."{item_fk_field}" AND rel."tenantkey" = b."tenantkey" AND rel."del_flag" = 0-
If a relationship table genuinely has no tenant column, resolve it through a tenant-scoped authoritative parent table before using it. Do not assume that carrying the main table's tenant key into the result proves ownership of the joined row.
-
Require and document global uniqueness constraints if tenant-scoped joins are impossible. Such constraints should be enforced by the database rather than assumed by the template.
-
Add automated regression tests containing two tenants with deliberately duplicated item keys, case IDs, and execution IDs. Verify that each tenant's output contains only its own defects.
-
Add a static SQL review rule that flags joins between tenant-bearing tables when no tenant equality predicate is present.
-
Apply row-level security or tenant-filtered database views as defense in depth so that malformed analytical SQL cannot read records outside the authorized tenant.