Wenn es dann noch um die zentrale Benutzertabelle geht, wird es besonders spannend. Seit der Version 12.2 gibt es die Möglichkeit, einen Benutzer automatisch zu sperren, wenn dieser sich über eine gewisse Anzahl von Tagen nicht an der Datenbank angemeldet hat.
Dies wird über Profile, genauer das Limit INACTIVE_ACCOUNT_TIME, gesteuert (in Tagen).
Zum Test dieses Features erzeugen wir nur die absolut notwendigen Dinge:
1.) Profil anlegen:
CREATE PROFILE HIDE_ME LIMIT INACTIVE_ACCOUNT_TIME 15 FAILED_LOGIN_ATTEMPTS 3;
2.) Drei Benutzer anlegen:
CREATE USER START_OPEN IDENTIFIED BY secret PROFILE HIDE_ME;
CREATE USER START_LOCKED IDENTIFIED BY secret PROFILE HIDE_ME;
CREATE USER START_FAILED_LOGIN IDENTIFIED BY secret PROFILE HIDE_ME;
3.) Entsprechende Berechtigungen vergeben:
GRANT CREATE SESSION TO START_FAILED_LOGIN;
4.) Einen Benutzer sperren:
ALTER USER START_LOCKED ACCOUNT LOCK;
5.) Drei fehlgeschlagene Login-Versuche bei Benutzer START_FAILED_LOGIN erzeugen.
Nun sehen wir uns direkt den aktuellen Zustand an:
1.) Per View DBA_USERS:
SELECT username, account_status, lock_date, profile
FROM dba_users
WHERE username IN ('START_OPEN', 'START_LOCKED', 'START_FAILED_LOGIN');
USERNAME ACCOUNT_STATUS LOCK_DATE PROFILE
-------- ------------- --------- -------
START_OPEN OPEN NULL HIDE_ME
START_LOCKED LOCKED 15.05.2021 20:38:41 HIDE_ME
START_FAILED_LOGIN LOCKED(TIMED) 15.05.2021 20:38:45 HIDE_ME
2.) Per Tabelle SYS.USER$:
SELECT name, astatus, ltime
FROM sys.user$
WHERE name IN ('START_OPEN', 'START_LOCKED', 'START_FAILED_LOGIN');
NAME ASTATUS LTIME
--------- - ------- -----
START_OPEN 0 NULL
START_LOCKED 8 NULL
START_FAILED_LOGIN 4 15.05.2021 20:38:45
Die drei Benutzer haben dummerweise für 15 Tage Urlaub und melden sich deshalb in dieser Zeit nicht an der Datenbank an. Die 15 Tage begründen sich im Übrigen damit, dass als minimaler Wert für INACTIVE_ACCOUNT_TIME 15 zugelassen ist. Wählt man einen geringeren Zeitraum, so wird man von einer freundlichen Fehlermeldung begrüßt: ORA-02377: invalid profile limit INACTIVE_ACCOUNT_TIME.
Nach erfolgtem Urlaub führen wir die beiden Statements erneut aus:
1.) Per View DBA_USERS:
SELECT username, account_status, lock_date, profile
FROM dba_users
WHERE username IN ('START_OPEN', 'START_LOCKED', 'START_FAILED_LOGIN');
USERNAME ACCOUNT_STATUS LOCK_DATE PROFILE
-------- ------------- ------------- -------
START_OPEN LOCKED 31.05.2021 00:00:32 HIDE_ME
START_LOCKED LOCKED 31.05.2021 00:00:32 HIDE_ME
2.) Per Tabelle SYS.USER$:
SELECT name, astatus, ltime
FROM sys.user$
WHERE name IN ('START_OPEN', 'START_LOCKED', 'START_FAILED_LOGIN');
NAME ASTATUS LTIME
-------- ------- -----------
START_OPEN 8 31.05.2021 00:00:32
START_LOCKED 8 31.05.2021 00:00:32
START_FAILED_LOGIN 12 31.05.2021 00:00:32
Die Benutzer START_OPEN und START_LOCKED sind nun wie gewünscht aufgrund von Inaktivität gesperrt.
Aber: Der Benutzer START_FAILED_LOGIN ist jedoch auf wundersame Weise nicht mehr in der View DBA_USERS zu sehen.
So weit, so gut. Aber auch überraschend. Nur wo ist denn jetzt der Zusammenhang mit dem Update auf der SYS.USER$ Tabelle?
Wie die Abfrage auf die Tabelle SYS.USER$ zeigt, ist der User START_FAILED_LOGIN natürlich noch vorhanden.
Bei der Suche im Oracle-Support-Portal wird als Lösung für dieses Problem (MOS-Note 2734949.1) ein kleines Update vorgeschlagen:
UPDATE sys.user$ SET astatus = astatus - 4 WHERE ( astatus = 12 OR astatus = 13);
COMMIT;
Um zu verstehen, warum dies vorgeschlagen wird, sehen wir uns die möglichen Werte der Spalte ASTATUS einmal genauer an:
SELECT * FROM user_astatus_map;
STATUS# STATUS
------- ------
0 OPEN
1 EXPIRED
2 EXPIRED(GRACE)
4 LOCKED(TIMED)
8 LOCKED
5 EXPIRED & LOCKED(TIMED)
6 EXPIRED(GRACE) & LOCKED(TIMED)
9 EXPIRED & LOCKED
10 EXPIRED(GRACE) & LOCKED
Die Werte setzen sich dabei z.B. wie folgt zusammen:
1 + 4 = 5, d.h. EXPIRED & LOCKED(TIMED)
2 + 4 = 6, d.h. EXPIRED(GRACE) & LOCKED(TIMED)
1 + 8 = 9, d.h. EXPIRED & LOCKED
Interessant ist jedoch, dass weder der Status 12 noch der Status 13 aus dem vorgeschlagenen Update vorhanden sind. Warum?
Wenn man z.B. den Wert 12 untersucht, kann dieser entweder mit 4 + 8, oder 2 + 10 gebildet werden:
a) 4 + 8 = 12, d.h. LOCKED(TIMED) & LOCKED
b) 2 + 10 = 12, d.h. EXPIRED(GRACE) & EXPIRED(GRACE) & LOCKED
Beide Varianten machen jedoch keinen Sinn, da
a) ein User nicht sogleich „LOCKED“ als auch „LOCKED(TIMED)“ sein kann bzw.
b) der Wert für EXPIRED(GRACE) doppelt vorkommen würde.
Eine analoge Überlegung gilt auch für den Wert 13.
Fazit:
1.) Es handelt sich um einen Oracle-Bug, da es die Werte 12 bzw. 13 für den Account-Status nicht geben dürfte, insofern ist das vorgeschlagene Update des Oracle-Supports nachvollziehbar.
2.) Wer Profile mit dem Parameter INACTIVE_ACCOUNT_TIME nutzt, sollte prüfen, ob es ggf. einen entsprechenden Schiefstand in der Datenbank gibt, dazu reicht nachfolgendes Statement:
SELECT name FROM sys.user$ WHERE type#=1
MINUS
SELECT username FROM dba_users;
Wenn kein Ergebnis zurückgeliefert wird, dann liegt das Problem auch nicht vor.
3.) Ohne explizite Aufforderung durch den Oracle-Support sollte ein Update auf Data-Dictionary-Tabellen definitiv nicht durchgeführt werden.
4.) Das erste DB-Release-Update, das einen Fix zu diesem Bug (29341782) enthält, ist leider erst 19.11.0.0.210420 (APR 2021), es gibt jedoch diverse One-Off-Patches für z.B. 12.2.
5.) Eine Nachfrage beim Oracle-Support, ob es eine unter Support stehende Möglichkeit gibt, den Schwellwert von 15 Tagen zu reduzieren, wurde mit dem Hinweis beantwortet, dass es aktuell nicht möglich sei, aber ein Enhancement-Request existiere (Enh 30565610 - PROVISION FOR INACTIVE_ACCOUNT_TIME LESS THAN 15DAYS IN PROFILE).
Viel Erfolg bei der Prüfung Ihrer Datenbank(en) auf diese kleine Unschärfe der DBA_USER-View. Bei Fragen können Sie mich gerne per E-Mail kontaktieren.
Bleiben Sie gesund, uns gewogen und freuen Sie sich auf den nächsten Beitrag der Datenbank Kolumne!
Mitglied Delegiertenversammlung Korp. Mitglieder K500,
Themenverantwortung DevOps der DOAG Datenbank Community
---
© Bild von Markus Winkler auf Pixabay
