8

When i'm create new user or grant privileges to existing, i got this error:

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

Grant privileges ok on all tables, except information_schema, on this table i got access denied error. How i can fix? Dump all databases, drop all databases, and then restore from dump?

aranel
  • 191
  • 1
  • 1
  • 8
  • 2
    You didn't `FLUSH PRIVILEGES;`. Or put in the wrong permissions. – Daniel W. Jun 02 '14 at 07:34
  • mysql> grant all privileges on *.* to 'root'@'localhost' with grant option; ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) mysql> grant all privileges on some_db.* to 'root'@'localhost' with grant option; Query OK, 0 rows affected (0.00 sec) – aranel Jun 02 '14 at 08:03

3 Answers3

5

The MySQL documentation says:

... you can only read the contents of tables, not perform INSERT, UPDATE, or DELETE operations on them.

JasonMArcher
  • 14,195
  • 22
  • 56
  • 52
Adam John
  • 196
  • 1
  • 7
3

It is not necessary to grant access to these tables. All users already have access.

"Each MySQL user has the right to access these tables, but can see only the rows in the tables that correspond to objects for which the user has the proper access privileges. In some cases (for example, the ROUTINE_DEFINITION column in the INFORMATION_SCHEMA.ROUTINES table), users who have insufficient privileges will see NULL."

https://bugs.mysql.com/bug.php?id=45430

http://dev.mysql.com/doc/refman/5.1/en/information-schema.html

Wodin
  • 3,243
  • 1
  • 26
  • 55
1

Note that if you have a typo in the name information_schema, you would also get an denied to user message. This is confusing sometimes.

select * from bogusSchema.bogusTable;

The error would be

Error Code: 1142. SELECT command denied to user 'user123'@'localhost' for table 'bogusTable'

I am using an InfoBright variant of MySQL. Not sure if this same problem exists with other variants/versions of MySQL.

leeyuiwah
  • 6,562
  • 8
  • 41
  • 71