=====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 => [[dba:kill_disconnect_session|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!
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 ;
-- =============================================================
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(, )
Mit Kommentar ( der steht dann auch im Alert log)
set serveroutput on
begin
sys.kill_other_session(p_sid =>
, p_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 =>
, p_serial# =>
, p_inst_id => '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:
* ALTER SYSTEM => https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/ALTER-SYSTEM.html
* DBMS_SQL => https://docs.oracle.com/en/database/oracle/oracle-database/12.2/arpls/DBMS_SQL.html
Web:
* PARSE_AS_USER => https://www.morganslibrary.org/reference/pkgs/dbms_sys_sql.html#ssql36
* dbms_system.ksdwrt => https://www.morganslibrary.org/reference/pkgs/dbms_system.html#dsys09