What are the correct permissions to allow a user to query tables in `information_schema`

Help Security
1

Looking to grant our Tableau a user limited permissions.

So far, this has worked:

GRANT SELECT, CREATE TEMPORARY TABLES, SHOW METADATA ON warehouse.* TO tableau_server@'%';

In Tableau, I can see the information_schema database, but no tables appear. If I try to grant SELECT access to information_schema.*, I get

ERROR 1044 ER_DBACCESS_DENIED_ERROR: Access denied for user 'root'@'%' to database 'information_schema'

What is the right way to grant query access to all tables in information_schema?

2

In Tableau, I can see the information_schema database, but no tables appear.

The tables in information_schema always are present even if you do not have permissions. Please try looking at the information_schema database from a different client to see if it’s a Tableau-related issue.

If I try to grant SELECT access to information_schema.* , I get
ERROR 1044 ER_DBACCESS_DENIED_ERROR: Access denied for user 'root'@'%' to database 'information_schema'

Information schema permissions work differently, you don’t grant them directly. Information schema allows you to see information on the database objects you have access to - if you have permissions to see certain databases or certain tables, you will see those in information_schema, and not the ones you do not have permissions to. (Information schema is effectively a set of views with row-level security - not a set of base tables.)

Your grant includes SHOW METADATA which allows access to information_schema data on the warehouse database. This is a right way to grant access in information_schema.

3

Thanks. While the tables did not appear in the Tableau UI, we was able to access them with a custom question

SELECT * FROM information_schema.tables

and use that as the table.