MemberJunction / MemberJunction/MJ

TaskGraphDispatcher reads base table __mj.Task directly, bypassing vwTasks grants

Open
#4,513 0 comments 0 reactions 0 assignees View on GitHub
Dominant language
TSQL
Stars
29
Forks
6
Avg merge
1d 8h
Merged PRs (30d)
308

Description

`TaskGraphDispatcher` reconciliation reads the base table `__mj.Task` directly instead of `vwTasks`, so it fails for any login that is not `db_owner`. Found on CDP stage during gate 5 of the 6.1.1 certification (MemberJunction/MJ#4475), immediately after the upgrade completed.

## Symptom

Repeats on the dispatcher's timer, so it fills the log:

```
[TaskGraphDispatcher] Reconciliation failed: Error executing SQL
Error: The SELECT permission was denied on the object 'Task',
database 'CDP_DEV', schema '__mj'.
```

## Why this is not environment-specific

MJ's own generated security model grants on **views and procedures**, not base tables. That is exactly what CDP has, and it is what CodeGen produces:

| Object | Kind | Grants |
|---|---|---|
| `vwTasks` | view | `SELECT` to `cdp_Developer`, `cdp_Integration`, `cdp_UI` |
| `spCreateTask` | procedure | `EXECUTE` to `cdp_Developer`, `cdp_Integration` |
| `Task` | **base table** | **none** |

The base table having no grants is not a misconfiguration, it is the intended shape. We confirmed the same holds for the other `__mj` tables we sampled — `AIAgent`, `AIAgentRun`, `Entity`, `EntityField`, `Conversation`, `ConversationDetail`, `TaskType`, `TaskDependency`, `Skill`, `CredentialType` all have zero explicit table-level permissions.

So any deployment where the application connects as something other than `db_owner` will hit this. It is not caused by our upgrade path, our role setup, or the fact that our database had partially applied migrations.

It happens to be invisible on a stock `mj install`, because the installer's connection is typically the database owner — which is probably why it has not been caught.

## Expected

Reconciliation should read through `vwTasks`, the same way the rest of the runtime reaches entity data, so the grants generated for the entity actually apply.

## Reproduce

1. Install or upgrade to 6.1.1.
2. Connect MJAPI with a login that is **not** `db_owner` and holds only the generated role grants (i.e. `SELECT` on `vwTasks`, `EXECUTE` on the Task CRUD procedures).
3. Start the server and let the task graph dispatcher run its reconciliation pass.

Expected: reconciliation completes. Actual: `SELECT permission was denied on the object 'Task'`, repeating each cycle.

## Impact

Task graph reconciliation never runs on such a deployment. Everything else works — this did not block the upgrade, and the rest of our gate 5 smoke test passed. It is a runtime subsystem being silently dead rather than a data-loss or upgrade problem.

## Notes

Environment: SQL Server on Azure (GP_Gen5_4), MJ 6.1.1, upgraded from 5.51.2. The application login is `BC_Connect`; we verified with `HAS_PERMS_BY_NAME('__mj.Task', 'OBJECT', 'SELECT')` that it returns 0, and that the same login reads `vwTasks` without trouble.

Filing without the `cert-blocker` label — that call belongs to the certification owner. It does not fail a gate on its own, but it does affect every install on the standard role model rather than just ours, so it may be worth more than a routine finding.

Contributor guide

Open the contributing guide

Research direction

Start at the TaskGraphDispatcher reconciliation pass and trace the query that reads task data. Reproduce with a non-db_owner login holding the generated grants, comparing access to __mj.Task and vwTasks. Done means reconciliation completes without repeating SELECT permission errors under the standard role model.

Written by the indexing model from the issue text.

Assessment

Tech stack
sql
Domain
backend, database
Issue type
Bug
Difficulty
3/5
Estimated time
1-2 days
Activity status
Active
Clarity
Mostly clear
Newbie friendliness
72/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.