CRUD Security

Every SepoDesk app is a client that talks to one server endpoint. A window opens, the JS app calls ApiLogic, and a POST lands on /api/v2/data. From that point the request passes through a fixed stack of gates before a single row is read or written.

Nothing in the browser is trusted. The client decides what to show; the server decides what exists.

The stack at a glance

  BROWSER
  ┌──────────────────────────────────────────────┐
  │  0  App UI  (Widgets, AppDataUI)             │  cosmetic only
  │     ApiLogic -> EcmaScriptController         │
  └───────────────────┬──────────────────────────┘
                      │  POST /api/v2/data
  SERVER              ▼
  ┌──────────────────────────────────────────────┐
  │  1  Route gate      AuthMiddleware           │  is this URL open to your role?
  ├──────────────────────────────────────────────┤
  │  2  Credential gate ApiKeyModel, JWT, limits │  are you a real caller?
  ├──────────────────────────────────────────────┤
  │  3  Identity        AuthUserTrait            │  WHO are you? (uid)
  ├──────────────────────────────────────────────┤
  │  4  Capability      verifyAccess()           │  may you do this VERB at all?
  ├──────────────────────────────────────────────┤
  │  5  Table gate      isTablePermitted()       │  on THIS table?
  ├──────────────────────────────────────────────┤
  │  6  Resource gate   isResourcePermitted()    │  from THIS app, on THIS object?
  ├──────────────────────────────────────────────┤
  │  7  Row gate        ownership + sharing      │  on THESE rows?
  ├──────────────────────────────────────────────┤
  │  8  Response gate   RecordInjectionTrait     │  what may you SEE / be told?
  └───────────────────┬──────────────────────────┘
                      ▼
                  MultiTable  ->  PDO  ->  database

Layers 4, 5 and 6 all live in SepoEngine\Traits\CrudSecurityTrait, which every app controller uses. Layers 7 and 8 live in StandardCrudTrait, SharingTrait and RecordInjectionTrait.

Layer 0 — The client app

The app in the browser holds no authority. It carries a bootstrap credential handed to it by the server-rendered shell (window.SepoDeskConfig.bootstrap, holding an access token and the API public key) and posts through EcmaScriptController.

What it does with permissions is presentation: hiding a delete button, greying a tab. Those decisions are driven by the _can_* flags the server injects into each record (see Layer 8), so the UI stays consistent with the truth — but a forged request that skips the UI entirely gets nothing extra.

Rule: never treat a client-supplied user id, role, or table name as authority. Table names must resolve through the app's own methodTableMap, not through the request body.

Layer 1 — Route gate

AuthMiddleware and MiddlewareHelper decide whether the URL is reachable at all. /api/v2 is prefixed for the normalised roles admin, moderator, editor and general; normalizeRole() maps the raw sp_flag value onto one of those keys and returns an empty string for anything it does not recognise, so an unknown flag reaches nothing.

This gate is coarse on purpose. It answers "may this account touch the data API at all", not "may it read this table".

Layer 2 — Credential gate

Before dispatch, CoreDataApiController establishes that the caller is real:

the api-public-key header is validated by ApiKeyModel::validateKey
the key resolves to a role, and the role resolves to a target database (ApiRoleResolver to RoleTargetDbResolver to RoleCredentialsResolver)
AuthModelUser::enforceRateLimit throttles the caller
JWT access tokens are verified for stateless calls

The important consequence: the connection you get is already scoped by key. A key bound to a role that points at one database cannot be talked into reading another. That is a boundary below all the permission logic that follows.

Layer 3 — Identity

AuthUserTrait resolves the acting user into a single uid string and exposes isRequesterAdmin(). Everything after this point is expressed in terms of that uid.

CrudSecurityTrait needs a database handle to check permissions, and finds one through resolveSecurityPdo(), in this order:

a PDO explicitly injected with setSecurityPdo()
a $pdo property on the controller itself
a PDO reachable from an attached model — authModel, apiKeyModel or model — via getPdo(), or by reflection on a private $pdo property

If none resolve it throws a RuntimeException rather than continuing without a check. Failing loudly here is deliberate: a permission check that cannot reach its tables must never be treated as a pass.

Layer 4 — Capability: `verifyAccess()`

Question: does this user hold the raw right to perform this kind of operation anywhere?

$access = $this->verifyAccess($userId, $request->method, self::SECURITY_MAP);
if ($access['status'] !== 'success') {
    return $access;
}

How it resolves, in order:

