Rechercher dans le manuel MySQL
6.2.6 Access Control, Stage 1: Connection Verification
When you attempt to connect to a MySQL server, the server accepts or rejects the connection based on these conditions:
Your identity and whether you can verify your identity by supplying the correct password
Whether your account is locked or unlocked
The server checks credentials first, then account locking state. A failure for either step causes the server to deny access to you completely. Otherwise, the server accepts the connection, and then enters Stage 2 and waits for requests.
Credential checking is performed using the three
user
table scope columns
(Host
, User
, and
authentication_string
). Locking state is
recorded in the user
table
account_locked
column. The server accepts the
connection only if the Host
and
User
columns in some user
table row match the client host name and user name, the client
supplies the password specified in that row, and the
account_locked
value is 'N'
.
The rules for permissible Host
and
User
values are given in
Section 6.2.4, “Specifying Account Names”. Account locking can be changed
with the ALTER USER
statement.
Your identity is based on two pieces of information:
The client host from which you connect
Your MySQL user name
If the User
column value is nonblank, the user
name in an incoming connection must match exactly. If the
User
value is blank, it matches any user name.
If the user
table row that matches an incoming
connection has a blank user name, the user is considered to be an
anonymous user with no name, not a user with the name that the
client actually specified. This means that a blank user name is
used for all further access checking for the duration of the
connection (that is, during Stage 2).
The authentication_string
column can be blank.
This is not a wildcard and does not mean that any password
matches. It means that the user must connect without specifying a
password. If the server authenticates a client using a plugin, the
authentication method that the plugin implements may or may not
use the password in the authentication_string
column. In this case, it is possible that an external password is
also used to authenticate to the MySQL server.
Nonblank authentication_string
values in the
user
table represent encrypted passwords. MySQL
does not store passwords in cleartext form for anyone to see.
Rather, the password supplied by a user who is attempting to
connect is encrypted (using the password hashing method
implemented by the account authentication plugin). The encrypted
password then is used during the connection process when checking
whether the password is correct. This is done without the
encrypted password ever traveling over the connection. See
Section 6.3.1, “User Names and Passwords”.
From MySQL's point of view, the encrypted password is the
real password, so you should never give
anyone access to it. In particular, do not give
nonadministrative users read access to tables in the
mysql
system database.
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 |
It is possible for the client host name and user name of an
incoming connection to match more than one row in the
user
table. The preceding set of examples
demonstrates this: Several of the entries shown match a connection
from h1.example.net
by fred
.
When multiple matches are possible, the server must determine which of them to use. It resolves this issue as follows:
Whenever the server reads the
user
table into memory, it sorts the rows.When a client attempts to connect, the server looks through the rows in sorted order.
The server uses the first row that matches the client host name and user name.
The server uses sorting rules that order rows with the
most-specific Host
values first. Literal host
names and IP addresses are the most specific. (The specificity of
a literal IP address is not affected by whether it has a netmask,
so 198.51.100.13
and
198.51.100.0/255.255.255.0
are considered
equally specific.) The pattern '%'
means
“any host” and is least specific. The empty string
''
also means “any host” but sorts
after '%'
. Rows with the same
Host
value are ordered with the most-specific
User
values first (a blank
User
value means “any user” and is
least specific). For rows with equally-specific
Host
and User
values, the
order is nondeterministic.
To see how this works, suppose that the user
table looks like this:
+-----------+----------+-
| Host | User | ...
+-----------+----------+-
| % | root | ...
| % | jeffrey | ...
| localhost | root | ...
| localhost | | ...
+-----------+----------+-
When the server reads the table into memory, it sorts the rows using the rules just described. The result after sorting looks like this:
+-----------+----------+-
| Host | User | ...
+-----------+----------+-
| localhost | root | ...
| localhost | | ...
| % | jeffrey | ...
| % | root | ...
+-----------+----------+-
When a client attempts to connect, the server looks through the
sorted rows and uses the first match found. For a connection from
localhost
by jeffrey
, two of
the rows from the table match: the one with
Host
and User
values of
'localhost'
and ''
, and the
one with values of '%'
and
'jeffrey'
. The 'localhost'
row appears first in sorted order, so that is the one the server
uses.
Here is another example. Suppose that the user
table looks like this:
+----------------+----------+-
| Host | User | ...
+----------------+----------+-
| % | jeffrey | ...
| h1.example.net | | ...
+----------------+----------+-
The sorted table looks like this:
+----------------+----------+-
| Host | User | ...
+----------------+----------+-
| h1.example.net | | ...
| % | jeffrey | ...
+----------------+----------+-
A connection by jeffrey
from
h1.example.net
is matched by the first row,
whereas a connection by jeffrey
from any host
is matched by the second.
It is a common misconception to think that, for a given user
name, all rows that explicitly name that user are used first
when the server attempts to find a match for the connection.
This is not true. The preceding example illustrates this, where
a connection from h1.example.net
by
jeffrey
is first matched not by the row
containing 'jeffrey'
as the
User
column value, but by the row with no
user name. As a result, jeffrey
is
authenticated as an anonymous user, even though he specified a
user name when connecting.
If you are able to connect to the server, but your privileges are
not what you expect, you probably are being authenticated as some
other account. To find out what account the server used to
authenticate you, use the
CURRENT_USER()
function. (See
Section 12.15, “Information Functions”.) It returns a value in
format that indicates the user_name
@host_name
User
and
Host
values from the matching
user
table row. Suppose that
jeffrey
connects and issues the following
query:
- +----------------+
- +----------------+
- | @localhost |
- +----------------+
The result shown here indicates that the matching
user
table row had a blank
User
column value. In other words, the server
is treating jeffrey
as an anonymous user.
Another way to diagnose authentication problems is to print out
the user
table and sort it by hand to see where
the first match is being made.
Traduction non disponible
Le manuel MySQL n'est pas encore traduit en français sur l'infobrol. Seule la version anglaise est disponible pour l'instant.
Document créé le 26/06/2006, dernière modification le 26/10/2018
Source du document imprimé : https://www.gaudry.be/mysql-rf-connection-access.html
L'infobrol est un site personnel dont le contenu n'engage que moi. Le texte est mis à disposition sous licence CreativeCommons(BY-NC-SA). Plus d'info sur les conditions d'utilisation et sur l'auteur.
Références
Ces références et liens indiquent des documents consultés lors de la rédaction de cette page, ou qui peuvent apporter un complément d'information, mais les auteurs de ces sources ne peuvent être tenus responsables du contenu de cette page.
L'auteur de ce site est seul responsable de la manière dont sont présentés ici les différents concepts, et des libertés qui sont prises avec les ouvrages de référence. N'oubliez pas que vous devez croiser les informations de sources multiples afin de diminuer les risques d'erreurs.