Rechte pro Tabelle und sogar pro Spalte sinnvoll einstellen

Nachdem du in in unserem Artikel MySQL Datenbank erstellen und löschen gelernt hast, wie man in MySQL eine Datenbank erstellt und löscht und dir in unserem Artikel zum Thema Wie erstelle ich einen MySQL Benutzer und weise ihm Rechte zu? gezeigt wurde, wie man einem Nutzer Zugriff auf eine Datenbank in MySQL gewährt, wird dir in diesem Tutorial schrittweise näher gebracht, wie du Benutzerrechte nicht nur für eine Datenbank definierst, sondern pro Tabelle und sogar pro Spalte für bestimmte Nutzer festlegst. Beim Tutorial zu ‚MySQL Feinabstimmung der Benutzerrechte‘ werde ich mit dir im Sinne eines Real-Live Case eine Datenbank erstellen, diese mit Tabellen füllen und vier frei erfundenen Nutzern spezifische Rechte auf bestimmte Tabellen und/oder Tabellenspalten dieser Datenbank zuweisen. Einige in diesem Tutorial einführende Shell-Commands werden dir somit bereits aus unseren oben genannten Artikeln bekannt sein, werden jedoch trotzdem gezeigt, um den Gesamtkontext für dich greifbar zu machen. Du kannst also alle Schritte bei dir am Rechner anhand dieses Tutorials nachbilden und simulieren.

Fallbeispiel

• Feinabstimmung der Benutzerrechte

Es soll möglich sein Nutzerrechte nicht lediglich auf bestimmte Datenbanken zu vergeben, sondern diese sogar zum einen zu spezifizieren und in Anwendung auf den pro Kopf Nutzer zu individualisieren. Hierbei wird davon ausgegangen, dass einem bestimmten Nutzer nur auf bestimmte Tabellen oder nur auf bestimmte Spalten einer Tabelle oder mehrerer Tabellen Zugriff erlaubt wird. Im Hinblick auf Spalten wäre ein denkbares Szenario, dass ein bestimmter Nutzer in einer bestimmten Tabelle nur die Inhalte der Spalten A und B, nicht aber die der Spalte C anschauen und/oder editieren kann und lediglich ein Nutzer mit allen Rechten vollen Zugriff auf alle Tabellen und deren Inhalte hat.

Anwendungsszenario

Es wird zur Verbildlichung im Sinne eines Real-Live Case von einer Datenbank zur Systemverwaltung von Blogeinträgen ausgegangen, die nach der Implementierung die unten aufgeführten Tabellen FAVORITENLISTE, NUTZER, REDAKTEUR, CHEFREDAKTEUR, BLOGEINTRAG und FAVORISTENLISTEenthaltenBLOGEINTRAG mit Inhalten enthalten soll.

Für die Vergabe von Rechten ist hierbei wichtig, dass jeder Redakteur ein Nutzer ist, aber nicht jeder Nutzer ein Redakteur und dass jeder Chefredakteur ein Redakteur ist, aber nicht jeder Redakteur ein Chefredakteur. Chefredakteure haben kompletten Zugriff auf die Datenbank Blogeinträge, sie können unter anderem Tabellen anlegen und diese sichten, editieren und löschen. Redakteure hingegen haben eingeschränkten Zugriff auf Tabellen der Datenbank. Sie dürfen keine Tabellen anlegen und haben nur für bestimmte Tabellen und/oder Tabellenspalten Rechte. Ein Redakteur hat alle Rechte an der Tabelle REDAKTEUR. In der Tabelle NUTZER darf er lediglich die in den Spalten Benutzername, EMailAdresse und FavoritenlisteID befindlichen Inhalte einsehen. Auch die Inhalte der Tabelle BLOGEINTRAGenthaltenFAVORITENLISTE darf ein Redakteur lediglich einsehen, wobei er hier Zugriff auf alle Spalten hat. In der Tabelle BLOGEINTRAG darf ein Redakteur lediglich die Inhalte der Spalten Titel, Text und Benutzername updaten, jedoch sowohl die komplette Tabelle sichten, als auch neue Einträge hinzufügen.
Als Nutzer darf man in der Tabelle NUTZER die Spalten Benutzername, EMailAdresse und FavoritenlisteID einsehen, in den Tabellen REDAKTEUR, BLOGEINTRAG und FAVORITENLISTEenthaltenBLOGEINTRAG darf man jeweils lediglich die Inhalte sichten, das bezieht sich jedoch hierbei auf alle Spalten der jeweiligen Tabelle. Für die Tabelle FAVORITENLISTE sollen NUTZER gar keine Rechte haben.

Umsetzung

Zunächst werden unter dem root-Account die vier in der Einführung dieses Tutorials genannten Nutzer mit dem CREATE USER-Command, sowie die Datenbank
Blogeinträge mit dem CREATE DATABASE-Command angelegt.

mysql -u root -p
password: *********

mysql> CREATE USER 'jens'@'localhost' IDENTIFIED BY 'passwort1';
Query OK, 0 rows affected (0,00 sec)

mysql> CREATE USER 'katrin'@'localhost' IDENTIFIED BY 'passwort2';
Query OK, 0 rows affected (0,00 sec)

mysql> CREATE USER 'natascha'@'localhost' IDENTIFIED BY 'passwort3';
Query OK, 0 rows affected (0,00 sec)

mysql> CREATE USER 'michael'@'localhost' IDENTIFIED BY 'passwort4';
Query OK, 0 rows affected (0,00 sec)

