WL#11544: Current password required for SET PASSWORD

Affects: Server-8.0   —   Status: Complete

At present an user can change his password without needing to know the current
password. Consider the case when a malicious user gets access to the benign
user's terminal for a very brief period and changes the password. This might
have two implications -
1. Benign user will be unable to access the terminal unless his password is
   reset by administrator.
2. During this period, the malicious user can connect to the server through the
   benign user's changed credentials.

Hence, there is a scope to enhance the security by authorizing the users to
change their password only if they could provide the current password.

However, there could be exceptions to this policy. We must have to design
the feature such that it could be easy to enforce or exempt the current
password requirement at the time of changing it. Keeping these requirements
in mind, this workflow aims to :

1. Change the SQL syntax of CREATE USER and ALTER USER, SET PASSWORD commands
   to enable the user to specify the current password.

2. Introduce a global system variable on the server through which current
   password policy could be enforced to the users. current password will be
   required at the time of changing password in case the system variable is ON
   otherwise it will not be required.
Definitions
-----------
D-1: Change Current Password Policy -  Defines if the user needs to supply the
     current password or not at the time of changing it. It depends on
     following two factors -

   1.a) The value of 'password_require_current' column in the mysql.user table
        for every user.

   1.b) The value of global server variable 'password_require_current'.
        If the system variable is turned ON then this policy is enforced
        otherwise it is not.

      Here is a matrix that indicates how the policy is enforced. The flag
      value for users in the table gets the higher precedence.

      -- Column value ------- Global variable value -----------Policy
      --    N         -------       OFF             ----------- exempt
      --    N         -------       ON              ----------- exempt
      --    Y         -------       OFF             ----------- require
      --    Y         -------       ON              ----------- require
      --    NULL      -------       OFF             ----------- exempt
      --    NULL      -------       ON              ----------- require

D-2: Privileged User - If an user either has 'CREATE USER' privilege, or
     'UPDATE' privilege on the mysql database. This user can change the
     password of other users.

D-3: Refer the Interface Specification section for details about new SQL
     commands and variables introduced.     

Functional requirements
-----------------------

F-1: This feature must work with all plugins that store the password in the
     mysql.user.authentication_string


F-2: Based on the SQL command, enum flag must be set as following in the
     'password_require_current' column of the mysql.user table for an user.

     CREATE USER usr                                     --> NULL
     CREATE USER usr PASSWORD REQUIRE CURRENT            --> Y
     CREATE USER usr PASSWORD REQUIRE CURRENT DEFAULT    --> NULL
     CREATE USER usr PASSWORD REQUIRE CURRENT OPTIONAL   --> N

     ALTER USER usr                                      --> No-OP
     ALTER USER usr PASSWORD REQUIRE CURRENT             --> Y
     ALTER USER usr PASSWORD REQUIRE CURRENT DEFAULT     --> NULL
     ALTER USER usr PASSWORD REQUIRE CURRENT OPTIONAL    --> N

F-3: Privileged users may not provide the current password while changing
     it for self.

     Examples:

     mysql@root> CREATE USER u1 IDENTIFIED BY 'pwd',
               >             u2 IDENTIFIED BY 'pwd',
               >             u3 IDENTIFIED BY 'pwd';

     mysql@root> GRANT CREATE USER ON *.* TO 'u1' WITH GRANT OPTION;
     mysql@root> GRANT UPDATE on mysql.* TO 'u2';

     mysql@u1> ALTER USER user() IDENTIFIED BY 'new_pwd';
     mysql@u1> SET PASSWORD = 'new_pwd';

     mysql@u2> ALTER USER USER() IDENTIFIED BY 'new_pwd';
     mysql@u2> SET PASSWORD = 'new_pwd';

     mysql@u1> ALTER USER u3 IDENTIFIED BY 'new_pwd';
     mysql@u1> SET PASSWORD FOR u3 = 'new_pwd';

     mysql@u2> ALTER USER u3 IDENTIFIED BY 'new_pwd';
     mysql@u2> SET PASSWORD FOR u3 = 'new_pwd';
     
     F-3.1: However, if privileged user specifies the current password while
            changing its own password. It would be treated as if the user
            explicitly wants to verify the current password while changing it.

     mysql@u1> ALTER USER user() IDENTIFIED BY 'new_pwd' REPLACE 'current_pwd';
     mysql@u1> SET PASSWORD = 'new_pwd' REPLACE 'current_pwd';
     mysql@u2> ALTER USER u2 IDENTIFIED BY 'new_pwd' REPLACE 'current_pwd';
     mysql@u2> SET PASSWORD FOR u2 = 'new_pwd' REPLACE 'current_pwd';

     F-3.2: If the user specified his own current password wrong then error
            must be thrown. 
            Following statements must throw error.

     mysql@u1> ALTER USER user() IDENTIFIED BY 'new_pwd' REPLACE 'junk_pwd';
     mysql@u1> SET PASSWORD = 'new_pwd' REPLACE 'junk_pwd';
     mysql@u2> ALTER USER u2 IDENTIFIED BY 'new_pwd' REPLACE 'junk_pwd';
     mysql@u2> SET PASSWORD for u2 = 'new_pwd' REPLACE 'junk_pwd';

       Error - Incorrect current password. Specify the correct password.
       
