Rollenspiele mit MySQL

Mit der Version 8.0 wurde endlich das Konzept von Rollen im Usermanagement bei Oracle MySQL implementiert. Andere Distributoren (z.B. MariaDB) haben dieses nützliche Feature bereits vor längerer Zeit implementiert.

Die Spielvorbereitung

Rollen dienen im Allgemeinen dazu, eine bestimmte Anzahl von Berechtigungen zusammenzufassen. Ein Rolle kann beispielsweise die notwendigen Berechtigungen eines Applikations-Users beinhalten oder die eines Administrators (z.B. eines DBAs). Bis zur Oracle-MySQL-Version 5.7 konnten Berechtigungen nur dediziert Usern zugewiesen werden. Wollte man einen weiteren gleichberechtigten User anlegen, so hat man, zum Beispiel über ein Skript, die Rechte des Users A ausgelesen und einen User B mit den identischen Berechtigungen ausgestattet. Natürlich gab es aber keine Sicherheit, dass die User auch in Zukunft über identische Berechtigungen verfügten, da ja jederzeit jeder Account weiter manipuliert werden konnte.

mysql> show grants for 'app'@'localhost';
+------------------------------------------------------------------------------------------------------------+
| Grants for app@localhost                                                                                   |
+------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'app'@'localhost' IDENTIFIED BY PASSWORD '*5BCB3E6AC345B435C7C2E6B7949A04CE6F6563D3' |
| GRANT SELECT ON `sakila`.`actor` TO 'app'@'localhost'                                                      |
| GRANT SELECT, UPDATE ON `sakila`.`customer` TO 'app'@'localhost'                                           |
| GRANT SELECT ON `sakila`.`film` TO 'app'@'localhost'                                                       |
+------------------------------------------------------------------------------------------------------------+
4 rows in set (0,03 sec) 

Mit ein bisschen "sed"-Magie lässt sich so zum Beispiel der User "app" als User "web" kopieren.

mysql> \T web_user.sql
Logging to file 'web_user.sql'

mysql> pager sed -e "s/'app'/'web'/g"
PAGER set to 'sed -e "s/'app'/'web'/g"'

mysql> show grants for 'app'@'localhost';
+------------------------------------------------------------------------------------------------------------+
| Grants for app@localhost                                                                                   |
+------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'web'@'localhost' IDENTIFIED BY PASSWORD '*5BCB3E6AC345B435C7C2E6B7949A04CE6F6563D3' |
...                                  |
mysql> \t
Outfile disabled. 

Spielaufbau

Anstatt Rechte an User zu "granten", kann ab MySQL 8.0 das Objekt einer Rolle erzeugt und alternativ genutzt werden, um die Berechtigungen entgegenzunehmen. Dazu können eine oder mehrere Rollen erzeugt werden. In diesem Beispiel möchten wir anstatt des Users "app" zwei Rollen "app_ro" (lesender Zugriff) und "app_rw" (schreibender Zugriff) erzeugen. Diese Rollen erhalten die entsprechenden Rechte.

mysql> create role app_ro, app_rw;
Query OK, 0 rows affected (0.0167 sec)

mysql> grant select on sakila.* to 'app_ro';
Query OK, 0 rows affected (0.0166 sec)
mysql> grant insert, update, delete on sakila.* to 'app_rw';
Query OK, 0 rows affected (0.0151 sec) 

Spielfigur wählen

Diese vorhandenen Rollen können nun klassischen User-Accounts zugewiesen werden. Dies erfolgt über die altbekannten GRANT-Anweisungen.

mysql> create user 'application'@'localhost' identified by 'geheim';
Query OK, 0 rows affected (0.0172 sec)
mysql> grant app_ro, app_rw to 'application'@'localhost';
Query OK, 0 rows affected (0.0160 sec) 

Was passiert nun, wenn sich der User 'application' an der Datenbank authentifiziert?

MySQL  localhost:3307 ssl  sakila  SQL > \c application:geheim@localhost:3307
Creating a session to 'application@localhost:3307'
Your MySQL connection id is 21
...
MySQL  localhost:3307 ssl  SQL > select * from sakila.actor;
ERROR: 1142 (42000): SELECT command denied to user 'application'@'localhost' for table 'actor' 

Ok, hier scheint etwas nicht zu stimmen. Eine Rolle, sofern sie nicht bei der Vergabe als "default" gesetzt wird, muss vom User aktiv "genommen" werden. Der User hat damit in diesem Fall die Kontrolle, mit welcher Rolle er arbeiten möchte oder ob er sogar zeitgleich mehrere Rollen nutzen möchte.

