MariaDB remote access ERROR 1045 (28000): Access denied

Question

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?

Answer 1

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.