F-4: Privileged users must not provide the current password while changing
     it for other users.

     Examples : (... From F-3)

     mysql@u1> ALTER USER u3 IDENTIFIED BY 'new_pwd' REPLACE 'current_pwd';
     mysql@u1> SET PASSWORD FOR u3 = 'new_pwd' REPLACE 'current_pwd';
     mysql@u2> ALTER USER u3 IDENTIFIED BY 'new_pwd' REPLACE 'junk_pwd';
     mysql@u2> SET PASSWORD FOR u3 = 'new_pwd' REPLACE 'junk_pwdd';

        Error - No need to specify the current password while changing it for
                other users.

F-5: If the non-privileged user has 'Y' flag set in the table then
     the 'change current password' policy must be enforced irrespective of the
     value of global variable 'password_require_current'

     mysql@root> CREATE USER u1 IDENTIFIED BY '123' PASSWORD REQUIRE CURRENT;
     mysql@root> SHOW VARIABLES LIKE 'password_require_current';
          password_require_current : ON/OFF

     mysql@u1> ALTER USER USER() IDENTIFIED BY '789' REPLACE '123';
     mysql@u1> SET PASSWORD='789' REPLACE '123';

     F-5.1: Throw error in following cases.

     mysql@u1> ALTER USER USER() IDENTIFIED BY '789';
     mysql@u1> SET PASSWORD='789';

        Error - Current password needs to be specified to change the password

F-6: If the non-privileged user has no flag set in the table(i.e NULL value)
     then the 'change current password' policy must be in effect as per the
     value of global variable 'password_require_current'

     mysql@root> CREATE USER u1 IDENTIFIED BY '123' PASSWORD REQUIRE CURRENT
               >  DEFAULT;

     mysql@root> SHOW VARIABLES LIKE 'password_require_current';
            password_require_current : ON

     mysql@u1> ALTER USER USER() IDENTIFIED BY '789' REPLACE '123';
     mysql@u1> SET PASSWORD='789' REPLACE '123';

     mysql@root> SHOW VARIABLES LIKE 'password_require_current';
           password_require_current : OFF

     mysql@u1> ALTER USER USER() IDENTIFIED BY '789';
     mysql@u1> SET PASSWORD='789';

F-7: The non-privileged user do not need to specify the current password,
     if the user is exempted(Refer D-1) from the 'change current password' 
     policy.

     mysql@root> CREATE USER u1 IDENTIFIED BY '123' PASSWORD REQUIRE CURRENT
               > OPTIONAL;

     mysql@u1> ALTER USER USER() IDENTIFIED BY 'new_pwd';
     mysql@u1> SET PASSWORD='new_pwd';

     If the user still specifies the current password then it means user wants
     to the current password to be verified intentionally through it is
     optional

     F7.1) If the current password matches then allow to change the password.

      mysql@u1> ALTER USER USER() IDENTIFIED BY 'new_pwd' REPLACE
              > 'current_pwd';
      mysql@u1> SET PASSWORD='new_pwd' REPLACE 'current_pwd';

     F7.2) If the current password mismatch/not found then error out.

      mysql@u1> ALTER USER USER() IDENTIFIED BY 'new_pwd' REPLACE
              >  'wrong_current_pwd';
      mysql@u1> SET PASSWORD='new_pwd' REPLACE 'wrong_current_pwd';

      Error - Incorrect current password. Specify the correct password.

