WL#6409: deprecate PASSWORD() and extend ALTER USER syntax to manage authentication attributes.

Affects: Server-5.7   —   Status: Complete   —   Priority: Medium

The PASSWORD() function was introduced as a way to manually update the
mysql.user table. This is generally a bad idea and we want to leave the task of
updating the passwords to a dedicated statement which automatically find which
authentication plugin is being used and adjust the password algorithm accordingly.

The proposal is to introduce a new statement, SET CREDENTIALS, which does
exactly which while deprecating SET PASSWORD and the PASSWORD() function.

The new statement would look like this:

SET CREDENTIALS [FOR user] [TO 'password'] [WITH plugin [AS 'auth_string']]
    [REQUIRE {NONE | ssl_option [[AND] ssl_option] ...}]

If <user> is omitted the current_user is used instead.

Statement is eligible for password obfuscation in rewrite module
(sql/sql_rewrite.cc), and should not be added to mysql client history.

Note that this mechanism should support conversion of an existing account from
one authentication plugin to another.  Because some authentication plugins do
not require passwords, this must be optional.  Additionally, the plugin name and
auth_string may be optionally supplied.

F1. CREATE/ALTER USER statement will be extended to support following:
    * authentication plugin using IDENTIFIED WITH
    * authentication string
    * SSL properties (ssl_type, ssl_cipher etc)
    * connection attributes (max_user_connections, max_queries_per_hour etc) 
F2. ALTER USER statement can be used to manage authentication plugins
    for existing users.
F3. ALL user passwords will be stored in authentication_string column and 
    password column will be removed from mysql.user table.
F4. ALTER USER statement when specified with IDENTIFIED WITH clause then
    plugin column is updated, authentication_string will be set to 
    NULL and password_expired column will be set to TRUE in mysql.user table.
F5. ALTER USER statement when specified with IDENTIFIED WITH and AS clause then
    plugin, authentication_string column will be updated, authentication string
    will be used as password.
F6. CREATE/GRANT statement when specified with IDENTIFIED WITH .. AS 
    syntax a deprecated warning will be reported. Also IDENTIFIED BY PASSWORD
    'password' will also be deprecated. 
F7. CREATE/ALTER USER statement when specified with SSL properties like SSL
    type, CIPHER issuer etc corresponding columns will be updated in 
    mysql.user table.
F8. CREATE/ALTER USER statement when specified with connection properties like
    MAX_QUERIES_PER_HOUR, MAX_CONNECTIONS_PER_HOUR etc corresponding columns
    will be updated in mysql.user table.
F9. CREATE/ALTER USER statement can be used to update these attributes for 
    multiple users as well.
    Syntax is extended in such a way that IDENTIFIED WITH plugin_name BY 'str'
    will be specified with every user and other attributes will be globally 
    applied to all users.
    ex: CREATE USER u1@localhost, u2@localhost, u3@localhost, u4@localhost
    IDENTIFIED WITH 'my_plugin' BY 'str' REQUIRE SSL;
    In this case IDENTIFIED WITH 'my_plugin' BY 'str' is applicable only to 
    u4 user and SSL restriction is applied to all u1,u2,u3,u4 users.
F10.CREATE USER statement will now allow creating of users with SSL properties
    and connection properties.
F11.SET PASSWORD statement with PASSWORD() function will not be allowed.
F12.SET PASSWORD='abcd' statement will update authentication_string in 
    mysql.user table of current user. 
F13.SET PASSWORD FOR user1@localhost='abcd' statement will update   
    authentication_string in mysql.user table of user1@localhost.
F14.SET PASSWORD when specified on non SSL connection will report a warning.
F15.GRANT statement if used to create a new user will be deprecated.
F16.GRANT statement if used to modify properties of existing users a warning
    will be reported. GRANT should be used only to specify access rights.
F17.ALTER USER can also be used by a user to change only credential information
    for himself. ex: ALTER USER IDENTIFIED BY 'secret'; Here current user will
    be set in the context.
F18.mysqladmin tool when used to change password on a non ssl connection will
    not be allowed.
F19.rewrite_user_query system variable when ON will rewrite <user_specification>
    syntax of CREATE/ALTER/GRANT statement as 
    "user_name IDENTIFIED WITH plugin_name AS 'hash_string'".
F20.rewrite_user_query system variable when OFF will rewrite
    <user_specification> syntax of CREATE/ALTER/GRANT statement as 
    "user_name IDENTIFIED BY PASSWORD 'hash_string'".
F22.rewrite_user_query system variable will ensure backward compatibility.
F23.SHOW CREATE USER <user_name> will output syntax of CREATE USER <user_name>
    statement. This output when submitted to server should create the user 
    without any syntax error.
Presently there is no means to manage authentication methods for an existing
user. To handle this existing ALTER USER syntax will be modified.
ALTER USER syntax will be modified to specify:
 * authentication plugin using IDENTIFIED WITH
 * password will be specified as authentication string
 * SSL properties (ssl_type, ssl_cipher)
 * connection attributes (max_user_connections, max_queries_per_hour etc )

ALTER USER syntax:
------------------
ALTER USER user_specification [, user_specification] ...
       [REQUIRE <opt_constraints_attr>]
       [WITH <opt_connect_attr>]
       [<opt_password_attr>]
user_specification:
     user [ IDENTIFIED <opt_identified_attr> ]

opt_identified_attr:
      BY 'auth_string'
    | WITH auth_plugin
    | WITH auth_plugin BY 'auth_string'
    | WITH auth_plugin AS 'hash_string'
opt_constraints_attr:
     SSL
   | X509
   | CIPHER 'cipher'
   | ISSUER 'issuer'
   | SUBJECT 'subject'
opt_connect_attr:
      MAX_QUERIES_PER_HOUR count
    | MAX_UPDATES_PER_HOUR count
    | MAX_CONNECTIONS_PER_HOUR count
    | MAX_USER_CONNECTIONS count 
opt_password_attr:
      PASSWORD EXPIRE
    | PASSWORD EXPIRE DEFAULT
    | PASSWORD EXPIRE NEVER
    | PASSWORD EXPIRE INTERVAL N DAY

With this WL authentication_string column of mysql.user table will become
the storage for credential information. All users created/altered
irrespective of any plugin specified will have their credentials stored
in authentication_string column of mysql.user table. With this WL there
will be no difference between authentication string or password string
and will be used interchangeably in several different context in the WL.

ALTER USER statement will modify only the attributes which are specified.
However there will be an exception to this when ALTER USER is used to alter
the authentication plugin. If a user is altered by specifying IDENTIFIED WITH
and without BY <auth_string>, authentication_string column of mysql.user table
will be reset to empty and password_expired column will be set to true so that
modified user is not allowed to login with empty credentials.

Semantics of <opt_identified_attr> clause:
------------------------------------------
Whatever is specified in BY clause will be used as credentials and will be
stored in authentication_string column of mysql.user table.

<BY 'auth_string'> - Plugin will be set to default plugin and auth_string
will be passed to plugin for hash calculation and stored in authentication
string column of mysql.user table.

<WITH auth_plugin> - Plugin will be auth_plugin and authentication
string will be set to NULL.

<WITH auth_plugin BY 'auth_string'> - Plugin will be auth_plugin
and auth_string will be passed to auth_plugin plugin for hash generation
and stored in authentication_string column of mysql.user table.

<WITH auth_plugin AS 'hash_string'> - This syntax will be supported only for 
slave thread. For ex: following statement ALTER USER IDENTIFIED BY 'a' 
will be rewritten in binary logs as ALTER USER IDENTIFIED WITH <default_plugin>
AS 'hash_string'; In slave thread first the default_plugin will validate the
hash string and then stored in mysql.user table.

Password Column of mysql.user table:
------------------------------------
Password column from mysql.user table will be deleted. Whenever mysql server
is upgraded mysql_upgrade has to be run to update the system tables.
mysql_upgrade will first copy all the existing values present in password 
column to authentication_string column and then delete the password column.

Few Scenarios:
--------------
CASE1: 
ALTER USER u1@localhost;
The above statement will be a no op;

CASE2:
ALTER USER u1@localhost IDENTIFIED WITH 'sha256_password';
The above ALTER statement will reset authentication_string column to empty 
value and update the plugin column of mysql.user table. Since there is no 
credential information specified the password_expired column will be set to
true, so that the user is requested to set password during login.

CASE3:
ALTER USER u1@localhost IDENTIFIED WITH 
'mysql_native_password' BY 'auth_str';
The above ALTER statement will update authentication_string, plugin column,
password_last_changed of mysql.user table.

CASE4:
ALTER USER u1@localhost IDENTIFIED WITH 'sha256_password', 
u2@localhost REQUIRE SSL WITH max_user_connections 2;
In this case for u1 plugin column will be updated and password expire column
will be set to true. Other attributes mentioned will be applied to both u1,u2.

