Bug #108202 Temptable views on information_schema fails on filters

• Submitted: 19 Aug 2022 21:58
• Modified: 1 Sep 2022 15:21
• Reporter: Leonardo Fernandes
• Status: Closed
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 8.0.29, 8.0.30
• OS: Any
• CPU Architecture: Any

[19 Aug 2022 21:58] Leonardo Fernandes

Description:
You get the error below when using a temptable view on informatino_schema:
ERROR 3566 (HY000): Access to native function 'can_access_view' is rejected.

How to repeat:
On 8.0.29 and 8.0.30, queries on the view below fail: 

mysql > select @@version;
+-----------+
| @@version |
+-----------+
| 8.0.30    |
+-----------+
1 row in set (0.00 sec)

mysql > create or replace algorithm=temptable definer=`root`@`localhost` sql security definer view `test` AS select VIEW_DEFINITION from `information_schema`.`VIEWS`;
Query OK, 0 rows affected (0.02 sec)

mysql > select * from test where VIEW_DEFINITION like 'asdf';
ERROR 3566 (HY000): Access to native function 'can_access_view' is rejected.

If you create the same view with algorithm=merge, it works:

mysql > create or replace algorithm=merge definer=`root`@`localhost` sql security definer view `test` AS select `VIEW_DEFINITION` from `information_schema`.`VIEWS`;
Query OK, 0 rows affected (0.01 sec)

mysql > select * from test where VIEW_DEFINITION like 'asdf';
Empty set (0.00 sec)

This is not reproducible in 8.0.28.

[20 Aug 2022 15:29] MySQL Verification Team

Thank you for the bug report.

[1 Sep 2022 15:21] Christine Cole

Posted by developer:
 
Fixed as of the upcoming MySQL 8.0.32 release, and here's the proposed changelog entry from the documentation team:

Views that access system views could encounter an access-denied error if
the pushdown condition included expressions that used native functions
from the system view.

Thank you for the bug report.

[2 Sep 2022 11:24] MySQL Verification Team

Thank you, Christine ....