[SOLVED] SQL permissions behaving unexpectedly

Issue

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

Solution

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.

Answered By - Matheus Canon

Answer Checked By - Senaida (WPSolving Volunteer)