Oracle: Let users kill their sessions autonomously

From time to time my developers are haunted by hanging DB-sessions on their Dev-Instances. To let them kill their lost sessions in that cases I created this procedure as SYS and granted them the “EXECUTE”-Privilege on it:

create or replace
PROCEDURE kill_usersession(
    n_sid    IN NUMBER,
    n_serial IN NUMBER)
AS
BEGIN
  DECLARE
    v_myuser   VARCHAR2(30) DEFAULT '';
    v_killuser VARCHAR2(30) DEFAULT '';
  BEGIN
    SELECT sys_context('USERENV','SESSION_USER') INTO v_myuser FROM dual;
    SELECT username
    INTO v_killuser
    FROM v$session
    WHERE sid    =n_sid
    AND serial#  =n_serial;
    IF ( v_killuser=v_myuser ) THEN
      EXECUTE immediate('ALTER SYSTEM KILL SESSION ''' || n_sid || ',' || n_serial || '''');
    ELSE
      raise_application_error(-20000, 'Username mismatch: You can not kill the session of another schema!');
    END IF;
  EXCEPTION
  WHEN OTHERS THEN
    raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
  END;
END kill_usersession;

The procedure checks if the executing username equals the to-be-killed username so that users can only terminate sessions of their own schema.

Advertisements