CODEWITHSUNDEEP
mysqlrootprivileges
John
John

Reputation: 1338

root access denied trying to change information_schema

I am logged in as the root user to my MySQL database (Community version 5.6). I have global privileges as I can see by executing SHOW GRANTS;

GRANT ALL PRIVILEGES ON . TO 'root'@'%' IDENTIFIED BY PASSWORD '*E5BDEE3EBCA26FB68C52549E9AD22869FCE94926' WITH GRANT OPTION

However, I am trying to change a variable in the information_schema database global_variables table and getting access denied for user root@% to database information_schema

How can I change this value in the global_variables table?

Upvotes: 4

Views: 3790

Answers (2)

Bernd Buffen
Bernd Buffen

Reputation: 15057

You can change the host of the grants like this

UPDATE mysql.user SET Host='%' WHERE Host='localhost' AND User='username';
FLUSH PRIVILEGES;

Upvotes: 0

Petr Hejda
Petr Hejda

Reputation: 43571

information_schema is read-only by design. As you can read in the MySQL manual, you can't write in the table even if the user has permissions to do so.

You can change global variables by SQL query if the user has permissions:

SET GLOBAL <key> = <value>;

Upvotes: 2

Related Questions