MySQL  localhost:3307 ssl  SQL > 
select current_user(), current_role();
+-----------------------+----------------+
| current_user()        | current_role() |
+-----------------------+----------------+
| application@localhost | NONE           |
+-----------------------+----------------+

MySQL  localhost:3307 ssl  SQL > set role app_ro;

MySQL  localhost:3307 ssl  SQL > select current_user(), current_role();
+-----------------------+----------------+
| current_user()        | current_role() |
+-----------------------+----------------+
| application@localhost | `app_ro`@`%`   |
+-----------------------+----------------+

MySQL  localhost:3307 ssl  SQL > set role app_ro, app_rw;

MySQL  localhost:3307 ssl  SQL > select current_user(), current_role();
+-----------------------+---------------------------+
| current_user()        | current_role()            |
+-----------------------+---------------------------+
| application@localhost | `app_ro`@`%`,`app_rw`@`%` |
+-----------------------+---------------------------+
1 row in set (0.0018 sec)

MySQL  localhost:3307 ssl  SQL > select count(*) from sakila.film;
+----------+
| count(*) |
+----------+
|        0 |
+----------+ 

• SET ALL
• SET NONE
• SET ALL [EXECEPT A, B, C, ...]

Spielregeln verstehen und durchsetzen

Wie oben bereits erwähnt, lassen sich aber auch gewisse Spielregeln erzwingen. So kann man als Administrator Usern per "default" eine Rolle auferlegen. 

MySQL  localhost:3307 ssl  SQL > set default role 'app_ro' to 'application'@'localhost';
Query OK, 0 rows affected (0.0220 sec) 

Wenn ein User wissen möchte, welche Rollen und welche Rechte für ihn bereitstehen, hat er mehrere Möglichkeiten:

MySQL  localhost:3307 ssl  SQL > \c application:geheim@localhost:3307
Creating a session to 'application@localhost:3307'
...
 MySQL  localhost:3307 ssl  SQL > show grants;
+--------------------------------------------------------------+
| Grants for application@localhost                             |
+--------------------------------------------------------------+
| GRANT USAGE ON *.* TO `application`@`localhost`              |
| GRANT SELECT ON `sakila`.* TO `application`@`localhost`      |
| GRANT `app_ro`@`%`,`app_rw`@`%` TO `application`@`localhost` |
+--------------------------------------------------------------+
3 rows in set (0.0526 sec) 

Der User darf über seine "default"-Rolle "app_ro" auf die "sakila"-Datenbank zugreifen. Verwirft er sein Rollenrecht, so sehen seine Rechte gleich anders aus.

MySQL  localhost:3307 ssl  SQL > set role none;
Query OK, 0 rows affected (0.0003 sec)

MySQL  localhost:3307 ssl  SQL > show grants;
+--------------------------------------------------------------+
| Grants for application@localhost                             |
+--------------------------------------------------------------+
| GRANT USAGE ON *.* TO `application`@`localhost`              |
| GRANT `app_ro`@`%`,`app_rw`@`%` TO `application`@`localhost` |
+--------------------------------------------------------------+
2 rows in set (0.0006 sec) 

Ein User hat aber auch die Möglichkeit, sich die Berechtigungen einer Rolle anzusehen, ohne diese vorher aktivieren zu müssen:

MySQL  localhost:3307 ssl  SQL > show grants for 'application'@'localhost' using 'app_rw';
+-------------------------------------------------------------------------+
| Grants for application@localhost                                        |
+-------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `application`@`localhost`                         |
| GRANT INSERT, UPDATE, DELETE ON `sakila`.* TO `application`@`localhost` |
| GRANT `app_ro`@`%`,`app_rw`@`%` TO `application`@`localhost`            |
+-------------------------------------------------------------------------+
3 rows in set (0.0046 sec) 

Spielschluss

Ein Rollenkonzept war überfällig. Es liegt die Vermutung nahe, dass dieses Feature nicht ganz freiwillig in MySQL implementiert wurde. Lange Zeit wurde seitens der Community danach verlangt. Oftmals war als Reaktion darauf zu hören, dass ein solches Feature für MySQL nicht notwendig sei, da MySQL oftmals in einem Umfeld (gemeint waren wahrscheinlich einfache Webapplikationen) genutzt werde, in dem nicht viele User-Accounts zum Tragen kommen (eine Applikation = ein User ?!).
Nachdem aber MariaDB dieses Konzept umgesetzt hatte, ließ sich Oracle nicht mehr lange bitten.
Was auch immer letztendlich der Grund gewesen sein mag, es erhöht den Spielspaß im MySQL-Umfeld für den Administrator aber auch für den Entwickler erheblich.

Sie haben Fragen rund um das Thema MySQL? Sprechen Sie mit uns.