mysql> CREATE DATABASE Blogeinträge;
Query OK, 0 rows affected (0,00 sec)

Danach werden die Rechte vergeben, die ein Chefredakteur haben soll, wie ich zu Beginn des Anwendungsszenarios geschildert habe.

mysql> GRANT ALL PRIVILEGES ON Blogeinträge.* TO 'jens'@'localhost';
Query OK, 0 rows affected (0,00 sec)

In MySQL werden Berechtigungen in bestimmten Systemtabellen gespeichert, deren Werte mittels dem SELECT-Command zur Einsicht wiedergegeben werden können.
Informationen zu Nutzerberechtigungen werden in der mysql.user-Systemtabelle gespeichert. In der mysql.db-Systemtabelle sind die Werte zu Datenbankberechtigungen zu finden. Die Daten zu Tabellenberechtigungen hingegen sind in der mysql.tables_priv-Systemtabelle gespeichert und Informationen zu Spaltenberechtigungen in der mysql.columns_priv-Systemtabelle. Die Attribute der mysql.tables_priv-Systemtabelle beziehen sich auf Rechte an Tabellen einer bestimmten Datenbank – in diesem Fall die Datenbank Blogeinträge – und die Attribute der Tabelle mysql.columns_priv-Systemtabelle nehmen Bezug auf Rechte an bestimmten Spalten dieser Tabellen. Hinter den Attributen der jeweiligen Tabelle, wie Sie in der unten stehenden Tabelle aufgelistet sind, stehen Werte. Möchte man die Werte der Attribute User, Host, Db, Select_priv und Insert_priv aus der mysql.db-Systemtabelle zur Sichtung wiedergegeben haben, würde der Command SELECT user,host,db,select_priv,insert_priv FROM db dies erfüllen. Andernfalls, wenn man z.B. alle Attributwerte angezeigt bekommen möchte, würde der Command SELECT * FROM db genügen. Eine Referenz zu den in der unten stehenden Tabelle aufgelisteten Attributen, bzw. Privilegien-Parametern ist in der MySQL-Online-Dokumentation zu finden, wo erklärt wird, welche MySQL Privilegien bereitgestellt werden.

Im Hinblick auf das Anwendungsszenario müsstest du unter dem root-Account in die Standard-MySQL-Datenbank wechseln, um dir die Attributwerte der
mysql.user-Systemtabelle oder der mysql.db-Systemtabelle anzeigen zu lassen. Aus dem
eingeblendeten Output entnimmst du, dass der Nutzer Jens in der Datenbank z.B. für
sämtliche SELECT- und INSERT-Operationen Rechte besitzt. Besäße er sie nicht, wäre der
Attributwert nicht ‚Y‘ für ‚Yes‘, sondern ‚N‘ für ‚No‘.

mysql> USE MYSQL;

mysql> SELECT user,db,select_priv,insert_priv FROM db;
+---------------+---------------+-------------+-------------+
| user          | db            | select_priv | insert_priv |
+---------------+---------------+-------------+-------------+
| jens          | Blogeinträge  | Y           | Y           |
+---------------+---------------+-------------+-------------+
1 row in set (0,00 sec)

Tabellen in MySQL anlegen und Attributwerte validieren

Da der Nutzer Jens als Chefredakteur alle Rechte an der Datenbank Blogeinträge hat, können unter seinem Account Tabellen angelegt werden. Hierzu loggst du dich zunächst mit seinen Benutzerdaten ein und wechselst mittels dem USE-Command in die Datenbank Blogeinträge.

mysql -u jens -p
password: *********

mysql> USE Blogeinträge;
Database changed

Im Anschluss daran kannst du als Chefredakteur mit dem CREATE TABLE-Command Tabellen zur Datenbank hinzufügen. Die Syntax des CREATE TABLE-Command sieht vor, dass in der Klammerung, die nach CREATE TABLE folgt, die jeweiligen Attribute (Spalten) der Tabelle gelistet sind. Hinter jedem Attribut steht der Datentyp. IDs z.B. lassen sich gut als ganzzahlige Werte abbilden, was einem INTEGER entspricht. Nach der Definition des Datentyps folgt die Definition, die klar stellt, ob es sich um einen Primärschlüssel oder einen Fremdschlüssel handelt oder der Wert hinter dem Datentypen einfach nicht null sein darf. Im Anschluss daran kann ein CHECK erfolgen, muss aber nicht. Mittels dem CHECK kannst du eine Bedingung definieren, die bei jedem neuen Eintrag in die betreffende Spalte prüft, ob der Eintrag valide ist. Zur Verdeutlichung dient z.B. der CHECK in der Tabelle NUTZER für die Spalte Geschlecht. Hier ist der Datentyp als Character der Länge 1 definiert und darf nicht null sein, was bedeutet, dass für jeden Eintrag in dieser Spalte ein Wert stehen muss. Letztlich folgt der CHECK, in dem geprüft wird, ob die durch INSERT– oder UPDATE-Operation hinzugefügte Zeichenkette der Länge 1 den Wert ‘w’ oder ‘m’ hat. Bei der Festlegung des Fremdschlüssels ist hingegen REFERENCES wichtig. Erneut nehme ich Bezug auf die Tabelle NUTZER. Als letztes Attribut dieser Tabelle steht die FavoritenlisteID. Zunächst wird sie, wie alle anderen Attribute, nach der vorgestellten Syntax deklariert. Im Nachgang daran wird über REFERENCES zusätzlich zur erfolgten Deklaration die Referenz des Fremdschlüssels FavoritenlisteID hergestellt, was bedeutet, dass eindeutig gemacht wird, aus welcher Tabelle der Fremdschlüssel FavoritenlisteID kommt, also in welcher Tabelle er selber Primärschlüssel ist. Und dies wiederum ist die Tabelle FAVORITENLISTE, die vor der Tabelle NUTZER via CREATE TABLE-Command erstellt wurde. In diesem Zusammenhang solltest du generell immer die Reihenfolge berücksichtigen, in der du Tabellen anlegst. Tabellen, deren Primärschlüssel in anderen Tabellen Fremdschlüssel ist, sollten immer vor diesen zweitgenannten Tabellen angelegt werden. In der MySQL-Dokumentation kannst du weiterführende Informationen zur CREATE TABLE-Syntax nachlesen.

