1

I'm wanting some assistance in understanding mysql root account that connects by unix socket. I do not understand the unix_socket very well. Logging in without a password concerns me as I may be misunderstanding how the login is happening.

I'm running Ubuntu server 18.04.1 LTS and installed mysql-server Ver 14.14 Distrib 5.7.23. I ran mysql_secure_installation.

I cannot login to mysql with the password at all.

mysql -u root -p

I can login to mysql if executed as sudo without password.

sudo mysql -u root

I understand this is because it's using the unix_socket instead of mysql_native_password.

Can a non-sudo user login to the mysql server with my current setup?

Improve this question
3
  • I doubt that you will need to set that up!
    – George Udosen
    Aug 22, 2018 at 9:26
  • 1
    When you start mysql, you really start a connection to the SQL server. That connection uses a pair userid/pwd, created independantly from Linux users. The root you use in your examples is the one for Mysql, that normally has been set when running mysqy_secure_installation. I am surprised that you need to run mysqlwhith sudo. It is not really necessary as any user needs to know a pair userid/password (as defined in mysql) to login.
    – Marc Vanhoomissen
    Aug 22, 2018 at 9:45
  • That surprises me too. Entering the root id and password does not let me login no matter what. Not from an SSH connection, nor from the web via. phpmyadmin. The only way in is with sudo, and then it does not ask for the root password which I created with mysql_secure_installation.
    – Benihana
    Aug 22, 2018 at 16:41

2 Answers 2

Reset to default
0

Assuming that your root MySQL user has unix_socket authentication enabled, then NO; a non-root, non-sudo user can NOT access your MySQL root user account at all.

If someone can get root (Linux user) access to your machine, then they can access your MySQL root user account, regardless of which authentication method you use. E.g. the root user can (re)start MySQL with authentication disabled and log in with no authentication at all.

So using the unix_socket authentication method does NOT make your MySQL setup any less secure, just more convenient. It could actually be argued that it makes it more secure because regardless of how you configure MySQL, the root user can only connect via localhost - no root user remote connections are allowed. Note that in this context, a direct connection to localhost counts as localhost; e.g. via SSH.

Using a password instead (which BTW, can be done if you wish) gives NO real advantages. Unless of course you wish to allow the root user to connect remotely (NOT recommended).

BTW, with unix_socket authentication enabled, from the root (Linux user) account you can start the MySQL shell (as the root MySQL user by simply running:

mysql

AFAIK that should also work from a sudoer user account too. I.e.:

sudo mysql
Improve this answer
-1

Can a non-sudo user login to the mysql server with my current setup?

No. But anyone with physical access to your system can when you are logged into the desktop.

I myself would do a 

ALTER USER 'root'@'localhost' IDENTIFIED BY '{password}';

at the mysql prompt. That will force a password for root@localhost. On Ubuntu you can also do

sudo dpkg-reconfigure mysql-server-5.7

(adjust the 5.7 to your version).

Improve this answer
3
  • I have one account on my server with a long convoluted password. If somebody has physical access to my system, they would still need that userid and password to get into the mysql installation, correct?
    – Benihana
    Aug 22, 2018 at 16:47
  • Also wanted to mention I never login to the physical system itself, always through SSH using pubkey authentication. In which case, say I stepped away from my computer while logged in via. SSH, the malicious user would still need my password to sudo into the mysql installation, correct?
    – Benihana
    Aug 22, 2018 at 16:49
  • If unix_socket auth is enabled, adding a password to the root MySQL user account will not allow username/password login. Nor does not make it more secure. If anyone can get root (Linux user) access, then all bets are off regardless of whether you use a MySQL password or not. They can simply start MySQL with authentication disabled and do whatever they like...
    – Jeremy Davis
    Mar 13, 2020 at 3:33

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .