[Bug]: DAB in multi tenant db throws error when paging results

Bắt đầu từ phần xử lý yêu cầu REST cho phân trang $after và theo dõi cách các vị từ policy.database sử dụng @claims.companyId được kết hợp cho các truy vấn ban đầu và tiếp tục. Bổ sung phạm vi kiểm thử tích hợp cho một entity có item policy ở cấp cơ sở dữ liệu và phân trang vượt quá trang đầu tiên. Hoàn tất nghĩa là các trang tiếp theo trả về kết quả 200 thay vì các phản hồi 403 AuthorizationCheckFailed với cùng JWT.

What happened?

Bug Report: AuthorizationCheckFailed (403) on Paginated ($after) Requests When Using Database-Level Item Policies

Submit at

https://github.com/Azure/data-api-builder/issues/new/choose

---

Summary

When an entity has a database-level authorization policy (policy.database) that references a JWT claim (e.g., @claims.companyId), the first page of a GET request succeeds normally. However, every subsequent paginated request using the $after continuation cursor returned in nextLink fails with:

{
  "error": {
    "code": "AuthorizationCheckFailed",
    "message": "Authorization Failure: Access Not Allowed.",
    "status": 403
  }
}

This occurs 100% of the time, using the same valid JWT token that succeeded on page 1, across multiple different entities with an identical policy shape.

Environment

• DAB Version: Microsoft.DataApiBuilder 2.0.12+0b38aa7cbf4118034ad8dee1f2712b1c4bac4c32
• Database: Microsoft SQL Server (mssql)
• Authentication provider: AzureAD (custom JWT issuer, "provider": "AzureAD" with custom issuer/audience)
• Host mode: development
• Deployment: Data API builder running as a REST API in front of SQL Server views

Steps to Reproduce

1. Configure an entity backed by a SQL view, with a database-level item policy referencing a JWT claim:

"xxxView": {
  "source": {
    "object": "[dbo].[xxxView]",
    "type": "view"
  },
  "fields": [
    { "name": "AppealId", "primary-key": true }
  ],
  "permissions": [
    {
      "role": "authenticated",
      "actions": [
        {
          "action": "read",
          "policy": {
            "database": "@item.CompanyId eq @claims.companyId"
          }
        }
      ]
    }
  ]
}

2. Authenticate and obtain a valid JWT containing a companyId claim.
3. Issue a GET request to the entity's REST endpoint, e.g.:

   GET https://<dab-host>/api/AppealSearchView
   Authorization: Bearer <token>
   

   → Returns 200 OK with up to 1000 rows and a nextLink in the response body, e.g.:

   {
     "value": [ ... up to 1000 rows ... ],
     "nextLink": "AppealSearchView?$after=<base64-cursor>"
   }
   

4. Issue a second GET request using the returned nextLink/$after cursor, with the same Authorization: Bearer <token> header:

   GET https://<dab-host>/api/AppealSearchView?$after=<base64-cursor>
   Authorization: Bearer <token>
   

   → Expected: 200 OK with the next page of results.
   → Actual: 403 Forbidden:

   {
     "error": {
       "code": "AuthorizationCheckFailed",
       "message": "Authorization Failure: Access Not Allowed.",
       "status": 403
     }
   }
   

Reproducibility

• 100% reproducible across 3 different entities (xxxView, xxxView, xxxView), all sharing the identical policy pattern (@item.CompanyId eq @claims.companyId).
• Reproduced under both light (single request) and heavy concurrent load (15 concurrent users / 100 requests).
• Reproduced consistently before and after upgrading DAB from an earlier version to 2.0.12 — upgrading did not resolve the issue.

Root-Cause Investigation / Things Already Ruled Out

We suspected and tested the following client-side causes, all of which were ruled out:

1. Missing Authorization header on the paginated request
   Confirmed via code review and request tracing that the identical Bearer token is attached to every request, including all paginated continuation requests.

2. Missing X-MS-API-ROLE header
   Added an explicit X-MS-API-ROLE: authenticated header to every request (including paginated ones). This had no effect — the same 403 occurred.

3. URL encoding / corruption of the $after cursor value
   The nextLink cursor is base64-encoded and can contain +, /, = characters. We explicitly re-parsed and re-percent-encoded the $after query parameter before issuing the follow-up request (confirmed via request tracing that $after= was correctly encoded as %24after= with a properly escaped value). This had no effect — the same 403 occurred.

Given that:

• Page 1 always succeeds with the token,
• The identical token, role header, and a cleanly re-encoded cursor are used on page 2,
• The failure is 100% reproducible only when a database-level item policy referencing a JWT claim is present,

this strongly suggests the issue lies in how DAB's query builder composes the item-level policy predicate (@item.CompanyId eq @claims.companyId) together with the keyset pagination predicate (based on the $after cursor and primary key) when building/evaluating the SQL for continuation requests — resulting in the authorization check for the paginated query incorrectly evaluating to "no access" rather than a data or SQL error.

Impact

This makes it impossible to reliably paginate through result sets larger than the page size (1000 rows) for any entity that uses a database-level item policy tied to a JWT claim — a common multi-tenant security pattern. Any client (SPA, load tester, mobile app, etc.) attempting to page through results for such an entity will receive a 403 on the second page onward.

Suggested Fix Areas

• Review the SQL query-building logic for paginated ($after) requests specifically when a policy.database predicate is present on the entity's read action.
• Verify the policy predicate substitution/composition logic is applied identically for both the initial query and the continuation (keyset) query.
• Add integration test coverage for: entity with policy.database + pagination beyond page 1.

Attachments / Additional Info Available on Request

• Full dab-config.json entity definitions (redacted connection string) for the 3 affected entities.
• Sample failing request/response pairs (page 1 success + page 2 403) for all 3 entities.
• Decoded JWT claim structure used during testing (contains companyId, userId, userName, role claim).

Version

2.0.12

What database are you using?

Azure SQL

What hosting model are you using?

Local (including CLI)

Which API approach are you accessing DAB through?

REST

Relevant log output

Code of Conduct

• I agree to follow this project's Code of Conduct