0

I tried to grants information_schema but it gave me this error

first I showed my grants but nothing:

mysql> show grants for root;
ERROR 1141 (42000): There is no such grant defined for user 'root' on host '%'

then I tried to grant:

 mysql> grant select on information_schema.* to 'root'@'%' identified by   'password123';
 ERROR 1064 (42000): You have an error in your SQL syntax; check the manual   that corresponds to your MySQL server version for the right syntax to use near    'identified by 'password123'' at line 1

but with show grants; :

mysql> show grants;
 +--------------------------------------------------------------------------------   ---------------------------------------------------------------------------------   ---------------------------------------------------------------------------------   ---------------------------------------------------------------------------------   -----------------------------------------------------------------------+
 | Grants for root@localhost                                                                                                                                                                                                                                                                                                                                                                                    |
 +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN,   PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY  TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `root`@`localhost` WITH GRANT OPTION   |
| GRANT   BACKUP_ADMIN,BINLOG_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,GROUP_REPLICATION _ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SET_USER_ID,SYSTEM_VARIABLES_ADMIN,XA_RECOVER_ADMIN  ON *.* TO `root`@`localhost` WITH GRANT OPTION                                                                                                    |
 | GRANT ALL PRIVILEGES ON `testdb`.* TO `root`@`localhost`                                                                                                                                                                                                                                                                                                                                                  |
 | GRANT ALL PRIVILEGES ON `%`.* TO `root`@`localhost`                                                                                                                                                                                                                                                                                                                                                      |
 | GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION                                                                                                                                                                                                                                                                                                                                             |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 5 rows in set (0.00 sec)

I tried also this but with error:

mysql> GRANT ALL PRIVILEGES ON 'information_schema'.* TO 'root'@'localhost';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual   that corresponds to your MySQL server version for the right syntax to use near   ''information_schema'.* TO 'root'@'localhost'' at line 1

How can I grant my information_schema?

Mohammad Khaled
  • 19
  • 2
  • 13
  • Possible duplicate of [How to grant all privileges to root user in MySQL 8.0](https://stackoverflow.com/questions/50177216/how-to-grant-all-privileges-to-root-user-in-mysql-8-0) – Solarflare Nov 10 '18 at 09:59
  • @solarflare I tried all solutions in that link but didn't work with me! – Mohammad Khaled Nov 10 '18 at 10:35
  • Your code doesn't work because MySQL 8 does not support the syntax you used anymore. The answer in that link contains the corresponding MySQL 8.0 syntax. If it does not work, please add the statements you tried and the error message you get to your question (as it should, if used correctly, work). – Solarflare Nov 10 '18 at 11:16
  • I created a new root user and grant the table db on it and now is granted with root. – Mohammad Khaled Nov 10 '18 at 12:24
  • but information_schema still get error...is it important to grant it if I made grant all? – Mohammad Khaled Nov 10 '18 at 12:34
  • I think I misinterpreted your question. You do not need to/cannot set grants for information_schema. Everyone can use it, but the data will only include objects that the user has some grant to. To set those grants, you need to use the 8.0 syntax. – Solarflare Nov 10 '18 at 13:09
  • @Solarflare So now I granted all tables in db with root user but still I can not be able to see the column of table in dataset in datasource which my min problem which I thought is related to granted – Mohammad Khaled Nov 10 '18 at 13:34
  • this is my main issue https://stackoverflow.com/questions/53224812/cannt-add-mysql-db-table-in-vb-datasource?noredirect=1#comment93352304_53224812 – Mohammad Khaled Nov 10 '18 at 13:38
  • This sounds like a problem in your developing environment. To check the mysql grants, login as the root user (from not-localhost) on a normal client and test if you can access the information schema and can see the table. For the access from VB.net, have to tried [this](https://stackoverflow.com/q/47589648)? – Solarflare Nov 10 '18 at 13:42
  • I tried this https://stackoverflow.com/questions/47589648/could-not-retrieve-schema-information-for-table-or-view-error-in-vs-2017-m-a but didn't work as well. – Mohammad Khaled Nov 10 '18 at 13:45
  • I cannot tell you more about the vb.net problem, but to figure out where the problem lies (so you can focus your investigation there), login (with any mysql client) as this user and then try to access the data. If you can query the information schema and check if you can see the table/view. If you can, the problem is on the vb.net side, if you can't, add details about the error you get (and the query you tried) to your question. If it is on the vb-net side, it might be possible that you need to update your sdk (to support e.g. MySQL 8), although I do not know any details about vb.net. – Solarflare Nov 10 '18 at 13:51
  • I can log as non root with admin user I created in mysql workbench then I can see all tables and columns but in VB still get the error – Mohammad Khaled Nov 10 '18 at 14:06
  • a) In your question, you used a root user. You have to do the grants with the exact same user you used in your vb-connection-settings. Doublecheck if that is the case, otherwise you might be looking for the problem at the wrong place. b) You have to check, with that vb-user-login, using a mysql client on the same pc that runs vb.net, if you can query and see that table in your information schema. If you can, your MySQL grants are fine and the problem is in vb.net (and I cannot help you any further). If you can not, please add details about what does (not) happen to your question. – Solarflare Nov 10 '18 at 16:04
  • a) Yes I used root in vb connection to mysql database and connection is working. and when I add my database in server exploration tab in vb is OK and can see the table with column. b)I am using mysql with the same PC with vb. But the issue in dataset which can import the data table without retrieve the columns. – Mohammad Khaled Nov 10 '18 at 17:36
  • You stated you could log in as **non root**, that is why I wanted to make sure you are using the correct user for your tests. You have not verified/checked that you were able to successfully access the information schema from the mysql client, but I assume that you can (since you can access the data), but I assume you can, and in that case, the problem is on the site of visual studio. Unfortunately, I cannot tell you where to look/how to fix it in vb.net, although as an idea I would try to test MySQL 5.7.22 or below (in case you are on MySQL 8), your driver might not be up to date. – Solarflare Nov 10 '18 at 18:41
  • @Solarflare I added some screenshots in my issue here https://stackoverflow.com/questions/53224812/cannt-add-mysql-db-table-in-vb-datasource please if you can help – Mohammad Khaled Nov 11 '18 at 08:25
  • when I retrieve data table from server explorer in VS it works but when go to datasource not retrieved! – Mohammad Khaled Nov 13 '18 at 15:48

1 Answers1

0
  1. The syntax for granting privileges should be like:
GRANT ALL PRIVILEGES ON testDB.* TO 'root'@'localhost' identified by 'test';

Note: The database name won't be inside quotes.

After overcoming syntax error also, we don't have permission to grant any privilege to the information_schema database.

  1. SHOW GRANTS; or SHOW GRANTS FOR CURRENT_USER; both shows all the granted privileges for the current user. To see grants of root user you need to log in as root and then execute the above command.
robsiemb
  • 6,157
  • 7
  • 32
  • 46
Suparna Podder
  • 16
  • 1