How to grants information_schema in mysql windows
up vote
1
down vote
favorite
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?
mysql
asked Nov 10 at 8:16
Mohammad Khaled
368
|
show 12 more comments
up vote
1
down vote
favorite
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?
mysql
asked Nov 10 at 8:16
Mohammad Khaled
368
Possible duplicate of How to grant all privileges to root user in MySQL 8.0
– Solarflare
Nov 10 at 9:59
@solarflare I tried all solutions in that link but didn't work with me!
– Mohammad Khaled
Nov 10 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 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 at 12:24
but information_schema still get error...is it important to grant it if I made grant all?
– Mohammad Khaled
Nov 10 at 12:34
|
show 12 more comments
up vote
1
down vote
favorite
up vote
1
down vote
favorite
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?
mysql
asked Nov 10 at 8:16
Mohammad Khaled
368
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?
mysql
mysql
asked Nov 10 at 8:16
Mohammad Khaled
368
asked Nov 10 at 8:16
Mohammad Khaled
368
edited Nov 10 at 8:40
asked Nov 10 at 8:16
Mohammad Khaled
368
asked Nov 10 at 8:16
Mohammad Khaled
368
asked Nov 10 at 8:16
Mohammad Khaled
368
368
Possible duplicate of How to grant all privileges to root user in MySQL 8.0
– Solarflare
Nov 10 at 9:59
@solarflare I tried all solutions in that link but didn't work with me!
– Mohammad Khaled
Nov 10 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 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 at 12:24
but information_schema still get error...is it important to grant it if I made grant all?
– Mohammad Khaled
Nov 10 at 12:34
|
show 12 more comments
Possible duplicate of How to grant all privileges to root user in MySQL 8.0
– Solarflare
Nov 10 at 9:59
@solarflare I tried all solutions in that link but didn't work with me!
– Mohammad Khaled
Nov 10 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 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 at 12:24
but information_schema still get error...is it important to grant it if I made grant all?
– Mohammad Khaled
Nov 10 at 12:34
Possible duplicate of How to grant all privileges to root user in MySQL 8.0
– Solarflare
Nov 10 at 9:59
Possible duplicate of How to grant all privileges to root user in MySQL 8.0
– Solarflare
Nov 10 at 9:59
@solarflare I tried all solutions in that link but didn't work with me!
– Mohammad Khaled
Nov 10 at 10:35
@solarflare I tried all solutions in that link but didn't work with me!
– Mohammad Khaled
Nov 10 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 at 11:16
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 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 at 12:24
I created a new root user and grant the table db on it and now is granted with root.
– Mohammad Khaled
Nov 10 at 12:24
but information_schema still get error...is it important to grant it if I made grant all?
– Mohammad Khaled
Nov 10 at 12:34
but information_schema still get error...is it important to grant it if I made grant all?
– Mohammad Khaled
Nov 10 at 12:34
|
show 12 more comments
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
draft saved
draft discarded
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53237202%2fhow-to-grants-information-schema-in-mysql-windows%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Possible duplicate of How to grant all privileges to root user in MySQL 8.0
– Solarflare
Nov 10 at 9:59
@solarflare I tried all solutions in that link but didn't work with me!
– Mohammad Khaled
Nov 10 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 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 at 12:24
but information_schema still get error...is it important to grant it if I made grant all?
– Mohammad Khaled
Nov 10 at 12:34