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?
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';
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.
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.
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.
The MySQL documentation says:
... you can only read the contents of tables, not perform INSERT, UPDATE, or DELETE operations on them.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With