Implementierung der MySQL-Benutzerrechteverwaltung

1. Einführung in MySQL-Berechtigungen

Es gibt 4 Tabellen, die Berechtigungen in MySQL steuern,分別為user表，db表，tables_priv表，columns_priv表. Meine aktuelle Version ist MySQL 5.7.22.

Der Überprüfungsprozess der MySQL-Berechtigungstabelle ist:

1. Zunächst wird in der Benutzertabelle aus den drei Feldern Host, Benutzer und Passwort ermittelt, ob die verbundene IP, der Benutzername und das Passwort vorhanden sind. Wenn dies der Fall ist, ist die Überprüfung erfolgreich.
2. Nach der Identitätsauthentifizierung werden Berechtigungen in der Reihenfolge Benutzer, Datenbank, Tabellenpriv. und Spaltenpriv. zugewiesen und überprüft. Das heißt, überprüfen Sie zuerst den globalen Berechtigungstabellenbenutzer. Wenn die entsprechende Berechtigung im Benutzer Y ist, ist die Berechtigung dieses Benutzers für alle Datenbanken Y und db, tables_priv und columns_priv werden nicht mehr überprüft. Wenn sie N ist, überprüfen Sie die spezifische Datenbank, die diesem Benutzer in der db-Tabelle entspricht, und erhalten Sie die Berechtigung Y in db. Wenn db N ist, überprüfen Sie die spezifische Tabelle, die dieser Datenbank in tables_priv entspricht, und erhalten Sie die Berechtigung Y in der Tabelle und so weiter.

1.1 MySQL-Berechtigungsstufen

Unterteilt in:
Globale Verwaltungsberechtigungen: Gilt für die gesamte MySQL-Instanzebene. Berechtigungen auf Datenbankebene: Gilt für eine bestimmte Datenbank oder alle Datenbanken. Berechtigungen auf Datenbankobjektebene: Gilt für ein bestimmtes Datenbankobjekt (Tabelle, Ansicht usw.) oder alle Datenbankobjekte.

Die Berechtigungen werden in der MySQL-Datenbank in den Systemtabellen „user“, „db“, „tables_priv“, „columns_priv“ und „procs_priv“ gespeichert, die nach dem Start der MySQL-Instanz in den Speicher geladen werden.

Überprüfen Sie, welche Benutzer MySQL hat:

mysql> wähle Benutzer, Host aus mysql.user;

Schauen wir uns root 用戶在權限系統表中的數據an:

mysql> mysql verwenden;
mysql> select * from user where user='root' and host='localhost'\G; #Alle Berechtigungen sind Y, was bedeutet, dass alle Berechtigungen verfügbar sindmysql> select * from db where user='root' and host='localhost'\G; # Kein solcher Datensatzmysql> select * from tables_priv where user='root' and host='localhost'; # Kein solcher Datensatzmysql> select * from columns_priv where user='root' and host='localhost'; # Kein solcher Datensatzmysql> select * from procs_priv where user='root' and host='localhost'; # Kein solcher Datensatz

Wie oben erwähnt: der Überprüfungsprozess der Berechtigungen

Überprüfen Sie die Berechtigungen des Benutzers root@'localhost'

mysql> Berechtigungen für root@localhost anzeigen;
+---------------------------------------------------------------------+
| Berechtigungen für root@localhost |
+---------------------------------------------------------------------+
| GEWÄHREN SIE ALLE PRIVILEGIEN FÜR *.* AN 'root'@'localhost' MIT DER GRANT-OPTION |
| GRANT PROXY AUF ''@'' AN 'root'@'localhost' MIT GRANT-OPTION |
+---------------------------------------------------------------------+
2 Zeilen im Satz (0,00 Sek.)

2. Detaillierte Erklärung der MySQL-Berechtigungen

All/All Privileges權限代表全局或者全數據庫對象級別的所有權限

Mit der Berechtigung „Ändern“ können Sie die Tabellenstruktur ändern. Sie muss jedoch von den Berechtigungen „Erstellen“ und „Einfügen“ begleitet werden. Wenn Sie eine Tabelle umbenennen möchten, müssen Sie über die Berechtigung zum Ändern und Löschen der Originaltabelle sowie zum Erstellen und Einfügen der neuen Tabelle verfügen.

Mit der Berechtigung „Routine ändern“ können Sie gespeicherte Prozeduren und Funktionen ändern oder löschen.

Mit der Berechtigung „Erstellen“ können Sie neue Datenbanken und Tabellen erstellen.

Mit der Berechtigung „Routine erstellen“ können Sie gespeicherte Prozeduren und Funktionen erstellen.

Mit der Berechtigung „Tablespace erstellen“ können Sie Tablespaces und Protokollgruppen erstellen, ändern und löschen.

Mit der Berechtigung „Temporäre Tabellen erstellen“ können Sie temporäre Tabellen erstellen.

Mit der Berechtigung „Benutzer erstellen“ können Sie Benutzer erstellen, ändern, löschen und umbenennen.

Die Berechtigung „Ansicht erstellen“ stellt die Berechtigung zum Erstellen einer Ansicht dar.

Mit der Löschberechtigung können Sie Zeilendaten löschen.

Mit der Berechtigung „Drop“ können Sie Datenbanken, Tabellen und Ansichten löschen, einschließlich des Befehls „truncate table“.

Mit Ereignisberechtigungen können Sie MySQL-Ereignisse abfragen, erstellen, ändern und löschen.

Mit der Ausführungsberechtigung können Sie gespeicherte Prozeduren und Funktionen ausführen.

Mit Dateiberechtigungen können Sie Datenträgerdateien in Verzeichnissen lesen und schreiben, auf die MySQL zugreifen kann. Zu den verfügbaren Befehlen gehören die Funktionen „Daten in Datei laden“, „... in Ausgabedatei auswählen“ und „Datei laden()“.

Die Option „Berechtigung erteilen“ gibt an, ob dieser Benutzer die Berechtigungen erteilen oder widerrufen darf, die Sie anderen Benutzern erteilt haben. Diese Berechtigung ist erforderlich, wenn dem Administrator erneut Berechtigungen erteilt werden.

Indexberechtigungen geben an, ob das Erstellen und Löschen von Indizes zulässig ist

Die Einfügeberechtigung gibt an, ob Daten in die Tabelle eingefügt werden können. Die Einfügeberechtigung ist auch beim Ausführen der Anweisungen „Tabelle analysieren“, „Tabelle optimieren“ und „Tabelle reparieren“ erforderlich.

Mit der Sperrberechtigung können Sie eine Tabelle mit Auswahlberechtigung sperren, um zu verhindern, dass andere Links diese Tabelle lesen oder in sie schreiben.

Mit Prozessberechtigungen können Sie Prozessinformationen in MySQL anzeigen, beispielsweise Befehle wie „show processlist“, „mysqladmin processlist“ und „show engine“ ausführen.

Die Referenzberechtigung wurde nach Version 5.7.6 eingeführt und gibt an, ob die Erstellung von Fremdschlüsseln zulässig ist.

Die Berechtigung „Neu laden“ ermöglicht die Ausführung des Flush-Befehls, der angibt, dass die Berechtigungstabelle erneut in den Systemspeicher geladen wird. Der Befehl „Aktualisieren“ bedeutet, dass die Protokolldatei geschlossen und erneut geöffnet und alle Tabellen aktualisiert werden.

Mit den Berechtigungen des Replikationsclients können Sie die Befehle show master status, show slave status und show binary logs ausführen.

Replikations-Slave-Berechtigung bedeutet, dass der Slave-Host über diesen Benutzer eine Verbindung zum Master herstellen darf, um eine Master-Slave-Replikationsbeziehung herzustellen

Mit der Select-Berechtigung können Sie Daten aus einer Tabelle anzeigen. Für einige Select-Anweisungen, die keine Tabellendaten abfragen, ist diese Berechtigung nicht erforderlich, z. B. Select 1+1 und Select PI()+2. Die Select-Berechtigung ist auch erforderlich, wenn Update-/Delete-Anweisungen ausgeführt werden, die eine Where-Bedingung enthalten.

Die Berechtigung „Datenbanken anzeigen“ bedeutet, dass Sie alle Datenbanknamen anzeigen können, indem Sie den Befehl „Datenbanken anzeigen“ ausführen.

Mit der Berechtigung „Ansicht anzeigen“ können Sie die Anweisungen anzeigen, die durch Ausführen des Befehls „show create view“ eine Ansicht erstellen.

Mit der Shutdown-Berechtigung können Sie die Datenbankinstanz herunterfahren. Zu den ausgeführten Anweisungen gehören mysqladmin shutdown

Mit Superberechtigungen können Sie eine Reihe von Datenbankverwaltungsbefehlen ausführen, darunter den Kill-Befehl zum Erzwingen des Schließens einer Verbindung, den Change-Master-Befehl zum Erstellen einer Replikationsbeziehung und den Befehl „Server erstellen/ändern/löschen“.

Mit der Trigger-Berechtigung können Sie Trigger erstellen, löschen, ausführen und anzeigen.

Mit der Aktualisierungsberechtigung können Sie Daten in der Tabelle ändern.

Nutzungsberechtigungen sind die Standardberechtigungen nach dem Erstellen eines Benutzers, die selbst die Verbindungsanmeldeberechtigung darstellen

2.1 Systemberechtigungstabelle

User表: speichert Benutzerkontoinformationen und Berechtigungen auf globaler Ebene (alle Datenbanken), die bestimmen, welche Benutzer von welchen Hosts auf die Datenbankinstanz zugreifen können. Wenn有全局權限則意味著對所有數據庫都有此權限
Db-Tabelle: speichert Berechtigungen數據庫級別, die bestimmen, welche Benutzer von welchen Hosts auf diese Datenbank zugreifen können
Tables_priv-Tabelle:存放表級別的權限, die bestimmen, welche Benutzer von welchen Hosts auf diese Tabelle in der Datenbank zugreifen können
Columns_priv-Tabelle:存放列級別的權限, die bestimmen, welche Benutzer von welchen Hosts auf dieses Feld in der Datenbanktabelle zugreifen können
Procs_priv-Tabelle:存放存儲過程和函數

Das Wichtigste ist die Benutzertabelle

2.1.1 Struktur der Benutzer- und Datenbankberechtigungstabelle

Spezielle Felder in der Tabellenstruktur für Benutzerberechtigungen:

• Plugin, Feld authentication_string speichert Benutzerauthentifizierungsinformationen
• Password_expired ist auf „Y“ gesetzt, was bedeutet, dass der DBA das Kennwort dieses Benutzers so festlegen kann, dass es abläuft, und den Benutzer auffordern kann, das Kennwort nach Ablauf zurückzusetzen (Benutzer ändern/Kennwort festlegen, um das Kennwort zurückzusetzen).
• Password_last_changed ist ein Zeitstempelfeld, das den Zeitpunkt der letzten Änderung des Passworts angibt.此數值自動更新wenn Sie einen Benutzer erstellen oder das Passwort eines Benutzers ändern, indem Sie Befehle wie create user/alter user/set password/grant ausführen.
• „Password_lifetime“ stellt die Anzahl der Tage ab dem Zeitpunkt der letzten Änderung des Passworts bis zum Ablauf des Passworts dar.
• Account_locked代表此用戶被鎖住，無法使用

Vor MySQL 5.7 gab es in der Benutzertabelle ein Kennwortfeld.

2.1.2 Struktur der Berechtigungstabellen Tables_priv und columns_priv

Berechtigungswerte „Tables_priv“ und „columns_priv“

2.1.3 Struktur der procs_priv-Berechtigungstabelle

• Routine_type ist ein Aufzählungstyp, der angibt, ob es sich um eine gespeicherte Prozedur oder eine Funktion handelt.
• Die Felder „Zeitstempel“ und „Grantor“ werden vorerst nicht verwendet.

Tabelle mit Feldlängenbeschränkungen für die Systemberechtigungstabelle

Groß-/Kleinschreibung bei der Autorisierungsauthentifizierung

• Bei den Feldern user, password, authencation_string, db und table_name muss die Groß- und Kleinschreibung beachtet werden.
• Bei den Feldern „Host“, „Column_Name“ und „Routine_Name“ wird nicht zwischen Groß- und Kleinschreibung unterschieden.

2.2 Informationsverwaltung für Benutzerrechte

2.2.1 Informationen zu Benutzerberechtigungen anzeigen

Überprüfen Sie, welche Benutzer MySQL hat

mysql> wähle Benutzer, Host aus mysql.user;

Zeigen Sie die Berechtigungen an, die dem Benutzer erteilt wurden, z. B. root

mysql> Berechtigungen für root@'localhost' anzeigen;

Andere nicht autorisierte Informationen des Benutzers anzeigen

mysql> anzeigen, Benutzer root@'localhost' erstellen; 

2.2.2 Benutzerzusammensetzung

Autorisierte MySQL-Benutzer bestehen aus zwei Teilen:用戶名和登錄主機名

• Die Syntax zum Ausdrücken eines Benutzers lautet „Benutzername“@„Hostname“.
• Einfache Anführungszeichen sind optional, aber包含特殊字符則是必須的
• "@'localhost' steht für anonyme Login-Benutzer
• Host_name kann ein Hostname oder eine IPv4/IPv6-Adresse sein. Localhost steht für den lokalen Rechner, 127.0.0.1 für die lokale IPv4-Adresse und ::1 für die lokale IPv6-Adresse
• Im Feld Host_name können %和_ verwendet werden. Beispielsweise steht '%' für alle Hosts, '%.mysql.com' für

