Rechercher dans le manuel MySQL
6.3.13 SQL-Based MySQL Account Activity Auditing
Applications can use the following guidelines to perform SQL-based auditing that ties database activity to MySQL accounts.
MySQL accounts correspond to rows in the
mysql.user
system table. When a client connects
successfully, the server authenticates the client to a particular
row in this table. The User
and
Host
column values in this row uniquely
identify the account and correspond to the
'
format in which account names are written in SQL statements.
user_name
'@'host_name
'
The account used to authenticate a client determines which
privileges the client has. Normally, the
CURRENT_USER()
function can be
invoked to determine which account this is for the client user.
Its value is constructed from the User
and
Host
columns of the user
table row for the account.
However, there are circumstances under which the
CURRENT_USER()
value corresponds
not to the client user but to a different account. This occurs in
contexts when privilege checking is not based the client's
account:
Stored routines (procedures and functions) defined with the
SQL SECURITY DEFINER
characteristicViews defined with the
SQL SECURITY DEFINER
characteristicTriggers and events
In those contexts, privilege checking is done against the
DEFINER
account and
CURRENT_USER()
refers to that
account, not to the account for the client who invoked the stored
routine or view or who caused the trigger to activate. To
determine the invoking user, you can call the
USER()
function, which returns a
value indicating the actual user name provided by the client and
the host from which the client connected. However, this value does
not necessarily correspond directly to an account in the
user
table, because the
USER()
value never contains
wildcards, whereas account values (as returned by
CURRENT_USER()
) may contain user
name and host name wildcards.
For example, a blank user name matches any user, so an account of
''@'localhost'
enables clients to connect as an
anonymous user from the local host with any user name. In this
case, if a client connects as user1
from the
local host, USER()
and
CURRENT_USER()
return different
values:
- +-----------------+----------------+
- +-----------------+----------------+
- | user1@localhost | @localhost |
- +-----------------+----------------+
The host name part of an account can contain wildcards, too. If
the host name contains a '%'
or
'_'
pattern character or uses netmask notation,
the account can be used for clients connecting from multiple hosts
and the CURRENT_USER()
value will
not indicate which one. For example, the account
'user2'@'%.example.com'
can be used by
user2
to connect from any host in the
example.com
domain. If user2
connects from remote.example.com
,
USER()
and
CURRENT_USER()
return different
values:
- +--------------------------+---------------------+
- +--------------------------+---------------------+
- | user2@remote.example.com | user2@%.example.com |
- +--------------------------+---------------------+
If an application must invoke
USER()
for user auditing (for
example, if it does auditing from within triggers) but must also
be able to associate the USER()
value with an account in the user
table, it is
necessary to avoid accounts that contain wildcards in the
User
or Host
column.
Specifically, do not permit User
to be empty
(which creates an anonymous-user account), and do not permit
pattern characters or netmask notation in Host
values. All accounts must have a nonempty User
value and literal Host
value.
With respect to the previous examples, the
''@'localhost'
and
'user2'@'%.example.com'
accounts should be
changed not to use wildcards:
If user2
must be able to connect from several
hosts in the example.com
domain, there should
be a separate account for each host.
To extract the user name or host name part from a
CURRENT_USER()
or
USER()
value, use the
SUBSTRING_INDEX()
function:
- +---------------------------------------+
- +---------------------------------------+
- | user1 |
- +---------------------------------------+
- +----------------------------------------+
- +----------------------------------------+
- | localhost |
- +----------------------------------------+
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-account-activity-auditing.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.