Bug #91116 Sql query working with Mysql server 5.7 is not working with 8.0.11

• Submitted: 3 Jun 2018 18:30
• Modified: 4 Jun 2018 4:37
• Reporter: Viral Jain
• Status: Not a Bug
• Category: MySQL Server: Security: Privileges
• Severity: S3 (Non-critical)
• Version: 8.0.11
• OS: Windows (Windows 7 64 bit)
• CPU Architecture: Any

[3 Jun 2018 18:30] Viral Jain

Description:
Following sql query is working fine with sql server 5.7.xx and failing with 8.0.11:

GRANT ALL PRIVILEGES ON kickload.* TO 'kickloadadmin'@'%' IDENTIFIED BY 'kickloadadmin' WITH GRANT OPTION;

How to repeat:
1. Install mysql 8.0.11
2. Install following queries in sequence:
# Create the schema
CREATE SCHEMA kickload DEFAULT CHARACTER SET 'utf8';

# Create user for the schema
CREATE USER kickloadadmin IDENTIFIED BY 'kickloadadmin';

# Grant all privileges to the user for the schema
GRANT ALL PRIVILEGES ON kickload.* TO 'kickloadadmin'@'%' IDENTIFIED BY 'kickloadadmin' WITH GRANT OPTION;

[4 Jun 2018 4:37] MySQL Verification Team

Hi Viral,

Thank you for the report.
Imho this is an expected and documented behavior in 8.0.11. Quoting from our manual. 

"Deprecation and Removal Notes"
.

- IDENTIFIED BY PASSWORD 'hash_string' syntax for CREATE USER and GRANT. Instead, use IDENTIFIED WITH auth_plugin AS 'hash_string' for CREATE USER and ALTER USER, where the 'hash_string' value is in a format compatible with the named plugin.

- Additionally, because IDENTIFIED BY PASSWORD syntax has been removed, the log_builtin_as_identified_by_password system variable is superfluous and has been removed. Please refer - https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-11.html#mysqld-8-0-11-deprecation...

Thanks,
Umesh