[Feature] Fine-grained privilege for dual password clauses (APPLICATION_PASSWORD_ADMIN equivalent)

[raghav-reglobe]

Search before asking

• I had searched in the issues and found no similar issues.

Description

#66115 gates RETAIN CURRENT PASSWORD / DISCARD OLD PASSWORD behind the GRANT tier, including on one's own account. That closes the park-a-secondary-password hole, but it also means the only accounts that can rotate themselves with an overlap window are accounts that already hold account-management power — which excludes the population that needs online rotation most: low-privilege machine identities (BI tools, service accounts, AI/MCP integrations) holding a long-lived credential.

MySQL's answer is a dedicated dynamic privilege: APPLICATION_PASSWORD_ADMIN authorizes the dual password clauses on one's own account, so a DBA can delegate exactly "you may rotate yourself with an overlap" and nothing else.

Two pieces:

1. APPLICATION_PASSWORD_ADMIN_PRIV — a new GLOBAL-context entry in Privilege (index 15; 9–11 are the contaminated legacy indexes, 12–14 are taken). Grantable via the existing grammar (GRANT APPLICATION_PASSWORD_ADMIN_PRIV ON *.*.* TO user — privilege names resolve through AccessPrivilege.fromName(), no lexer work). The own-account clause checks in SetPassVarOp.validate() / AlterUserInfo.validate() accept it as an alternative to GRANT/ADMIN; clauses on other accounts keep the GRANT tier (MySQL requires CREATE USER there, and GRANT is Doris's account-management tier).
2. Secondary-password introspection — per the [feat](auth) Support MySQL-compatible dual password (RETAIN CURRENT PASSWORD / DISCARD OLD PASSWORD) #66115 review, there is currently no way to ask "who is holding a retained secondary password right now" (fe.audit.log keeps the RETAIN clause, but that's forensics, not state). MySQL exposes it in mysql.user.User_attributes. Surface a boolean per account (e.g. in SHOW GRANTS output or an information_schema column) so operators can find leftovers to DISCARD.

How

All FE-side, small surface:

• Privilege.java: new enum entry + privileges map slot 15 + the notBelongTo* arrays (global-only, like NODE/ADMIN).
• AccessPrivilege.java: name + toDorisPrivilege() mapping.
• PrivPredicate: a predicate covering APPLICATION_PASSWORD_ADMIN_PRIV | GRANT_PRIV | ADMIN_PRIV, used by the own-account clause checks.
• Compatibility: a new privilege bit in PrivBitSet follows the same forward/backward story as SHOW_VIEW_PRIV (index 14) did — an older FE ignores the unknown bit; granting the privilege before all FEs are upgraded means older followers won't honor it until upgraded.
• Tests: grant → self-RETAIN works without GRANT; revoke → rejected; other-account clauses still require GRANT.

Happy to implement this — it falls out of the #66115 review discussion with @CalvinKirs, who suggested deferring the fine-grained privilege until someone actually needs the delegation. We do: fleets of read-only service accounts that must rotate on a schedule without a maintenance window and must never hold GRANT.

Use case

Online credential rotation for low-privilege machine identities (BI tools, service accounts) on a stable username: the account rotates its own password with SET PASSWORD ... RETAIN CURRENT PASSWORD under a delegated privilege, consumers converge during the overlap window, and no account-management-capable credential is involved in the loop.

Related issues

#66115 #66113

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