MySQL Bugs: #111580: view CREATE with SQL SECURITY DEFINER can SELECT but cannot EXPLAIN

Bug #111580	view CREATE with SQL SECURITY DEFINER can SELECT but cannot EXPLAIN
Submitted:	27 Jun 2023 11:01	Modified:	28 Jun 2023 13:08
Reporter:	Zihao Wang (OCA)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Security: Privileges	Severity:	S3 (Non-critical)
Version:	8.0, 8.0.31	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	explain, privileges, VIEW

Description:
I created a user that doesn't have the SELECT_ACL on table `foo`, but does have SELECT_ACL and SHOW_VIEW_ACL on the view containing table `foo`. 

Since the view was created with `SQL SECURITY DEFINER`, current user has the privileges to `SELECT * from view` and `SHOW CREATE VIEW view`.

But `EXPLAIN SELECT * from view` was returned with ERROR ER_VIEW_NO_EXPLAIN since current user lacking privileges for underlying table `foo`.

I checked the source code, and the reason explained by the annotation in function "sql_view.cc: parse_view_definition()" is: "This user also should have SELECT_ACL on all underlying tables of the view since this EXPLAIN will disclose information about the number of rows in it."

In this case, current user already has the privileges to get tuples in the view, why can't it get the result of the `EXPLAIN`? 

I think user's privileges for `sql` and `explain sql` should be consistent in any cases.

How to repeat:
#############################################################
login as root
#############################################################
use test;

CREATE TABLE foo(a int);
CREATE VIEW test_priv_view AS SELECT * FROM foo;

CREATE USER nope@localhost;
GRANT ALL PRIVILEGES ON test.test_priv_view to nope@localhost;

#############################################################
login as nope
#############################################################

mysql -u nope -A test

MySQL [test]> SELECT * FROM foo;
ERROR 1142 (42000): SELECT command denied to user 'norb'@'localhost' for table 'foo'

MySQL [test]> SELECT * FROM test_priv_view;
+------+
| a    |
+------+
|    1 |
+------+

MySQL [test]> SHOW CREATE VIEW test_priv_view\G;
*************************** 1. row ***************************
                View: test_priv_view
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `test_priv_view` AS select `foo`.`a` AS `a` from `foo`
character_set_client: utf8mb3
collation_connection: utf8mb3_general_ci

MySQL [test]> EXPLAIN SELECT * FROM test_priv_view;
ERROR 1345 (HY000): EXPLAIN/SHOW can not be issued; lacking privileges for underlying table

Suggested fix:
Keep users with the same privileges on `sql` and `explain sql`, when `sql` contains a view which users has SELECT_ACL & SHOW_CREATE_ACL but no SELECT_ACL on underlying tables.

More specific: when checking SELECT_ACL on view_tables in "sql_view.cc: parse_view_definition()", do the same thing as "sql_resolver.cc: check_view_privileges()". That is, if the view is CREATE with SQL SECURITY DEFINER, then use DEFINER's security_ctx to check user's SELECT_ACL on view_tables.

HI Mr. Wang,

Thank you for your bug report.

However, it is not a bug.

This is described in our Manual, where it clearly says that EXPLAIN requires additional privileges:

"
Additionally, EXPLAIN also requires the SHOW VIEW privilege for any explained view.
"

Not a bug.

Yes, I see that EXPLAIN needs SHOW VIEW privileges for explained view. And maybe you misunderstood my report.

In this case, user `nope` do have the privileges to `SHOW CREATE VIEW test_priv_view`. When I repeat the problem I've also shown it.

The problem that causes the `EXPLAIN` stmt to fail is, user `nope` does not have the SELECT_ACL on table `foo` which is contained in the view `test_priv_view`. The definition of `test_priv_view` is `SELECT * FROM foo`.

Specific to the source code, in "sql_view.cc parse_view_definition()":
"
if (check_table_access(thd, SELECT_ACL, view_tables, false, UINT_MAX,
                           true) ||
        check_table_access(thd, SHOW_VIEW_ACL, &view_no_suid, false, UINT_MAX,
                           true))
      view_ref->view_no_explain = true;
"

You can see that, the first `check_table_access` checks whether the current user has the `SELECT_ACL` to `view_tables`. `view_tables` here is table `foo`. And because user `nope` missing the SELECT_ACL, the first `check_table_access` returns true. Here the SHOW_VIEW_ACL is not even checked.

Hi Mr. Wang,

No SHOW_VIEW_ACL is not checked in that part of code that you indicate, but that code passes through many more functions where that privilege is checked for.

Not a bug.

Sorry for taking so long to reply.

The key problem of this bug is, user do have SHOW VIEW privilege.

I would really appreciate it if you could tell me why I have both SELECT and SHOW VIEW privilege but still can't EXPLAIN the view.

See this:
"
MySQL [test]> SELECT * FROM test_priv_view;
+------+
| a    |
+------+
|    1 |
+------+

MySQL [test]> SHOW CREATE VIEW test_priv_view\G;
*************************** 1. row ***************************
                View: test_priv_view
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `test_priv_view` AS select `foo`.`a` AS `a` from `foo`
character_set_client: utf8mb3
collation_connection: utf8mb3_general_ci

MySQL [test]> EXPLAIN SELECT * FROM test_priv_view;
ERROR 1345 (HY000): EXPLAIN/SHOW can not be issued; lacking privileges for underlying table
"

===============================================================================

As for the source code, gdb shows that the error code was generate in that part of code. Let me show you a little bit more about that part:

"
if (check_table_access(thd, SELECT_ACL, view_tables, false, UINT_MAX,
                           true) ||
        check_table_access(thd, SHOW_VIEW_ACL, &view_no_suid, false, UINT_MAX,
                           true))
      view_ref->view_no_explain = true;

if (old_lex->is_explain() && is_explainable_query(old_lex->sql_command)) {
  // EXPLAIN statement should be allowed on views created in
  // information_schema
  if (!is_infoschema_db(view_ref->db) && view_ref->view_no_explain) {
    my_error(ER_VIEW_NO_EXPLAIN, MYF(0));
    result = true;
    return true;
  }
}
"

ERROR 1345(ER_VIEW_NO_EXPLAIN) is generate here.

Hi,

Can you show a list of privileges assigned to the user who can not run EXPLAIN ?????

There could be a problem with a lack of explanation in the  documentation, which would make this a documentation bug.

Hi,

Some other news. 

All teams agreed that this is not a bug.

Here are more details ......

First of all, it is supposed to be

  GRANT SHOW VIEW ...

without the underscore.

The reason is that EXPLAIN reveals details of the underlying tables that the user does not have access to. By knowing those details, it may be possible to perform other actions on those tables that give access to table data (by utilizing another security hole). Therefore, one must have privileges to the underlying tables to explain a query containing a view.

This is official explanation.

It will not be added to the Manual, since putting all possible details into it would make it too large.

Not a bug.

All right, thank you very much for the explanation.

You are welcome !!!!