Thursday, June 4, 2015

Myql - create new user and grant remote access to whole subnet

Today, someone ask a question at JomWeb facebook group about getting remote access with specific ip and whole subnet. Here, have a look at the question :

hello. sorry if i am asking too much question. but earlier i have a problem regarding connection between phpmyadmin and mysql server and it have been solved by grating the permission to the server. This time i got this kind of error when i am trying to use other server. can anyone help me this time around? i already granted the permission needed.

So, here my solution..and I wrote it back on my blog for my future reference and maybe I can help someone that face the same problem. As PoC,I create new user "dummy" and grant the access. Btw, in my opinion user "root" should not be remote and just use in local machine, because of security reason.

1 - Let's create new mysql user called as "dummy". You can skip this part if you already have target mysql user :
mysql> CREATE USER 'dummy'@'localhost' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

2 - Now let's grant the user with all database access and connection from 192.168.1.* (modified this based on you own need) :
mysql> GRANT ALL PRIVILEGES ON *.* TO 'dummy'@'192.168.1.0/255.255.255.0' 
IDENTIFIED BY 'password' WITH GRANT OPTION;

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
P/s : You can set different password for each IP address.

3 - Try give a look our mysql user and mysql host access :
mysql> SELECT user,host FROM mysql.user;
+------------------+---------------------------+
| user             | host                      |
+------------------+---------------------------+
| root             | 127.0.0.1                 |
| dummy            | 192.168.1.0/255.255.255.0 |
| root             | ::1                       |
| debian-sys-maint | localhost                 |
| dummy            | localhost                 |
| phpmyadmin       | localhost                 |
| root             | localhost                 |
+------------------+---------------------------+
7 rows in set (0.00 sec)

Succes! That all for now.. :)

No comments:

Post a Comment