MySQL - Grant access permissions does not work

Question

I am trying to give access permissions to a user for specific database in mysql. But the command does not seem to take effect.

Here's what I mean:

step 1:

• create db 'testdb'
• grant permissions to 'dummyuser'@'%' on testbd.*
• try to access mysql with testdb user (FAILS)

root@bnode1 /]# mysql -h 172.22.191.202 -u root -ppassword

MariaDB [(none)]> 
MariaDB [(none)]> CREATE DATABASE IF NOT EXISTS testdb;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> grant all privileges on testdb.* to 'dummyuser'@'%'   identified by 'password';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> exit
Bye
[root@bnode1 /]# 
[root@bnode1 /]# mysql -h 172.22.191.202 -u dummyuser -ppassword
ERROR 1045 (28000): Access denied for user 'dummyuser'@'bnode1' (using   password: YES)

On the mysql database I can see the grant priviliges for dummyuser as below:

MariaDB [(none)]> show grants for 'dummyuser';
+-----------------------------------------+
| Grants for dummyuser@%                                                                                        |
+-----------------------------------------+
| GRANT USAGE ON *.* TO 'dummyuser'@'%' IDENTIFIED BY PASSWORD     '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19' |
| GRANT ALL PRIVILEGES ON `testdb`.* TO 'dummyuser'@'%'                                                   |
+-----------------------------------------+
2 rows in set (0.00 sec)

Step 2: Now I tried another thing. grant permisson to user 'dummyuser'@'bnode1', same commands, and with that now I am able to access.

root@bnode1 /]# mysql -h 172.22.191.202 -u root -ppassword     
Welcome to the MariaDB monitor.  Commands end with ; or \g.
...

MariaDB [(none)]> grant all privileges on testdb.* to '**dummyuser'@'bnode1'** identified by 'passwor>
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> exit
Bye
[root@bnode1 /]# 
[root@bnode1 /]# 
[root@bnode1 /]# mysql -h 172.22.191.202 -u dummyuser -ppassword
Welcome to the MariaDB monitor.  Commands end with ; or \g.
...
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

I am not an expert in mysql, can someone explain to me what I am doing wrong.

Answer 1

ERROR 1045 (28000): Access denied for user 'dummyuser'@'bnode1' (using password: YES)

You may need to try to connect from the other host to see if 'bnode1' is restricted for 'dummyuser'

Or you can create a fresh user(as you didn't show the create user command I assume it's an existing user) and the grant command you did should work.

Not likely but just in case, you need ensure 'bind=0.0.0.0' in my.cnf.