How to kill session from non-DBA User / Grant kill session privilege to non-DBA user.

ALTER SYSTEM privilege is required to kill session, it is not safe to give ALTER SYSTEM privilege to non-DBA user, but when you have to give kill session privilege to non-DBA you can create procedure to kill session and grant them to execute.


SQL> CREATE OR REPLACE PROCEDURE SP_KILL_SESSION (P_SID NUMBER, P_SERIAL NUMBER)
AS
  2    3     V_USER_NAME   VARCHAR2 (30);
  4  BEGIN
  5     SELECT USERNAME
  6       INTO V_USER_NAME
  7       FROM V$SESSION
  8      WHERE SID = P_SID AND SERIAL# = P_SERIAL;
  9
 10     IF V_USER_NAME IN ('DEVUSER')
 11     THEN
 12        EXECUTE IMMEDIATE
 13           'ALTER SYSTEM KILL SESSION ''' || P_SID || ',' || P_SERIAL || '''';
 14     ELSIF V_USER_NAME IS NULL
 15     THEN
      RAISE_APPLICATION_ERROR (-20001, 'Invalid Session ID');
 16   17     END IF;
 18  END SP_KILL_SESSION;
 19  /

Procedure created.

SQL> grant execute on sys.SP_KILL_SESSION to devuser;

Grant succeeded.

SQL>
SQL> CREATE SYNONYM DEVUSER.SP_KILL_SESSION FOR SYS.SP_KILL_SESSION;

Synonym created.

########### Now login into devuser schema ############

SQL> conn devuser/devuser
Connected.
SQL>
SQL> SELECT SID, SERIAL# FROM V$SESSION
WHERE USERNAME='DEVUSER';  2

       SID    SERIAL#
---------- ----------
      3978      49965

########### Open another terminal and login into devuser schema ############

SQL> SELECT SID, SERIAL# FROM V$SESSION
WHERE USERNAME='DEVUSER';   2

       SID    SERIAL#
---------- ----------
      3418      29112
      3978      49965

SQL> exec SP_KILL_SESSION(3418,29112);

PL/SQL procedure successfully completed.

SQL>
SQL> SELECT SID, SERIAL# FROM V$SESSION
WHERE USERNAME='DEVUSER';  2

       SID    SERIAL#
---------- ----------
      3978      49965