Cannot grant MySQL user access to information_schema database

user_1996152 0
Viewed 2172

I am creating a Laravel App and testing it in production mode on a fresh installation of Ubuntu 19.10.03. Fresh LAMP installation (PHP 7.3, MySQL 8.0.19-0).

I can create a new database, and a new user that has full access to the database, but Laravel requires that the user have the SELECT option from the 'information_schema' database as well. Here is the process I am using:

$ sudo mysql

mysql> CREATE DATABASE IF NOT EXISTS `my-laravel-database`;
mysql> CREATE USER IF NOT EXISTS 'laravelUser'@'localhost' IDENTIFIED WITH mysql_native_password BY 'myPass';
mysql> GRANT ALL PRIVILEGES ON `my-laravel-database`.* TO 'laravelUser'@'localhost' WITH GRANT OPTION;
mysql> GRANT SELECT ON `information_schema`.* TO 'laravelUser'@'localhost';

The last command always returns 

ERROR 1044 (4200): Access denied for user 'root'@'localhost' to database 'information_schema'

I have tried running the mysql_secure_installation command to set the root password, this did not help. I can run the app as the root user, but I do not want to do this in production.

I have also tried a fresh installation of Ubuntu with just the LAMP services installed.

laravel ubuntu mysql
2 Answers

I wasted DAYS on something tangential to this problem. The answer was in the MySQL manual:

For most INFORMATION_SCHEMA tables, each MySQL user has the right to access them, but can see only the rows in the tables that correspond to objects for which the user has the proper access privileges.

So you can't grant permission to INFORMATION_SCHEMA directly, you have to grant permission to the tables on your own schemas, and as you do, those tables will start showing up in INFORMATION_SCHEMA queries.

Unfortunate, because I'd love have a user who's able to see INFORMATION_SCHEMA, but not the content of the actual tables, and there doesn't seem to be a way to do that.

user_25606410

Try the following:

  1. USE mysql;

  2. ANALYZE TABLE db;   In case table is broken run the following:

  3. REPAIR TABLE db;

Then restart MySQL server.

user_85559370
Related