MYSQL—HowTo: Why does MySQL need database permissions for derived tables? (Community)

View the original community article here

Last tested: Mar 9, 2021
 

At the time the code for the MySQL dialect was written, it did not support WITH, so we had to write derived tables a bit differently. Instead of WITH we use CREATE temp TABLE, which requires database permissions. (This is still different than a PDT since we are not writing the table to the schema, just creating a temp table)

This error can occur when trying to use derived tables without setting up the database permissions:

temp derived_table sql_availability_audit creation failed: SQL Error in CREATE TABLE as SELECT: java.sql.SQLSyntaxErrorException: Access denied for user 'looker'@'%' to database 'looker_tmp'

Later versions of MySQL now support CTEs (WITH), but as of Mar. 2021 Looker still uses temporary tables for ephemeral derived tables.