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

[raghav-reglobe]

Search before asking

• I had searched in the issues 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

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

• [Feature] Identifying user session in audit logs #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?

• Yes I am willing to submit a PR!

Code of Conduct

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