Auswahl  

 

Oracle
DBA
RDBMS 11.2:RDBMS 12.1:RDBMS 12.2:RDBMS 18.1
29.06.18 (MP)
29.06.18 (MP)
DBA

Body

Haben Sie sich als Datenbank-Administrator schon einmal eine der folgenden Fragen gestellt?

Wie kann ich die Anzahl an gleichzeitigen Sessions eines Benutzers einschränken?
Wie kann ich verhindern, dass die Benutzer unbegrenzt lange eine Verbindung zur Datenbank haben (, aber nichts mehr tun)?
Gibt es eine Möglichkeit die erforderliche Arbeitslast durch Benutzerzugriffe zu limitieren?
Wie kann ich die Laufzeit der Benutzer-Passwörter begrenzen?
Kann ich die Oracle-Passwörter sicherer machen?
Falls Sie eine oder mehrere Fragen mit ja beantworten können und bislang noch keine wirkliche Antwort dafür gefunden haben, dann haben wir etwas interessantes für Sie.

Der folgende Beitrag erläutert die Bedeutung und Verwaltung von Benutzer-Profilen und verdeutlicht die einzelnen Parameter, die darüber gesetzt werden können.

 

DER PROFIL-BEGRIFF
Mit einem Benutzer-Profil können Sie die Passwortverwaltung und die Ressourcennutzung der Benutzer steuern bzw. limitieren. Dafür stehen insgesamt 16 Parameter zur Verfügung, die innerhalb einer CREATE PROFILE oder ALTER PROFILE-Anweisung definiert werden können. In jeder Oracle Datenbank gibt es ein Profil namens DEFAULT, dass standardmäßig jedem Benutzer zugewiesen wird.

Eine Übersicht über die vorhandenen Profile und deren Begrenzungen erhalten Sie über:

SELECT * FROM dba_profiles
ORDER BY profile, resource_type, resource_name;

- Standard-Ausgabe zu 11g

