Granting mysql access rights to all machines on subnet

Question

I have a mysql instance (and schema) running on windows that I can access via a connection string based on localhost as the server.

now I want to be able to access this db from another machine on the same subnet.

If possible I would like to use a single user but allow it to access from any machine on the same subnet.

how do I setup security for this? (I already opened the relevant firewall port)

Thanks, Eyal

score 19 · Accepted Answer · answered Jun 19 '11 at 07:53

19

You can do it like this:

GRANT ALL PRIVILEGES ON mydb TO 'username'@'192.168.1.0/255.255.255.0';

change subnet and IP accordingly

answered Jun 19 '11 at 07:53

BugFinder

17,474
4
36
51

I suppose its would not be a good idea to do this on root. do I need to create 'username' some how before this grant statement? and if so, how do I set the password for username ? – epeleg Jun 19 '11 at 10:32
not work for me with Percona XtraDB Cluster 5.7 :-( – zx1986 Oct 22 '19 at 08:04
That maybe down to something in their setup you should check their documentation – BugFinder Oct 22 '19 at 08:24
For me: for mysql 8: 192.168.1.0/24 works well, but for mysql 5.7 works only 192.168.1.0/255.255.255.0 – user989840 Jul 11 '22 at 19:37

score 3 · Answer 2 · answered Nov 10 '15 at 08:28

You can also use wildcards, rather than a masks.

You can specify wildcards in the host name. For example, user_name@'%.example.com' applies to user_name for any host in the example.com domain, and user_name@'192.168.1.%' applies to user_name for any host in the 192.168.1 class C subnet.

See the Account Names and Passwords section of the in the GRANT docs.

Granting mysql access rights to all machines on subnet

2 Answers2