CASE5:
ALTER USER u1@localhost, u2@localhost REQUIRE SSL WITH max_user_connections 2;
In this case the attributes mentioned will be applied to both u1,u2.

SET PASSWORD syntax:
--------------------
SET PASSWORD [FOR user]= <cleartext_password>
PASSWORD() function will be removed. If SET PASSWORD is used on non SSL 
connection a warning will be reported.
If an account is expired, then that user will be allowed to do SET PASSWORD
or ALTER USER <user> IDENTIFIED [WITH ..] BY 'credentials' only.

List of things to be deprecated:
--------------------------------
1. IDENTIFIED BY PASSWORD 'hash_string'
2. IDENTIFIED WITH auth_plugin AS 'auth_string'
3. PASSWORD() function.
4. Using GRANT to create new users.
5. Using GRANT to change user credentials/ssl_options/connection attributes
   for users.

CREATE USER syntax:
-------------------
CREATE USER user_specification [, user_specification] ...
       [REQUIRE <opt_constraints_attr>]
       [WITH <opt_connect_attr>]
user_specification:
     user [ IDENTIFIED <opt_identified_attr> ]

opt_identified_attr:
      BY [PASSWORD] 'auth_string'
    | WITH auth_plugin
    | WITH auth_plugin BY 'auth_string'
    | WITH auth_plugin AS 'auth_string'
opt_constraints_attr:
     SSL
   | X509
   | CIPHER 'cipher'
   | ISSUER 'issuer'
   | SUBJECT 'subject'
opt_connect_attr:
      MAX_QUERIES_PER_HOUR count
    | MAX_UPDATES_PER_HOUR count
    | MAX_CONNECTIONS_PER_HOUR count
    | MAX_USER_CONNECTIONS count 

Few Scenarios:
--------------
CREATE USER user1@localhost;
CREATE USER user1@localhost, user2@localhost;
CREATE USER user1@localhost IDENTIFIED BY 'pass';
CREATE USER user1@localhost IDENTIFIED WITH 'sha256_password' REQUIRE SSL;
CREATE USER user1@localhost, user2@localhost WITH MAX_QUERIES_PER_HOUR 4;
CREATE USER user1@localhost IDENTIFIED WITH 'sha256_password' BY 'pass'
REQUIRE SSL WITH MAX_USER_CONNECTIONS 2 ;
CREATE USER user1@localhost WITH MAX_USER_CONNECTIONS 2, user2@localhost
IDENTIFIED WITH 'sha256_password';

GRANT statement:
----------------
GRANT syntax will still remain the same except that <user_specification>
syntax will be changed to be in sync with CREATE/ALTER statements. When GRANT 
statement is used to create a new user a warning will be reported stating 
"Create new user with CREATE USER statement. Using GRANT for creating new user 
will be deprecated.".
When GRANT statement is used to specify credentials, ssl_options or connection
attributes for a user a warning will be reported stating "Specify credentials
or ssl_opionts or connection attributes for a user with ALTER USER statement. 
Using GRANT for changing user authentication properties will be deprecated."
Sql mode NO_AUTO_CREATE_USER will no longer be used with GRANT.

New System Variable:
--------------------
create_user_new_syntax

  Name                : rewrite_user_query
  Scope               : Global
  Type                : Bool
  Default             : OFF
  Command line option : Yes

This variable will control the way in which user management queries are
rewritten into general/audit/replication logs.
If the server is started with this variable old syntax will be written to log 
files. However default behaviour will be to log user management queries with
new syntax. This variable will ensure cross version replication setup to work
without any issues.

Backward Compatibility:
-----------------------
To ensure backward compatibility the existing syntax for CREATE/GRANT USER
is not changed.
IDENTIFIED BY PASSWORD syntax will be rewritten in binlog as 
IDENTIFIED WITH default_auth_plugin AS 'hash_string'. 
IDENTIFIED BY, IDENTIFIED WITH ... BY syntax will be rewritten in binlog as
IDENTIFIED WITH auth_plugin AS 'hash_string'.
This rewritten syntax will only be allowed in slave threads.
In case of general query log and audit log the credential details will be
masked to ensure there is no security breach.

Extending Authentication API:
-----------------------------
Authentication plugin will be extended with 2 new plugin API for generating
their own password digest, validating the password digest.Since password
column will be removed from mysql.user table all credential informations will
be stored in authentication_string column, it will be entire responsibility of 
the authentication plugins to take care of hash generation of credentials and
validation of digest.

