Introduction
This article will show how to solve an error message. The error message appear upon changing a password from a user exist in the MariaDB Database Server. The following is the information of the MariaDB Database Server :
[root@host ~]# mysql -uroot -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 3167 Server version: 5.5.56-MariaDB MariaDB Server Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]>
Basically, changing the password directly to the field with the name of ‘password’ is not working. Before updating the password of a certain user, just list the user first by executing the following command :
MariaDB [mysql]> select * from user\G
....
*************************** 8. row ***************************
Host: localhost
User: db_user
Password: *2FD92886D8571B01D752A24B8ADED9FE3E6FC2A0
Select_priv: N
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Reload_priv: N
Shutdown_priv: N
Process_priv: N
File_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Show_db_priv: N
Super_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Execute_priv: N
Repl_slave_priv: N
Repl_client_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Create_user_priv: N
Event_priv: N
Trigger_priv: N
Create_tablespace_priv: N
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin:
authentication_string:
8 rows in set (0.00 sec)
MariaDB [mysql]> update user set password=md5('password') where user = 'db_user';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [mysql]>
After updating the password, reexecute the command for listing the user to check whether the password is changed or not as follows :
MariaDB [mysql]> select * from user where user='db_user'\G
*************************** 1. row ***************************
Host: localhost
User: db_user
Password: *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19
Select_priv: N
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Reload_priv: N
Shutdown_priv: N
Process_priv: N
File_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Show_db_priv: N
Super_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Execute_priv: N
Repl_slave_priv: N
Repl_client_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Create_user_priv: N
Event_priv: N
Trigger_priv: N
Create_tablespace_priv: N
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin:
authentication_string:
1 row in set (0.00 sec)
MariaDB [mysql]> quit
Bye
[root@host ~]#
The password has successfully changed. But apparently, after changing the password and then retry to login to the MariaDB database server, the process ends in failure as follows :
[root@host ~]# mysql -udb_user -p Enter password: ERROR 1045 (28000): Access denied for user 'db_user'@'localhost' (using password: YES) [root@host ~]#
Solution
So, in this article’s context, the solution for the above problem exist in the introduction is by executing another type of query. The previous query for changing the process is failing but not in the following query. So, execute the following query to update the password :
[root@host ~]# mysql -uroot -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 3167
Server version: 5.5.56-MariaDB MariaDB Server
Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [mysql]> update user set authentication_string = password('password') where user = 'db_user' and host = 'localhost';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [mysql]> flush privileges;
Query OK, 0 rows affected (0.02 sec)
MariaDB [mysql]> quit
Bye
[root@host ~]#
The following is the output of the execution :
MariaDB [mysql]> select * from user where user='db_user'\G
*************************** 1. row ***************************
Host: localhost
User: db_user
Password: *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19
Select_priv: N
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Reload_priv: N
Shutdown_priv: N
Process_priv: N
File_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Show_db_priv: N
Super_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Execute_priv: N
Repl_slave_priv: N
Repl_client_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Create_user_priv: N
Event_priv: N
Trigger_priv: N
Create_tablespace_priv: N
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin:
authentication_string:
1 row in set (0.00 sec)
MariaDB [mysql]>
Unfortunately, the command for updating the authentication_string end in failure. Although the command is a success. So, make sure to execute the command as follow without having a space between :
MariaDB [mysql]> update user set authentication_string=password('password') where user='db_user' and host='localhost';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [mysql]> flush privileges;
Query OK, 0 rows affected (0.01 sec)
MariaDB [mysql]> select * from user where user='db_user'\G
*************************** 1. row ***************************
Host: localhost
User: db_user
Password: *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19
Select_priv: N
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Reload_priv: N
Shutdown_priv: N
Process_priv: N
File_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Show_db_priv: N
Super_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Execute_priv: N
Repl_slave_priv: N
Repl_client_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Create_user_priv: N
Event_priv: N
Trigger_priv: N
Create_tablespace_priv: N
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin:
authentication_string: *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19
1 row in set (0.00 sec)
The previous command to update authentication_string has the pattern of the following with empty space between :
update user set authentication_string = password('password') where user = 'db_user' and host = 'localhost';
It is unavoidable because the authentation_string field is empty after executing the query for selecting the list of user available. But upon executing the same command by removing the empty space between as in the following part : ‘authentication_string=password(‘password’)’, actually a success. So, just pay attention to remove the space between. This may be the behaviour of the query that strictly for to update by removing any space available between that part of query. Furthermore, the rest of the parameter can be vary between environment. Such as the ‘password’ in the parameter of password(). Moreover, user=’db_user’ and also host=’localhost’. So, in order to update the authentication_string field, just execute the correct query as follows :
update user set authentication_string=password('password') where user='db_user' and host='localhost';