MySQL account names consist of a user name and a host name. This enables creation of accounts for users with the same name who can connect from different hosts. This section describes how to write account names, including special values and wildcard rules.
Syntax for account names is
An account name consisting only of a user name is equivalent to
'. For example,
'me' is equivalent to
The user name and host name need not be quoted if they are legal as unquoted
identifiers. Quotes are necessary to specify a
string containing special characters (such as "
-"), or a
host_name string containing special characters or wildcard
characters (such as "
%"); for example,
Quote user names and host names as identifiers or as strings, using either
single quotation marks ("
or double quotation marks ("
The user name and host name parts, if quoted, must be quoted separately. That is,
the latter is interpreted as
MySQL stores account names in grant tables in the
mysql database using separate
columns for the user name and host name parts:
user table contains one row for each account. The
Host columns store the user name and
host name. This table also indicates which global privileges the account has.
Other grant tables indicate privileges an account has for databases and objects
within databases. These tables have
Host columns to store the account name. Each row in these tables
associates with the account in the
user table that has the same
For additional detail about grant table structure, see Section 6.2.2, "Privilege System Grant Tables".
User names and host names have certain special values or wildcard conventions, as described following.
A user name is either a nonblank value that literally matches the user name for incoming connection attempts, or
a blank value (empty string) that matches any user name. An account with a blank user name is an anonymous user.
To specify an anonymous user in SQL statements, use a quoted empty user name part, such as
The host name part of an account name can take many forms, and wildcards are permitted:
A host value can be a host name or an IP address (IPv4 or IPv6). The name
'localhost' indicates the local host. The IP address
indicates the IPv4 loopback interface. The IP address
'::1' indicates the
IPv6 loopback interface.
You can use the wildcard characters
%" and "
_" in host name or
IP address values. These have the same meaning as for pattern-matching operations performed with the
For example, a host value of
'%' matches any host name, whereas a value of
'%.mysql.com' matches any host in the
'192.168.1.%' matches any
host in the 192.168.1 class C network.
Because you can use IP wildcard values in host values (for example,
to match every host on a subnet), someone could try to exploit this capability by naming a host
192.168.1.somewhere.com. To foil such attempts, MySQL disallows
matching on host names that start with digits and a dot. Thus, if you have a host named something
1.2.example.com, its name never matches the host part of account
names. An IP wildcard value can match only IP addresses, not host names.
The syntax is
. For example:
CREATE USER 'david'@'18.104.22.168/255.255.255.0';
david to connect from any client host having an IP address
client_ip for which the following condition is true:
That is, for the
USER statement just shown:
client_ip& 255.255.255.0 = 22.214.171.124
IP addresses that satisfy this condition and can connect to the MySQL server are those in the range
The netmask can only be used to tell the server to use 8, 16, 24, or 32 bits of the address. Examples:
192.0.0.0/255.0.0.0: Any host on the 192
class A network
192.168.0.0/255.255.0.0: Any host on the
192.168 class B network
192.168.1.0/255.255.255.0: Any host on the
192.168.1 class C network
192.168.1.1: Only the host with this
specific IP address
The following netmask will not work because it masks 28 bits, and 28 is not a multiple of 8:
The server performs matching of host values in account names against the client host using the value returned by the system DNS resolver for the client host name or IP address. Except in the case that the account host value is specified using netmask notation, this comparison is performed as a string match, even for an account host value given as an IP address. This means that you should specify account host values in the same format used by DNS. Here are examples of problems to watch out for:
Suppose that a host on the local network has a fully qualified name of
host1.example.com. If DNS returns name lookups for this host as
host1.example.com, use that name in account host values. But if DNS returns
If DNS returns the IP address for a given host as
192.168.1.2, that will match an account host value of
192.168.01.2. Similarly, it will match an account host pattern like
192.168.1.% but not
To avoid problems like this, it is advisable to check the format in which your DNS returns host names and addresses, and use values in the same format in MySQL account names.