EXPLAIN Select * from view requires different privileges to MySQL

[erjiaqing]

Bug Report

1. What did you do?
   If possible, provide a recipe for reproducing the error.

root:

use test;
create table t ( c int );
create view v as select * from t;
create user u@'%';
grant select on v to u@'%';

u@'%':

use test;
select * from v;
--> ok, no error
select * from t;
--> ok, no priv
explain select * from v;

2. What did you expect to see?

ERROR 1345 (HY000): EXPLAIN/SHOW can not be issued; lacking privileges for underlying table

3. What did you see instead?

no error

4. What version of TiDB are you using (tidb-server -V or run select tidb_version(); on TiDB)?

v3.0.0-beta-271-g630671e41

[wjhuang2016]

Note that to explain a view, user also need privSHOW VIEW See https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_show-view
and

EXPLAIN requires the SELECT privilege for any tables or views accessed, including any underlying tables of views. For views, EXPLAIN also requires the SHOW VIEW privilege.

[xiekeyi98]

And the key is :

@wjhuang2016 May you help us try to solve this issue?

[wjhuang2016]

And the key is :

@wjhuang2016 May you help us try to solve this issue?

Sure :)

[xiekeyi98]

@wjhuang2016
Thank you!
There are some similar PRs for your reference:

• privilege: add drop_priv check when truncate table #9870
• privilege: alter table && rename privilege make same with MySQL #9872

And now we are compatible with MySQL 5.7, rather than MySQL 8.0.

[xiekeyi98]

Note that to explain a view, user also need privSHOW VIEW See https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_show-view
and

EXPLAIN requires the SELECT privilege for any tables or views accessed, including any underlying tables of views. For views, EXPLAIN also requires the SHOW VIEW privilege.

I found the document you used is MySQL8.0, I think we'd better use MySQL5.7 manual.

[wjhuang2016]

Note that to explain a view, user also need privSHOW VIEW See https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_show-view
and

EXPLAIN requires the SELECT privilege for any tables or views accessed, including any underlying tables of views. For views, EXPLAIN also requires the SHOW VIEW privilege.

I found the document you used is MySQL8.0, I think we'd better use MySQL5.7 manual.

Ok, and could you tell me that in what sisutaion we should use 8.0 doc?

[xiekeyi98]

The existing features of TiDB are all compatible with MySQL 5.7.
Currently, we don’t have a plan to make them compatible with MySQL 8.0.
For now, only the Window Functions and RBAC are compatible with MySQL 8.0.

[wjhuang2016]

The existing features of TiDB are all compatible with MySQL 5.7.
Currently, we don’t have a plan to make them compatible with MySQL 8.0.
For now, only the Window Functions and RBAC are compatible with MySQL 8.0.

Thanks, I got it.

[wjhuang2016]

@xiekeyi98
When build the visitInfo in BuildDataSourceFromView we need to know that whether the current stmt is a explain stmt or not. However, planBuilder doesn't provide info about explain.
So I want to add a isExplainStmt in planBuilder.
And the problem is:

tidb/planner/core/planbuilder.go

Lines 1659 to 1667 in d6490c1

	func (b *PlanBuilder) buildExplain(explain *ast.ExplainStmt) (Plan, error) {
	if show, ok := explain.Stmt.(*ast.ShowStmt); ok {
	return b.buildShow(show)
	}
	targetPlan, err := OptimizeAstNode(b.ctx, explain.Stmt, b.is)
	if err != nil {
	return nil, errors.Trace(err)
	}

Presently we have no way to pass the isExplainStmt info to OptimizeAstNode, it's seem that we need to modify the interface of OptimizeAstNode and Optimize
I'm not sure if it's a good idea, could you think of a good idea or give we some advises?

[xiekeyi98]

@zz-jason PTAL, Thanks.

[zz-jason]

@wjhuang2016 You can add a InExplain boolean field in StatementContext, which can be obtained by b.ctx.GetSessionVars().StmtCtx

[wjhuang2016]

@wjhuang2016 You can add a InExplain boolean field in StatementContext, which can be obtained by b.ctx.GetSessionVars().StmtCtx

Great! It's prefect!