PROFILE              RESOURCE_NAME                    RESOURCE LIMIT
-------------------- -------------------------------- -------- ----------
DEFAULT              COMPOSITE_LIMIT                  KERNEL   UNLIMITED
DEFAULT              CONNECT_TIME                     KERNEL   UNLIMITED
DEFAULT              CPU_PER_CALL                     KERNEL   UNLIMITED
DEFAULT              CPU_PER_SESSION                  KERNEL   UNLIMITED
DEFAULT              IDLE_TIME                        KERNEL   UNLIMITED
DEFAULT              LOGICAL_READS_PER_CALL           KERNEL   UNLIMITED
DEFAULT              LOGICAL_READS_PER_SESSION        KERNEL   UNLIMITED
DEFAULT              PRIVATE_SGA                      KERNEL   UNLIMITED
DEFAULT              SESSIONS_PER_USER                KERNEL   UNLIMITED
DEFAULT              FAILED_LOGIN_ATTEMPTS            PASSWORD 10
DEFAULT              PASSWORD_GRACE_TIME              PASSWORD 7
DEFAULT              PASSWORD_LIFE_TIME               PASSWORD 180
DEFAULT              PASSWORD_LOCK_TIME               PASSWORD 1
DEFAULT              PASSWORD_REUSE_MAX               PASSWORD UNLIMITED
DEFAULT              PASSWORD_REUSE_TIME              PASSWORD UNLIMITED
DEFAULT              PASSWORD_VERIFY_FUNCTION         PASSWORD NULL
MONITORING_PROFILE   COMPOSITE_LIMIT                  KERNEL   DEFAULT
MONITORING_PROFILE   CONNECT_TIME                     KERNEL   DEFAULT
MONITORING_PROFILE   CPU_PER_CALL                     KERNEL   DEFAULT
MONITORING_PROFILE   CPU_PER_SESSION                  KERNEL   DEFAULT
MONITORING_PROFILE   IDLE_TIME                        KERNEL   DEFAULT
MONITORING_PROFILE   LOGICAL_READS_PER_CALL           KERNEL   DEFAULT
MONITORING_PROFILE   LOGICAL_READS_PER_SESSION        KERNEL   DEFAULT
MONITORING_PROFILE   PRIVATE_SGA                      KERNEL   DEFAULT
MONITORING_PROFILE   SESSIONS_PER_USER                KERNEL   DEFAULT
MONITORING_PROFILE   FAILED_LOGIN_ATTEMPTS            PASSWORD
UNLIMITED
MONITORING_PROFILE   PASSWORD_GRACE_TIME              PASSWORD DEFAULT
MONITORING_PROFILE   PASSWORD_LIFE_TIME               PASSWORD DEFAULT
MONITORING_PROFILE   PASSWORD_LOCK_TIME               PASSWORD DEFAULT
MONITORING_PROFILE   PASSWORD_REUSE_MAX               PASSWORD DEFAULT
MONITORING_PROFILE   PASSWORD_REUSE_TIME              PASSWORD DEFAULT
MONITORING_PROFILE   PASSWORD_VERIFY_FUNCTION         PASSWORD DEFAULT
WKSYS_PROF           COMPOSITE_LIMIT                  KERNEL   DEFAULT
WKSYS_PROF           CONNECT_TIME                     KERNEL   DEFAULT
WKSYS_PROF           CPU_PER_CALL                     KERNEL   DEFAULT
WKSYS_PROF           CPU_PER_SESSION                  KERNEL   DEFAULT
WKSYS_PROF           IDLE_TIME                        KERNEL   DEFAULT
WKSYS_PROF           LOGICAL_READS_PER_CALL           KERNEL   DEFAULT
WKSYS_PROF           LOGICAL_READS_PER_SESSION        KERNEL   DEFAULT
WKSYS_PROF           PRIVATE_SGA                      KERNEL   DEFAULT
WKSYS_PROF           SESSIONS_PER_USER                KERNEL   DEFAULT
WKSYS_PROF           FAILED_LOGIN_ATTEMPTS            PASSWORD UNLIMITED
WKSYS_PROF           PASSWORD_GRACE_TIME              PASSWORD DEFAULT
WKSYS_PROF           PASSWORD_LIFE_TIME               PASSWORD DEFAULT
WKSYS_PROF           PASSWORD_LOCK_TIME               PASSWORD DEFAULT
WKSYS_PROF           PASSWORD_REUSE_MAX               PASSWORD DEFAULT
WKSYS_PROF           PASSWORD_REUSE_TIME              PASSWORD DEFAULT
WKSYS_PROF           PASSWORD_VERIFY_FUNCTION         PASSWORD DEFAULT


Ab Version 10g wird bei einer Standard-Installation ein zweites Profil mit dem Namen MONITORING_PROFILE erzeugt und dem Benutzer DBSNMP zugewiesen.

Ab Version 11g kommt noch das Profil WKSYS_PROF dazu, das der Benutzer WKSYS erhält.

Welcher Benutzer welches Profil bekommen hat, ermitteln Sie über:

SELECT username, user_id, profile FROM dba_users;

DIE RESSOURCEN-PARAMETER EINES PROFILS
SESSIONS_PER_USER

Limitiert die Anzahl an gleichzeitig geöffneten Sessions eines Benutzers. Bei Überschreiten erhält man folgenden Fehler beim Anmeldeversuch:

ORA-02391: exceeded simultaneous SESSIONS_PER_USER limit


CPU_PER_SESSION

Limitiert die CPU-Zeit einer einzelnen Session in hundertstel Sekunden. Bei Überschreiten beendet Oracle das Statement bzw. rollt es zurück, meldet den Benutzer ab und gibt die folgende Fehlermeldung aus:

ORA-02392: exceeded session limit on CPU usage, you are being logged off


CPU_PER_CALL

Limitiert die CPU-Zeit eines einzelnen Aufrufs (eines Parse-, Execute- oder Fetch-Vorgangs) in hundertstel Sekunden. Bei Überschreiten beendet Oracle nur dieses Statement bzw. rollt es zurück und gibt folgeden Fehler aus (die vorangegangenen Befehle bleiben davon unberührt):

ORA-02393: exceeded call limit on CPU usage


CONNECT_TIME

Limitiert die gesamte Verbindungszeit einer Session in Minuten. Wird die Grenze überschritten, wird eine offene Transaktion zurückgerollt, der Benutzer abgemeldet und es kommt zu folgendem Fehler:

