WL#11772: CREATE USER IDENTIFIED BY RANDOM PASSWORD

Currently we need to supply a password for CREATE USER if the password policy
mandates it. This means, coming up with a decent password, setting it into MySQL
and then via mysql_config_editor to the login path file.

We could, as an option, have the CREATE USER/ALTER USER/SET PASSWORD commands
generate a random password that it will return as a resultset row to the client. 

The client can then decide to store that password in a login path (either the
current one or another one).

The result will be that the user can then do subsequent logins without having to
retrieve or specify a password. 

And this will track all changes of the password done through the ACL statements.

If it goes out of sync, e.g. when setting the password from another workstation.
we'll need mysql_config_editor to enter the new password and get it back in sync.

This worklog deals with the server side functionality of this task.

R1. The capability of generating a random password will be limited to user
accounts which normally would accept passwords. Setting a random password for an
account for which you can't set a manual password should continue to fail.

R2. Continuation principle
R2.1 The feature must not break backward compatibility. Clients will rely on the
behavior that CREATE USER statements return OK in all situations when a password
isn't automatically created.

R3. Introduce a new syntax.
R3.1 CREATE USER x IDENTIFIED BY RANDOM PASSWORD
This automatically sets a random password generated by a previously specified
global password policy.
R3.2 SET PASSWORD [FOR user] TO RANDOM
This statement set a password for the user to a random password generated by a
previously specified global password policy.
R3.3 ALTER USER user IDENTIFIED BY RANDOM PASSWORD
This statement set a password for the user to a random password generated by a
previously specified global password policy.

R4. Passwords will be returned as an ordinary SELECT result set of the form
(user STRING, host STRING, generated_password STRING)
Example:
+------+------+----------------------+
| user | host | generated password   |
+------+------+----------------------+
| x    | %    | Cr[y89DIoVBUX:tH;vQ, |
+------+------+----------------------+
1 row in set (0,03 sec)


mysql> create user u1 identified by random password, u2 identified by random
password;
+------+------+----------------------+
| user | host | generated password   |
+------+------+----------------------+
| u1   | %    | 64qfYiY2jSmJqGvJGM[n |
| u2   | %    | 6Bs,LYK,6BIEr;)6aGJ< |
+------+------+----------------------+
2 rows in set (0,03 sec)

mysql> set password for u1 to random;
+------+------+----------------------+
| user | host | generated password   |
+------+------+----------------------+
| u1   | %    | n9s!nTiSf!L>Q 6T?%K} |
+------+------+----------------------+
1 row in set (0,02 sec)


R5. The MySQL C client library must be able to receive the result set as an
ordinary result set.

R6. The new statement must be able to execute as a prepared statement and return
a result set.

R7. Global policy is expressed through a global variable with session scope
which regulates the length of the generated password. If need arise to regulate
password generation in detail the architecture must allow for this. Default
length of the password will be set to 20. The min/max range of this variable is
[5, 255]. To modify the global value the SYSTEM_VARIABLES_ADMIN privilege is
required.

R8. The generated password must not be rejected by any password validation
mechanism. Password validation is there to help humans to make better passwords.

R9. A CREATE USER, ALTER USER, or SET PASSWORD statement that contains an
IDENTIFIED BY RANDOM PASSWORD clause shall be written to the binary log as a
CREATE USER or ALTER USER statement with an IDENTIFIED WITH ... AS 'hash_string'
clause.

S1. Syntax expansion for CREATE USER statements

The auth_option part of the statement is expanded with a rule for creating
random passwords as:

CREATE USER [IF NOT EXISTS]
    user [auth_option] [, user [auth_option]] ...
    DEFAULT ROLE role [, role ] ...
    [REQUIRE {NONE | tls_option [[AND] tls_option] ...}]
    [WITH resource_option [resource_option] ...]
    [password_option | lock_option] ...

auth_option: {
    IDENTIFIED BY 'auth_string'
  | IDENTIFIED WITH auth_plugin
  | IDENTIFIED WITH auth_plugin BY 'auth_string'
  | IDENTIFIED WITH auth_plugin AS 'auth_string'
  | IDENTIFIED BY RANDOM PASSWORD
  | IDENTIFIED WITH auth_plugin BY RANDOM PASSWORD
}

S2. Password corpus will be hard coded as       
  "1234567890abcdefghijklmnopqrstuvwxyz" \
  "ABCDEFGHIJKLMNOPQRSTUVWXYZ"\
  ",.-;:_+*!%&/(){}[]<>@"

The password will be generated from random_password_length random bytes using
openssl RAND_bytes and each byte value will be linearly mapped to the corpus.

S3. generated_random_password_length will be introduced as a global server
variable with a default value of 20.

S4. Syntax expansion for SET PASSWORD statements.

SET PASSWORD [FOR user]
    {TO RANDOM |  = 'auth_string'}
    [REPLACE 'current_auth_string']
    [RETAIN CURRENT PASSWORD]

S5. Syntax expansion for ALTER USER statements.
ALTER USER [IF EXISTS]
    user [auth_option] [, user [auth_option]] ...
    [REQUIRE {NONE | tls_option [[AND] tls_option] ...}]
    [WITH resource_option [resource_option] ...]
    [password_option | lock_option] ...

auth_option:
auth_option: {
    IDENTIFIED BY 'auth_string'
        [REPLACE 'current_auth_string']
        [RETAIN CURRENT PASSWORD]
  | IDENTIFIED WITH auth_plugin
  | IDENTIFIED WITH auth_plugin BY 'auth_string'
        [REPLACE 'current_auth_string']
        [RETAIN CURRENT PASSWORD]
  | IDENTIFIED WITH auth_plugin BY RANDOM PASSWORD
        [REPLACE 'current_auth_string']
        [RETAIN CURRENT PASSWORD]
  | IDENTIFIED WITH auth_plugin AS 'auth_string'
  | DISCARD OLD PASSWORD
  | IDENTIFIED BY RANDOM PASSWORD
}