MemberJunction / MemberJunction/MJ
TaskGraphDispatcher reads base table __mj.Task directly, bypassing vwTasks grants
- 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
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