Rechercher dans le manuel MySQL

6.3.2 Adding User Accounts

Table des matières

To create MySQL accounts, use the account-management statements intended for creating accounts and establishing their privileges, such as CREATE USER and GRANT. These statements cause the server to make appropriate modifications to the underlying grant tables. All such statements are described in Section 13.7.1, “Account Management Statements”.

Note

Direct modification of grant tables using statements such as INSERT, UPDATE, or DELETE is discouraged and done at your own risk. The server is free to ignore rows that become malformed as a result of such modifications.

For any operation that modifies a grant table, the server checks whether the table has the expected structure and produces an error if not. mysql_upgrade must be run to update the tables to the expected structure.

Another option for creating accounts is to use the GUI tool MySQL Workbench. Also, several third-party programs offer capabilities for MySQL account administration. phpMyAdmin is one such program.

The following examples show how to use the mysql client program to set up new accounts. These examples assume that privileges have been set up according to the defaults described in Section 2.10.4, “Securing the Initial MySQL Account”. This means that to make changes, you must connect to the MySQL server as the MySQL root user, which has the CREATE USER privilege.

First, use the mysql program to connect to the server as the MySQL root user: 

shell> mysql --user=root mysql

If you have assigned a password to the root account, you must also supply a --password or -p option.

After connecting to the server as root, you can add new accounts. The following example uses CREATE USER and GRANT statements to set up four accounts:

  1. mysql> CREATE USER 'finley'@'localhost' IDENTIFIED BY 'password';
  2. mysql> GRANT ALL PRIVILEGES ON *.* TO 'finley'@'localhost'
  3.     ->     WITH GRANT OPTION;
  4. mysql> CREATE USER 'finley'@'%' IDENTIFIED BY 'password';
  5. mysql> GRANT ALL PRIVILEGES ON *.* TO 'finley'@'%'
  6.     ->     WITH GRANT OPTION;
  7. mysql> CREATE USER 'admin'@'localhost' IDENTIFIED BY 'password';
  8. mysql> GRANT RELOAD,PROCESS ON *.* TO 'admin'@'localhost';
  9. mysql> CREATE USER 'dummy'@'localhost';

The accounts created by those statements have the following properties:

  • Two accounts have a user name of finley. Both are superuser accounts with full privileges to do anything. The 'finley'@'localhost' account can be used only when connecting from the local host. The 'finley'@'%' account uses the '%' wildcard for the host part, so it can be used to connect from any host.

    The 'finley'@'localhost' account is necessary if there is an anonymous-user account for localhost. Without the 'finley'@'localhost' account, that anonymous-user account takes precedence when finley connects from the local host and finley is treated as an anonymous user. The reason for this is that the anonymous-user account has a more specific Host column value than the 'finley'@'%' account and thus comes earlier in the user table sort order. (user table sorting is discussed in Section 6.2.6, “Access Control, Stage 1: Connection Verification”.)

  • The 'admin'@'localhost' account can be used only by admin to connect from the local host. It is granted the RELOAD and PROCESS administrative privileges. These privileges enable the admin user to execute the mysqladmin reload, mysqladmin refresh, and mysqladmin flush-xxx commands, as well as mysqladmin processlist . No privileges are granted for accessing any databases. You could add such privileges using GRANT statements.

  • The 'dummy'@'localhost' account has no password (which is insecure and not recommended). This account can be used only to connect from the local host. No privileges are granted. It is assumed that you will grant specific privileges to the account using GRANT statements.

To see the privileges for an account, use SHOW GRANTS:

  1. mysql> SHOW GRANTS FOR 'admin'@'localhost';
  2. +-----------------------------------------------------+
  3. | Grants for admin@localhost                          |
  4. +-----------------------------------------------------+
  5. | GRANT RELOAD, PROCESS ON *.* TO 'admin'@'localhost' |
  6. +-----------------------------------------------------+

To see nonprivilege properties for an account, use SHOW CREATE USER:

  1. mysql> SHOW CREATE USER 'admin'@'localhost'\G
  2. *************************** 1. row ***************************
  3. CREATE USER for admin@localhost: CREATE USER 'admin'@'localhost'
  4. IDENTIFIED WITH 'mysql_native_password'
  5. AS '*67ACDEBDAB923990001F0FFB017EB8ED41861105'
  6. REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK

The next examples create three accounts and grant them access to specific databases. Each of them has a user name of custom and password of password:

  1. mysql> CREATE USER 'custom'@'localhost' IDENTIFIED BY 'password';
  2. mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
  3.     ->     ON bankaccount.*
  4.     ->     TO 'custom'@'localhost';
  5. mysql> CREATE USER 'custom'@'host47.example.com' IDENTIFIED BY 'password';
  6. mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
  7.     ->     ON expenses.*
  8.     ->     TO 'custom'@'host47.example.com';
  9. mysql> CREATE USER 'custom'@'%.example.com' IDENTIFIED BY 'password';
  10. mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
  11.     ->     ON customer.*
  12.     ->     TO 'custom'@'%.example.com';

The three accounts can be used as follows:

  • The first account can access the bankaccount database, but only from the local host.

  • The second account can access the expenses database, but only from the host host47.example.com.

  • The third account can access the customer database, from any host in the example.com domain. This account has access from all machines in the domain due to use of the % wildcard character in the host part of the account name.


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-adding-users.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