F-8: Current password require preference must be changeable as following in
     any order.

     CREATE USER u1;  or CREATE USER u1 PASSWORD REQUIRE CURRENT DEFAULT;
     # It will set the column 'password_require_current' as 'NULL'  in the
     mysql.user table. It means that 'change current password' policy will
     vary according to the value of server variable 'password_require_current'.

     ALTER USER u1 PASSWORD REQUIRE CURRENT
     # It will set the column 'password_require_current' as 'Y'  in the
     mysql.user table. It means that 'change current password' policy will
     enforced for that user irrespective the value of the server variable 
     'password_require_current'.

     ALTER USER u1 PASSWORD REQUIRE CURRENT DEFAULT
     # It will set the column 'password_require_current' as 'NULL'  in the
     mysql.user table. It means that 'change current password' policy will
     vary according to the value of server variable 'password_require_current'.

     ALTER USER u1 PASSWORD REQUIRE CURRENT OPTIONAL
     # It will set the column 'password_require_current' as 'N'  in the
     mysql.user table. It means that 'change current password' policy will
     be enforced only if the user specify the current password.
     Refer 'F-7' for examples.

F-9: Changing the variable will become effective immediately globally.

      mysql@root> CREATE USER u1;
      mysql@root> SET VARIABLE @@global.password_require_current='ON'

      mysql@u1> SET PASSWORD='1234' REPLACE '4567';
      mysql@u1> ALTER USER USER() IDENTIFIED BY '1234' REPLACE '4567';

      mysql@root> SET VARIABLE @@global.password_require_current='OFF'

      mysql@u1> SET PASSWORD='1234';
      mysql@u1> ALTER USER USER() IDENTIFIED BY '1234';


F-10: Impact on replication :

      F-10.1: The new clauses to CREATE/ALTER USER will be replicated into the
              ACL statement text if specified in the original statement.

              mysql@root> CREATE USER foo;
              mysql@root> ALTER USER foo;
              mysql@root> CREATE USER foo PASSWORD REQUIRE CURRENT;
              mysql@root> ALTER  USER foo PASSWORD REQUIRE CURRENT;
              mysql@root> CREATE USER foo PASSWORD REQUIRE CURRENT OPTIONAL;
              mysql@root> ALTER  USER foo PASSWORD REQUIRE CURRENT OPTIONAL;
              mysql@root> CREATE USER foo PASSWORD REQUIRE CURRENT DEFAULT;
              mysql@root> ALTER USER foo IDENTIFIED_BY '1234'
                        > PASSWORD REQUIRE CURRENT DEFAULT;

              Must be as following in the binlog.

              CREATE USER foo;
              ALTER USER foo;
              CREATE USER foo PASSWORD REQUIRE CURRENT;
              ALTER USER foo PASSWORD REQUIRE CURRENT;
              CREATE USER foo PASSWORD REQUIRE CURRENT OPTIONAL;
              ALTER USER foo PASSWORD REQUIRE CURRENT OPTIONAL;
              CREATE USER foo PASSWORD REQUIRE CURRENT DEFAULT;
              ALTER USER foo IDENTIFIED_BY '1234' PASSWORD REQUIRE
              CURRENT DEFAULT;
              
       F-10.2: We must omit writing the REPLACE clause of
               SET PASSWORD/ALTER USER in the binlog. This is to avoid having
               the clear text current password.
               We assume that the slave applier will run with enough privileges
               that it won't require it in all cases.
              
               mysql@foo> SET PASSWORD='1234' REPLACE '143';
               mysql@foo> ALTER USER user() IDENTIFIED BY '1234' REPLACE '143';
              
               Must be as follwing in the binlog: 

               SET PASSWORD='1234';
               ALTER USER user() IDENTIFIED BY '1234'; 
                  
               
