0

MariaDB 10.3.27.
Debian 10 Buster.

I've followed the instructions to grant remote access to a user to my remote MariaDB server:

  1. Edited /etc/mysql/my.cnf:

     [mysqld]
     #skip-networking
     #bind-address = <some ip-address>
     skip-networking=0
     skip-bind-address
    
  2. Created a user:

     CREATE USER 'theuser'@'%' IDENTIFIED BY 'mypass';
     GRANT ALL PRIVILEGES ON *.* TO 'theuser'@'%' IDENTIFIED BY 'mypass' WITH GRANT OPTION;
     FLUSH PRIVILEGES;
     SELECT User, Host FROM mysql.user WHERE Host <> 'localhost';
     +---------+------+
     | User    | Host |
     +---------+------+
     | theuser | %    |
     +---------+------+
    
  3. Restarted MariaDB:

     $ sudo systemctl restart mariadb
     $ systemctl status mariadb
     ● mariadb.service - MariaDB 10.3.27 database server
        Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled)
        Active: active (running) since Mon 2021-05-17 01:00:59 -03; 20min ago
    
  4. Opened the 3306 port:

     $ sudo ufw allow 3306/tcp
     $ sudo ufw status | grep 3306
     3306                       ALLOW       Anywhere                  
     3306/tcp                   ALLOW       Anywhere 
    
  5. Checked open port locally:

     $ nmap -p3306 remote_ip
     PORT     STATE SERVICE
     3306/tcp open  mysql
    

But when I try to access from my local machine to the remote server:

$ mysql -utheuser -pmypass -hremote_ip
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'theuser'@'my_ip' (using password: YES)

I'm missing something or there are some errors I can't see?

6
  • Restarting the server and FLUSH PRIVILEGES aren't needed. I tested locally was needed to drop user ''@localhost; and then I was able to remotely login. I'm still looking into it.
    – danblack
    May 17, 2021 at 5:39
  • Upstream bug lodged MDEV-25688. I'm really hoping, like you, something terribly simple is missed.
    – danblack
    May 17, 2021 at 6:43
  • @danblack Thank you! You are going to kill me, but the problem was that my password was not between single quotes. It has a dollar sign $ in it so the shell doesn't escape it if not between double quotes. May 17, 2021 at 7:08
  • @danblack I'm using the password in the command right now because I'm managing a couple of servers and multiple passwords... May 17, 2021 at 7:11
  • Sinle quotes I meant. May 17, 2021 at 7:28

1 Answer 1

0

The problem was simple, the password in the command line has a special character, the dollar sign $ which is not escaped by the shell unless in single quotes. So the proper command is:

$ mysql -utheuser -p'mypass' -hremote_ip

Nonetheless, security issues are arises when exposing the password in the shell:

6.1.2.1 End-User Guidelines for Password Security

MySQL users should use the following guidelines to keep passwords secure.

When you run a client program to connect to the MySQL server, it is inadvisable to specify your password in a way that exposes it to discovery by other users. The methods you can use to specify your password when you run client programs are listed here, along with an assessment of the risks of each method. In short, the safest methods are to have the client program prompt for the password or to specify the password in a properly protected option file.

1
  • not so much "is not escaped", but has a special meaning of being a variable except in a single quoted string. MYSQL_PWD environment variable is one way depending on how you use it.
    – danblack
    May 19, 2021 at 23:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.