mysql> CREATE TABLE FAVORITENLISTE(
FavoritenlisteID INT PRIMARY KEY CHECK(FavoritenlisteID > 0));
Query OK, 0 rows affected (0,04 sec)

mysql> CREATE TABLE NUTZER(
Benutzername VARCHAR(50) PRIMARY KEY CHECK(LENGTH(Benutzername) > 0), 
EMailAdresse VARCHAR(50) NOT NULL CHECK(EMailAdresse LIKE '%@%.de' OR EMailAdresse LIKE '%@%.com'), 
Geschlecht CHAR(1) NOT NULL CHECK(Geschlecht IN ('w', 'm')), 
Geburtsdatum DATE NOT NULL, 
Passwort VARCHAR(10) NOT NULL CHECK(LENGTH(Passwort) > 0), 
FavoritenlisteID INT NOT NULL CHECK(FavoritenlisteID > 0), 
FOREIGN KEY(FavoritenlisteID) REFERENCES FAVORITENLISTE(FavoritenlisteID));
Query OK, 0 rows affected (0,06 sec)

mysql> CREATE TABLE REDAKTEUR(
Benutzername VARCHAR(50) PRIMARY KEY CHECK(LENGTH(Benutzername) > 0), 
Vorname VARCHAR(50) NOT NULL CHECK(LENGTH(Vorname) > 0), 
Name VARCHAR(50) NOT NULL CHECK(LENGTH(Name) > 0), 
Vorstellungstext TEXT CHECK(LENGTH(Vorstellungstext)  CREATE TABLE CHEFREDAKTEUR(
Benutzername VARCHAR(50) PRIMARY KEY CHECK(LENGTH(Benutzername) > 0), 
Telefonnummer VARCHAR(20) NOT NULL CHECK(LENGTH(Telefonnummer) > 0), 
FOREIGN KEY(Benutzername) REFERENCES REDAKTEUR(Benutzername));
Query OK, 0 rows affected (0,06 sec)

mysql> CREATE TABLE BLOGEINTRAG(
BlogeintragID INT PRIMARY KEY CHECK(BlogeintragID > 0), 
Erstellungsdatum DATE NOT NULL, 
Änderungsdatum DATE CHECK(Änderungsdatum >= Erstellungsdatum), 
Titel VARCHAR(250) NOT NULL CHECK(LENGTH(Titel) > 0), 
Text TEXT CHECK(LENGTH(Text)  0), 
FOREIGN KEY(Benutzername) REFERENCES REDAKTEUR(Benutzername));
Query OK, 0 rows affected (0,08 sec)

mysql> CREATE TABLE FAVORITENLISTEenthaltenBLOGEINTRAG(
BlogeintragID INT NOT NULL CHECK(BlogeintragID > 0), 
FavoritenlisteID INT NOT NULL CHECK(FavoritenlisteID > 0), 
PRIMARY KEY(BlogeintragID, FavoritenlisteID), 
FOREIGN KEY(BlogeintragID) REFERENCES BLOGEINTRAG(BlogeintragID), 
FOREIGN KEY(FavoritenlisteID) REFERENCES FAVORITENLISTE(FavoritenlisteID));
Query OK, 0 rows affected (0,05 sec)

Mittels dem SHOW TABLES-Command kannst du dir im Anschluss daran, angemeldet als Nutzer Jens, nun die in der Datenbank Blogeinträge befindlichen Tabellen ansehen. Es werden alle Tabellen angezeigt, da der Nutzer Jens als Chefredakteur alle Rechte an allen Tabellen hat.

mysql> SHOW TABLES;
+------------------------------------+
| Tables_in_Blogeinträge             |
+------------------------------------+
| BLOGEINTRAG                        |
| FAVORITENLISTEenthaltenBLOGEINTRAG |
| CHEFREDAKTEUR                      |
| FAVORITENLISTE                     |
| NUTZER                             |
| REDAKTEUR                          |
+------------------------------------+
6 rows in set (0,00 sec)

Rechte in MySQL pro Tabelle und/oder Spalte definieren

Nachdem die Datenbank Tabellen enthält, kannst du die jeweiligen Nutzerrechte pro Tabelle und /oder pro Spalte definieren. Beginnen wir mit dem Nutzer Katrin, welcher die Rechte eines Redakteurs haben soll. Wenn du dich unter dem Account von Katrin einloggst und in die Datenbank Blogeinträge wechseln willst, bekommst du die im unteren Codeblock eingeblendete Fehlermeldung, die dich darauf aufmerksam macht, dass dir die Rechte auf die Datenbank Blogeinträge fehlen.

mysql -u katrin -p
password: *********

mysql> USE Blogeinträge;
ERROR 1044 (42000): Access denied for user 'katrin'@'localhost' to database 'Blogeinträge'

Um dem Nutzer Katrin spezifische Rechte an bestimmten Tabellen und/oder Spalten der Datenbank Blogeinträge zu gewähren, müssen diese zunächst – wie die allgemeinen Rechte, die Jens als Chefredakteur hat – definiert werden. Nur sind die Rechte des Redakteurs eben eingeschränkt. Zunächst musst du dich wieder unter dem Root-Account in MySQL einloggen.

mysql -u root -p
password: *********

Aus der Einleitung zum Anwendungsszenario weißt du, dass ein Redakteur alle Rechte an der Tabelle REDAKTEUR hat. Der GRANT-Command ist dir bereits aus dem in der Einleitung dieses Tutorials erwähnten Artikel Wie erstelle ich einen MySQL Benutzer und weise ihm Rechte zu? bekannt und wird bei der Spezifizierung von Tabellenrechten ebenso genutzt, wie bei der allgemeinen Rechtevergabe, wie ich sie bereits für den Chefredakteur Jens zu Beginn des Anwendungsszenarios umgesetzt habe. Jedoch wird der GRANT-Command nun detaillierter. Um festzulegen, dass Katrin alle Rechte an der Tabelle REDAKTEUR hat, wird nicht der bisher geläufige Command im Sinne von

mysql> GRANT ALL PRIVILEGES ON Blogeinträge.* TO 'katrin'@'localhost';
Query OK, 0 rows affected (0,00 sec)

genutzt, sondern er wird genauer formuliert, nämlich als

mysql> GRANT ALL PRIVILEGES ON Blogeinträge.REDAKTEUR TO 'katrin'@'localhost';
Query OK, 0 rows affected (0,00 sec)

Hierbei wird nicht nur die Datenbank angetriggert, sondern die hinter der Datenbank stehende Tabelle REDAKTEUR. Wenn du die Rechte noch weiter spezifizieren willst, also nicht lediglich auf bestimmte Tabellen einschränken willst, sondern sogar auf Spalten, sieht der GRANT-Command wieder etwas anders aus. Dann wird nämlich für jede Art von Zugriff bzw. Operation definiert, auf welche Spalten einer bestimmten Tabelle der betreffende Nutzer diese Operation ausüben darf. Hierzu wird in der Syntax der Name der betreffenden Operation, gefolgt von dem in Klammern gesetzten Bestandteil vor dem ON TABELLE.SPALTE des Commands deklariert. Wie dies in der Kommandozeile realisiert wird, siehst du in den folgenden Shell-Snippets, in denen die weiteren Rechtevergaben für den Nutzer Katrin stattfinden, wie sie in der Einleitung zum Anwendungsszenario erläutert worden sind. Dort wird für Katrin als erstes die SELECT-Operation auf die Spalten Benutzername, EMailAdresse und FavoritenlisteID der Tabelle NUTZER gewährt.

mysql> GRANT SELECT (Benutzername, EMailAdresse, FavoritenlisteID) ON Blogeinträge.NUTZER TO 'katrin'@'localhost';
Query OK, 0 rows affected (0,00 sec)

Des Weiteren wird im nächsten GRANT-Befehl Katrin das Recht an der SELECT-Operation auf die komplette Tabelle FAVORITENLISTEenthaltenBLOGEINTRAG zugewiesen.

mysql> GRANT SELECT ON Blogeinträge.FAVORITENLISTEenthaltenBLOGEINTRAG TO 'katrin'@'localhost';
Query OK, 0 rows affected (0,00 sec)

Im Anschluss daran erfolgt eine weitere GRANT-Anweisung, laut der Katrin als Redakteur sowohl die SELECT-, als auch die UPDATE– und DELETE-Operation auf die Spalten Titel, Text und Benutzername der Tabelle BLOGEINTRAG vornehmen darf.

mysql> GRANT SELECT, INSERT, UPDATE (Titel, Text, Benutzername) ON Blogeinträge.BLOGEINTRAG TO 'katrin'@'localhost';
Query OK, 0 rows affected (0,00 sec)

In den folgenden Shell-Snippets zeige ich dir noch, wie du die Rechte im Fall des Nutzers Natascha so umsetzen würdest, wie ich es zu Beginn des Anwendungsszenarios geschildert habe. Da sich Natascha und Michael jeweils in der Rolle des Nutzers rechtlich auf derselben Ebene befinden, solltest du bei der Nachbildung dieses Leitfadens die auf den Nutzer Natascha gesetzten Rechte im Nachgang ebenso auf den Nutzer Michael festlegen, um dieselben Outputs zu erzielen, wie ich sie im folgenden Teil des Tutorials präsentieren werde.

mysql> GRANT SELECT (Benutzername, EMailAdresse, FavoritenlisteID) ON Blogeinträge.NUTZER TO 'natascha'@'localhost';
Query OK, 0 rows affected (0,00 sec)

mysql> GRANT SELECT ON Blogeinträge.REDAKTEUR TO 'natascha'@'localhost';
Query OK, 0 rows affected (0,00 sec)

mysql> GRANT SELECT ON Blogeinträge.BLOGEINTRAG TO 'natascha'@'localhost';
Query OK, 0 rows affected (0,00 sec)

mysql> GRANT ALL PRIVILEGES ON Blogeinträge.FAVORITENLISTEenthaltenBLOGEINTRAG TO 'natascha'@'localhost';
Query OK, 0 rows affected (0,00 sec)

Nachdem du die Rechte pro Nutzer individuell festgelegt hast, kannst du dir den Gesamtkontext der Rechtevergabe, so wie er durch dich implementiert wurde, via SELECT-Statement ausgeben lassen. Hierzu musst du aber zunächst die Datenbank wechseln, da du dich noch in der Datenbank Blogeinträge befindest. Also wechselst du mittels dem USE-Command in die MySQL-Datenbank.

mysql> USE mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

Über die Ausführung des folgenden Kommandos kannst du dir die Attributwerte der tables_priv-Systemtabelle ausgeben lassen. Hieraus lassen sich die Tabellenrechte in Relation zu den Spaltenrechten ablesen. Angewandt auf die erste Zeile würdest du daraus verstehen, dass dem Redakteur Katrin auf Tabellenebene für die Tabelle BLOGEINTRAG sowohl die SELECT– als auch die INSERT-Operation erlaubt sind. Im Hinblick auf die Spaltenebene ist hingegen lediglich die UPDATE-Operation zulässig.

mysql> SELECT * from tables_priv;
+-----------+---------------+---------------+------------------------------------+----------------------+---------------------+----------------------------------------------------------------------------------------------+-------------+
| Host      | Db            | User          | Table_name                         | Grantor              | Timestamp           | Table_priv                                                                                   | Column_priv |
+-----------+---------------+---------------+------------------------------------+----------------------+---------------------+----------------------------------------------------------------------------------------------+-------------+
| localhost | Blogeinträge  | katrin        | BLOGEINTRAG                        | root@localhost       | 0000-00-00 00:00:00 | Select,Insert                                                                                | Update      |
| localhost | Blogeinträge  | katrin        | FAVORITENLISTEenthaltenBLOGEINTRAG | root@localhost       | 0000-00-00 00:00:00 | Select                                                                                       |             |
| localhost | Blogeinträge  | michael       | NUTZER                             | root@localhost       | 0000-00-00 00:00:00 |                                                                                              | Select      |
| localhost | Blogeinträge  | natascha      | FAVORITENLISTEenthaltenBLOGEINTRAG | root@localhost       | 0000-00-00 00:00:00 | Select                                                                                       |             |
| localhost | Blogeinträge  | natascha      | BLOGEINTRAG                        | root@localhost       | 0000-00-00 00:00:00 | Select                                                                                       |             |
| localhost | Blogeinträge  | katrin        | NUTZER                             | root@localhost       | 0000-00-00 00:00:00 |                                                                                              | Select      |
| localhost | Blogeinträge  | katrin        | REDAKTEUR                          | root@localhost       | 0000-00-00 00:00:00 | Select,Insert,Update,Delete,Create,Drop,References,Index,Alter,Create View,Show view,Trigger |             |
| localhost | Blogeinträge  | natascha      | REDAKTEUR                          | root@localhost       | 0000-00-00 00:00:00 | Select                                                                                       |             |
| localhost | Blogeinträge  | natascha      | NUTZER                             | root@localhost       | 0000-00-00 00:00:00 |                                                                                              | Select      |
| localhost | Blogeinträge  | michael       | FAVORITENLISTEenthaltenBLOGEINTRAG | root@localhost       | 0000-00-00 00:00:00 | Select                                                                                       |             |
| localhost | Blogeinträge  | michael       | BLOGEINTRAG                        | root@localhost       | 0000-00-00 00:00:00 | Select                                                                                       |             |
| localhost | Blogeinträge  | michael       | REDAKTEUR                          | root@localhost       | 0000-00-00 00:00:00 | Select                                                                                       |             |
+-----------+---------------+---------------+------------------------------------+----------------------+---------------------+----------------------------------------------------------------------------------------------+-------------+
12 rows in set (0,01 sec)

Und über die Ausführung des darauffolgenden Kommandos kannst du dir die Attributwerte der columns_priv-Systemtabelle ausgeben lassen. Hierbei erkennst du dann weiter verfeinert, dass Katrin nach der Implementierung der Rechte in der Tabelle BLOGEINTRAG lediglich auf die Spalten Benutzername, Text und Titel die UPDATE-Operation ausführen darf, was letztlich das wiedergibt, was vorgesehen ist: nämlich dass die UPDATE-Operation für die restlichen Spalten der Tabelle (nämlich für die Spalten BlogeintragID, Erstellungsdatum und Änderungsdatum) für Katrin nicht erlaubt sein soll, sondern lediglich die SELECT– und die INSERT-Operation.

mysql> SELECT * from columns_priv;
+-----------+---------------+----------+-------------+------------------+---------------------+-------------+
| Host      | Db            | User     | Table_name  | Column_name      | Timestamp           | Column_priv |
+-----------+---------------+----------+-------------+------------------+---------------------+-------------+
| localhost | Blogeinträge  | katrin   | BLOGEINTRAG | Benutzername     | 0000-00-00 00:00:00 | Update      |
| localhost | Blogeinträge  | katrin   | BLOGEINTRAG | Text             | 0000-00-00 00:00:00 | Update      |
| localhost | Blogeinträge  | katrin   | BLOGEINTRAG | Titel            | 0000-00-00 00:00:00 | Update      |
| localhost | Blogeinträge  | katrin   | NUTZER      | FavoritenlisteID | 0000-00-00 00:00:00 | Select      |
| localhost | Blogeinträge  | katrin   | NUTZER      | EMailAdresse     | 0000-00-00 00:00:00 | Select      |
| localhost | Blogeinträge  | katrin   | NUTZER      | Benutzername     | 0000-00-00 00:00:00 | Select      |
| localhost | Blogeinträge  | natascha | NUTZER      | FavoritenlisteID | 0000-00-00 00:00:00 | Select      |
| localhost | Blogeinträge  | natascha | NUTZER      | EMailAdresse     | 0000-00-00 00:00:00 | Select      |
| localhost | Blogeinträge  | natascha | NUTZER      | Benutzername     | 0000-00-00 00:00:00 | Select      |
| localhost | Blogeinträge  | michael  | NUTZER      | FavoritenlisteID | 0000-00-00 00:00:00 | Select      |
| localhost | Blogeinträge  | michael  | NUTZER      | EMailAdresse     | 0000-00-00 00:00:00 | Select      |
| localhost | Blogeinträge  | michael  | NUTZER      | Benutzername     | 0000-00-00 00:00:00 | Select      |
+-----------+---------------+----------+-------------+------------------+---------------------+-------------+
12 rows in set (0,00 sec)

In den folgenden drei Shell-Snippets zeige ich dir, wie du dir ausgehend vom jeweiligen Benutzer-Account anzeigen lassen kannst, auf welche Tabellen der betreffende Nutzer Zugriff hat. Hierzu wird wieder der SHOW TABLES-Command genutzt, den ich schon unter dem Account von Jens verwendet habe, um mir seinen Zugriff auf alle Tabellen ausgeben zu lassen.

mysql -u katrin -p
password: *********

mysql> USE Blogeinträge;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> SHOW TABLES;
+------------------------------------+
| Tables_in_Blogeinträge             |
+------------------------------------+
| BLOGEINTRAG                        |
| FAVORITENLISTEenthaltenBLOGEINTRAG |
| NUTZER                             |
| REDAKTEUR                          |
+------------------------------------+
4 rows in set (0,00 sec)

mysql -u natascha -p
password: *********

mysql> USE Blogeinträge;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> SHOW TABLES;
+------------------------------------+
| Tables_in_Blogeinträge             |
+------------------------------------+
| BLOGEINTRAG                        |
| FAVORITENLISTEenthaltenBLOGEINTRAG |
| FAVORITENLISTE                     |
| NUTZER                             |
| REDAKTEUR                          |
+------------------------------------+
5 rows in set (0,00 sec)

mysql -u michael -p
password: *********

mysql> USE Blogeinträge;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> SHOW TABLES;
+------------------------------------+
| Tables_in_Blogeinträge             |
+------------------------------------+
| BLOGEINTRAG                        |
| FAVORITENLISTEenthaltenBLOGEINTRAG |
| FAVORITENLISTE                     |
| NUTZER                             |
| REDAKTEUR                          |
+------------------------------------+
5 rows in set (0,00 sec)

Tabellen nach Inhalten durchsuchen, mit Inhalten befüllen, updaten und löschen

Als nächstes werde ich dir zeigen, wie du die bisher implementierten Tabellen mit Inhalten befüllst oder bereits bestehende Inhalte updatest oder löscht. Wie du weißt, kann lediglich der Chefredakteur Jens Inhalte in allen Tabellen via INSERT-Operation einfügen und lediglich der Redakteur Katrin kann in der Tabelle BLOGEINTRAG die INSERT-Operation durchführen. Zunächst wechselst du in den Account des Chefredakteurs Jens.

mysql -u jens -p
password: *********

mysql> USE Blogeinträge;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

Dann befüllst du die Tabellen FAVORITENLISTE und NUTZER mit Inhalten. Die hierzu notwendige INSERT-Operation hat die Syntax, in der der Command INSERT INTO gefolgt wird von dem Datenbanknamen getrennt durch einen Punkt vom Tabellennamen. Hier kann allerdings auch lediglich der Tabellenname stehen. Also anstatt INSERT INTO Blogeinträge.FAVORITENLISTE könnte im ersten INSERT-Beispiel unten auch einfach nur INSERT INTO FAVORITENLISTE stehen, da du dich bereits in der Datenbank Blogeinträge befindest. Darauf folgt im Command VALUES, wo die zu befüllenden Attributwerte in Klammerung gelistet eingetragen werden. Hierbei ist es wichtig, auf die Reihenfolge der einzutragenden Attributwerte zu achten: diese muss der Reihenfolge entsprechen, in der die Spalten in einer Tabelle aufeinander folgen.

mysql> INSERT INTO Blogeinträge.FAVORITENLISTE VALUES(1001);
Query OK, 1 row affected (0,03 sec)

mysql> INSERT INTO Blogeinträge.FAVORITENLISTE VALUES(1002);
Query OK, 1 row affected (0,03 sec)

mysql> INSERT INTO Blogeinträge.FAVORITENLISTE VALUES(1003);
Query OK, 1 row affected (0,00 sec)

mysql> INSERT INTO Blogeinträge.FAVORITENLISTE VALUES(1004);
Query OK, 1 row affected (0,01 sec)

mysql> INSERT INTO Blogeinträge.NUTZER VALUES('jens', '[email protected]', 'm', '1984-05-30', 'ferrari', 1001);
Query OK, 1 row affected (0,04 sec)

mysql> INSERT INTO Blogeinträge.NUTZER VALUES('katrin', '[email protected]', 'w', '1990-01-16', 'darkeyes', 1002);
Query OK, 1 row affected (0,03 sec)

mysql> INSERT INTO Blogeinträge.NUTZER VALUES('natascha', '[email protected]', 'w', '1987-08-22', 'starwish', 1003);
Query OK, 1 row affected (0,01 sec)

mysql> INSERT INTO Blogeinträge.NUTZER VALUES('michael', '[email protected]', 'm', '1989-03-14', 'infotech', 1004);
Query OK, 1 row affected (0,00 sec)

Um dir zu zeigen, dass Katrin fähig ist, die Tabelle BLOGEINTRAG mit Inhalten zu füllen, werde ich die hierzu notwendige Operation von ihrem Account aus ausführen, jedoch wäre Jens als Chefredakteur hierzu natürlich auch in der Lage.

mysql -u katrin -p
password: *********

mysql> USE Blogeinträge;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> INSERT INTO Blogeinträge.REDAKTEUR VALUES('katrin', 'Katrin', 'Loos', 'Hi, mein Name ist Katrin und ich bin seit ...');
Query OK, 1 row affected (0,01 sec)

mysql> INSERT INTO Blogeinträge.REDAKTEUR VALUES('jens', 'Jens', 'Müller', 'Hey, ich freue mich auf dieser Plattform als ...');
Query OK, 1 row affected (0,03 sec)

Für die Befüllung mit Inhalten der Tabellen CHEFREDAKTEUR, BLOGEINTRAG und FAVORITENLISTEenthaltenBLOGEINTRAG wechsele ich wieder in den Account von Jens, da Katrin – wie bereits bekannt – für das Hinzufügen von Inhalten in Tabellen bis auf die Tabelle BLOGEINTRAG die Rechte fehlen.

mysql -u jens -p
password: *********

mysql> USE Blogeinträge;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> INSERT INTO Blogeinträge.CHEFREDAKTEUR VALUES('jens', '+49 178 3339990');
Query OK, 1 row affected (0,01 sec)

mysql> INSERT INTO Blogeinträge.BLOGEINTRAG VALUES(2001, '2018-02-20', NULL, 'Test-Headline', 'In diesem Blogeintrag möchte ich Euch zeigen ...', 'katrin');
Query OK, 1 row affected (0,01 sec)

mysql> INSERT INTO Blogeinträge.BLOGEINTRAG VALUES(2002, '2018-02-20', '2018-02-24', 'Test-Headline', 'Heute werde ich über ...', 'jens');
Query OK, 1 row affected (0,03 sec)

mysql> INSERT INTO Blogeinträge.FAVORITENLISTEenthaltenBLOGEINTRAG VALUES(2001, 1003);
Query OK, 1 row affected (0,03 sec)

mysql> INSERT INTO Blogeinträge.FAVORITENLISTEenthaltenBLOGEINTRAG VALUES(2001, 1004);
Query OK, 1 row affected (0,01 sec)

mysql> INSERT INTO Blogeinträge.FAVORITENLISTEenthaltenBLOGEINTRAG VALUES(2002, 1002);
Query OK, 1 row affected (0,03 sec)

Nachdem du nun alle Tabellen mit Inhalten befüllt hast, sehen sie so aus, wie ich dir zu Beginn des Anwendungsszenarios gezeigt habe. Zum Ende dieses Tutorials möchte ich dir nun noch jeweils ein Beispiel für eine SELECT-, sowie eine UPDATE– und eine DELETE-Operation zeigen. Das Beispiel für die SELECT-Operation zeige ich dir ausgehend von den Benutzerrechten, die Natascha als Nutzer hat und die ebenso auf ein Beispiel von Michaels Account aus anwendbar wären. Das jeweilige Beispiel für die UPDATE– und DELETE-Operation zeige ich dir ausgehend von den Rechten, die Katrin als Redakteur besitzt. Zunächst loggst du dich unter Nataschas Account in MySQL ein und wechselst mit dem USE-Command in die Datenbank Blogeinträge.

mysql -u natascha -p
password: *********

mysql> USE Blogeinträge;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

Wenn du nun versuchst, die Einträge zum Benutzernamen und dem dazugehörigen Geburtsdatum aus der Tabelle NUTZER zu selektieren, wird dir eine Fehlermeldung ausgegeben, in der du darauf hingewiesen wirst, dass die Spalte Geburtsdatum nicht erkannt wird, wie du im unteren Beispiel erkennst.

SELECT Benutzername, Geburtsdatum FROM REDAKTEUR;
ERROR 1054 (42S22): Unknown column 'Geburtsdatum' in 'field list'

Selektierst du hingegen die Spalteninhalte zu den Spalten Benutzername, EMailAdresse und FavoritenlisteID aus der Tabelle NUTZER, erhältst du entsprechenden Output, wie er im Beispiel darunter zu sehen ist.

SELECT Benutzername, EMailAdresse, FavoritenlisteID FROM NUTZER;
+--------------+---------------------------------+------------------+
| Benutzername | EMailAdresse                    | FavoritenlisteID |
+--------------+---------------------------------+------------------+
| jens         | [email protected]      |             1001 |
| katrin       | [email protected]      |             1002 |
| michael      | [email protected] |             1004 |
| natascha     | [email protected]               |             1003 |
+--------------+---------------------------------+------------------+
4 rows in set (0,00 sec)

Möchtest du dir als Nutzer Natascha lediglich Informationen anzeigen lassen, die dich selber betreffen, dann schränke das SELECT-Statement mit der WHERE-Bedingung ein, indem du z.B. die Bedingung anhand des Benutzernamens setzen kannst und somit die Treffermenge einschränkst, wie ich es dir im Beispiel unten verdeutliche.

SELECT Benutzername, EMailAdresse, FavoritenlisteID FROM NUTZER;
+--------------+---------------------------------+------------------+
| Benutzername | EMailAdresse                    | FavoritenlisteID |
+--------------+---------------------------------+------------------+
| natascha     | [email protected]               |             1003 |
+--------------+---------------------------------+------------------+
4 rows in set (0,00 sec)

Um nun das jeweilige Beispiel zur UPDATE– und DELETE-Operation unter Katrins Rechten nachzuvollziehen, loggst du dich unter ihrem Account ein und wechselst wieder in die Datenbank Blogeinträge.

mysql -u katrin -p
password: *********

mysql> USE Blogeinträge;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

Die Syntax der UPDATE-Operation setzt voraus, dass nach dem UPDATE der Name der Tabelle gesetzt werden muss oder der Name der Tabelle gefolgt von einem Punkt und der betreffenden Spalte. Dann wird mittels SET der neue Wert gesetzt und die bekannte WHERE-Bedingung dient auch bei dieser Operation zur Einschränkung der Treffermenge. Sollen mehr als ein Wert neu gesetzt werden, kann dies über die booleschen Operatoren AND oder OR erfolgen. Wenn du hier nun versuchst das Änderungsdatum zu ändern, wird dir die Fehlermeldung ausgegeben, die dich darüber in Kenntnis setzt, dass du keinen Zugriff auf das Ändern von Werten in der Spalte Änderungsdatum besitzt, wie du dem Beispiel unten entnimmst.

UPDATE BLOGEINTRAG SET Änderungsdatum = '2018-04-01' WHERE Titel = 'Test-Headline' AND Benutzername = 'katrin';
ERROR 1143 (42000): UPDATE command denied to user 'katrin'@'localhost' for column 'Änderungsdatum' in table 'BLOGEINTRAG'

Wenn du hingegen den Titel eines bestimmten Blogeintrags änderst, wie im Beispiel unten, wird der UPDATE-Command durchgeführt, weil der Redakteur eben UPDATE-Rechte an der Spalte Titel der Tabelle BLOGEINTRAG hat. Hättest du im Beispiel unten ohne WHERE-Bedingung gearbeitet, würde der Titel in allen Zeilen der Tabelle in ‘Hallo Welt!’ geändert werden.

UPDATE BLOGEINTRAG Titel SET Titel = 'Hallo Welt!' WHERE Titel = 'Test-Headline' AND Benutzername = 'katrin';
Query OK, 1 row affected (0,03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Das Ergebnis deiner UPDATE-Operation kannst du dir im Nachgang via SELECT-Operation ausgeben lassen, wie ich es dir z.B. unten zeige.

SELECT Titel FROM BLOGEINTRAG WHERE Benutzername = 'katrin';
+-------------+
| Titel       |
+-------------+
| Hallo Welt! |
+-------------+
1 row in set (0,01 sec)

Im letzten Beispiel, in dem ich mit dir eine DELETE-Operation durchführe, gehen wir davon aus, dass in der Tabelle REDAKTEUR zwischenzeitlich ein neuer Redakteur hinzugefügt wurde, nämlich der Redakteur mit dem Benutzernamen Sabine, wie in der Tabelle unten aufgeführt ist. Dieser neu hinzugefügte Redakteur soll nun aber wieder aus der Tabelle REDAKTEUR entfernt werden, weil Frau Stein nunmehr doch nicht mehr in dem betreffenden Blogsystem als Redakteur tätig sein möchte. Frau Stein könnte als Redakteur die Löschung auch selber durchführen, aber in meinem Beispiel lasse ich dies Katrin tun.

Die Syntax der DELETE-Operation sieht vor, dass nach dem DELETE FROM die betreffende Tabelle angetriggert wird und mit der WHERE-Bedingung hier – anders als wie im Fall der SELECT– oder UPDATE-Operation – die WHERE-Bedingung essentiell ist und nicht lediglich zur Wahl steht, um die Treffermenge einzuschränken.

mysql> DELETE FROM REDAKTEUR WHERE Benutzername = 'sabine';
Query OK, 1 row affected (0,03 sec)

Auch in diesem Beispiel lassen wir uns zur Prüfung das Ergebnis der DELETE-Operation ausgeben, indem wir uns alle Benutzernamen anzeigen lassen, die sich nun noch in der Tabelle REDAKTEUR befinden, wie du im Beispiel darunter siehst.

mysql> SELECT Benutzername FROM REDAKTEUR;
+--------------+
| Benutzername |
+--------------+
| jens         |
| katrin       |
+--------------+
2 rows in set (0,00 sec)

Wie dir aus der Beschreibung des Anwendungsszenarios bekannt ist, ist jeder Redakteur zwangsläufig auch ein Nutzer. Wenn Frau Stein also komplett aus der Datenbank entfernt werden soll, dann müsste final eine DELETE-Operation in der Tabelle NUTZER hierzu stattfinden, was du in der Rolle, bzw. unter dem Account des Chefredakteurs Jens umsetzen kannst, um die Tabelle NUTZER in dem Zustand zu haben, wie im Bild zu Beginn des Anwendungsszenarios gezeigt.

Fazit

Nachdem wir nun zusammen eine Datenbank erstellt, aufgebaut und Rechte pro Nutzer individuell zugeteilt haben, hast du ein Gefühl dafür bekommen, wie flexibel und steuerbar die Vergabe von Rechten in MySQL ist und bist in der Lage, die Rechte pro Nutzer in bestimmten Datenbanken fein abzustimmen und somit einen gewissen Grad an Datenkontrolle zu gewährleisten. Mir hat es Freude gemacht, dich durch das Anwendungsszenario zu führen und ich wünsche dir viel Spaß bei der Umsetzung der gelernten Inhalte.

Zurück zur Tutorial Übersicht Back to Tutorial Overview