5 Minuten Lesezeit (1048 Worte)

Hallo? Ist da noch jemand? MySQL User Tracking

Dienstag, 30. Mai 2023

In vielen Fällen sammeln sich Datenbank-Accounts über viele Jahre hinweg an. Gerade wenn keine IAM- (Identity Access Management) oder „Singe-Sign-On“-Lösung genutzt wird, ergibt sich die Situation, dass Accounts „liegen bleiben“, obwohl der real existierende Anwender gar nicht mehr in der Firma ist. Leider gibt es keine interne Systemsicht (Tabelle/View), welche diese Frage direkt beantwortet. Wie findet man also solche „Leichen“ in seiner MySQL-Datenbank.

Viele Wege führen nach Rom …

Oftmals möchte man den Bestand an User-Accounts verifizieren und bereinigen. Gerade vor Upgrades, Migrationen, bei Security-Audits oder neuen Applikationsreleases stellt man sich oft die Frage:

Werden all diese Accounts eigentlich noch benötigt?

In der Praxis gibt es mehrere Lösungen. Eine Lösung wäre die Aktivierung des „general-log“-Parameters ([1]) für einen bestimmten Zeitraum. Dieser sorgt dafür, dass alle Kommandos, auch ein Verbindungsaufbau (connect), protokolliert werden. Nach dem definierten Zeitraum könnten dann alle User, die nicht über das Logfile identifiziert werden können, zunächst gesperrt („gelocked“) und nach einem weiteren Zeitraum ggf. entfernt („gedropped“) werden.

Der „Preis“ dafür ist jedoch hoch. Es wird eben alles (!) protokolliert. Dies bedeutet, jedes (!) SQL-Kommando wird dokumentiert. Im Zweifel bremst dies die Performance des Servers und kostet viel Platz im Filesystem.

Eine andere Möglichkeit wäre das Setzen eines Passwort-Intervalls bzw. eines Ablaufdatums für Passwörter. User, die aktiv sind, werden sich früher oder später gezwungenermaßen ein neues Passwort setzen. Inaktive User werden auf diesem Wege nach Ablauf des Zeitfensters gesperrt und wiederum nach einer weiteren Periode dann ggf. entfernt. Auch hier gibt es einen gewissen Preis. Ein Applikationsuser, der nicht interaktiv mit Client arbeitet, läuft ggf. in die Situation, dass das Passwort abläuft, ohne dass ein neues Passwort gesetzt wird. Der Account wäre damit dann zunächst einmal gesperrt und müsste folgerichtig erst einmal wieder entsperrt werden. In der Zwischenzeit wäre die Außenwirkung auf die Kunden und/oder Nutzer der Applikation sicherlich negativ.

Trigger das mal …

DBAs anderer Datenbankprodukte würden an dieser Stelle sicher mit einem Login-Trigger argumentieren. Ein Login-Trigger löst Aktivitäten (z. B. SQL-Code) in dem Moment aus, zu dem sich ein User anmeldet. MySQL kennt zwar Trigger und auslösende Events, ein Login zählt aber leider nicht dazu. Jedoch gibt es einen DB-Parameter, der etwas Ähnliches leisten kann:

init_connect [1]

„init_connect" führt ebenfalls Code für alle Nicht-DBA-User beim Aufbau einer Verbindung aus. Mit dieser Einschränkung (alle Nicht DBAs) kann man aber sicher leben. Wer auf einer Datenbank als DBA arbeitet, sollte im Unternehmen in jedem Fall bekannt sein.

Was jetzt noch fehlt, sind ein paar Datenstrukturen (Tabelle) und etwas SQL, bzw. prozeduraler Code, der von jedem User beim Login ausgeführt werden soll.

Bastelstunde …

Im ersten Schritt kümmern wir uns um die Datenstrukturen, mit denen wir festhalten wollen, welcher Account noch benutzt wird:

drop database if exists my_tools;
create database if not exists my_tools;

CREATE TABLE my_tools.user_track (
    user VARCHAR(255),
    last_login TIMESTAMP,
    logins INT,
    PRIMARY KEY (user)
);

Dazu legen wir uns eine Datenbank mit einer Protokolltabelle (siehe oben) an. Zusätzlich brauchen wir eine Prozedur, welche diese Tabelle befüllt:

DELIMITER //
    
