Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

mysql: access denied on information_schema

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?

like image 709
aranel Avatar asked Jun 02 '14 07:06

aranel


People also ask

How do I fix MySQL error Access denied for user root localhost?

You will get this error when the user user_name does not have the right to access your MySQL database. To resolve the error, you must create a user with the following command: mysql> GRANT ALL ON *. * to user_name@localhost IDENTIFIED BY 'password';

What is information_schema in MySQL?

INFORMATION_SCHEMA provides access to database metadata, information about the MySQL server such as the name of a database or table, the data type of a column, or access privileges. Other terms that are sometimes used for this information are data dictionary and system catalog.

What is Mariadb information_schema?

The information_schema database (often called I_S for brevity) is a virtual database that contains informative tables. These tables can be divided into several groups: Metadata tables: Tables such as SCHEMATA , TABLES , and COLUMNS contain information about the structure of databases, tables, columns, and so on.

Is information_schema a database?

information_schema is the database where the information about all the other databases is kept, for example, names of a database or a table, the data type of columns, access privileges, etc. It is a built-in virtual database with the sole purpose of providing information about the database system itself.


2 Answers

The MySQL documentation says:

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

like image 169
Adam John Avatar answered Sep 27 '22 17:09

Adam John


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

like image 20
Wodin Avatar answered Sep 27 '22 16:09

Wodin