Bug Report: CTE names are matched against ACLs and are denied when not present

[wiebeytec]

Overview of the Issue

The name of a CTE is considered a table (object) for the ACL checks. So if you don't approve the CTE name, it fails:

mysql> with my_cte as (select * from sites) select * from my_cte limit 1;
ERROR 1105 (HY000): target: legacy2023.0.primary: vttablet: rpc error: code = PermissionDenied desc = Select command denied to user 'root' for table 'my_cte' (ACL check error) (CallerID: root)

Reproduction Steps

Use this ACL with vttablet --queryserver-config-strict-table-acl --enforce-tableacl-config --table-acl-config file.json

{
    "table_groups": [
        {
            "name": "all_normal_tables",
            "table_names_or_prefixes": [
              "only",
              "explicitly",
              "named",
              "tables"
            ],
            "readers": [
              "root"
            ],
            "writers": [
              "root"
            ],
            "admins": [
              "root"
            ]
        }
    ]
}

And run

with my_cte as (select * from explicitly) select * from my_cte limit 1

Result is:

ERROR 1105 (HY000): target: legacy2023.0.primary: vttablet: rpc error: code = PermissionDenied desc = Select command denied to user 'root' for table 'my_cte' (ACL check error) (CallerID: root)

When adding my_cte to the permission list, or my% it works again.

Binary Version

vtgate --version
vtgate version Version: 20.0.0 (Git revision 8aeb274f6c8eeb1d0e80e17ab6b0ab10a01f60dc branch 'HEAD') built on Thu Jun 27 05:07:30 UTC 2024 by runner@fv-az1543-229 using go1.22.4 linux/amd64

Operating System and Environment details

# cat /etc/lsb-release 
DISTRIB_ID=Ubuntu
DISTRIB_RELEASE=24.04
DISTRIB_CODENAME=noble
DISTRIB_DESCRIPTION="Ubuntu 24.04 LTS"

### Log Fragments

_No response_

[GuptaManan100]

We discussed this in the team, and we have a solution that we would like to try out. We're gonna use schema-tracking to get the list of tables that are available. Any table name that is not present there, we will not check ACLs for them. This will fix the issue for CTEs since those aren't actual tables and we would then skip ACL checks for them.