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 'user_name'@'host_name' format in which account names are written in SQL statements.

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 characteristic

  • Views defined with the SQL SECURITY DEFINER characteristic

  • Triggers 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:

  1. mysql> SELECT USER(), CURRENT_USER();
  2. +-----------------+----------------+
  3. | USER()          | CURRENT_USER() |
  4. +-----------------+----------------+
  5. | user1@localhost | @localhost     |
  6. +-----------------+----------------+

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:

  1. mysql> SELECT USER(), CURRENT_USER();
  2. +--------------------------+---------------------+
  3. | USER()                   | CURRENT_USER()      |
  4. +--------------------------+---------------------+
  5. | user2@remote.example.com | user2@%.example.com |
  6. +--------------------------+---------------------+

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:

  1. RENAME USER ''@'localhost' TO 'user1'@'localhost';
  2. RENAME USER 'user2'@'%.example.com' TO 'user2'@'remote.example.com';

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:

  1. +---------------------------------------+
  2. +---------------------------------------+
  3. | user1                                 |
  4. +---------------------------------------+
  5.  
  6. +----------------------------------------+
  7. +----------------------------------------+
  8. | localhost                              |
  9. +----------------------------------------+

Rechercher dans le manuel MySQL

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

  1. Consulter le document html Langue du document :en Manuel MySQL : https://dev.mysql.com/

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.

Table des matières Haut