Rechercher dans le manuel MySQL
6.2.4 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.
MySQL role names are similar to account names, with some differences described at Section 6.2.5, “Specifying Role Names”.
In SQL statements such as CREATE
USER
, GRANT
, and
SET PASSWORD
, account names follow
these rules:
Account name syntax is
'
.user_name
'@'host_name
'An account name consisting only of a user name is equivalent to
'
. For example,user_name
'@'%''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 space or-
), or ahost_name
string containing special characters or wildcard characters (such as.
or%
) (for example,'test-user'@'%.com'
).Quote user names and host names as identifiers or as strings, using either backticks (
`
), single quotation marks ('
), or double quotation marks ("
). For string-quoting and identifier-quoting guidelines, see Section 9.1.1, “String Literals”, and Section 9.2, “Schema Object Names”.The user name and host name parts, if quoted, must be quoted separately. That is, write
'me'@'localhost'
, not'me@localhost'
; the latter is actually equivalent to'me@localhost'@'%'
.A reference to the
CURRENT_USER
orCURRENT_USER()
function is equivalent to specifying the current client's user name and host name literally.
MySQL stores account names in grant tables in the
mysql
system database using separate columns
for the user name and host name parts:
The
user
table contains one row for each account. TheUser
andHost
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
User
andHost
columns to store the account name. Each row in these tables associates with the account in theuser
table that has the sameUser
andHost
values.For access-checking purposes, comparisons of User values are case-sensitive. Comparisons of Host values are not case sensitive.
For additional detail about grant table structure, see Section 6.2.3, “Grant Tables”.
User names and host names have certain special values or wildcard conventions, as described following.
The user name part of an account 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 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'127.0.0.1'
indicates the IPv4 loopback interface. The IP address'::1'
indicates the IPv6 loopback interface.The
%
and_
wildcard characters are permitted in host name or IP address values. These have the same meaning as for pattern-matching operations performed with theLIKE
operator. For example, a host value of'%'
matches any host name, whereas a value of'%.mysql.com'
matches any host in themysql.com
domain.'198.51.100.%'
matches any host in the 198.51.100 class C network.Because IP wildcard values are permitted in host values (for example,
'198.51.100.%'
to match every host on a subnet), someone could try to exploit this capability by naming a host198.51.100.somewhere.com
. To foil such attempts, MySQL does not perform matching on host names that start with digits and a dot. For example, if a host is named1.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.For a host value specified as an IPv4 address, a netmask can be given to indicate how many address bits to use for the network number. Netmask notation cannot be used for IPv6 addresses.
The syntax is
. For example:host_ip
/netmask
This enables
david
to connect from any client host having an IP addressclient_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 = 198.51.100.0
IP addresses that satisfy this condition range from
198.51.100.0
to198.51.100.255
.A netmask typically begins with bits set to 1, followed by bits set to 0. Examples:
198.0.0.0/255.0.0.0
: Any host on the 198 class A network198.51.100.0/255.255.0.0
: Any host on the 198.51 class B network198.51.100.0/255.255.255.0
: Any host on the 198.51.100 class C network198.51.100.1
: Only the host with this specific IP address
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, the server performs this comparison 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 ashost1.example.com
, use that name in account host values. If DNS returns justhost1
, usehost1
instead.If DNS returns the IP address for a given host as
198.51.100.2
, that will match an account host value of198.51.100.2
but not198.051.100.2
. Similarly, it will match an account host pattern like198.51.100.%
but not198.051.100.%
.
To avoid problems like these, it is advisable to check the format in which your DNS returns host names and addresses. Use values in the same format in MySQL account names.
Nederlandse vertaling
U hebt gevraagd om deze site in het Nederlands te bezoeken. Voor nu wordt alleen de interface vertaald, maar nog niet alle inhoud.Als je me wilt helpen met vertalingen, is je bijdrage welkom. Het enige dat u hoeft te doen, is u op de site registreren en mij een bericht sturen waarin u wordt gevraagd om u toe te voegen aan de groep vertalers, zodat u de gewenste pagina's kunt vertalen. Een link onderaan elke vertaalde pagina geeft aan dat u de vertaler bent en heeft een link naar uw profiel.
Bij voorbaat dank.
Document heeft de 26/06/2006 gemaakt, de laatste keer de 26/10/2018 gewijzigd
Bron van het afgedrukte document:https://www.gaudry.be/nl/mysql-rf-account-names.html
De infobrol is een persoonlijke site waarvan de inhoud uitsluitend mijn verantwoordelijkheid is. De tekst is beschikbaar onder CreativeCommons-licentie (BY-NC-SA). Meer info op de gebruiksvoorwaarden en de auteur.
Referenties
Deze verwijzingen en links verwijzen naar documenten die geraadpleegd zijn tijdens het schrijven van deze pagina, of die aanvullende informatie kunnen geven, maar de auteurs van deze bronnen kunnen niet verantwoordelijk worden gehouden voor de inhoud van deze pagina.
De auteur Deze site is als enige verantwoordelijk voor de manier waarop de verschillende concepten, en de vrijheden die met de referentiewerken worden genomen, hier worden gepresenteerd. Vergeet niet dat u meerdere broninformatie moet doorgeven om het risico op fouten te verkleinen.