Skip navigation links
Security in MySQL :: 3 The MySQL Access Privilege System :: 3.3 Specifying Account Names
3.4 Access Control, Stage 1: Connection Verification »
Section Navigation      [Toggle]

3.3. Specifying Account Names

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.

Within SQL statements such as CREATE USER, GRANT, and SET PASSWORD, account names are written using the following rules:

  • Syntax for account names is 'user_name'@'host_name'.

  • An account name consisting only of a user name is equivalent to 'user_name'@'%'. For example, 'me' is equivalent to 'me'@'%'.

  • The user name and host name need not be quoted if they are legal as unquoted identifiers. Quotes are necessary to specify a user_name string containing special characters (such as “-”), or a host_name string containing special characters or wildcard characters (such as “%”); for example, 'test-user'@'%.com'.

  • Quote user names and host names as identifiers or as strings, using either backticks (“`”), single quotes (“'”), or double quotes (“"”).

  • The user name and host name parts, if quoted, must be quoted separately. That is, write 'me'@'localhost', not 'me@localhost'; the latter is interpreted as 'me@localhost'@'%'.

Account names are stored in grant tables using separate columns for the user name and host name parts:

  • The user table contains one row for each account. The User and Host columns store the user name and host name. Another column, Password, stores the account password. 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 User and Host columns to store the account name. Each row in these tables associates with the account in the user table that has the same User and Host values.

For additional detail about grant table structure, see Section 3.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 ''@'localhost'.

The host part of an account name can take many forms, and wildcards are allowed:

  • A host value can be a host name or an IP number. 'localhost' indicates the local host. '127.0.0.1' indicates the loopback interface.

  • You can use the wildcard characters “%” and “_” in host values. These have the same meaning as for pattern-matching operations performed with the LIKE operator. For example, a host value of '%' matches any host name, whereas a value of '%.mysql.com' matches any host in the mysql.com domain. '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, '192.168.1.%' 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 like 1.2.example.com, its name never matches the host part of account names. An IP wildcard value can match only IP numbers, not host names.

    MySQL Enterprise.  An overly broad host specifier such as “%” constitutes a security risk. The MySQL Enterprise Monitor provides safeguards against this kind of vulnerability. For more information, see http://www.mysql.com/products/enterprise/advisors.html.

  • For host values specified as IP numbers, you can specify a netmask indicating how many address bits to use for the network number. The syntax is host_ip/netmask. For example: 

    CREATE USER 'david'@'192.58.197.0/255.255.255.0';

    This enables david to connect from any client host having an IP number client_ip for which the following condition is true: 

    client_ip & netmask = host_ip

    That is, for the CREATE USER statement just shown: 

    client_ip & 255.255.255.0 = 192.58.197.0

    IP numbers that satisfy this condition and can connect to the MySQL server are those in the range from 192.58.197.0 to 192.58.197.255.

    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: anything on the 192 class A network

    • 192.168.0.0/255.255.0.0: anything on the 192.168 class B network

    • 192.168.1.0/255.255.255.0: anything on the 192.168.1 class C network

    • 192.168.1.1: only this specific IP

    The following netmask (28 bits) will not work: 

    192.168.0.1/255.255.255.240