Jamie McClelland: 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 existThe 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
Thunderbird in Debian stable (Bookworm) has received
This needs a user CSS file to override the button as non-visible.
To make it process the user CSS Thunderbird needs a config setting to be enabled:
The eighth release of the still fairly new
Dormitory room in Zostel Ernakulam, Kochi.
Beds in Zostel Ernakulam, Kochi.
Onam sadya menu from Brindhavan restaurant.
Sadya lined up for serving
Sadya thali served on banana leaf.
We were treated with such views during the Wayanad trip.
A road in Rippon.
Entry to Kanthanpara Falls.
Kanthanpara Falls.
A view of Zostel Wayanad.
A map of Wayanad showing tourist places.
A view from inside the Zostel Wayanad property.
Terrain during trekking towards the Chembra peak.
Heart-shaped lake at the Chembra peak.
Me at the heart-shaped lake.
Views from the top of the Chembra peak.
View of another peak from the heart-shaped lake.
My previously mentioned 






Like each month, have a look at the work funded by
For documentation, please have a look at: