Keine Cache-Version


Caching deaktiviert Standardeinstellung für diese Seite:aktiviert (code DEF204)
Wenn die Anzeige zu langsam ist, können Sie den Benutzermodus deaktivieren, um die zwischengespeicherte Version anzuzeigen.

Rechercher dans le manuel MySQL

6.2.8 Adding Accounts, Assigning Privileges, and Dropping Accounts

Inhaltsverzeichnis

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.

Inhaltsverzeichnis 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.

Inhaltsverzeichnis 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. +---------------------------------------------+

Inhaltsverzeichnis 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';

Suchen Sie im MySQL-Handbuch

Deutsche Übersetzung

Sie haben gebeten, diese Seite auf Deutsch zu besuchen. Momentan ist nur die Oberfläche übersetzt, aber noch nicht der gesamte Inhalt.

Wenn Sie mir bei Übersetzungen helfen wollen, ist Ihr Beitrag willkommen. Alles, was Sie tun müssen, ist, sich auf der Website zu registrieren und mir eine Nachricht zu schicken, in der Sie gebeten werden, Sie der Gruppe der Übersetzer hinzuzufügen, die Ihnen die Möglichkeit gibt, die gewünschten Seiten zu übersetzen. Ein Link am Ende jeder übersetzten Seite zeigt an, dass Sie der Übersetzer sind und einen Link zu Ihrem Profil haben.

Vielen Dank im Voraus.

Dokument erstellt 26/06/2006, zuletzt geändert 26/10/2018
Quelle des gedruckten Dokuments:https://www.gaudry.be/de/mysql-rf-creating-accounts.html

Die Infobro ist eine persönliche Seite, deren Inhalt in meiner alleinigen Verantwortung liegt. Der Text ist unter der CreativeCommons-Lizenz (BY-NC-SA) verfügbar. Weitere Informationen auf die Nutzungsbedingungen und dem Autor.

Referenzen

  1. Zeigen Sie - html-Dokument Sprache des Dokuments:en Manuel MySQL : https://dev.mysql.com/

Diese Verweise und Links verweisen auf Dokumente, die während des Schreibens dieser Seite konsultiert wurden, oder die zusätzliche Informationen liefern können, aber die Autoren dieser Quellen können nicht für den Inhalt dieser Seite verantwortlich gemacht werden.
Der Autor Diese Website ist allein dafür verantwortlich, wie die verschiedenen Konzepte und Freiheiten, die mit den Nachschlagewerken gemacht werden, hier dargestellt werden. Denken Sie daran, dass Sie mehrere Quellinformationen austauschen müssen, um das Risiko von Fehlern zu reduzieren.

Inhaltsverzeichnis Haut