plan replayer can't handle views correctly if it's dumped and loaded by different users

[time-and-fate]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

mysql -h 127.0.0.1 -P 4000 -u root

create user 'test1';
grant all privileges on test.* to 'test1';

mysql -h 127.0.0.1 -P 4000 -u test1

use test;
create table t(a int, b int);
create view v1 as select a from t;
plan replayer dump explain select * from v1;

Start a new tidb cluster.

mysql -h 127.0.0.1 -P 4000 -u root

plan replayer load 'xxx';
explain select * from v1;

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

The execution plan is displayed.

3. What did you see instead (Required)

> explain select * from v1;
ERROR 1356 (HY000): View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

4. What is your TiDB version? (Required)

master (v9.0.0)

[time-and-fate]

Root cause

If you look at the definition of the view in the plan replayer, you'll find:

create database if not exists `test`; use `test`;
CREATE ALGORITHM=UNDEFINED DEFINER=`test1`@`%` SQL SECURITY DEFINER VIEW `v1` (`a`) AS SELECT `a` AS `a` FROM `test`.`t`

Notice the DEFINER=`test1`@`%` SQL SECURITY DEFINER here.
By default, if it's not specified in the create view statement, the DEFINER would be the current user, and the SQL SECURITY would be DEFINER. This means no matter who is querying this view, it's using the privilege of the DEFINER.

When we load the plan replayer, usually we'll use the root user. Apparently the DEFINER, i.e. the test1 user, doesn't have the privilege to query the test.t table in the new environment.

Workaround

If you meet the error when loading the plan replayer, you can drop the views, remove the DEFINER clause from the create view statements, and manually create the views again.

Possible fix

1. Do not include the DEFINER and SQL SECURITY clauses for view definitions when dumpling the plan replayer.
2. Forcefully specify SQL SECURITY INVOKER for view definitions when dumpling the plan replayer.

[ghazalfamilyusa]

I vote for #1 since most likely replayer is not used to debug issues related to privileges. Also, I suggest adding description of this behaviour to the docs.