Author: grianggrai.n

อยากฆ่าคน เอ้ย session บน oracle แต่ไม่มีสิทธิ์ (Privilege) ไปขอสิทธิ์ DBA แล้ว DBA บอกว่าไม่มีสิทธิ์ kill session จะแจกให้!!! ทำไงดี

เข้าระบบฐานข้อมูล Oracle ด้วยผู้ใช้ system หรือ sys แล้วสร้าง Procedure ชื่อว่า sp_kill_dev_session ด้วยคำสั่ง

CREATE OR REPLACE PROCEDURE sys.sp_kill_dev_session(p_sid NUMBER, p_serial NUMBER)
AS
    v_user VARCHAR2(30);
BEGIN
    SELECT MAX(username)
    INTO v_user
    FROM v$session
    WHERE sid = p_sid
      AND serial# = p_serial;

    IF v_user IN ('SCOTT') THEN --the list can be extended
         EXECUTE IMMEDIATE 'ALTER SYSTEM KILL SESSION ''' || p_sid || ',' || p_serial || '''';
    ELSIF v_user IS NULL THEN
         RAISE_APPLICATION_ERROR(-20001,'Session has Expired or Invalid sid/serial Arguments Passed');
    ELSE
         RAISE_APPLICATION_ERROR(-20002,'Unauthorized Attempt to Kill a Non-Dev Session has been Blocked.');
    END IF;
END sp_kill_dev_session;
/

จาก code ข้างต้น ให้เปลี่ยน SCOTT เป็น username ที่เป็นเจ้าของ session เช่น

IF v_user IN ('HIRXPXT','PHU_XDMIHHION') THEN --the list can be extended

เป็นต้น จากตัวอย่าง จะมี 2 user คือ HIRXPXT และ PHU_XDMIHHION

แล้ว grant สิทธิ์ ให้สามารถ exec กับ user ที่ต้องการฆ่า session เหล่านั้นด้วยคำสั่ง ตัวอย่างจะให้ user ชื่อว่า HIRXPXT มีสิทธิ์ exec

GRANT EXECUTE ON sp_kill_dev_session TO HIRXPXT;

แล้วจะทำให้ user ที่ชื่อ HIRXPXT สามารถฆ่า (kill) session ของ user ได้ 2 คนคือ HIRXPXT และ PHU_XDMIHHION

วิธีใช้งาน เข้าระบบด้วย user ที่ชื่อ HIRXPXT แล้วเรียกใช้คำสั่ง

exec sys.sp_kill_dev_session(sid,serial num);

เช่น

exec sys.sp_kill_dev_session(154,42802);

โดย 14 คือ sid และ 26043 คือ serial ซึ่งสองค่านี้สามารถเรียกดูได้จากคำสั่ง

SELECT SID, SERIAL#, STATUS, SERVER
FROM V$SESSION
WHERE USERNAME = upper('<username>');

เปลี่ยน <username> เป็น username ที่ต้องการ แต่การที่จะดูค่าเหล่านี้ได้ต้องมีสิทธิ์ จากตัวอย่าง user ที่ชื่อ HIRXPXT สามารถ select view และ table เหล่านี้ได้

SYS.V_$PROCESS
SYS.V_$SESSION
SYS.V_$TRANSACTION
SYS.V_$SESSION_LONGOPS
SYS.V_$LOCK
SYS.V_$SESSTAT
SYS.V_$STATNAME
SYS.V_$ACCESS
SYS.V_$ROLLNAME
SYS.V_$SQLTEXT
SYS.V_$SQLTEXT_WITH_NEWLINES
SYS.V_$SQL
SYS.V_$OPEN_CURSOR
SYS.V_$SESSION_WAIT
SYS.V_$SESSION_EVENT
SYS.V_$SESSION_CONNECT_INFO
SYS.V_$EVENT_NAME
SYS.V_$SESS_IO
SYS.V_$EVENTMETRIC
SYS.DBA_ROLLBACK_SEGS

เท่านี้ก็เพียงพอที่จะให้ทีมพัฒนาสามารถฆ่า (kill) session ของตัวเองที่ค้างอยู่บน server ได้โดยไม่ต้องมาจิกหัว DBA กรั่กๆ

ต้นฉบับ

https://sqlpatterns.wordpress.com/2015/03/02/how-to-safely-grant-alter-system-kill-session-privilege-to-non-dba-users/

จบ…. ขอให้สนุก