Alle Hosts aus der Domäne mysql.com. „192.168.1.%“ steht für alle Hosts aus dem Netzwerksegment 192.168.1.

2.2.3 Benutzerberechtigungen ändern

Nach dem Erteilen, Widerrufen, Festlegen eines Kennworts oder Umbenennen von Benutzerberechtigungen zum Ändern lädt MySQL die geänderten Berechtigungsinformationen automatisch in den Systemspeicher.

Wenn Sie die Systemberechtigungstabelle einfügen/aktualisieren/löschen, müssen Sie den Befehl zum Aktualisieren der Berechtigungen ausführen, um sie mit dem Systemspeicher zu synchronisieren. Die Befehle zum Aktualisieren der Berechtigungen umfassen: flush privileges /mysqladmin flush-privileges / mysqladmin reload

Wenn Sie die Berechtigungen auf Tabellen- und Spaltenebene ändern, werden die neuen Berechtigungen beim nächsten Ausführen des Clients wirksam.

Wenn Sie Berechtigungen auf Datenbankebene ändern, werden die neuen Berechtigungen wirksam, nachdem der Client den Befehl „use database“ ausgeführt hat.

Wenn Sie die Berechtigungen auf globaler Ebene ändern, müssen Sie die Verbindung neu erstellen, damit die neuen Berechtigungen wirksam werden.

Wenn Sie die Berechtigungen auf globaler Ebene ändern, müssen Sie die Verbindung neu erstellen, damit die neuen Berechtigungen wirksam werden (z. B. durch Ändern des Kennworts).

2.2.4 MySQL-Benutzer erstellen

Es gibt zwei Möglichkeiten, einen autorisierten MySQL-Benutzer zu erstellen

• 執行create user/grant命令(empfohlene Methode).
• Bedienen Sie die MySQL-Systemberechtigungstabelle direkt über die Einfügeanweisung

# Finley erstellen Dies erstellt nur einen Benutzer und hat keine Berechtigungen mysql> CREATE USER 'finley'@'localhost' IDENTIFIED BY 'some_pass';
# Machen Sie finley zum Administrator usermysql> GRANT ALL PRIVILEGES ON *.* TO 'finley'@'localhost' WITH
Gewährungsoption;
#Erstellen Sie einen Benutzer und erteilen Sie RELOAD,PROCESS-Berechtigungen für alle Bibliotheken und Tabellenmysql> GRANT RELOAD,PROCESS ON *.* TO 'admin'@'localhost' identifiziert durch '123456';
 
# Benutzer „keme“ erstellen, der nur Auswahlberechtigung für die ID-Spalte in der Testdatenbank und der temporären Tabelle hat.mysql> grant select(id) on test.temp to keme@'localhost' identified by '123456';

2.2.4 MySQL-Berechtigungen zurückfordern

Widerrufen Sie die Berechtigungen des Benutzers mit dem Befehl „Widerrufen“. Überprüfen Sie beim Widerrufen, welche Berechtigungen der Benutzer hat, und widerrufen Sie diese dann. Ich werde den Administratorbenutzer testen.

mysql> Berechtigungen für admin@'localhost' anzeigen;
mysql> wähle Benutzer, Host aus mysql.user;
mysql> widerrufe PROCESS ON *.* FROM admin@'localhost'; 

2.2.5 MySQL-Benutzer löschen

Löschen Sie MySQL-Benutzer, indem Sie den Befehl drop user ausführen. Sie können sie auch über die Systemberechtigungstabelle löschen (nicht empfohlen).

mysql> Benutzer admin@'localhost' löschen;

2.2.6 Festlegen von MySQL-Benutzerressourcenlimits

Durch Festlegen der globalen Variable max_user_connections können Sie die Anzahl der Verbindungen begrenzen, die alle Benutzer gleichzeitig zu einer MySQL-Instanz herstellen können. Dieser Parameter kann jedoch nicht verwendet werden, um jeden Benutzer unterschiedlich zu behandeln. Daher bietet MySQL eine Ressourcenlimitverwaltung für jeden Benutzer.

MAX_QUERIES_PER_HOUR: Die Anzahl der Abfragen, die ein Benutzer in einer Stunde ausführen kann (im Wesentlichen einschließlich aller Anweisungen)