New plugin API method:
----------------------
bool (*generate_authenticaiton_string_storage)(uchar *outBuffer, 
      int *inoutBufLength, const uchar *inClearText, int inClearTextLength);
inClearText       - authentication string in clear text as specified in   
                    CREATE/ALTER USER statement
inClearTextLength - length of authentication string
outBuffer         - buffer to store the encrypted authentication string
inoutBufLength    - length of encrypted authentication string.

This API will return FALSE if outBuffer is not enough to store encrypted
string.

bool (*validate_authenticaiton_string_hash)(const char *buffer, 
                                            int bufLength);
buffer            - authentication string hash
bufLength         - length of authentication string hash

This API will return FALSE if hash string is not in correct format as
defined by the plugin.

New Plugin Service:
-------------------
Server will provide a new plugin service to validate the password, evaluate  
strength of a password. This service will provide 2 service functions which
1. validate password based on common policy.
2. Check for strength of a password based on common policy.

bool validate_password(String *password);
int calculate_password_strength(const char *inPassword, int password_length); 

These service functions will make use of validate password plugin if loaded.

Consider a simple example of create user:
CREATE USER u1@localhost IDENTIFIED WITH test_auth_plugin AS 'secret';

1. Client sends create user statement to server.
2. Server based on the plugin specified in create user invokes plugin
   specific hash generating function to get hash from  cleartext 
   password.
3. Inside this plugin API above service functions will be invoked to validate
   password. 
4. If service function returns TRUE then encrypted hash string from the plugin     
   method will be stored by server in mysql.user table else an error will be 
   reported.

modified files:
  sql/auth/auth_common.h
  sql/auth/auth_internal.h
  sql/auth/sql_user.cc
  sql/auth/sql_user_table.cc
  sql/sql_parse.cc
  sql/sql_yacc.yy

=== modified file 'sql/auth/auth_common.h'
bool mysql_alter_user(THD *thd, List <LEX_USER> &list);
Introduce a new function to handle ALTER USER statement. This function will 
replace mysql_user_password_expire().

=== modified file 'sql/auth/auth_internal.h'
bool update_user_table(THD *thd, TABLE *table, LEX_USER *combo,
                       ACL_USER *acl_user); 
update_user_table will be modified as above.

=== modified file 'sql/auth/sql_user.cc'
mysql_user_password_expire() will be renamed to mysql_alter_user() and in this
a call to update_user_table() will take care of all the handling of updating
mysql.user column.

=== modified file 'sql/auth/sql_user_table.cc'

