Unable to Login to Zabbix Frontend After Running SQL Update Query

[Rohllik28]

Hello everyone,

I encountered a serious issue with my Zabbix setup and need some help to resolve it.

I ran the following SQL query on my Zabbix database, which caused me to be unable to log in to the frontend even as a superadmin:

UPDATE zabbixdb.users AS a INNER JOIN zabbixdb.users AS b ON b.username='guest' SET a.passwd = b.passwd;
The intention was to synchronize passwords, but it seems to have caused some unintended issues.

Current Status:

1. Password Update: This query changed the passwords for all users to the password of the guest user. Now, I can't log in as Admin or any other user.
2. Database Integrity Check:
   • I have checked the users table, and it appears that only the guest user's password hash is present, while other users' passwords might have been erased.
   
   SELECT username, passwd FROM zabbixdb.users;
3. Tried Solutions:
   • I attempted to reset the Admin password directly in the database using MD5 hash:
   
   UPDATE zabbixdb.users SET passwd=md5('NewAdminPassword') WHERE username='Admin';
   • After updating the password, I restarted the Zabbix services:
     • However, I'm still unable to log in with the new password.
4. Additional Attempts:
   • I checked for the integrity of the users table and attempted to create a new admin user, but faced multiple errors related to foreign key constraints and missing values.

Thanks

[Markku]

Never tried but maybe this helps for resetting the Admin password back to "zabbix":

$ zgrep "users.*Admin" /usr/share/zabbix-sql-scripts/mysql/server.sql.gz
INSERT INTO `users` (`userid`,`username`,`name`,`surname`,`passwd`,`ur l`,`autologin`,`autologout`,`refresh`,`rows_per_pa ge`,`roleid`) values ('1','Admin','Zabbix','Administrator','$2y$10$92nD no4n0Zm7Ej7Jfsz8WukBfgSS/U0QkIuu8WkJPihXBb2A1UrEK','','1','0','30s','50','3 ');

Markku

[Markku]

Note that there are extra spaces in the copy-paste, I couldn't edit them away

[Markku]

Never tried but maybe this helps for resetting the Admin password back to "zabbix":

$ zgrep "users.*Admin" /usr/share/zabbix-sql-scripts/mysql/server.sql.gz
INSERT INTO `users` (`userid`,`username`,`name`,`surname`,`passwd`,`ur l`,`autologin`,`autologout`,`refresh`,`rows_per_pa ge`,`roleid`) values ('1','Admin','Zabbix','Administrator','$2y$10$92nD no4n0Zm7Ej7Jfsz8WukBfgSS/U0QkIuu8WkJPihXBb2A1UrEK','','1','0','30s','50','3 ');

Markku

[Markku]

Note that there are extra spaces in the copy-paste, I couldn't edit them away

[Rohllik28]

INSERT INTO `users` (`userid`,`username`,`name`,`surname`,`passwd`,`ur l`,`autologin`,`autologout`,`refresh`,`rows_per_pa ge`,`roleid`) values ('1','Admin','Zabbix','Administrator','$2y$10$92nD no4n0Zm7Ej7Jfsz8WukBfgSS/U0QkIuu8WkJPihXBb2A1UrEK','','1','0','30s','50','3 ');

Markku

Thanks you saved me once again.

The query couldn't insert a record directly into the admin user at position 1 because such a record already existed. However, it was possible to create a temporary super-admin using it (i.e., I had to use a higher sequential number than the current users and a unique username). With this temporary super-admin, I was able to reset the passwords of all other users from the frontend.

Thanks again!