Unable to query for thread information in mysql using root

Question

I'm connected to a server's mySql database hosted on google cloud using the user 'root' with a password. I was under the impression that the root user had all privileges but when I try to get the thread information using SHOW ENGINE INNODB STATUS, I get an error:

[Error Code: 1227, SQL State: 42000] Access denied; you need (at least one of) the PROCESS privilege(s) for this operation 

Does it have something to do with me trying to access the database remotely? What's the difference between root@localhost and root@%?

Answer 1

This is something you quickly learn with GCP (CloudSQL), AWS (RDS), and all Cloud Providers of MySQL: Even root does not have all privileges.

You could run SHOW GRANTS when logged in as root. Regardless of the privileges it says it has, the Cloud Provider will restrict it.

You asked for a difference between root@localhost and root@%

• root@localhost will allow you to connect to mysqld from within the OS of the MySQL Instance. This requires access mysqld via the socket file. Using any
• root@% will allow you to connect to mysqld from any server via TCP
• Please read my post about how mysqld authenticates users (MySQL error: Access denied for user 'a'@'localhost' (using password: YES))

Sadly, regardless of which root user you use and what SHOW GRANTS says you have, the Cloud Provider will preempt any operation that uses SUPER and PROCESS. I have written 7 posts in the past 4 years describing how Amazon does this with RDS. So, it is not surprising Google does the same thing with CloudSQL.

After all, they don't want to expose the MySQL Container with fixed log file sizes, log file locations, datadir location, and the OS itself.