Mysql – Unable to query for thread information in thesql using root

mariadbMySQLpermissionsprocess

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@%?

Best Answer

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.

Related Solutions

Mysql – Access denied for user ‘root’@’localhost’ (using password yes)

If you installed MySQL 5.6 from the command line, the easiest way to find the temporary password is to go to the Linux command line do the following:

cd
cat `ls -la | grep mysql | grep secret | awk '{print $9}'` | awk '{print $18}'

This will quickly print the temporary password.

Try to login using that password

cd
OLDPASSFILE=`ls -la | grep mysql | grep secret | awk '{print $9}'`
PASSWORD=`cat ${OLDPASSFILE} | awk '{print $18}'
mysql -uroot -p${PASSWORD}

If the temporary password is no longer available, then you will have do the following:

NEWPASS="whateveriwant"
service mysql stop
service mysql start --skip-grant-tables
mysql -ANe"update mysql.user set password=PASSWORD('${NEWPASS}') where user='root'"
service mysql restart

Give it a Try !!!

Mysql – What’s the minimum privilege needed to alter a foreign key constraint

You need to add the "REFERENCES" privilege to your role.

Best Answer

Related Solutions

Mysql – Access denied for user ‘root’@’localhost’ (using password yes)

Give it a Try !!!

Mysql – What’s the minimum privilege needed to alter a foreign key constraint

Related Question