Users without passwords

About fifteen years ago, while debugging a database probem, I was horrified to discover that we had two root users - one with the password I had been using and one without a password. Nooo!

So, I wrote a simple maintenance script that searched for and deleted any user in our database without a password. I even made it part of our puppet recipe - since the database server was in use by users and I didn’t want anyone using SQL statements to change their password to an empty value.

Then I forgot about it.

Recently, I upgraded our MariaDB databases to Debian bullseye, which inserted the mariadb.sys user which…. doesn’t have a password set. It seems to be locked down in other ways, but my dumb script didn’t know about that and happily deleted the user.

Who needs that mariadb.sys user anyway?

Apparently we all do. On one server, I can’t login as root anymore. On another server I can login as root, but if I try to list users I get an error:

ERROR 1449 (HY000): The user specified as a definer (‘mariadb.sys’@’localhost’) does not exist

The Internt is full of useless advice. The most common is to simply insert that user. Except…

MariaDB [mysql]> CREATE USER `mariadb.sys`@`localhost` ACCOUNT LOCK PASSWORD EXPIRE;
ERROR 1396 (HY000): Operation CREATE USER failed for 'mariadb.sys'@'localhost'
MariaDB [mysql]> 

Yeah, that’s not going to work.

It seems like we are dealing with two changes. One, the old mysql.user table was replaced by the global_priv table and then turned into a view for backwards compatibility.

And two, for sensible reasons the default definer for this view has been changed from the root user to a user that, ahem, is unlikely to be changed or deleted.

Apparently I can’t add the mariadb.sys user because it would alter the user view which has a definer that doesn’t exist. Although not sure if this really is the reason?

Fortunately, I found an excellent suggestion for changing the definer of a view. My modified version of the answer is, run the following command which will generate a SQL statement:

SELECT CONCAT("ALTER DEFINER=root@localhost VIEW ", table_name, " AS ", view_definition, ";") FROM information_schema.views WHERE table_schema='mysql' AND definer = 'mariadb.sys@localhost';

Then, execute the statement.

And then also update the mysql.proc table:

UPDATE mysql.proc SET definer = 'root@localhost' WHERE definer = 'mariadb.sys@localhost';

And lastly, I had to run:

DELETE FROM tables_priv WHERE User = 'mariadb.sys';
FLUSH privileges;

Wait, was the tables_priv entry the whole problem all along? Not sure. But now I can run:

CREATE USER `mariadb.sys`@`localhost` ACCOUNT LOCK PASSWORD EXPIRE;
GRANT SELECT, DELETE ON `mysql`.`global_priv` TO `mariadb.sys`@`localhost`;

And reverse the other statements:

SELECT CONCAT("ALTER DEFINER=`mariadb.sys`@localhost VIEW ", table_name, " AS ", view_definition, ";") FROM information_schema.views WHERE table_schema='mysql' AND definer = 'root@localhost';

[Execute the output.]

UPDATE mysql.proc SET definer = 'mariadb.sys@localhost' WHERE definer = 'root@localhost';

And while we’re on the topic of borked MariaDB authentication, here are the steps to change the root password and restore all root privielges if you can’t get in at all or your root user is missing the GRANT OPTION (you can change “ALTER” to “CREATE” if the root user does not even exist):

systemctl stop mariadb
mariadbd-safe --skip-grant-tables --skip-networking &
mysql -u root
[mysql]> FLUSH PRIVILEGES
[mysql]> ALTER USER `root`@`localhost` IDENTIFIED VIA mysql_native_password USING PASSWORD('your-secret-password') OR unix_socket; 
[mysql]> GRANT ALL PRIVILEGES ON *.* to 'root'@'localhost' WITH GRANT OPTION;
mariadbd-admin shutdown
systemctl start mariadb