MySQL root always has access

by

The question:

Background

I’m setting up a new server, and I’m attempting to secure the new MySQL installation.

  • mysql Ver 8.0.28-0ubuntu0.21.10.3 for Linux on x86_64 ((Ubuntu))

I’m logged in to Ubuntu as root.

I have run:

  • mysql_secure_installation

… and flushed priveleges and rebooted.

It still does not ask me for a password. I have also tried to update this using:

  • ALTER USER 'root'@'localhost' IDENTIFIED BY 'test';

Question

When I attempt to login, I simply am automatically logged in if I type anything from:

  • mysql
  • mysql -u root
    … etc

I assume it’s not just “working out that I’m server root” and letting me in.

How do I configure this?

Info

Running select user,host from mysql.user; gives:

+------------------+-----------+
| user             | host      |
+------------------+-----------+
| debian-sys-maint | localhost |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+

The Solutions:

Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.

Method 1

MySQL

Important In MySQL 8.0, caching_sha2_password is the default
authentication plugin rather than mysql_native_password. For
information about the implications of this change for server operation
and compatibility of the server with clients and connectors, see caching_sha2_password as the Preferred Authentication Plugin.

Test on my MySQL 8.0.25 server

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.25    |
+-----------+
1 row in set (0.00 sec)

mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'MyPassword';
Query OK, 0 rows affected (0.05 sec)

Or you could use :

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'MyPassword';

mysql> exit;
Bye
[email protected]:~# mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
[email protected]:~# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 23
Server version: 8.0.25 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

mysql>


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Comment