MAX_UPDATES_PER_HOUR: Die Häufigkeit, mit der ein Benutzer in einer Stunde Änderungen vornehmen kann (nur Anweisungen, die Datenbanken oder Tabellen ändern, werden berücksichtigt)

MAX_CONNECTIONS_PER_HOUR: Die Anzahl der Verbindungen, die ein Benutzer pro Stunde zu MySQL herstellen kann.

MAX_USER_CONNECTIONS:同一時間連接MySQL實例的數量

Ab Version 5.0.3 bezieht sich das Ressourcenlimit für den Benutzer „user“@„%.example.com“ auf alle Verbindungen vom Domänenhost example.com zum Benutzer user und nicht auf Verbindungen von host1.example.com bzw. host2.example.com.

2.2.7 MySQL-Benutzerkennwort ändern

Sie können das Benutzerkennwort auf folgende Weise ändern:

mysql> ALTER USER 'jeffrey'@'localhost' IDENTIFIZIERT DURCH 'mypass';
mysql> PASSWORT FÜR 'jeffrey' festlegen@'localhost' = PASSWORT('mypass');
mysql> GRANT USAGE ON *.* AN ,jeffrey‘@,localhost‘ IDENTIFIZIERT DURCH ,mypass‘;
Shell> mysqladmin -u Benutzername -h Hostname Passwort "neues Passwort"

Geben Sie beim Erstellen eines Benutzers ein Kennwort an

mysql> ERSTELLEN SIE BENUTZER 'jeffrey'@'localhost' IDENTIFIZIERT DURCH 'mypass';

Zum Ändern des Benutzerkennworts der aktuellen Sitzung stehen folgende Möglichkeiten zur Verfügung:

mysql> ALTER USER USER() IDENTIFIED BY 'mypass';
mysql> PASSWORT FESTLEGEN = PASSWORT('meinpass');

2.2.8 Festlegen der Ablaufrichtlinie für MySQL-Benutzerkennwörter

Legen Sie den Systemparameter default_password_lifetime so fest, dass er für alle Benutzerkonten gilt

• default_password_lifetime=180 Setzen Sie das Ablaufdatum auf 180 Tage
• default_password_lifetime=0 Passwort so einstellen, dass es nicht abläuft

Wenn für jeden Benutzer eine Richtlinie zum Ablaufen von Passwörtern festgelegt ist, überschreibt diese die oben genannten Systemparameter.

ALTER USER 'jeffrey'@'localhost' PASSWORT ABLAUFINTERVALL 90 TAGE;
ALTER USER 'jeffrey'@'localhost' PASSWORD EXPIRE NEVER; Passwort läuft nicht ab ALTER USER 'jeffrey'@'localhost' PASSWORD EXPIRE DEFAULT; Standardmäßige Ablaufrichtlinie

Manuelles Ablaufen des Kennworts eines Benutzers erzwingen

ALTER USER 'jeffrey'@'localhost' PASSWORT LÄUFT AB;

2.2.9 MySQL-Benutzersperre

Setzen Sie den Sperrstatus des Benutzers, indem Sie den Befehl create user/alter user mit der Klausel account lock/unlock ausführen.

Der Standardbenutzerstatus der Anweisung „Benutzer erstellen“ ist „entsperrt“.

# Sperren Sie den Benutzer mysql beim Erstellen> erstellen Sie den Benutzer abc2@localhost, identifiziert durch die Kontosperre „mysql“;

Standardmäßig ändert die Anweisung „Alter user“ nicht den Sperr-/Entsperrstatus des Benutzers.

# Ändern Sie den zu entsperrenden Benutzer
mysql> Benutzer ändern abc2@'localhost' Konto entsperren;

Wenn sich der Client mit einem Benutzer im gesperrten Zustand bei MySQL anmeldet, erhält er die folgende Fehlermeldung:
Der Zugriff für den Benutzer „Benutzername“@„Hostname“ wurde verweigert.
Das Konto ist gesperrt.

Offizielle Dokumentation: https://dev.mysql.com/doc/refman/5.7/en/privilege-system.html

Dies ist das Ende dieses Artikels über die Implementierung der MySQL-Benutzerrechteverwaltung. Weitere relevante Inhalte zur MySQL-Benutzerrechteverwaltung finden Sie in früheren Artikeln auf 123WORDPRESS.COM oder in den folgenden verwandten Artikeln. Ich hoffe, dass jeder 123WORDPRESS.COM in Zukunft unterstützen wird!