Rechercher dans le manuel MySQL

6.2.8 Adding Accounts, Assigning Privileges, and Dropping Accounts

Inhoudsopgave

To manage MySQL accounts, use the SQL statements intended for that purpose:

Account-management statements cause the server to make appropriate modifications to the underlying grant tables, which are discussed in Section 6.2.3, “Grant Tables”.

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. To update the tables to the expected structure, perform the MySQL upgrade procedure. See Section 2.11, “Upgrading MySQL”.

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.

This section discusses the following topics:

For additional information about the statements discussed here, see Section 13.7.1, “Account Management Statements”.

Creating Accounts and Granting Privileges

The following examples show how to use the mysql client program to set up new accounts. These examples assume that the MySQL root account has the CREATE USER privilege and all privileges that it grants to other accounts.

At the command line, connect to the server as the MySQL root user, supplying the appropriate password at the password prompt: 

shell> mysql -u root -p
Enter password: (enter root password here)

After connecting to the server, you can add new accounts. The following example uses CREATE USER and GRANT statements to set up four accounts (where you see 'password', substitute an appropriate password):

  1. CREATE USER 'finley'@'localhost'
  2.   IDENTIFIED BY 'password';
  3. GRANT ALL
  4.   ON *.*
  5.   TO 'finley'@'localhost'
  6.   WITH GRANT OPTION;
  7.  
  8. CREATE USER 'finley'@'%.example.com'
  9.   IDENTIFIED BY 'password';
  10. GRANT ALL
  11.   ON *.*
  12.   TO 'finley'@'%.example.com'
  13.   WITH GRANT OPTION;
  14.  
  15. CREATE USER 'admin'@'localhost'
  16.   IDENTIFIED BY 'password';
  17. GRANT RELOAD,PROCESS
  18.   ON *.*
  19.   TO 'admin'@'localhost';
  20.  
  21. 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 global privileges to do anything. The 'finley'@'localhost' account can be used only when connecting from the local host. The 'finley'@'%.example.com' account uses the '%' wildcard in the host part, so it can be used to connect from any host in the example.com domain.

    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. (For information about user table sorting, see 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 global 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.

The previous example grants privileges at the global level. The next example creates three accounts and grants them access at lower levels; that is, to specific databases or objects within databases. Each account has a user name of custom, but the host name parts differ:

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

The three accounts can be used as follows:

  • The 'custom'@'localhost' account has all database-level privileges to access the bankaccount database. The account can be used to connect to the server only from the local host.

  • The 'custom'@'host47.example.com' account has specific database-level privileges to access the expenses database. The account can be used to connect to the server only from the host host47.example.com.

  • The 'custom'@'%.example.com' account has specific table-level privileges to access the addresses table in the customer database, from any host in the example.com domain. The account can be used to connect to the server from all machines in the domain due to use of the % wildcard character in the host part of the account name.

Inhoudsopgave Haut

Checking Account Privileges and Properties

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> SET print_identified_with_as_hex = ON;
  2. mysql> SHOW CREATE USER 'admin'@'localhost'\G
  3. *************************** 1. row ***************************
  4. CREATE USER for admin@localhost: CREATE USER 'admin'@'localhost'
  5. IDENTIFIED WITH 'caching_sha2_password'
  6. AS 0x24412430303524301D0E17054E2241362B1419313C3E44326F294133734B30792F436E77764270373039612E32445250786D43594F45354532324B6169794F47457852796E32
  7. REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK
  8. PASSWORD HISTORY DEFAULT
  9. PASSWORD REUSE INTERVAL DEFAULT
  10. PASSWORD REQUIRE CURRENT DEFAULT

Enabling the print_identified_with_as_hex system variable (available as of MySQL 8.0.17) causes SHOW CREATE USER to display hash values that contain unprintable characters as hexadecimal strings rather than as regular string literals.

Inhoudsopgave Haut

Revoking Account Privileges

To revoke account privileges, use the REVOKE statement. Privileges can be revoked at different levels, just as they can be granted at different levels.

Revoke global privileges:

  1. REVOKE ALL
  2.   ON *.*
  3.   FROM 'finley'@'%.example.com';
  4.  
  5. REVOKE RELOAD
  6.   ON *.*
  7.   FROM 'admin'@'localhost';

Revoke database-level privileges:

  1. REVOKE CREATE,DROP
  2.   ON expenses.*
  3.   FROM 'custom'@'host47.example.com';

Revoke table-level privileges:

  1. REVOKE INSERT,UPDATE,DELETE
  2.   ON customer.addresses
  3.   FROM 'custom'@'%.example.com';

To check the effect of privilege revocation, use SHOW GRANTS:

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

Inhoudsopgave Haut

Dropping Accounts

To remove an account, use the DROP USER statement. For example, to drop some of the accounts created previously:

  1. DROP USER 'finley'@'localhost';
  2. DROP USER 'finley'@'%.example.com';
  3. DROP USER 'admin'@'localhost';
  4. DROP USER 'dummy'@'localhost';

Zoek in de MySQL-handleiding

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-creating-accounts.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

  1. Bekijk - html-document Taal van het document:en Manuel MySQL : https://dev.mysql.com/

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.

Inhoudsopgave Haut