raghav-reglobe opened a new issue, #67506: URL: https://github.com/apache/doris/issues/67506
### Search before asking - [x] I had searched in the [issues](https://github.com/apache/doris/issues?q=is%3Aissue) and found no similar issues. ### Description **The problem: a multi-tenant middle tier cannot make Doris enforce the *end user's* entitlements, nor attribute the query to that person.** A common deployment shape is a service that queries Doris on behalf of many people — an AI/BI gateway, an MCP server, an embedded-analytics backend. Today it has two options, both bad: | Option | What Doris sees | Consequence | |---|---|---| | One shared service account | `svc_x` for every query | `fe.audit.log` and `ROW POLICY … TO <user>` cannot tell people apart; the service must re-implement access control on top of Doris | | One connection per end user with that user's own credentials | the person | Doris sees the person's **full** role union — including grants the service must never exercise (write privileges, other tenants' tables). The middle tier ends up more privileged than the tenant it serves, and it must hold every user's password | What the middle tier actually needs is what MySQL's **`PROXY` privilege** and Oracle's **`ALTER USER … GRANT CONNECT THROUGH`** provide: authenticate as the service, then run *this session* as a named end user, restricted to an explicit subset of that user's roles. Doris has no equivalent — RBAC activates **all** of a user's roles on every connection, and there is no way to narrow a session (#49915 asks for session attribution in the audit log; this is the identity half of the same need). **Proposed feature** ```sql -- session-scoped; no metadata change; only the authenticated session is affected SU 'alice'@'%' WITH ROLES ('tenant_42', 'tenant_42_scoped') [WORKLOAD GROUP 'wg_tenant_42']; ``` Semantics: 1. **Authorization to switch is a grantable privilege using MySQL's own name and syntax**: `GRANT PROXY_PRIV ON 'alice'@'%' TO 'svc_x'@'%'` (MySQL: `GRANT PROXY ON 'alice'@'%' TO 'svc_x'@'%'`), with `''@''` as the wildcard exactly as MySQL defines it, and `ADMIN_PRIV` implying it. Without it the statement is denied. Doris's privilege vocabulary already mirrors MySQL (`SELECT_PRIV`, `GRANT_PRIV`, `USAGE_PRIV`), so `PROXY_PRIV` is the natural name; `SHOW GRANTS` lists it like any other privilege. The one deliberate difference from MySQL: in MySQL a proxy user acquires the proxied user's *full* privileges, whereas here the `WITH ROLES` list is **mandatory** — proxying always narrows (point 2). We think that is the safer default for a middle tier, but are open to allowing a no-list form that behaves exactly like MySQL if maintainers prefer parity. 2. **The role list REPLACES the target's role union for this session** — never widens it. Every requested role must be granted to the target user (the "ceiling"); a role the target does not hold is refused. The target's default/personal grants are *not* active under the switch unless named. 3. **`current_user()` returns the target**, so `ROW POLICY … TO <user>` / `TO ROLE …`, column masking and any `current_user()`-keyed predicate evaluate as the person; **`fe.audit.log` records the person as `User`** with the switcher kept alongside (a new audit column or the existing `Client`/comment field) so the trail shows both "who ran it" and "through what". 4. **Session-only and one-shot**: a second `SU` in a switched session is refused; `resetConnection()` / `COM_CHANGE_USER` revert to the *authenticated* identity (never to the target's full roles); nothing is persisted. 5. **Dormant roles** (the piece that makes the service strictly less privileged than the person): a role property such as `'default_active' = 'false'` marks a role as **inert in ordinary sessions** — `SHOW GRANTS` lists it, but its privileges only apply when explicitly activated through `SU … WITH ROLES`. Tenant-scoped roles are created dormant, so a person logging in directly with their own account does *not* get the gateway's tenant view, and the gateway cannot get anything the person was not granted. 6. A builtin `session_is_narrowed()` (BOOLEAN, FE-constant-folded like `current_user()`) lets policies and diagnostics distinguish a switched session. Implementation sketch (one choke point): `Auth.getRolesByUserWithLdap` returns the session override when one is set on the `ConnectContext`, and filters dormant roles when none is. Everything downstream (privilege checks, row policies, `SHOW GRANTS` for the *session*) follows from that, with no changes to the privilege tables. `current_user()` already reads the context's identity. We have this running as a fork patch: `SU … WITH ROLES` and `session_is_narrowed()` as described; the grantable privilege is currently a config allowlist and dormant roles a config regex, to keep the patch small. The upstream-native shape above (`PROXY_PRIV` + the `default_active` role property) is what we would contribute, and we are happy to adjust naming and syntax to whatever the maintainers prefer — the ask here is agreement on the model before we open the PR. ### Use case An MCP/AI gateway serves ~N tenants. Each tenant is a Doris role (`tenant_<id>`) granting SELECT on exactly that tenant's tables plus a `RESTRICTIVE` row policy keyed on `current_user()` through a membership table. The gateway authenticates once as `svc_gateway`, then per user session runs `SU '<person>' WITH ROLES ('tenant_<id>', 'tenant_<id>_scoped') WORKLOAD GROUP 'wg_tenant_<id>'`. Result: - Doris — not the gateway — enforces the table allowlist, the row policy and the resource lane, because the session *is* the person with exactly those roles. - The audit log names the person; the gateway holds only its own credential. - The same person connecting directly (BI tool, `mysql` client) does **not** see the tenant view, because the tenant roles are dormant — the gateway's grants are not a back door for the person's own account. Without this, the gateway must either run everything as one account (no per-person audit, app-level authz) or hold every person's password and accept that the session carries their full role union. ### Related issues - #49915 — session identification in the audit log (the attribution half of this need) - MySQL `PROXY` privilege (`GRANT PROXY ON 'alice'@'%' TO 'svc'@'%'`, https://dev.mysql.com/doc/refman/8.4/en/proxy-users.html) and Oracle proxy authentication (`ALTER USER alice GRANT CONNECT THROUGH svc WITH ROLE tenant_42`) are the precedents for the shape proposed here — note MySQL's `SET ROLE` (activate a subset of your *own* roles) is deliberately **not** the model: the requirement is a service acting *as another user* with a bounded role set, which is proxy authentication, not role toggling. ### Are you willing to submit PR? - [x] Yes I am willing to submit a PR! ### Code of Conduct - [x] I agree to follow this project's [Code of Conduct](https://www.apache.org/foundation/policies/conduct) -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