F-11: Upgrade/Downgrade scenarios :

    F-11.1: The 'mysql_upgrade' utility will add the 'password_require_current'
            column in the mysql.user table with default value 'NULL' for each
            user. 
            That means server must look for the value of the global variable
            'password_require_current' at the time of changing own password.

    F-11.2: 'CREATE|ALTER USER' must adhere to the 'PASSWORD REQUIRE' clause
            if the mysql.user has the column 'password_require_current'.
            In other words server must run fine even without the new column
            'password_require_current' as long as there is no non-default
            "...PASSWORD REQUIRE CURRENT..."|"...REPLACE..." clause specified
            in the CREATE|ALTER|SET PASSWORD statements.

            This will also mean that an 8.0.x server can run fine on a 8.0.x-1
            database. If the any of the clause are specified, server must throw
            an error.

    F-11.3: 8.0.x-1 server must run on a database(created through 8.0.x server)
            that has newly created column.
            
F-12: SHOW CREATE USER will return the PASSWORD REQUIRE CURRENT clause as it
      will be configured at that moment. The same output must be considered for
      any other client i.e. mysqlpump

      Examples :

      mysql@root> CREATE USER u1;
      mysql@root> CREATE USER u1 PASSWORD REQUIRE CURRENT DEFAULT;

      Both of the above statements will set the default value of
      'PASSWORD REQUIRE CURRENT' clause.

      mysql@root> SHOW CREATE USER u1\G
        *************************** 1. row ***************************
        CREATE USER for u1@%: CREATE USER 'u1'@'%' IDENTIFIED WITH
        'caching_sha2_password' REQUIRE NONE PASSWORD EXPIRE DEFAULT
        ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT
        PASSWORD CURRENT REQUIRE DEFAULT

      mysql@root> ALTER USER u1 PASSWORD REQUIRE CURRENT;
      mysql@root> SHOW CREATE USER u1\G
        *************************** 1. row ***************************
        CREATE USER for u1@%: CREATE USER 'u1'@'%' IDENTIFIED WITH
        'caching_sha2_password' REQUIRE NONE PASSWORD EXPIRE DEFAULT
        ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT
        PASSWORD CURRENT REQUIRE

      mysql@root> ALTER USER u1 PASSWORD REQUIRE CURRENT OPTIONAL;
      mysql@root> SHOW CREATE USER u1\G
        *************************** 1. row ***************************
        CREATE USER for u1@%: CREATE USER 'u1'@'%' IDENTIFIED WITH
        'caching_sha2_password' REQUIRE NONE PASSWORD EXPIRE DEFAULT
        ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT
        PASSWORD CURRENT REQUIRE OPTIONAL

      mysql@root> ALTER USER u1 IDENTIFIED BY '1234' PASSWORD REQUIRE CURRENT
                > OPTIONAL;
      mysql@root> SHOW CREATE USER u1\G
        *************************** 1. row ***************************
        CREATE USER for u1@%: CREATE USER 'u1'@'%' IDENTIFIED WITH
        'caching_sha2_password' AS '$A$005$M^h¶ ])1;%Z↓<▼mz \r■^Zc0IxjKzDaeJz32
        edtf0Bc2CJm/EMZbZpZraU.bvod7' REQUIRE NONE PASSWORD EXPIRE DEFAULT
        ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT
        PASSWORD CURRENT REQUIRE OPTIONAL

F-13: When printing the CREATE USER statement in
      audit log/performance schema/error log/query log/ the current password
      will be masked out using exactly the same rules as the new password
      i.e. it will depend on the --log-raw and query rewrite plugin installed.
      
F-14: Providing the incomplete SQL clause must result in usual error.

     >CREATE USER user PASSWORD REQUIRE;
     >ALTER USER user PASSWORD REQUIRE;
     >ALTER USER user() IDENTIFIED BY 'pwd' REPLACE;
     >SET USER user() IDENTIFIED BY 'pwd' REPLACE;
        ERROR 1064 (42000): You have an error in your SQL syntax...