CREATE PROCEDURE my_tools.p_track_user()
SQL SECURITY DEFINER
BEGIN
INSERT INTO my_tools.user_track VALUES (user(), now(), 1)
ON DUPLICATE KEY UPDATE last_login = NOW(), logins = logins + 1;
END;

//
DELIMITER ;

Die Prozedur legt entweder für einen User einen neuen Datensatz an (INSERT-Teil) oder inkrementiert für einen bereits bekannten Account eine Zählvariable und setzt ein neues Datum (ON DUPLICATE KEY-Teil). Darüber hinaus brauchen wir ein Berechtigungskonzept, welches es jedem User überhaupt erlaubt, die Tabelle zu beschreiben und die Prozedur aufzurufen. Beide wurden in unserem Fall als User „root“ (also der Admin des MySQL-Servers) erzeugt:

create role user_track;
grant insert, update on my_tools.user_track to 'user_track';
grant execute on my_tools.* to 'user_track';

Wir nutzen für diesen Zweck eine Rolle, welcher die entsprechenden Rechte zugewiesen werden.

Ein wenig unschön ist die Tatsache, dass wir nun jedem (auch allen zukünftigen Usern) die Rolle zuweisen müssen, damit die Protokollierung funktionieren kann. Eine allgemeine Zuweisung an jeden User (z. B. „GRANT TO PUBLIC“; wie dies bei Oracle Datenbanken möglich wäre), funktioniert bei MySQL leider nicht. Für diesen Zweck schreiben wir uns ein schnelles SQL-Skript:

mysql> SELECT
    ->     CONCAT('grant \'user_track\' to \'',
    ->             user,
    ->             '\'@\'',
    ->             host,
    ->             '\';',
    ->             'set default role \'user_track\' to \'',
    ->             user,
    ->             '\'@\'',
    ->             host,
    ->             '\';')
    -> FROM
    ->     mysql.user
    -> WHERE
    ->     user NOT LIKE 'mysql.%'
    ->         AND account_locked != 'Y'
    ->         AND super_priv != 'Y';

Dieses Skript erzeugt den SQL-Code, um den einzelnen Accounts die notwendigen Rechte zu geben:

grant 'user_track' to 'anybody'@'%';set default role 'user_track' to 'anybody'@'%';
grant 'user_track' to 'nobody'@'%';set default role 'user_track' to 'nobody'@'%';

Nachdem wir die Rechte den Datenbank-Accounts zugewiesen haben, aktivieren wir den Parameter „init_connect“, der dann für jede neue (!) Verbindung eines Anwenders die Protokollierung startet.

set global init_connect="call my_tools.p_track_user();";

Und das Ergebnis ist.

In unserer Laborumgebung haben wir mit zwei, drei Usern schnell ein paar Logins „produziert“. Das Ergebnis ist wie erwartet. In unserem Fall wird der letzte Login mit Datum und Uhrzeit und die Gesamtheit der Logins eines Users dokumentiert. Sicherlich sind hier komplexere Szenarien/Auswertungen/mehr Informationen denkbar.

mysql> select * from user_track;
+-------------------+---------------------+--------+
| user              | last_login          | logins |
+-------------------+---------------------+--------+
| anybody@localhost | 2023-05-04 11:54:56 |      9 |
| nobody@localhost  | 2023-05-04 11:22:37 |      2 |
| root@192.168.32.2 | 2023-05-04 11:22:12 |      2 |
+-------------------+---------------------+--------+
3 rows in set (0.00 sec)

Natürlich muss einem bewusst sein, dass auch dieses Vorgehen nur eine Momentaufnahme sein kann und keine 100 % Gewissheit erzeugen kann. Es kann auch hier noch Anwender geben, die sich erst wieder nach der Auswertung und Bereinigung hätten einloggen wollen und von daher bis jetzt nicht erfasst wurden. Aus diesem Grund sollten die Accounts auch in diesem Szenario nur gesperrt („locked“) und noch nicht gleich gelöscht werden.

Fazit

Die Lösung funktioniert und wurde von uns in diversen Projekten in dieser Art bereits eingesetzt! Sicherlich gibt es andere Datenbankprodukte, die dieses Szenario „eleganter“ und eventuell effizienter lösen. In der Praxis hat sich dieses Vorgehen aber bewährt und ist letztendlich auch nicht überbordend komplex.

Sie haben Fragen rund um den Betrieb von MySQL? Sprechen Sie mit uns.