No uid — denied immediately. There is no anonymous CRUD path.
System root — SystemObject::isSystemRoot($userId) loads the hardcoded root permission object and grants.
Admin flag — if the controller exposes isRequesterAdmin() and it returns true, fallback admin permissions are loaded and access is granted.
Operation mapping — the method name is looked up in the permission map. An unmapped operation is denied with SECURITY_MAPPING_MISSING. New methods are closed by default until you map them.
Permission load — a row is read from tsp_sys_security for the uid. If there is no row, the user's sp_flag is read from tsp_sys_users and SystemObject::getFallbackPermissions() supplies the defaults for that flag.
Enforcement — the mapped column must be 1.

The map. The built-in map covers the whole engine vocabulary, grouped by what it protects:

Data operations — get, select, add, create, update, delete, createRecord, updateRecord, updateBulkRecords, deleteRecord, cascadeDeleteRecord and the MultiTable family (getMulti, getSingle, getSelected, insertSingle, insertMultiple, updateMultiple, deleteMultiple, joinData, fetchData) map onto can_select_data, can_create_data, can_update_data, can_delete_data.
Schema operations — getSchema, uploadSchema, addColumn, renameField, setPrimaryKey, setAutoIncrement, setDefaultValue, dropField and friends map onto can_select_table, can_create_table, can_update_table, and dropTable onto can_drop_table.
Migration and maintenance — folder deletions and cache purges map onto the drop and delete rights; healTable, analyzeTable, optimizeTable and defragmentInnoDBTable require can_update_table; inspection calls require can_select_data.

Separating data rights from table rights is the point of this layer: a user can be allowed to write records forever and still never be allowed to alter the shape of a table.

Custom maps. Apps pass their own methodSecurityMap as the third argument. Note the semantics carefully:

A non-empty custom map replaces the base map. It does not merge with it. If your app still routes base-named operations such as get or update through verifyAccess(), your custom map must list them too, or they will be rejected as unmapped.

Deny shape. Failures return an array, never an exception:

[
  'status'  => 'error',
  'message' => "Access Denied: You have no access to the 'delete' operation.",
  'table'   => ['status' => 'error', 'message' => "Access Denied for 'delete'."]
]

The nested table key exists so the client renderer can drop the message straight into the grid area it was going to populate.

Side effect worth knowing: verifyAccess() caches the resolved permission row on $this->userPermissions. Layer 5 falls back to that cache. Call verifyAccess() first — always — or the later gates evaluate against null.

Layer 5 — Table: `isTablePermitted()`

Question: does this user have structural rights on this specific physical table?

if (!$this->isTablePermitted($uid, $tableName, 'can_update_table')) {
    return $this->error('No access to this table.');
}

Resolution order:

Admin bypass — isRequesterAdmin() short-circuits to true.
User override — a row in tsp_sys_users_access_tables matching uid plus table name wins outright, allow or deny.
Role grant — the user's sp_user_category is read from tsp_sys_users, then matched against tsp_sys_roles_access_tables. If no category resolves, access is refused.
Global fallback — with no row at either level, the answer falls back to the global permission cached in Layer 4.

Any thrown error inside this method returns false. The gate fails closed.

Step 4 is the one to think about when you design a deployment. It means the table registry is an override system, not an allow-list: a user with a global can_update_table right reaches any unregistered table. If you want strict allow-list behaviour, register every table explicitly and change that fallback to false.

Layer 6 — Resource: `isResourcePermitted()`

Question: does this subject — the user, or any group or role they belong to — have a grant covering this object, issued by this app?

$allowed = $this->isResourcePermitted(
    $uid,
    ['usr_' . $uid, 'role_' . $roleName],   // priority order
    'tsp_sys_email_config',                  // resource type, or '*'
    'getUsers',                              // raw method name
    self::APP_ID,                            // e.g. app.users.manager.com
    self::SECURITY_MAP
);

This is the contextual layer, and the one that makes an app's grants portable. Grants live in tsp_sys_user_resources keyed by three things: the subject (a user, group or role identifier), the resource source (the calling app's hardcoded domain id), and the resource type (a table name, or * for everything the app owns).

Resolution order:

Admin bypass — true for real admins.
Empty subject list — false.
Action normalisation — the raw method is mapped through the app's security map, then reduced to one of four columns: can_select, can_create, can_update, can_delete. An action that reduces to nothing returns false, so unknown verbs are closed.
Specific grant — the first matching row for the resource type wins. Subject priority is enforced in SQL with ORDER BY FIELD(...) against the subject array you passed, so a personal grant listed first beats a role grant listed second — including a personal deny.
Wildcard grant — if there was no specific row and the request was not already for *, the same lookup runs against a * resource type. The wildcard is checked column by column; it is a broad grant, not a bypass.
No grant — false.

Again, any exception returns false.

Why the subject array matters: its order is your precedence policy. Put the most specific identifier first. A user listed after their role cannot be individually revoked.

Passing Layer 6 gets you to the table, not to everyone's rows.

