[Bug] Workspace permission matching uses non-sargable LOCATE without path boundaries

[mcop1]

Summary

Workspace permission resolution matches paths with LOCATE() (substring/prefix), which (a) is not index-usable and scans the workspace table, and (b) does not enforce a path-boundary, so a workspace path can match an unrelated element whose path merely shares a leading string. Rewrite the matching to be exact and index-friendly.

Problem

The permission layer uses LOCATE() in two directions across all three element types:

• Ancestor direction — models/Element/Dao.php:87: WHERE LOCATE(cpath, ?) = 1 (shared by object/asset/document via table suffix).
• Traversal direction — WHERE LOCATE(CONCAT(path, key), cpath) = 1: object AbstractObject/Dao.php:300,397; asset Asset/Dao.php:335,390 and Asset/Listing.php:76; document Document/Dao.php:338,375.

Two issues with this pattern:

1. Correctness — no path boundary. LOCATE tests raw string containment/prefix with no / boundary check, so a workspace configured on a path can match a sibling element whose name shares the same leading characters (e.g. a rule on .../Car also matching .../Carpets/...). Two code paths in core disagree on this: Element/Dao::permissionByTypes() already matches exactly via cid IN (<ancestor ids>), while the LOCATE-based paths do not — so the same logical permission can resolve differently depending on which method is hit.
2. Performance — not sargable. LOCATE(cpath, ?) and LOCATE(CONCAT(path,key), cpath) are functions of the column, so no index applies; MySQL evaluates them row-by-row over the candidate workspace rows and filesorts. Cost grows with the number of workspace rules (users × roles × configured paths), and in Asset/Listing.php:76 the pattern sits inside a per-row correlated subquery on listings.

Proposed solution

Replace substring matching with exact matching against a precomputed path set, keeping the "most specific workspace wins" resolution (ORDER BY LENGTH(cpath) DESC) byte-identical.

• Ancestor direction: the element's path is known, so its ancestor paths are a simple string-split. Replace LOCATE(cpath, ?) = 1 with cpath IN (<ancestor paths>) — exact matches, pure index lookups, sort bounded by tree depth.

  -- before
  WHERE LOCATE(cpath, '/Product Data/Cars/bmw/318i') = 1
  -- after
  WHERE cpath IN ('/', '/Product Data', '/Product Data/Cars',
                  '/Product Data/Cars/bmw', '/Product Data/Cars/bmw/318i')

• Traversal direction: the user's list=1 workspace paths are few and known; fetch them once, expand their ancestor/descendant sets in PHP, and collapse the correlated EXISTS(... LOCATE ...) into a constant, boundary-exact id IN (...) / cpath IN (...) — removing the per-row subquery entirely. (Same principle the generic-data-index workspace query already uses on the search side.)

Add a composite index on (userId, cpath) on the users_workspaces_* tables if not already present.

Behavior / risk

This changes permission semantics (a grant/deny difference in the boundary edge case), so it must be treated as a correctness/security change, not a pure refactor:

• Ship behind a characterization test matrix: generate workspace configurations × element paths, run old vs new resolution side by side, and review every diff. Expected diffs are confined to the path-boundary edge case, each one a correction (the stricter, boundary-exact result is the intended behavior — consistent with permissionByTypes()).
• The diff itself is read-only queries with no shared state — trivially revertable — but the review gate is the test matrix, not the code size.
• Assess backport scope for supported LTS lines given the correctness aspect.

Effort

M — the query rewrites are small; the majority is the old-vs-new characterization suite and the backport assessment.

Related

Part of the performance audit. Companion improvement tickets: #212 (per-request permission memoization) and #213 (cache write limit). Memoization (#212) removes repeated checks; this ticket makes the first check of each element cheap and correct.