3/31/2023 0 Comments Error code 1064 mysql workbenchIf an account specified in the GRANT statement does not exist, then GRANT will fail to create an account unless the non-empty password is specified via IDENTIFIED BY or via IDENTIFIED WITH.GRANT may create a user account if it does not exist.As of MySQL version 5.7.2, if an account already exists, IDENTIFIED by ‘password’ is prohibited and it should be used only during account creation ie., with CREATE USER or ALTER USER.So removing IDENTIFIED from GRANT means, the account remains with the password that was set during creation. Assume if there is an account that already exists with a different password, executing GRANT privileges along with IDENTIFIED BY password will overwrite the existing password. Using IDENTIFIED BY password with GRANT privileges will set any password specified as the new password for the account.The change makes sense for the below reasons: mysql> CREATE USER IDENTIFIED BY 'qc123' Īdd privileges using GRANT: mysql> GRANT ALL PRIVILEGES ON qdb.* TO OK, 0 rows affected (0.03 sec) It means you need to use IDENTIFIED by password with CREATE USER or ALTER USER and apply GRANT PRIVILEGES as shown below:ĬREATE USER and assign a password. Tushar, the use of IDENTIFIED BY password with GRANT statement has been deprecated since MySQL version 5.7.6. mysql> GRANT ALL PRIVILEGES ON qdb.* TO IDENTIFIED BY 'qcuser123' ĮRROR 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 'quser123' at line 1 Error while using GRANT with IDENTIFIED by password However, the same failed in MySQL version 8.0.26 on my new CentOS Stream release 8. Question: I have been using IDENTIFIED BY ‘password’ along with GRANT statement for years in many MySQL versions.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |