MySQL root always has access



  • 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 |
    +------------------+-----------+
    


  • https://dev.mysql.com/doc/refman/8.0/en/caching-sha2-pluggable-authentication.html

    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 https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-caching-sha2-password

    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
    root@ergesttstsrv:~# mysql
    ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
    root@ergesttstsrv:~# 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>


Log in to reply
 


Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2