StandardCrudTrait asserts ownership before create, update, delete, bulk and cascade operations, and resolves aliases through the app's validPkFields and inferAliasFromId() so a caller cannot address a row by a field the app did not declare.
SharingTrait widens reads with fetchWithSharing() and its variants, matching sp_owner_uid against the uid and sp_shared_with against the user's identifiers, plus sp_general_sharing for broadcast records.
Writes resolve the set of authorised primary keys first, then write only those. A bulk update never becomes a table-wide update because one id in the batch was not yours.

The equivalent on the server-rendered admin dashboard is the reserved SCOPE key in a resource definition's baseWhere, which compiles the same owner-or-shared predicate and fails closed to 1 = 0 on an empty uid. Same policy, different renderer.

Layer 8 — Response

The last gate shapes what comes back.

RecordInjectionTrait::injectSharedPermission() stamps each record with _can_* flags describing what the caller may do with that row. This is what the client uses to render controls.
cleanData() strips those flags again before anything is written back, so the client cannot round-trip an inflated permission into storage.
ResponseTrait normalises the envelope so a denial and a success have the same shape and the app never leaks a stack trace or an SQL fragment.

Error text is deliberately generic. "Access Denied for 'delete'" does not reveal whether the row exists.

Where the rules are stored

tsp_sys_security — per-user global capability row: can_create_data, can_select_data, can_update_data, can_delete_data, can_select_table, can_create_table, can_update_table, can_drop_table, can_grant.
tsp_sys_users — sp_flag (drives fallback permissions when there is no security row) and sp_user_category (drives role table grants).
tsp_sys_users_access_tables — per-user, per-table structural overrides.
tsp_sys_roles_access_tables — per-role, per-table structural grants.
tsp_sys_user_resources — subject plus app source plus resource type, with can_select, can_create, can_update, can_delete.
SystemObject — code, not data: the root object and the fallback permission sets per user flag. This is the floor the whole system stands on, so treat edits to it as security changes.

Wiring a new app

The single entry point pattern, in order:

public function index(Request $request): array
{
    // 3. identity
    $uid = $this->resolveRequesterUid($request);

    // 4. capability
    $access = $this->verifyAccess($uid, $request->method, self::METHOD_SECURITY_MAP);
    if ($access['status'] !== 'success') {
        return $access;
    }

    // resolve the target table from YOUR map, never from the request
    $table = self::METHOD_TABLE_MAP[$request->method] ?? null;
    if ($table === null) {
        return $this->error('Unknown operation.');
    }

    // 6. resource grant
    if (!$this->isResourcePermitted($uid, $this->subjectsFor($uid), $table,
                                    $request->method, self::APP_ID,
                                    self::METHOD_SECURITY_MAP)) {
        return $this->error('Access denied for this resource.');
    }

    // 7. + 8. ownership, sharing and response shaping happen inside
    return $this->multi->index($request);
}

Checklist for a new method:

Add it to methodSecurityMap — an unmapped method is dead on arrival.
Add it to methodTableMap so the table never comes from the request.
If it touches schema, map it to a *_table right, not a *_data right.
If it addresses rows by anything other than the primary key, add that field to validPkFields and handle it in inferAliasFromId().
If it deletes across relations, declare the shape in getCascadeMap().

Fail-closed summary

No uid — denied.
Unmapped operation — denied.
Unknown action verb at the resource layer — denied.
Unresolvable database handle — exception, not a pass.
Any exception inside a table or resource check — denied.
Unknown role at the route layer — denied.
Empty uid in a scoped query — 1 = 0.

The one place the default runs the other way is the global fallback at the end of isTablePermitted().

Review notes

Points worth revisiting as the permission model grows:

Custom maps replace rather than merge. Consider array_merge($baseMap, $customMap) so apps extend the vocabulary instead of replacing it, and cannot accidentally unmap a base operation they still use.
$mappedPermission is interpolated into SQL in isTablePermitted() as a column name. It is safe only because callers pass constants. Validate it against the known column list before use, so a future caller cannot pass a request value through it. (isResourcePermitted() is already safe here — its column comes from a match expression.)
A missing user row falls back to default user permissions. In verifyAccess(), an unknown uid yields USER_FLAG defaults rather than a denial. Enforcement then rests entirely on the upstream identity layer. Denying outright when the user row does not exist would be stricter.
The global table fallback. As noted in Layer 5, this makes the table registry an override list rather than an allow-list.
Reflection-based PDO discovery in resolveSecurityPdo() reaches into private model properties. It works, but it couples the trait to model internals — prefer setSecurityPdo() at construction time in new code.
Permission cache lifetime. $this->userPermissions lives for the request and is keyed to whoever was checked first. If a controller instance ever serves two identities in one request, clear it between them.