MySQL - Is it possible to assign permission to count the number of tables in a database without permissions on individual tables?

Question

As part of a script I am trying to count the number of tables that exist in a particular MySql schema.

Using my own user I am using the query

SELECT COUNT(*) FROM information_schema.tables
WHERE table_schema = dbName 

However I'd like the user running this script to have as few permissions as possible, specifically I don't want them to have any select privileges on any of the tables belonging to the schema

Is it possible to assign a grant which would allow a user to count the number of tables without having any access to the tables themselves?

Answer 1

afaik, this is not possible. However, as an workaround you can create function which makes count(*) under required schema and grant execute on this function to user. User will not be able to access table, but will get required count.

Answer 2

One option would be to give the user access to a single non-NULL column that doesn't provide very much information, but will allow COUNT(*) to succeed.

For example:

GRANT SELECT(TABLE_TYPE) ON INFORMATION_SCHEMA.TABLES TO blind_user@localhost;

Now, blind_user@localhost can execute SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES but can't get any private data out of the table itself.