Thursday, December 22, 2016

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

3 comments:


  1. very interesting article I feel very enthusiastic while reading and the information
    provided in this article is so useful for me. Content in this article guides in clarifying some of my doubts.

    oracle fusion financials training
    oracle fusion financials online training
    oracle fusion Procurement training training
    oracle fusion procurement online training

    ReplyDelete
  2. hi, thanks for this articule, but, is not working with me. which privilege must be given to the user "devuser"?,how can the user devuser make an alter system?.
    Thanks!!

    ReplyDelete