CODEWITHSUNDEEP
mysql
user10429564
user10429564

Reputation:

Creating new MySQL user: what does the host matching mean?

I am following a tutorial on udemy and I'm stuck setting up a new user. I have managed to get through the best part of it but the tutorial uses phpmyadmin, now I'm on workbench.

Can anyone explain what the "limit to host matching" is used for? Does it only allow access to users with a certain IP address or domain name? I am just running on a local server and I could use root for the project but hey, I have to learn it at some stage so why not now?

Upvotes: 2

Views: 17056

Answers (1)

Mike Lischke
Mike Lischke

Reputation: 53447

Not strictly a MySQL Workbench question, but nonetheless essential for user creation: the host part can be either a hostname (which will be resolved by MySQL to an IP address) or an IP address (IPv4 or IPv6). These values determine from which host a user can connect as seen from the host the MySQL server runs on (e.g. localhost means the MySQL server machine, not the machine you connect from, unless it's the same box of course). For each of the variants you can use wildcards and placeholders, to match a range of machines.

This page of the MySQL documentation describes the matching process and shows examples:

The following table shows how various combinations of User and Host values in the user table apply to incoming connections.

╔═════════════╦═══════════════════════════════╦═══════════════════════════════════════════════════════════════════════════════════════════════════════════╗
║ User Value  ║          Host Value           ║                                          Permissible Connections                                          ║
╠═════════════╬═══════════════════════════════╬═══════════════════════════════════════════════════════════════════════════════════════════════════════════╣
║ 'fred'      ║ 'h1.example.net'              ║ fred, connecting from h1.example.net                                                                      ║
║ ''          ║ 'h1.example.net'              ║ Any user, connecting from h1.example.net                                                                  ║
║ 'fred'      ║ '%'                           ║ fred, connecting from any host                                                                            ║
║ ''          ║ '%'                           ║ Any user, connecting from any host                                                                        ║
║ 'fred'      ║ '%.example.net'               ║ fred, connecting from any host in the example.net domain                                                  ║
║ 'fred'      ║ 'x.example.%'                 ║ fred, connecting from x.example.net, x.example.com, x.example.edu, and so on; this is probably not useful ║
║ 'fred'      ║ '198.51.100.177'              ║ fred, connecting from the host with IP address 198.51.100.177                                             ║
║ 'fred'      ║ '198.51.100.%'                ║ fred, connecting from any host in the 198.51.100 class C subnet                                           ║
║ 'fred'      ║ '198.51.100.0/255.255.255.0'  ║ Same as previous example                                                                                  ║
╚═════════════╩═══════════════════════════════╩═══════════════════════════════════════════════════════════════════════════════════════════════════════════╝

Upvotes: 5

Related Questions