1

Recently I started learning php and sql because I have a fair bit of front end understanding, so I wanted to strengthen my understanding of back end systems. I bought a raspberry pi, and set up a simple LAMP server, which works great(ish). I can run php form it which is a good start.

However, I installed MariaDB v10.3.22 and I am having a lot of trouble with it. Upon successful installation, I went to log in, and expected a blank password. That didn't work.

mysql -u root -p

I get the error ERROR 1698 (28000): Access denied for user 'root'@'localhost' And because it was a fresh install I tried:

mysql_secure_installation

Anyway, I eventually found out that typing sudo mysql -u root -p would let me in, without a password at all. In fact even running the secure install with sudo made it work, meant I didn't need to enter a correct password. I could enter whatever password I wanted to get into the MariaDB shell and the secure script when running sudo, and it only worked with the root db user. Why is this? Why can sudo bypass all of this, especially because I haven't seen any use of sudo in the documentation.

I created a new user with full permissions and it works fine, I don't need sudo and the password actually works.

Sorry if this is confusing, but this is the best I can explain it because I am just as confused.

Thanks, Angus

Angus
  • 78
  • 8

1 Answers1

1

This may be due to the Unix Socket authentication plugin being used by the root user.

As the documentation for the plugin elaborates:

The unix_socket authentication plugin allows the user to use operating system credentials when connecting to MariaDB via the local Unix socket file.

The unix_socket authentication plugin works by calling the getsockopt system call with the SO_PEERCRED socket option, which allows it to retrieve the uid of the process that is connected to the socket. It is then able to get the user name associated with that uid. Once it has the user name, it will authenticate the connecting user as the MariaDB account that has the same user name.

Assuming that you aren't logged as root on your shell session, by running sudo mysql -u root -p you execute the command as root, and that's why you are not bypassing the authentication, it's just using the socket authentication as intended. It does not require a password since the OS user matches the MySQL user.

You can check if the root uses the Unix Socket authentication by doing the following:

MariaDB [(none)]> SELECT User, Host, plugin FROM mysql.user;
+------+-----------+-------------+
| User | Host      | plugin      |
+------+-----------+-------------+
| root | localhost | unix_socket |
+------+-----------+-------------+

I also suggest you to check this other question which addresses the same situation on MySQL.

Community
  • 1
  • 1
Matheus Canon
  • 115
  • 1
  • 9
  • Thanks, this makes sense.However, will this also have effect on things like 'service mysql start/stop' because when I enter this command and I am not root user, it asks for a password (which I don't know) and with 'sudo' it works fine whatever password I use. Also, how can I change the root password for the root MariaDB account so I don;'t have to use sudo every time? – Angus May 14 '20 at 05:12
  • 1
    No, it will not affect things on the OS, like `service`, only the MariaDB user. You can change the root password doing the following: `ALTER USER root@localhost IDENTIFIED VIA mysql_native_password;` `SET PASSWORD = PASSWORD('new-password');` There is an example on the plugin documentation: https://mariadb.com/kb/en/authentication-plugin-unix-socket/#switching-to-password-based-authentication – Matheus Canon May 14 '20 at 05:21
  • 1
    You could also create a new user instead of using `root`: https://stackoverflow.com/a/37241990/7122251 – Matheus Canon May 14 '20 at 05:23
  • Thanks however when I run service mysql stop in super user, I am not required to enter any password, whereas with out it, I am. – Angus May 14 '20 at 05:26