ORA-02399: exceeded maximum connect time, you are being logged off


IDLE_TIME

Limitiert die zusammenhängende inaktive Verbindungszeit (Leerlaufzeit) innerhalb einer Session in Minuten. Langläufer sind davon nicht betroffen. Wird die Grenze überschritten, wird eine offene Transaktion zurückgerollt, der Benutzer abgemeldet und es kommt zu folgendem Fehler:

ORA-02396: exceeded maximum idle time, please connect again


LOGICAL_READS_PER_SESSION

Limitiert die Gesamtanzahl von Datenblock-Lesezugriffen in einer Sitzung. Dazu gehören Blocklesezugriffe vom Speicher und der Platte. Bei Überschreiten beendet Oracle das Statement bzw. rollt es zurück, meldet den Benutzer ab und gibt die folgende Fehlermeldung aus:

ORA-02394: exceeded session limit on IO usage, you are being logged OFF


LOGICAL_READS_PER_CALL

Limitiert die Höchstzahl von Datenblock-Lesezugriffen für einen Aufruf (Parse-, Execute- oder Fetch-Vorgang) zur Verarbeitung einer SQL-Anweisung. Bei Überschreiten beendet Oracle nur dieses Statement bzw. rollt es zurück und gibt folgeden Fehler aus (die vorangegangenen Befehle bleiben davon unberührt):

ORA-02395: exceeded call limit on IO usage


PRIVATE_SGA

Limitiert die allokierbare Größe einer einzelnen Session im Shared Pool der SGA in Bytes, Kilo- oder Megabytes. Dies gilt allerdings nur für Shared Server Verbindungen. Bei Überschreiten beendet Oracle das Statement bzw. rollt es zurück, meldet den Benutzer ab und gibt die folgende Fehlermeldung aus:

ORA-02397: exceeded PRIVATE_SGA limit, you are being logged off


COMPOSITE_LIMIT

Limitiert die gesamten Ressourcenkosten für eine Sitzung. Die Kosten für eine Sitzung entsprechen der gewichteten Summe von CPU_PER_SESSION, CONNECT_TIME, LOGICAL_READS_PER_SESSION, and PRIVATE_SGA. Mit dem ALTER RESOURCE COST Befehl lässt sich die Gewichtung verändern.

Beispiel

ALTER RESOURCE COST
   CPU_PER_SESSION 200
   CONNECT_TIME      2;

Damit ergibt sich folgende Kostenformel für eine Sitzung

cost = (200 * CPU_PER_SESSION) + (2 * CONNECT_TIME)

Die Werte für CPU_PER_SESSION und CONNECT_TIME werden dabei aus dem Benutzer-Profil für die laufende Session übernommen. Ist einem Benutzer über sein Profil ein COMPOSITE_LIMIT-Wert von 1000 zugewiesen worden, dann würde beispielsweise eine CPU Zeit von 0,05 und eine Laufzeit von 126 Minuten dieses Limit übersteigen. Bei Überschreiten beendet Oracle das Statement bzw. rollt es zurück, meldet den Benutzer ab und gibt die folgende Fehlermeldung aus:

ORA-02390: exceeded COMPOSITE_LIMIT, you are being logged off

DIE PASSWORT-PARAMETER EINES PROFILS
FAILED_LOGIN_ATTEMPTS

Limitiert die Anzahl an nicht erfolgreichen Anmeldeversuchen, danach wird der Account gesperrt.


PASSWORD_LOCK_TIME

Gibt die Anzahl an Tagen an, die der Account nach Erreichen der FAILED_LOGIN_ATTEMPTS-Grenze gesperrt ist.


PASSWORD_LIFE_TIME

Begrenzt die Laufzeit eines Passworts in Tagen.

ORA-28001: the password has expired

Sollte PASSWORD_GRACE_TIME ebenfalls gesetzt sein, läuft das Passwort erst ab, falls es nicht innerhalb der Verlängerungsfrist geändert wird.


PASSWORD_GRACE_TIME

Bestimmt die Anzahl an Tagen der Verlängerungsfrist. Innerhalb dieser Frist ist die Anmeldung weiterhin erlaubt, der Benutzer erhält aber eine Warnung:

