MariaDB-Kurs / Benutzerkonten anlegen und erforderliche Rechte mit GRANT vergeben

Ein MariaDB-Konto wird durch die Kombination aus Benutzername und Host identifiziert. GRANT vergibt Berechtigungen für einen bestimmten Geltungsbereich. Trennen Sie Verwaltungs- und Anwendungsverbindungen und prüfen Sie erlaubte sowie abgewiesene Operationen tatsächlich mit dem eingeschränkten Konto.

Kontenidentität, Verbindungsherkunft und Berechtigungsumfang unterscheiden

Derselbe Benutzername mit localhost und mit 127.0.0.1 kann unterschiedliche Kontendefinitionen bezeichnen. Verbindungsverfahren und Namensauflösung beeinflussen die Zuordnung. CURRENT_USER nennt in der direkten Clientsitzung das für die Berechtigungsprüfung verwendete Konto; es muss nicht mit Benutzername und Herkunft aus USER übereinstimmen. Die CURRENT-USER-Referenz erklärt die Unterscheidung.

Verwendet eine Anwendung root für gewöhnliche Leseabfragen, lässt sich ihr Zugriff kaum auf die erforderlichen Operationen begrenzen. Bestimmen Sie zuerst die benötigten Aufgaben und deren Geltungsbereich, statt zur Vermeidung von Fehlermeldungen globale Verwaltungsrechte zu vergeben.

Geltungsbereich Beispiel Zweck
Global *.* Serverweite Verwaltungsaufgaben
Datenbank shopcourse.* Operationen innerhalb dieser Datenbank
Tabelle shopcourse.c43_products Produktabfragen dieser Übung
Spalte UPDATE(stock) Änderungen nur an einer bestimmten Spalte

Ersetzen Sie das Demonstrationspasswort durch einen eigenen eindeutigen Wert. Nutzen Sie beim Clientstart die Passwortabfrage oder eine Optionsdatei mit eingeschränktem Dateizugriff. Halten Sie echte Zugangsdaten aus sichtbaren Befehlsargumenten und veröffentlichten Protokollen heraus. Die offiziellen Referenzen zu CREATE USER und GRANT beschreiben Konten und Berechtigungsstufen.

Die Daten getrennt von der Kontenverwaltung vorbereiten

Verbinden Sie sich als course_user mit dem Übungsserver und wählen Sie shopcourse. Die Anleitung zum Client und zu UTF-8-SQL-Dateien beschreibt das Speichern und SOURCE C:/mariadb-lab/lesson.sql; mit Ihrem tatsächlichen Dateipfad. Führen Sie SQL innerhalb des MariaDB-Clients aus.

Prüfen Sie SELECT VERSION(), DATABASE(); und SHOW TABLES;, bevor Sie c43_customers, c43_products, c43_orders und c43_items anlegen. Diese Namen gehören zur Übung. Prüfen Sie vorhandene Definitionen und Daten und erhalten Sie benötigte Anwendungstabellen.

