Privilege required to run ALTER INSTANCE RELOAD TLS is too high

[mzhang77]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

% mysql -h 127.0.0.1 -P4000 -u root      

mysql> create user 'jack'@'%' identified by 'tidb';
Query OK, 0 rows affected (0.01 sec)

mysql> grant connection_admin on *.* to 'jack'@'%';
Query OK, 0 rows affected (0.01 sec)

mysql> exit

% mysql -h 127.0.0.1 -P4000 -u jack -p   

mysql> ALTER INSTANCE RELOAD TLS;
ERROR 1227 (42000): SUPER
mysql> exit

% mysql -h 127.0.0.1 -P4000 -u root   

mysql> grant super on *.* to 'jack'@'%';
Query OK, 0 rows affected (0.01 sec)

mysql> exit

% mysql -h 127.0.0.1 -P4000 -u jack -p

mysql> ALTER INSTANCE RELOAD TLS;
Query OK, 0 rows affected (0.58 sec)

mysql> exit

2. What did you expect to see? (Required)

There is no Tidb document explain what privilege is required to run ALTER INSTANCE RELOAD TLS.

Per mysql document: https://dev.mysql.com/doc/refman/8.0/en/alter-instance.html

ALTER INSTANCE RELOAD TLS
This action reconfigures a TLS context from the current values of the system variables that define the context. It also updates the status variables that reflect the active context values. This action requires the CONNECTION_ADMIN privilege.

3. What did you see instead (Required)

But in TiDB, only CONNECTION_ADMIN is not enough. SUPER is required to run this command.

There are 2 issues here:

1. It is not compatible with mysql.
2. DBA have to grant SUPER privilege, give database user to much privileges.

4. What is your TiDB version? (Required)

tidb_version(): Release Version: v7.5.0
Edition: Community
Git Commit Hash: 069631e
Git Branch: heads/refs/tags/v7.5.0
UTC Build Time: 2023-11-24 08:50:14
GoVersion: go1.21.3
Race Enabled: false
Check Table Before Drop: false
Store: tikv

[dveeden]

mysql> ALTER INSTANCE RELOAD TLS;
ERROR 1227 (42000): SUPER

This is not a correct error message.

I think this might fix it:

diff --git a/pkg/planner/core/planbuilder.go b/pkg/planner/core/planbuilder.go
index 4192c0c0c1..80da51bc92 100644
--- a/pkg/planner/core/planbuilder.go
+++ b/pkg/planner/core/planbuilder.go
@@ -3310,7 +3310,7 @@ func (b *PlanBuilder) buildSimple(ctx context.Context, node ast.StmtNode) (Plan,
                err := ErrSpecificAccessDenied.GenWithStackByArgs("RELOAD")
                b.visitInfo = appendVisitInfo(b.visitInfo, mysql.ReloadPriv, "", "", "", err)
        case *ast.AlterInstanceStmt:
-               err := ErrSpecificAccessDenied.GenWithStack("SUPER")
+               err := ErrSpecificAccessDenied.GenWithStackByArgs("SUPER")
                b.visitInfo = appendVisitInfo(b.visitInfo, mysql.SuperPriv, "", "", "", err)
        case *ast.RenameUserStmt:
                err := ErrSpecificAccessDenied.GenWithStackByArgs("CREATE USER")

[ljun0712]

The dynamic privilege mechanism was introduced in MySQL 8.0, and now MySQL's dynamic privilege design is relatively complete.
Referring to the MySQL design, TiDB 5.1 also introduces the same dynamic privilege mechanism, but the current design is not perfect. Therefore, some operations are still only allowed to be executed with SUPER privilege. We are sorting out the current situation and improving TiDB’s dynamic privilege function.