update_user_table(THD *thd, TABLE *table, LEX_USER *combo, ACL_USER *acl_user)
{
  char user_key[MAX_KEY_LENGTH];
  int error;
  const char *password= empty_c_string;
  size_t password_len= 0;
  bool is_password_reset= false;
  bool is_auth_reset= false;
  bool update_password= false;

  DBUG_ENTER("update_user_table");

.......

  if (combo->uses_identified_with_clause)
  {
    if (combo->uses_identified_by_clause)
      update_password= true;
    else if (combo->uses_authentication_string_clause)
    {
      /* if authentication plugin is different reset password */
      if (my_strcasecmp(system_charset_info, combo->plugin.str,
                        acl_user->plugin.str))
        is_password_reset= true;
      table->field[MYSQL_USER_FIELD_AUTHENTICATION_STRING]->
          store(combo->auth.str, combo->auth.length, system_charset_info);
    }
    else
    {
      /* 
        only IDENTIFIED WITH clause is specified so reset
        password and authentication_string
      */
      is_password_reset= true;
      is_auth_reset= true;
    }
    /* update plugin column only if it is different */
    if (my_strcasecmp(system_charset_info, combo->plugin.str,
                      acl_user->plugin.str))
    {
      table->field[MYSQL_USER_FIELD_PLUGIN]->
             store(combo->plugin.str, combo->plugin.length, 
system_charset_info);
      table->field[MYSQL_USER_FIELD_PLUGIN]->set_notnull();
    }
  }
  else if (combo->uses_identified_by_clause ||
           combo->uses_identified_by_password_clause)
  {
    update_password= true;
    /* set default plugin */
    table->field[MYSQL_USER_FIELD_PLUGIN]->
           store(default_auth_plugin_name.str, default_auth_plugin_name.length,
                 system_charset_info);
  }
  if (update_password)
  {
    /* update plugin from acl_user */
    if (combo->plugin.length == 0)
    {
      combo->plugin.str= acl_user->plugin.str;
      combo->plugin.length= acl_user->plugin.length;
    }
    digest_password(thd, combo);
    password= combo->password.str;
    password_len= combo->password.length;

    if (!strcmp(combo->plugin.str, sha256_password_plugin_name.str) ||
      !my_strcasecmp(system_charset_info, combo->plugin.str,
                     sha256_password_plugin_name.str))
      table->field[MYSQL_USER_FIELD_AUTHENTICATION_STRING]->
             store(password, password_len, system_charset_info);
    else
    {
      table->field[MYSQL_USER_FIELD_PASSWORD]->
             store(password, password_len, system_charset_info);
      /* reset the auth string */
      table->field[MYSQL_USER_FIELD_AUTHENTICATION_STRING]->
             store("\0", 0, system_charset_info);
    }
    table->field[MYSQL_USER_FIELD_PASSWORD_EXPIRED]->
           store("N", 1, system_charset_info);
  }

  if (is_password_reset)
  {
    table->field[MYSQL_USER_FIELD_PASSWORD]->
           store("\0", 0, system_charset_info);
    table->field[MYSQL_USER_FIELD_PASSWORD_EXPIRED]->
           store("Y", 1, system_charset_info);
  }

  if (is_auth_reset)
  {
    table->field[MYSQL_USER_FIELD_AUTHENTICATION_STRING]->
           store("\0", 0, system_charset_info);
    table->field[MYSQL_USER_FIELD_PASSWORD_EXPIRED]->
           store("Y", 1, system_charset_info);
  }
...
..
    switch (thd->lex->ssl_type) {
    case SSL_TYPE_ANY:
      table->field[MYSQL_USER_FIELD_SSL_TYPE]->store(STRING_WITH_LEN("ANY"),
                                      &my_charset_latin1);
      table->field[MYSQL_USER_FIELD_SSL_CIPHER]->
        store("", 0, &my_charset_latin1);
      table->field[MYSQL_USER_FIELD_X509_ISSUER]->store("", 0, 
&my_charset_latin1);
      table->field[MYSQL_USER_FIELD_X509_SUBJECT]->store("", 0, 
&my_charset_latin1);
      break;
    case SSL_TYPE_X509:
      table->field[MYSQL_USER_FIELD_SSL_TYPE]->store(STRING_WITH_LEN("X509"),
                                      &my_charset_latin1);
      table->field[MYSQL_USER_FIELD_SSL_CIPHER]->
        store("", 0, &my_charset_latin1);
      table->field[MYSQL_USER_FIELD_X509_ISSUER]->store("", 0, 
&my_charset_latin1);
      table->field[MYSQL_USER_FIELD_X509_SUBJECT]->store("", 0, 
&my_charset_latin1);
      break;
    case SSL_TYPE_SPECIFIED:
      table->field[MYSQL_USER_FIELD_SSL_TYPE]-
>store(STRING_WITH_LEN("SPECIFIED"),
                                      &my_charset_latin1);
      table->field[MYSQL_USER_FIELD_SSL_CIPHER]->store("", 0, 
&my_charset_latin1);
      table->field[MYSQL_USER_FIELD_X509_ISSUER]->store("", 0, 
&my_charset_latin1);
      table->field[MYSQL_USER_FIELD_X509_SUBJECT]->store("", 0, 
&my_charset_latin1);
      if (thd->lex->ssl_cipher)
        table->field[MYSQL_USER_FIELD_SSL_CIPHER]->store(thd->lex->ssl_cipher,
                                strlen(thd->lex->ssl_cipher), 
system_charset_info);
      break;
    case SSL_TYPE_NOT_SPECIFIED:
      break;
    case SSL_TYPE_NONE:
      table->field[MYSQL_USER_FIELD_SSL_TYPE]->store("", 0, &my_charset_latin1);
      table->field[MYSQL_USER_FIELD_SSL_CIPHER]->store("", 0, 
&my_charset_latin1);
      table->field[MYSQL_USER_FIELD_X509_ISSUER]->store("", 0, 
&my_charset_latin1);
      table->field[MYSQL_USER_FIELD_X509_SUBJECT]->store("", 0, 
&my_charset_latin1);
      break;
    }  

    USER_RESOURCES mqh= thd->lex->mqh;
    if (mqh.specified_limits & USER_RESOURCES::QUERIES_PER_HOUR)
      table->field[MYSQL_USER_FIELD_MAX_QUESTIONS]->
        store((longlong) mqh.questions, TRUE);
    if (mqh.specified_limits & USER_RESOURCES::UPDATES_PER_HOUR)
      table->field[MYSQL_USER_FIELD_MAX_UPDATES]->
        store((longlong) mqh.updates, TRUE);
    if (mqh.specified_limits & USER_RESOURCES::CONNECTIONS_PER_HOUR)
      table->field[MYSQL_USER_FIELD_MAX_CONNECTIONS]->
        store((longlong) mqh.conn_per_hour, TRUE);
    if (table->s->fields >= 36 &&
        (mqh.specified_limits & USER_RESOURCES::USER_CONNECTIONS))
      table->field[MYSQL_USER_FIELD_MAX_USER_CONNECTIONS]->
        store((longlong) mqh.user_conn, TRUE);
    mqh_used= mqh_used || mqh.questions || mqh.updates || mqh.conn_per_hour;

..
..

}

