WL#10774: Remove old_passwords, PASSWORD(), other deprecated auth features

WL#6409 deprecated a number of items in MySQL 5.7:

1. IDENTIFIED BY PASSWORD 'hash_string'
2. PASSWORD() function and SET PASSWORD statement.
3. Using GRANT to create new users.
4. Using GRANT to change user credentials/ssl_options/connection attributes for users.
5. SQL MODE NO_AUTO_CREATE_USER.
6. old_passwords variable.

The current worklog is to track removal of those items in MySQL 8.0. NOTE: After WL#6409 was written, it was decided to modify removal of item 2:

• SET PASSWORD ... = PASSWORD('auth_string') is deprecated
• SET PASSWORD ... = 'auth_string' is NOT deprecated

so only the SET PASSWORD syntax with PASSWORD() should be removed.

NOTE: It was decided to remove system variable --log-builtin-as-identified-by-password without going through deprecation because underlying support for IDENTIFIED BY PASSWORD is removed.

This is removal worklog for features deprecated in MySQL 5.7.

1. GRANT will not be able to create new users.
2. GRANT will not be able to update user properties like SSL attributes, Resource restrictions, authentication information etc. GRANT can only grant privileges to users.
3. PASSWORD() function and old_password variable will be removed.
4. With 1, there is no need for NO_AUTO_CREATE_USER mode. Hence it will be removed too.
   1. Removal is inline with changes introduced by WL#8383 and WL#9465
      1. DD tables : events, triggers, routines will be updated to NO_AUTO_CREATE_USER
5. --log-builtin-as-identified-by-password will be removed.
   1. SET PASSWORD will now always be rewritten as ALTER USER ... IDENTIFIED WITH ... AS.

• Replication considerations:

1. Modifying user properties through GRANT

• In 5.7 master - 8.0 slave scenario, if master uses GRANT to modify user properties removed by the worklog, slave will halt.
• In 8.0 master - 5.7 slave scenario, such modifications won't happen on master so slave won't be affected.

1. NO_AUTO_CREATE_USER removal

• An 8.0 server will:
  • Ignore NO_AUTO_CREATE_USER if set, while reading from binary log
  • Write conditional comment to negate NO_AUTO_CREATE_USER when mysqlbinlog decodes binary log (This is an extension of WL#8383 and WL#9465 work.). Modified mask value : ~0x1003ff00.
• Case 1.1: 5.7 master - 8.0 slave NO_AUTO_CREATE_USER set to OFF
  • Impact: If master creates new user using GRANT, slave will:
    • Halt if user does not exists on slave.
    • Update user's grant if user exists. In this case master-slave had different set of users to begin with and therefore already diverged before the execution of GRANT.
• Case 1.2: 8.0 master - 5.7 slave
  • Impact: Master won't allow any modification in user properties through grant.

1. --log-builtin-as-identified-by-password removal : This variable was introduce to allow cross-version replication between 5.6-5.7 because 5.7 added ALTER USER ... IDENTIFIED ... WITH ...AS support and modified SET PASSWORD to accept plaintext instead of password hash. Once master-slave are updated to 5.7, there is no case for keeping this variable set to ON. With this worklog, IDENTIFIED BY PASSWORD is gone and there is no point supporting this variable any more.

• If --log-builtin-as-identified-by-password is set to 0 on 5.7 master(or slave), there is no impact.
• If --log-builtin-as-identified-by-password is set to 1 on 5.7 master, 8.0 slave will treat value in : SET PASSWORD FOR <user> = <value> as text and not as hash.
• If --log-builtin-as-identified-by-password is set to 1 on 5.7 slave, there won't be an impact because 8.0 master will never write SET PASSWORD to binary log.