Interface Specification
-----------------------
I-1: Alter user syntax will be modified as following to define whether
     'change current password' policy to be enforced or not for an user.

    ALTER USER [IF EXISTS] user [auth_option] [, user [auth_option]]
    [password_option]

    auth_option: {
        IDENTIFIED BY 'auth_string'  [REPLACE 'current_auth_string']
        IDENTIFIED WITH 'auth_plugin' BY 'auth_string'
            [REPLACE 'current_auth_string']
    }

    password_option : {
        PASSWORD REQUIRE CURRENT [DEFAULT | OPTIONAL]
    }

    Examples :
        mysql@root> ALTER USER foo PASSWORD REQUIRE CURRENT;
        mysql@root> ALTER USER foo PASSWORD REQUIRE CURRENT DEFAULT;
        mysql@root> ALTER USER foo PASSWORD REQUIRE CURRENT OPTIONAL;

        mysql@foo> ALTER USER user() IDENTIFIED BY 'new_pwd';
        mysql@foo> ALTER USER user() IDENTIFIED BY 'new_pwd'
                 > REPLACE 'current_pwd';

        mysql@foo> ALTER USER foo IDENTIFIED BY 'new_pwd';
        mysql@foo> ALTER USER foo IDENTIFIED BY 'new_pwd'
                 > REPLACE 'current_pwd';

        mysql@foo> ALTER USER foo IDENTIFIED BY 'new_pwd'
                 > REPLACE 'current_pwd';
        mysql@foo> ALTER USER foo IDENTIFIED WITH 'mysql_native_password'
                 > BY 'new_pwd' REPLACE 'current_pwd';

        mysql@foo> ALTER USER foo IDENTIFIED WITH 'mysql_native_password'
                 > BY 'new_pwd' REPLACE 'current_pwd' PASSWORD EXPIRE;

I-2: Create user syntax will be modified as following to define whether
     'change current password' policy to be enforced or not for an user.

    CREATE USER [IF NOT EXISTS] 
    user [auth_option] [, user [auth_option]] ...
    [password_option]


    password_option: {
        PASSWORD REQUIRE CURRENT [DEFAULT | OPTIONAL]
    }

     The default value of 'PASSWORD REQUIRE CURRENT' clause is 'DEFAULT'.


    Examples:

        mysql@root> CREATE USER foo PASSWORD REQUIRE CURRENT;
       
        mysql@root> CREATE USER foo PASSWORD REQUIRE CURRENT OPTIONAL;
       
        mysql@root> CREATE USER foo PASSWORD REQUIRE CURRENT DEFAULT;
       
        mysql@root> CREATE USER foo;    # Same effect as of previous statement.
       
        mysql@root> CREATE USER foo IDENTIDIED WITH 'mysql_native_password' BY
                  > '1234' PASSWORD REQUIRE CURRENT DEFAULT PASSWORD
                  > EXPIRE DEFAULT ACCOUNT UNLOCK;

        mysql@root> CREATE USER foo IDENTIDIED BY '1234'
                  > PASSWORD REQUIRE CURRENT DEFAULT PASSWORD
                  > EXPIRE DEFAULT PASSWORD REUSE INTERVAL 360 DAY;

        mysql@root> create user u1 identified by '12',
                                u2 identified by '34'
                                password require current;

I-3: Set password syntax will be modified as following to change the password
     for self.

    SET PASSWORD [FOR user] = password_option

    password_option : {
        'auth_string' [REPLACE 'auth_string']
    }

    Examples :

        SET PASSWORD = 'new_pwd' REPLACE 'current_password';
        SET PASSWORD FOR foo='new_pwd';


I-4: Following server variable will be added. It's global only.
     Either of the SUPER/SYSTEM_VARIABLES_ADMIN privilege is require in order 
     to change it.
        Name - password_require_current
        Scope - Global
        Variable type -  boolean
        Dynamic - Yes
        Default - OFF

I-5: A new config option --password-require-current must be added corresponding
     to the global variable added in I-4.

I-6: Following new column will be added in the mysql.user table.
        Field: password_require_current
        Type: enum('N','Y')
        Null: Yes
        Key:  No
        Default: NULL


High-Level Specification
------------------------

Here is simplified algorithm for this feature.

