[Feature] PROXY_PRIV + `SU <user> WITH ROLES (...)`: MySQL-style proxy authentication with mandatory role narrowing

### 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 ` 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 ` / `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_`) 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 '' WITH ROLES ('tenant_', 'tenant__scoped') WORKLOAD GROUP 'wg_tenant_'`. 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.

### Update (2026-09-08): working patch + three open questions

I now have a working patch behind this and want to check the model before turning it into a PR.

What runs today on a fork: `SU 'user'@'host' WITH ROLES (...)`, mandatory narrowing (the list replaces the target's role union and every role must already be granted to the target), one-shot per session, `current_user()` = target, `session_is_narrowed()`, and the narrowed role set carried through the information_schema scanner RPCs the same way #67444 threads `current_user_ident` — so a narrowed session doesn't see off-scope table names either. The grant gate and the dormant-role marker are still FE configs in that patch, which is the part I'd rather get right upstream than port as-is.

A few things I'd like a maintainer opinion on:

1. **Shape of the proxy grant.** Doris has no user-target GRANT and no per-user priv table, so the MySQL-faithful `GRANT PROXY_PRIV ON 'alice'@'%' TO 'svc'@'%'` means a new priv pattern on `Role` (image/editlog surface). The cheaper options are (a) a GLOBAL-scope `PROXY_PRIV` only — `GRANT PROXY_PRIV ON *.*.* TO svc`, which is MySQL's `''@''` wildcard case, just a new bit in `Privilege` with `ADMIN_PRIV` implying it — or (b) a user property on the service account (`SET PROPERTY FOR 'svc' 'proxy_users' = '...'`). I'd start with (a) and add per-target restriction later if anyone needs it. Reasonable?

2. **Dormant roles — withdrawn.** Thinking about it more, the `'default_active'` role property isn't needed: with the role list mandatory, the service is bounded by the target's granted roles, and a role the person holds is one they're entitled to use directly too. So the ceiling is simply the target's granted roles, and there's no new role property in this proposal (treat point 5 of the description above as dropped).

3. **Mandatory `WITH ROLES` vs MySQL parity.** I'd keep the list mandatory (the whole point is that the service ends up strictly less privileged than the person), but can add a no-list form that behaves like MySQL PROXY if parity matters more.

One more design note rather than a question: forwarded statements need the override to ride in the forward RPC so the master narrows instead of refusing — same pattern as #67444. And since #67577 reshapes `ConnectContext`/`StmtExecutor`, the override should probably live on whatever the unified session object becomes; happy to wait for Stage 1 there or land against current master, whichever you'd prefer. Can write this up as a DSIP if that's the better venue.

@CalvinKirs @zddr — you've been closest to `mysql/privilege` lately, would value your read.

### 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)

Start by reading Auth.getRolesByUserWithLdap and ConnectContext, then trace how the session identity and roles flow through privilege checks and information_schema scanner RPCs, including the pattern discussed in #67444. Review the ConnectContext/StmtExecutor changes in #67577 and the open proxy-grant and mandatory-role questions; done means the agreed session narrowing, forwarding, attribution, and reset behavior are specified well enough for a PR.

Written by the indexing model from the issue text.