"ERROR 8121 (HY000): privilege check for 'Select' fail" occurs with an UPDATE statement that includes a CTE

harry1129 commented 3 months ago

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

-- 创建表
create table test.tt1 (id bigint,pid bigint,name varchar(20),fullname varchar(20));

insert into test.tt1 values (1,null,'总公司',''),(2,1,'一级分公司',''),(3,2,'二级分公司','');

MySQL [(none)]> with  t_f as (
    -> select id,pid,name,'AAA' fullname from test.tt1 )
    -> update test.tt1 inner join t_f 
    -> set tt1.fullname=t_f.fullname
    -> where tt1.id=t_f.id;
ERROR 8121 (HY000): privilege check for 'Select' fail
MySQL [(none)]> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MySQL [test]> 
MySQL [test]> with  t_f as (
    -> select id,pid,name,'AAA' fullname from test.tt1 )
    -> update test.tt1 inner join t_f 
    -> set tt1.fullname=t_f.fullname
    -> where tt1.id=t_f.id;
Query OK, 3 rows affected (0.01 sec)
Rows matched: 3  Changed: 3  Warnings: 0

MySQL [test]>   show grants;
+--------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for User                                                                                                                            |
+--------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'u2'@'%'                                                                                                             |
| GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,CREATE ROUTINE,ALTER ROUTINE,ALTER,EXECUTE,INDEX,CREATE VIEW,SHOW VIEW ON test.* TO 'u2'@'%' |
+--------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.01 sec)

MySQL [test]> select tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v6.5.9
Edition: Community
Git Commit Hash: 9815b4534e22d5db87ad38347546071d27c58431
Git Branch: heads/refs/tags/v6.5.9
UTC Build Time: 2024-04-02 10:59:21
GoVersion: go1.19.13
Race Enabled: false
TiKV Min Version: 6.2.0-alpha
Check Table Before Drop: false
Store: tikv
1 row in set (0.00 sec)

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

No error.

3. What did you see instead (Required)

ERROR 8121 (HY000): privilege check for 'Select' fail

4. What is your TiDB version? (Required)

tidb_version(): Release Version: v6.5.9
Edition: Community
Git Commit Hash: 9815b4534e22d5db87ad38347546071d27c58431
Git Branch: heads/refs/tags/v6.5.9
UTC Build Time: 2024-04-02 10:59:21
GoVersion: go1.19.13
Race Enabled: false
TiKV Min Version: 6.2.0-alpha
Check Table Before Drop: false
Store: tikv
1 row in set (0.00 sec)

bb7133 commented 3 months ago

Hi @harry1129 , thanks for reporting this.

I'm not able to reproduce your issue with v6.5.9:

tidb> use test;
Database changed
tidb> create table test.tt1 (id bigint,pid bigint,name varchar(20),fullname varchar(20));
Query OK, 0 rows affected (0.12 sec)

tidb> insert into test.tt1 values (1,null,'总公司',''),(2,1,'一级分公司',''),(3,2,'二级分公司','');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

tidb> create user u2;
Query OK, 0 rows affected (0.03 sec)

tidb> GRANT USAGE ON *.* TO 'u2';
Query OK, 0 rows affected (0.02 sec)

tidb> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,CREATE ROUTINE,ALTER ROUTINE,ALTER,EXECUTE,INDEX,CREATE VIEW,SHOW VIEW ON test.* TO 'u2';
Query OK, 0 rows affected (0.02 sec)

tidb> ^DBye

➜  ~ mysql -u u2 -h 127.0.0.1 -P 4000
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 413
Server version: 5.7.25-TiDB-v6.5.9 TiDB Server (Apache License 2.0) Community Edition, MySQL 5.7 compatible

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| tt1            |
+----------------+
1 row in set (0.00 sec)

mysql> with  t_f as (
    ->     select id,pid,name,'AAA' fullname from test.tt1 )
    ->     update test.tt1 inner join t_f
    ->     set tt1.fullname=t_f.fullname
    ->     where tt1.id=t_f.id;
Query OK, 3 rows affected (0.01 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> show grants;
+--------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for User                                                                                                                            |
+--------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'u2'@'%'                                                                                                             |
| GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,CREATE ROUTINE,ALTER ROUTINE,ALTER,EXECUTE,INDEX,CREATE VIEW,SHOW VIEW ON test.* TO 'u2'@'%' |
+--------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

Could you please check your reproduce steps and see if there's anything I missed?

YangKeao commented 3 months ago

It failed in (p *MySQLPrivilege) RequestVerification. For CTE, the db is empty and table name is t_f. Maybe we should not append CTE tables to the visitInfo in PlanBuilder.buildUpdate :thinking: .

For selection, CTE is not added to the visitInfo, so SELECT works well:

func (b *PlanBuilder) buildDataSource(ctx context.Context, tn *ast.TableName, asName *model.CIStr) (base.LogicalPlan, error) {
    b.optFlag |= flagPredicateSimplification
    dbName := tn.Schema
    sessionVars := b.ctx.GetSessionVars()

    if dbName.L == "" {
        // Try CTE.
        p, err := b.tryBuildCTE(ctx, tn, asName)
        if err != nil || p != nil {
            return p, err // RETURN HERE, so it's not in `visitInfo`.
        }
...

@bb7133 If you execute the query without use test, it'll fail.

pingcap / tidb