=== modified file 'sql/sql_parse.cc'
-    if (!(res= mysql_user_password_expire(thd, lex->users_list)))
+    if (!(res= mysql_alter_user(thd, lex->users_list)))

=== modified file 'sql/sql_yacc.yy'
-        | CREATE USER clear_privileges grant_list
+        | CREATE USER clear_privileges grant_list require_clause opt_connection

...

-        | ALTER USER clear_privileges alter_user_list
+        | ALTER USER clear_privileges alter_user_list require_clause  
                      opt_connection

+opt_connection:
+          /* empty */ {}
+        | WITH opt_connection_list
+
+opt_connection_list:
+          opt_connection_list opt_connection_clause {}
+        | opt_connection_clause {}
+
+opt_connection_clause:
+          MAX_QUERIES_PER_HOUR ulong_num
+          {
+            LEX *lex=Lex;
+            lex->mqh.questions=$2;
+            lex->mqh.specified_limits|= USER_RESOURCES::QUERIES_PER_HOUR;
+          }
+        | MAX_UPDATES_PER_HOUR ulong_num
+          {
+            LEX *lex=Lex;
+            lex->mqh.updates=$2;
+            lex->mqh.specified_limits|= USER_RESOURCES::UPDATES_PER_HOUR;
+          }
+        | MAX_CONNECTIONS_PER_HOUR ulong_num
+          {
+            LEX *lex=Lex;
+            lex->mqh.conn_per_hour= $2;
+            lex->mqh.specified_limits|= USER_RESOURCES::CONNECTIONS_PER_HOUR;
+          }
+        | MAX_USER_CONNECTIONS_SYM ulong_num
+          {
+            LEX *lex=Lex;
+            lex->mqh.user_conn= $2;
+            lex->mqh.specified_limits|= USER_RESOURCES::USER_CONNECTIONS;
+          }
         ;

...
..


+        | user IDENTIFIED_SYM WITH ident_or_text BY TEXT_STRING
+          {
+            if (Lex->sql_command == SQLCOM_REVOKE)
+            {
+              my_syntax_error(ER(ER_SYNTAX_ERROR));
+              MYSQL_YYABORT;
+            }
+            $$= $1;
+            $1->password.str= $6.str;
+            $1->password.length= $6.length;
+            String *password = new (YYTHD->mem_root) String((const 
char*)$6.str,
+                                    YYTHD->variables.character_set_client);
+            check_password_policy(password);
+            $1->uses_identified_by_clause= true;
+            Lex->contains_plaintext_password= true;
+
+            $1->plugin.str= $4.str;
+            $1->plugin.length= $4.length;
+            $1->auth= EMPTY_CSTR;
+            $1->uses_identified_with_clause= true;
+          }
+        | user IDENTIFIED_SYM WITH ident_or_text BY PASSWORD TEXT_STRING
+          {
+            if (Lex->sql_command == SQLCOM_REVOKE)
+            {
+              my_syntax_error(ER(ER_SYNTAX_ERROR));
+              MYSQL_YYABORT;
+            }
+            $$= $1;
+            $1->password.str= $7.str;
+            $1->password.length= $7.length;
+            String *password = new (YYTHD->mem_root) String((const 
char*)$7.str,
+                                    YYTHD->variables.character_set_client);
+            check_password_policy(password);
+            $1->uses_identified_by_clause= true;
+            Lex->contains_plaintext_password= true;
+
+            $1->plugin.str= $4.str;
+            $1->plugin.length= $4.length;
+            $1->auth= EMPTY_CSTR;
+            $1->uses_identified_with_clause= true;
+          }