Als PDF Downloaden!
Werbung
Tipps & Tricks: Januar 2010 Bereich: DBA Erstellung: 01/2010 MM Versionsinfo: 9.2, 10.2, 11.1 Letzte Überarbeitung: 01/2010 MM Das Wichtigste rund um Benutzer-Profile 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 9i 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 UNLIMITED DEFAULT PASSWORD_GRACE_TIME PASSWORD UNLIMITED DEFAULT PASSWORD_LIFE_TIME PASSWORD UNLIMITED Muniqsoft GmbH Schulungszentrum, Grünwalder Weg 13a, 82008 Unterhaching, Tel. 089 / 679090-40 IT-Consulting & Support, Witneystraße 1, 82008 Unterhaching, Tel. 089 / 6228 6789-0 Seite 1 von 7 DEFAULT DEFAULT DEFAULT DEFAULT PASSWORD_LOCK_TIME PASSWORD_REUSE_MAX PASSWORD_REUSE_TIME PASSWORD_VERIFY_FUNCTION PASSWORD UNLIMITED PASSWORD UNLIMITED PASSWORD UNLIMITED PASSWORD NULL -- Standard-Ausgabe zu 10g 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 UNLIMITED DEFAULT PASSWORD_LIFE_TIME PASSWORD UNLIMITED DEFAULT PASSWORD_LOCK_TIME PASSWORD UNLIMITED 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 -- 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 Muniqsoft GmbH Schulungszentrum, Grünwalder Weg 13a, 82008 Unterhaching, Tel. 089 / 679090-40 IT-Consulting & Support, Witneystraße 1, 82008 Unterhaching, Tel. 089 / 6228 6789-0 Seite 2 von 7 DEFAULT DEFAULT DEFAULT DEFAULT DEFAULT DEFAULT DEFAULT DEFAULT DEFAULT DEFAULT MONITORING_PROFILE MONITORING_PROFILE MONITORING_PROFILE MONITORING_PROFILE MONITORING_PROFILE MONITORING_PROFILE MONITORING_PROFILE MONITORING_PROFILE MONITORING_PROFILE MONITORING_PROFILE UNLIMITED MONITORING_PROFILE MONITORING_PROFILE MONITORING_PROFILE MONITORING_PROFILE MONITORING_PROFILE MONITORING_PROFILE WKSYS_PROF WKSYS_PROF WKSYS_PROF WKSYS_PROF WKSYS_PROF WKSYS_PROF WKSYS_PROF WKSYS_PROF WKSYS_PROF WKSYS_PROF WKSYS_PROF WKSYS_PROF WKSYS_PROF WKSYS_PROF WKSYS_PROF WKSYS_PROF LOGICAL_READS_PER_SESSION PRIVATE_SGA SESSIONS_PER_USER FAILED_LOGIN_ATTEMPTS PASSWORD_GRACE_TIME PASSWORD_LIFE_TIME PASSWORD_LOCK_TIME PASSWORD_REUSE_MAX PASSWORD_REUSE_TIME PASSWORD_VERIFY_FUNCTION COMPOSITE_LIMIT CONNECT_TIME CPU_PER_CALL CPU_PER_SESSION IDLE_TIME LOGICAL_READS_PER_CALL LOGICAL_READS_PER_SESSION PRIVATE_SGA SESSIONS_PER_USER FAILED_LOGIN_ATTEMPTS KERNEL UNLIMITED KERNEL UNLIMITED KERNEL UNLIMITED PASSWORD 10 PASSWORD 7 PASSWORD 180 PASSWORD 1 PASSWORD UNLIMITED PASSWORD UNLIMITED PASSWORD NULL KERNEL DEFAULT KERNEL DEFAULT KERNEL DEFAULT KERNEL DEFAULT KERNEL DEFAULT KERNEL DEFAULT KERNEL DEFAULT KERNEL DEFAULT KERNEL DEFAULT PASSWORD PASSWORD_GRACE_TIME PASSWORD_LIFE_TIME PASSWORD_LOCK_TIME PASSWORD_REUSE_MAX PASSWORD_REUSE_TIME PASSWORD_VERIFY_FUNCTION COMPOSITE_LIMIT CONNECT_TIME CPU_PER_CALL CPU_PER_SESSION IDLE_TIME LOGICAL_READS_PER_CALL LOGICAL_READS_PER_SESSION PRIVATE_SGA SESSIONS_PER_USER FAILED_LOGIN_ATTEMPTS PASSWORD_GRACE_TIME PASSWORD_LIFE_TIME PASSWORD_LOCK_TIME PASSWORD_REUSE_MAX PASSWORD_REUSE_TIME PASSWORD_VERIFY_FUNCTION PASSWORD DEFAULT PASSWORD DEFAULT PASSWORD DEFAULT PASSWORD DEFAULT PASSWORD DEFAULT PASSWORD DEFAULT KERNEL DEFAULT KERNEL DEFAULT KERNEL DEFAULT KERNEL DEFAULT KERNEL DEFAULT KERNEL DEFAULT KERNEL DEFAULT KERNEL DEFAULT KERNEL DEFAULT PASSWORD UNLIMITED PASSWORD DEFAULT PASSWORD DEFAULT PASSWORD DEFAULT PASSWORD DEFAULT PASSWORD DEFAULT 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; Muniqsoft GmbH Schulungszentrum, Grünwalder Weg 13a, 82008 Unterhaching, Tel. 089 / 679090-40 IT-Consulting & Support, Witneystraße 1, 82008 Unterhaching, Tel. 089 / 6228 6789-0 Seite 3 von 7 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 Muniqsoft GmbH Schulungszentrum, Grünwalder Weg 13a, 82008 Unterhaching, Tel. 089 / 679090-40 IT-Consulting & Support, Witneystraße 1, 82008 Unterhaching, Tel. 089 / 6228 6789-0 Seite 4 von 7 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, Muniqsoft GmbH Schulungszentrum, Grünwalder Weg 13a, 82008 Unterhaching, Tel. 089 / 679090-40 IT-Consulting & Support, Witneystraße 1, 82008 Unterhaching, Tel. 089 / 6228 6789-0 Seite 5 von 7 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 Muniqsoft GmbH Schulungszentrum, Grünwalder Weg 13a, 82008 Unterhaching, Tel. 089 / 679090-40 IT-Consulting & Support, Witneystraße 1, 82008 Unterhaching, Tel. 089 / 6228 6789-0 Seite 6 von 7 / 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; logon denied Kennwort unverändert Die Passwortänderung ist nur über SQL*Plus der Version 11g möglich oder Sie setzen wieder den Parameter SEC_CASE_SENSITIVE_LOGON auf FALSE. Muniqsoft GmbH Schulungszentrum, Grünwalder Weg 13a, 82008 Unterhaching, Tel. 089 / 679090-40 IT-Consulting & Support, Witneystraße 1, 82008 Unterhaching, Tel. 089 / 6228 6789-0 Seite 7 von 7
