Inhaltsverzeichnis
Oracle Kill Session für normale User - Ohne besondere Rechte den Aufruf von "kill session" für normale DB User ermöglichen
Aufgaben:
In einer Oracle 12c Real Applikation Datenbank besitzen die Administratoren einer Webapplikation sehr weitgehende Rechte aber eben nicht die DBA Rolle und das „alter system“ Recht.
Diese Administratoren sollen nun aber auch Sessions in der DB beenden können ohne nur dafür viele Rechte dazu zu benötigen.
Lösung:
Wir erstellen eine Procedure für diese Aufgabe unter dem SYS Schema und granten diese Procedure einer Rolle.
Die Rolle wiederum vergeben wir an den jeweiligen DB User der diesen Task ausführen soll.
Nun kann der User die Procedure mit den notwendigen Parametern aufrufen, das Session Kill Kommando wird abgesetzt und der Aufruf in der Alert Log protokoliert.
Mehr zu Kill session auch hier ⇒ Eine Oracle Session beenden - Kill Session / Disconnect Session
Der Code
Wir arbeiten hier mit zwei wichtigen Eigenschaften, einmal ist die Procedure mit der AUTHID DEFINER definiert, d.h. beim Start über den der User A wird die Procedure intern trotzdem als SYS ausgeführt. Zusätzlich verwenden wir dbms_sys_sql um den eigentlichen Befehl als SYS User auszuführen.
Alles wird dann noch über sys.dbms_system.ksdwrt auch im Alert log protokolliert.
Bevor der Befehl ausgeführt wird, wird überprüft ob die Session auch existiert und die Session nicht vom Type BACKGROUND ist. Ein kill einer Background Session ist meist tödlich für die Instance und kann die ganze Datenbank gefährden!
- kill_other_session.sql
CREATE OR REPLACE PROCEDURE kill_other_session( p_sid IN NUMBER , p_serial# IN NUMBER , p_inst_id IN NUMBER DEFAULT NULL , p_comment IN VARCHAR2 DEFAULT NULL ) AUTHID DEFINER -- ============================================================= -- Procedure kill_other_session -- Kill as normal user a session in the oracle database over this procedure -- User need no special rights to execute this procedure -- -- grant rights over role: -- create role kill_other_session; -- grant execute on kill_other_session to kill_other_session; -- grant kill_other_session to <user_with_kill_rights>; -- ============================================================= IS v_user VARCHAR2(512); v_kill_message VARCHAR2(4000); v_sql sys.DBMS_SQL.varchar2a; v_cursor NUMBER; v_result PLS_INTEGER; v_sys_user_id NUMBER; BEGIN -- get the sys user id SELECT user_id INTO v_sys_user_id FROM sys.all_users WHERE username = 'SYS'; -- get user and remember some details -- to help to identif the user v_user :=SYS_CONTEXT('userenv','SESSION_USER') ||' from the PC '||SYS_CONTEXT('userenv','OS_USER') ||'('||SYS_CONTEXT('userenv','TERMINAL')||')'; v_kill_message:='User '|| v_user ||' try to initiate a kill with sys.kill_other_session of this session :: SID:'||p_sid ||', Serial:'||p_serial# ||' - InstID:'||NVL(p_inst_id,USERENV('instance')) ||' - Comment:'||NVL(p_comment,'n/a'); -- log the kill into the alert file of the database sys.dbms_system.ksdwrt ( sys.dbms_system.alert_file, '-- Info : '||v_kill_message); -- show output DBMS_OUTPUT.put_line('-- Info : ----------------------------'); DBMS_OUTPUT.put_line('-- Info : '||v_kill_message ); -- check for RAC -- check if the session exists -- and if the session is not a DB Prozess ( BACKGROUND session!) -- to avoid chrash of the instance! IF p_inst_id IS NULL THEN FOR rec IN ( SELECT 'x' FROM sys.gv_$session WHERE sid = p_sid AND serial# = p_serial# AND inst_id = USERENV('instance') AND TYPE != 'BACKGROUND') LOOP v_sql(1) := 'alter system kill session ''' || p_sid || ',' || p_serial# || ''''; END LOOP; ELSE FOR rec IN ( SELECT 'x' FROM sys.gv_$session WHERE sid = p_sid AND serial# = p_serial# AND inst_id = p_inst_id AND TYPE != 'BACKGROUND') LOOP v_sql(1) := 'alter system kill session ''' || p_sid || ',' || p_serial# || ',@' || p_inst_id || ''''; END LOOP; END IF; -- check if we found the session IF v_sql.COUNT > 0 THEN -- execute the kill command: -- get cursor v_cursor := sys.dbms_sys_sql.open_cursor; -- parse the statement sys.dbms_sys_sql.parse_as_user ( c => v_cursor , statement => v_sql , lb => 1 , ub => v_sql.COUNT , lfflg => TRUE , language_flag => sys.dbms_sys_sql.native , userid => v_sys_user_id ); -- exectute v_result := sys.dbms_sys_sql.EXECUTE(v_cursor); -- close the cursor sys.dbms_sys_sql.close_cursor( v_cursor ); DBMS_OUTPUT.put_line('-- Info : Kill of the session is requested - please check status of the session'); sys.dbms_system.ksdwrt ( sys.dbms_system.alert_file, '-- Info ::kill Session with kill_other_session initiated'); ELSE DBMS_OUTPUT.put_line('-- Error: ----------------------------'); DBMS_OUTPUT.put_line('-- Error: Session to kill not found or is BACKGROUND session!' ); DBMS_OUTPUT.put_line('-- Error: ----------------------------'); sys.dbms_system.ksdwrt ( sys.dbms_system.alert_file, '-- Info : kill Session to kill not extis or is BACKGROUND session (not allowed!)'); END IF; DBMS_OUTPUT.put_line('-- Info : ----------------------------'); EXCEPTION WHEN OTHERS THEN -- check for open cursor IF sys.dbms_sys_sql.is_open(v_cursor) THEN sys.dbms_sys_sql.close_cursor(v_cursor); END IF; DBMS_OUTPUT.put_line('-- Error: ----------------------------'); DBMS_OUTPUT.put_line('-- Error: Message :: '||SQLERRM ); DBMS_OUTPUT.put_line('-- Error: ----------------------------'); -- log this error also to the alert log sys.dbms_system.ksdwrt ( sys.dbms_system.alert_file, '-- Error: kill Session with sys.kill_other_session fails with ::'||SQLERRM); RAISE; END kill_other_session;
Aufruf
Im einfachsten Fall:
SET serveroutput ON EXEC sys.kill_other_session(<sid>, <serial>)
Mit Kommentar ( der steht dann auch im Alert log)
SET serveroutput ON BEGIN sys.kill_other_session(p_sid => <sid> , p_serial# => <serial> , p_comment => 'Siehe Ticket 22333'); END; / -- Ausgabe -- Info : ---------------------------- -- Info : User GPI from the PC SATURN\gpipperr(SATURN) try to initiate a kill with sys.kill_other_session of this Session :: SID:2, Serial:6 - InstID:1 - Comment:Siehe Ticket 22333 -- Error: ---------------------------- -- Error: Session to kill not found or is BACKGROUND session! -- Error: ---------------------------- -- Info : ----------------------------
Falls auf einer anderen Instance der Datenbank der Prozesse gestoppt werden soll:
SET serveroutput ON BEGIN sys.kill_other_session(p_sid => <sid> , p_serial# => <serial> , p_inst_id => <inst_id wie 1 oder 2) , p_comment => 'Siehe Ticket 22333'); END;
Alert.log Einträge
Da wir mit sys.dbms_system.ksdwrt das zusätzlich noch in der Alert.log protokollieren, sieht das am Ende so aus;
Im Erfolgsfall:
2018-07-23T10:51:10.497881+02:00 -- Info : User GPI from the PC SATURN\gpipperr(SATURN) try to initiate a kill with sys.kill_other_session of this session :: SID:64, Serial:45739 - InstID:1 - Comment:Siehe Ticket 22333 KILL SESSION for sid=(64, 45739): Reason = alter system kill session Mode = KILL SOFT -/-/- Requestor = USER (orapid = 82, ospid = 16104, inst = 1) Owner = Process: USER (orapid = 33, ospid = 8804) Result = ORA-0 -- Info ::kill Session with kill_other_session initiated
Im Fehlerfall:
2018-07-23T10:46:37.769816+02:00 -- Info : User GPI from the PC SATURN\gpipperr(SATURN) try to initiate a kill with sys.kill_other_session of this session :: SID:127, Serial:45356 - InstID:1 - Comment:Siehe Ticket 22333 KILL SESSION for sid=(127, 45356): Reason = alter system kill session Mode = KILL SOFT -/-/- Requestor = USER (orapid = 82, ospid = 16104, inst = 1) Owner = N/A Result = ORA-27 -- Error: kill Session with sys.kill_other_session fails with ::ORA-00027: cannot kill current session
Quellen
Doku:
Oracle:
Web:
- dbms_system.ksdwrt ⇒ https://www.morganslibrary.org/reference/pkgs/dbms_system.html#dsys09