bool is_required_current_password(ACL_USER *user) {
    if(user->flag_required_current_password == NULL)
        return global_variable_password_require_current_value;
    else if (user->flag_required_current_password == Yes)
        return true;
    else
        return false;
}

bool is_current_pwd_valid(current_pwd) {

if(current_pwd == user->password)
  return true;
else
  return false;
}

bool can_skip_current_pwd = !is_required_current_password(user);

if () {
    if (can_skip_current_pwd ||
        user->has_CREATE_USER_priv ||
        user->has_UPDATE_priv_ON_mysql_*) {

        goto change_pwd_with_optional_current_pass;

    } else {

        goto change_pwd_with_required_current_pass;
    }

} else {

	goto dont_require_the_current_pass;
}

change_pwd_with_optional_current_pass:
{
   if (SQL_command_has_REPLACE_pwd &&
       !is_current_pwd_valid(pwd) {
           error : "Incorrect current password. Specify the valid password."
   } else {
       change_password();
   }
}

change_pwd_with_required_current_pass:

if (not SQL_command_has_REPLACE_pwd) {
    error : "Current password is missing which is required to change the
            password"
} else if (not is_current_pwd_valid(pwd)) {
    error : "Incorrect current password. Specify the valid password."
}
else {
    change_password();
}

dont_require_the_current_pass:

if (SQL_command_has_REPLACE_pwd) {
    error: "No need to specify the current password while changing it for
            other users."
} else {
    change_password();
}

Error messages
---------------

ER_CURRENT_PASSWORD_NOT_REQUIRED
  eng "Do not specify the current password while changing it for other users."

ER_INCORRECT_CURRENT_PASSWORD
  eng "Incorrect current password. Specify the correct password which has to be 
replaced."

ER_MISSING_CURRENT_PASSWORD
  eng "Current password needs to be specified in the REPLACE clause in order to 
change it."

Possible implementation
-----------------------

1. A following method is added which is called from the 
   set_and_validate_user_attributes() method. 

  /**
    Checks, if the REPLACE clause is required, optional or not required.
    It throws error:
    If REPLACE clause is required but not specified.
    If REPLACE clause is not required but specified.
    If current password specified in the REPLACE clause does not match with
    authentication string of the user.

    The plaintext current password is erased from LEX_USER, iff its length > 0 
.

    @param thd      The execution context
    @param Str      LEX user
    @param acl_user The associated user which carries the ACL
    @param auth     Auth plugin to use for verification
    @param is_privileged_user     Whether caller has CREATE_USER_ACL
                                  or UPDATE_ACL over mysql.*
    @param user_exists  Whether user already exists

    @retval true operation failed
    @retval false success
   */
  static bool validate_password_require_current(THD *thd, LEX_USER *Str,
                                              ACL_USER *acl_user,
                                              st_mysql_auth *auth,
                                              bool is_privileged_user,
                                              bool user_exists) {}

2. Following code will insert the column value in the mysql.user table in the 
   existing replace_user_table() method

  if (combo->alter_status.update_current_password != Current_password::NONE) 
  {
    /* ALTER USER .. PASSWORD REQUIRE CURRENT */
    if (table->s->fields > MYSQL_USER_FIELD_PASSWORD_REQUIRE_CURRENT) {
      Field *fld = table->field[MYSQL_USER_FIELD_PASSWORD_REQUIRE_CURRENT];
      Current_password cp = combo->alter_status.update_current_password;
      if (cp == Current_password::DEFAULT) {
        fld->set_null();
      } else if (cp == Current_password::NOT_REQUIRE) {
        fld->store("N", 1, system_charset_info);
        fld->set_notnull();
      } else if (cp == Current_password::REQUIRE) {
        fld->store("Y", 1, system_charset_info);
        fld->set_notnull();
      } else {
        my_error(ER_BAD_FIELD_ERROR, MYF(0), "password_require_current",
                 "mysql.user");
        DBUG_RETURN(-1);
      }
    } else {
      my_error(ER_BAD_FIELD_ERROR, MYF(0), "password_require_current",
               "mysql.user");
      DBUG_RETURN(-1);
    }
  }