WL#6409: deprecate PASSWORD() and extend ALTER USER syntax to manage authentication attributes.
Affects: Server-5.7
—
Status: Complete
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 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
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
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 will output syntax of CREATE USER
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 ]
[WITH ]
[]
user_specification:
user [ IDENTIFIED ]
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 , 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 clause:
------------------------------------------
Whatever is specified in BY clause will be used as credentials and will be
stored in authentication_string column of mysql.user table.
- 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.
- Plugin will be auth_plugin and authentication
string will be set to NULL.
- 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.
- 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
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]=
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 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 ]
[WITH ]
user_specification:
user [ IDENTIFIED ]
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
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&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; + }
Copyright (c) 2000, 2024, Oracle Corporation and/or its affiliates. All rights reserved.