[Feature] Support caching_sha2_password and per-user IDENTIFIED WITH (move off two-stage SHA-1)

[CalvinKirs]

What we need

Doris only supports MySQL's two-stage SHA-1 password scheme (mysql_native_password): MysqlPassword.makeScrambledPassword() stores SHA1(SHA1(password)), unsalted, one round per stage. MySQL deprecated that plugin in 8.4 and removed it in 9.0.

Two things to add:

1. caching_sha2_password as a local credential type — SHA-256, per-account salt, 5000 iterations. It is what current MySQL clients already speak, so it costs users nothing on the client side.
2. IDENTIFIED WITH <plugin> BY '<password>' on CREATE USER / ALTER USER, so the scheme can be chosen per account and existing accounts can be migrated one at a time.

How

This all lands in fe-core. Worth stating up front, because it is easy to assume otherwise: the fe/fe-authentication/* plugin SPI is not the place for it. The dependency runs fe-core → fe-authentication-{api,spi,handler,role-mapping}, and fe-authentication-plugin-password depends only on api + spi, so it cannot reach org.apache.doris.mysql.privilege.Password or the FE image. That SPI serves delegated authentication (LDAP, integration), which never sees a locally stored hash. The local path is DefaultAuthenticator → Auth.checkPassword*() → UserManager.comparePassword() → MysqlPassword, entirely inside fe-core.

Four pieces:

1. Stored credential format — metadata change. Password is currently a bare byte[], GSON-serialized into the FE image and into journals (PrivInfo, AlterUserOperationLog). It needs to carry an algorithm id, a salt, and an iteration count. This is a metadata format change, so it needs a FeMetaVersion gate and a defined rolling upgrade / downgrade story. This piece should be settled first — it is the one that is hard to take back.

2. Wire protocol. caching_sha2_password is a different handshake, not just a different digest:

• the server advertises the plugin name in the handshake and has to handle AuthSwitchRequest for clients that default to native
• fast-auth path: scramble check against the stored SHA-256 digest
• full-auth path: the client sends the password in the clear, which requires either TLS or an RSA public-key exchange. enable_ssl defaults to false in Doris, so FE would need to manage an RSA keypair (MySQL's caching_sha2_password_private_key_path / _public_key_path), or full-auth has to be TLS-only. This is a design decision worth settling early.

Touches MysqlProto and the handshake/auth packet handling. The non-MySQL lanes (Arrow Flight, HTTP, stream load) need to be checked too, since they reuse the same credential check.

3. Per-account dispatch. Today the authentication method is a cluster-level config (AuthenticateType is DEFAULT | LDAP). With IDENTIFIED WITH it becomes a per-account property, so UserManager.checkPasswordInternal has to dispatch on the account's stored algorithm rather than on config.

4. Grammar and DDL. IDENTIFIED WITH in DorisParser.g4 (today only IDENTIFIED BY PASSWORD? <string>), plumbed through CreateUserInfo / AlterUserInfo, journaled, and surfaced per account in SHOW GRANTS / information_schema so operators can see who is still on the legacy scheme. Plus a config for the default plugin used by CREATE USER and the initial root account — native by default, flippable.

Migration. Hashes cannot be converted without the plaintext, so it is ALTER USER ... IDENTIFIED WITH caching_sha2_password BY '<password>' per account, at the operator's own pace. #66115 (dual password) would give the overlap window that makes this doable without downtime.

MySQL reference

• caching_sha2_password pluggable authentication
• Native pluggable authentication — deprecation notice
• Pluggable authentication
• CREATE USER / ALTER USER — IDENTIFIED WITH syntax