USE shopcourse;
CREATE TABLE c43_customers (
 customer_id INT NOT NULL PRIMARY KEY,
 customer_name VARCHAR(50) NOT NULL,
 city VARCHAR(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c43_products (
 product_id INT NOT NULL PRIMARY KEY,
 product_name VARCHAR(100) NOT NULL,
 category VARCHAR(30) NOT NULL,
 price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
 stock INT NOT NULL CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c43_orders (
 order_id INT NOT NULL PRIMARY KEY,
 customer_id INT NOT NULL,
 ordered_at DATETIME NOT NULL,
 status VARCHAR(20) NOT NULL,
 shipping_fee DECIMAL(10,2) NOT NULL DEFAULT 1000,
 CONSTRAINT fk_c43_order_customer FOREIGN KEY (customer_id)
  REFERENCES c43_customers(customer_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c43_items (
 order_id INT NOT NULL,
 product_id INT NOT NULL,
 quantity INT NOT NULL CHECK (quantity > 0),
 unit_price DECIMAL(10,2) NOT NULL CHECK (unit_price >= 0),
 PRIMARY KEY (order_id, product_id),
 CONSTRAINT fk_c43_item_order FOREIGN KEY (order_id)
  REFERENCES c43_orders(order_id) ON DELETE RESTRICT,
 CONSTRAINT fk_c43_item_product FOREIGN KEY (product_id)
  REFERENCES c43_products(product_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO c43_customers VALUES
 (1,'김민수','서울'),(2,'이서연','부산'),
 (3,'박지우','서울'),(4,'최하늘','대전');
INSERT INTO c43_products VALUES
 (1,'노트','문구',3500,20),(2,'볼펜','문구',1500,50),
 (3,'머그컵','생활',8000,12),(4,'스탠드','생활',25000,5);
INSERT INTO c43_orders
 (order_id,customer_id,ordered_at,status,shipping_fee) VALUES
 (101,1,'2026-01-10 10:00:00','paid',1000),
 (102,1,'2026-01-20 11:00:00','paid',1000),
 (103,2,'2026-02-05 12:00:00','paid',1000),
 (104,3,'2026-02-10 13:00:00','canceled',1000);
INSERT INTO c43_items VALUES
 (101,1,2,3500),(101,2,3,1500),(102,3,1,8000),
 (103,1,1,3500),(103,3,2,8000),(104,2,1,1500);

Die koreanischen Namen und Ortswerte bleiben erhalten. Produkte 1–4 sind Notizbuch, Kugelschreiber, Becher und Schreibtischlampe. Kunden 1 und 3 haben den Ortswert für Seoul, Kunde 2 für Busan und Kunde 4 für Daejeon. Bewahren Sie die UTF-8-Zeichenfolgen, wenn Sie die Ergebnisse nachstellen.

Das entsprechend berechtigte Übungskonto erstellt die Tabellen. CREATE USER und GRANT benötigen eine separate Verwaltungsverbindung mit den erforderlichen Rechten, auch für die Vergabe des Tabellenzugriffs. Prüfen Sie vorher, ob 'shop_reader43'@'127.0.0.1' bereits existiert. Wählen Sie gegebenenfalls einen anderen Übungsnamen und passen Sie alle Blöcke an. Ersetzen oder löschen Sie kein vorhandenes Konto, um den Namen wiederzuverwenden.

SELECT für genau eine Produkttabelle vergeben

Führen Sie den folgenden Block nach dem Ersetzen des Demonstrationspassworts in der Verwaltungsverbindung aus. Die Ausgabe von Kontendefinitionen kann Authentifizierungsinformationen enthalten. Prüfen Sie sie privat und veröffentlichen Sie weder Geheimnisse noch Passwort-Hashes.

CREATE USER 'shop_reader43'@'127.0.0.1' IDENTIFIED BY 'Replace_With_Unique_Lab_Password!';
GRANT SELECT ON shopcourse.c43_products TO 'shop_reader43'@'127.0.0.1';
SHOW GRANTS FOR 'shop_reader43'@'127.0.0.1';

Das Konto darf c43_products lesen. Es erhält weder Kunden- oder Bestellungsabfragen noch INSERT, UPDATE oder Strukturänderungen. Auf reguläre CREATE-USER- und GRANT-Anweisungen muss gewöhnlich kein FLUSH PRIVILEGES folgen. Verwenden Sie die vorgesehenen Kontenanweisungen statt direkter Änderungen an Berechtigungstabellen.

Eine eigene eingeschränkte Verbindung öffnen und beide Fälle testen

Starten Sie den folgenden Befehl im Betriebssystemterminal und geben Sie das neue Passwort an der Abfrage ein. Passen Sie den Port an, falls der Server nicht 3306 verwendet. Der Client verwendet ausdrücklich TCP zum angegebenen lokalen Ziel. Prüfen Sie die Rechte in der neuen Sitzung als shop_reader43, nicht in der weiterhin geöffneten Verbindung als course_user.

mariadb --protocol=tcp --host=127.0.0.1 --port=3306 --user=shop_reader43 --password --default-character-set=utf8mb4 shopcourse

Führen Sie in der neuen Clientsitzung Folgendes aus:

SELECT USER(),CURRENT_USER();
SELECT product_id,product_name FROM c43_products ORDER BY product_id;

CURRENT_USER muss zum eingeschränkten Konto passen, und die vier Produkte müssen lesbar sein. Führen Sie anschließend jede der folgenden Anweisungen einzeln in derselben Sitzung aus. Beide sollen abgewiesen werden:

SELECT * FROM c43_customers;
UPDATE c43_products SET stock=stock WHERE product_id=1;

Die erste Anweisung versucht eine nicht freigegebene Kundenabfrage. Die zweite benötigt UPDATE, obwohl stock nur sein eigener Wert zugewiesen wird. Leiten Sie aus einer Ablehnung nicht automatisch einen Bedarf für ALL PRIVILEGES ab. Prüfen Sie die tatsächlich benötigte Operation. Wenn Rollen verwendet werden, beziehen Sie zusätzlich die Rechte aktiver Rollen ein.

Den Rechteentzug mit einer neuen Verbindung prüfen

Wechseln Sie zur Verwaltungsverbindung und entziehen Sie die Produktleseberechtigung:

REVOKE SELECT ON shopcourse.c43_products FROM 'shop_reader43'@'127.0.0.1';
SHOW GRANTS FOR 'shop_reader43'@'127.0.0.1';

Schließen Sie die eingeschränkte Sitzung und öffnen Sie eine neue zur Gegenprobe. Nach dem Entzug ihres einzigen Datenbankrechts kann schon die Auswahl von shopcourse beim Verbindungsaufbau abgewiesen werden. Entfernen Sie in diesem Fall shopcourse am Ende des Clientbefehls, verbinden Sie sich ohne Standarddatenbank und versuchen Sie SELECT * FROM shopcourse.c43_products;. So unterscheiden Sie eine erfolgreiche Anmeldung von der Berechtigung zum Tabellenzugriff.

Wie Änderungen bestehende Sitzungen erreichen, hängt vom Berechtigungsumfang ab. Prüfen Sie deshalb auch eine neue Verbindung. USAGE kann ein vorhandenes Konto ohne sachliche Rechte auf der gezeigten Stufe kennzeichnen. Die REVOKE-Referenz erläutert den Rechteentzug.

Das Entfernen eines Kontos löscht weder gespeicherte Anwendungsdaten noch automatisch alle Objekte, die es als DEFINER verwenden. Prüfen Sie vor der Stilllegung eines echten Kontos Anwendungsverbindungen, Views und Routinen. Die Übung zu Views und Sicherheitskontext behandelt diese Abhängigkeit.

Nur das neue Übungskonto und die Übungstabellen entfernen

Bestätigen Sie anhand Ihrer Vorbereitung, dass das Konto ausschließlich für diese Übung neu angelegt wurde. Schließen Sie seine Verbindungen und entfernen Sie es über die Verwaltungsverbindung. Andere Anwendungskonten gehören nicht zu dieser Bereinigung.

DROP USER 'shop_reader43'@'127.0.0.1';

Prüfen Sie Datenbank, Definitionen und gespeicherte Inhalte und sichern Sie benötigte Daten vor einer Wiederholung. Entfernen Sie die folgenden Tabellen nur als entbehrliche Übungsobjekte, zuerst die abhängigen Tabellen. DROP TABLE löscht Struktur und Daten und ist nicht durch ein gewöhnliches ROLLBACK rückgängig zu machen.

DROP TABLE shopcourse.c43_items;
DROP TABLE shopcourse.c43_orders;
DROP TABLE shopcourse.c43_products;
DROP TABLE shopcourse.c43_customers;