ORA-28002: the password will expire within 7 days


PASSWORD_VERIFY_FUNCTION

Ermöglicht die Verwendung einer PL/SQL-Routine als Komplexitätsanforderung für ein zulässiges Kennwort. Diese Routine muss lokal zur Ausführung auf der Datenbank verfügbar sein, für die dieses Profil gilt.

Oracle stellt ein Standardskript (%ORACLE_HOME%\rdbms\admin\utlpwdmg.sql) bereit. Sie können jedoch auch eine eigene Routine erstellen oder eine Software von einem anderen Hersteller als Alternative verwenden. Die Routine zur Überprüfung des Kennworts muss SYS gehören.


PASSWORD_REUSE_TIME

Begrenzt die Anzahl an Tagen, bevor ein Kennwort nach dem Ablauf wiederverwendet werden kann.


PASSWORD_REUSE_MAX

Gibt an, wie oft ein Kennwort geändert werden muss, bevor es erneut verwendet werden kann.

Die letzten beiden Parameter müssen immer in Kombination gesetzt werden, ansonsten kann es sein, dass ein Passwort überhaupt nicht mehr verwendet werden kann.

 

BEISPIELE ZU PROFILEN
Erstellung eines Profils:

CREATE PROFILE my_profile LIMIT
  PASSWORD_REUSE_MAX       10
  PASSWORD_REUSE_TIME      30
  PASSWORD_LIFE_TIME       60
  PASSWORD_GRACE_TIME      10
  PASSWORD_LOCK_TIME       1/1440
  PASSWORD_VERIFY_FUNCTION verify_function
/
CREATE PROFILE app_profile LIMIT
  SESSIONS_PER_USER          UNLIMITED
  CPU_PER_SESSION            UNLIMITED
  CPU_PER_CALL               6000
  IDLE_TIME                  20
  LOGICAL_READS_PER_SESSION  DEFAULT
  LOGICAL_READS_PER_CALL     1000
  PRIVATE_SGA                3M
  COMPOSITE_LIMIT            5000000
/


Änderung eines Profils:

ALTER PROFILE my_profile LIMIT
  PASSWORD_LIFE_TIME 60
  PASSWORD_GRACE_TIME 10
  FAILED_LOGIN_ATTEMPTS 3
  PASSWORD_LOCK_TIME 1/1440
/

Löschen eines Profils:

DROP PROFILE app_profile;

Zuweisen eines Profils:

ALTER USER scott PROFILE my_profile;

HINWEISE UND TIPPS ZU PROFILEN
Wird ein Profil gelöscht, das noch einem Benutzer zugewiesen ist, dann erhält dieser automatisch das DEFAULT-Profil.

Werden beim Erstellen eines Profils nicht alle Parameter übergeben, bekommen diese die Einstellung des DEFAULT-Profils.

Damit die Ressourcen-Einschränkungen eines Profil greifen, muss noch der SPFILE-Parameter RESOURCE_LIMIT auf TRUE gesetzt werden. Die Passwortverwaltung ist davon nicht betroffen.

ALTER SYSTEM SET resource_limit = true;

Ab Version 11g wird bei den Passwörtern standardmäßig Groß- und Kleinschreibung unterschieden (case-sensitive). Wollen Sie dies wieder unterdrücken, so ist der SPFILE-Parameter SEC_CASE_SENSITIVE_LOGON auf FALSE zu setzen:

ALTER SYSTEM SET sec_case_sensitive_logon=false;

Dieser Parameter ist auch für folgendes Problem verantwortlich:

Sie wollen über SQL*Plus (Version 10g) auf eine Datenbank (Version 11g) zugreifen und werden bei der Anmeldung aufgefordert Ihr Passwort zu ändern:

conn scott/tiger@o11g
ERROR:
ORA-28001: the password has expired
Kennwort für scott wird geändert
Neues Kennwort: *****
Neues Kennwort erneut eingeben: *****
ERROR:
ORA-01017: invalid username/password;

Weitere tolle Tipps & Tricks aus der Praxis lernen Sie in unseren DBA Kursen.

Besuchen Sie uns doch bei einer unsere über 40 Oracle Schulungen in München - Unterhaching.