GRANT ALL ON *.* TO demoemployee@'1.5.6.4' IDENTIFIED BY 'my_password'

Error: 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 'my_password'' at line 1

I don't see any issue with the syntax above but still, it's not working and adding an extra ' on the end of my query.

I've tried this on my MySQL server 8.xx and on online tools like this: https://en.rakko.tools/tools/36/

I've also tried few other queries but couldn't solve.

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'my_password'

How Should I resolve this?

CodePudding user response：

Comparing the documentation of GRANT in MySQL 5.7 to those of 8.0 reveals that authentication characteristics are no longer supported. The 5.7 version also explicitly warns that they already are deprecated in 5.7 and are subject to removal in future versions:

Note

Use of GRANT to define account authentication characteristics is deprecated in MySQL 5.7. Instead, establish or change authentication characteristics using CREATE USER or ALTER USER. Expect this GRANT capability to be removed in a future MySQL release.

So simply remove IDENTIFIED BY 'my_password'.

CodePudding user response：

In reply to @Code Cooker comment: Doesn't really work , it works if you have the privilege to create another user:

mysql> create user  'demoemployee'@'1.5.6.4' IDENTIFIED BY 'TestPass';
Query OK, 0 rows affected (0.07 sec)

mysql> GRANT ALL PRIVILEGES  ON *.* TO 'demoemployee'@'1.5.6.4' ;
Query OK, 0 rows affected (0.09 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.03 sec)

mysql>  show grants for 'demoemployee'@'1.5.6.4';
 ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
| Grants for [email protected]                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |
 ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
| 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 `demoemployee`@`1.5.6.4`                                                                                                                                                                                                                    |
| GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,GROUP_REPLICATION_ADMIN,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `demoemployee`@`1.5.6.4` |
 ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
2 rows in set (0.00 sec)