How to give oracle kill own session privilege to Developers

Often developers ask how we can kill our own session as they dont want to wait for the DBA to respond and kill. They want the oracle kill own session privilege. We dont have any privilege like alter system kill session which can be given to the user. We can give alter system privilege but it will other grants also which Oracle DBA don’t want . This bottleneck can be achieved by creating a procedure and then giving execute on that procedure to the user . This way developer will be more independent and DBA will also get time to work on other priorities

Suppose we want to give kill session access to the user SCOTT to kill its own session

CREATE OR REPLACE PROCEDURE sys.kill_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 User Session has been Blocked.');
END IF;
END kill_session;
/

or 
create or replace procedure kill_session( p_sid in number, p_serial# in number)
 is
 v_count pls_integer;
 BEGIN
 select count(*) into v_count
 from V$session
 where username = 'SCOTT'
 and sid = p_sid
 and serial# = p_serial# ;

 if ( v_count = 1 )
 then
 execute immediate '
 alter system kill session ''' ||
 to_char(p_sid,'999999')||','||
 to_char(p_serial#,'999999')||'''';
 else
 raise_application_error( -20001,
 'You do not own session ''' ||
 p_sid || ',' || p_serial# ||
 '''' );
end if;
 END;
 /
grant execute on kill_session to SCOTT;

User can get the session information using the below query

select sid,serial#, Username ,module from v$session  where username='SCOTT';

He can identify the session to be killed and then execute the command

 set serveroutput on
 exec sys.kill_session(<sid>, <serial#>);

He will not be able to kill any session not owned and will get the error message

SQL> exec kill_session(1,2);
 BEGIN kill_session(1,2); END;
 *
 ERROR at line 1:
 ORA-20001: Session has Expired or Invalid sid/serial Arguments Passed
 ORA-06512: at "KILL_SESSION", line 14
 ORA-06512: at line 1
 SQL>  exec apps.kill_session(7522,58115);
 BEGIN apps.kill_session(7522,58115); END;
 *
 ERROR at line 1:
 ORA-20002: Unauthorized Attempt to Kill a Non User Session has been Blocked.
 ORA-06512: at "KILL_SESSION", line 18
 ORA-06512: at line 1

If you want to deploy the code where in any user can use the procedure the kill its own session, we can execute below steps

CREATE OR REPLACE PROCEDURE sys.kill_session(p_sid NUMBER, p_serial NUMBER)
 AS
 v_count pls_integer;
 run_by_user VARCHAR2(30);
 BEGIN
 SELECT SYS_CONTEXT ('USERENV', 'SESSION_USER') 
    into run_by_user  FROM DUAL;
 select count(*) into v_count
  from V$session
  where username = run_by_user
  and sid = p_sid
  and serial# = p_serial ;
 if ( v_count = 1 )
 then
 EXECUTE IMMEDIATE 'ALTER SYSTEM KILL SESSION ''' || p_sid || ',' || p_serial || '''';
 ELSE
 RAISE_APPLICATION_ERROR(-20002,'Unauthorized Attempt to Kill a Non User Session has been Blocked.');
 END IF;
 END kill_session;
 /

grant execute on  kill_session to Public

Now all user has the access to this Procedure and They can kill there own session only. They will not be able to kill any other user session and will get error message if he tries to execute that. Here is the summary of the process

I execute a small test to demonstrate the procedure usage so that there is no doubt and developers can use this procedure flawlessly. For this I created two session with the user HR with sqlplus utility

Session 1
sqlplus hr/<hr pass>
select * from dual;

SLeep 30

Session 2
 sqlplus hr/<hr pass>
select * from dual;

Now suppose user HR wants to kill the first session. So he can execute the below query in the second session

SQL> select sid,serial# ,last_call_et from V$session where username='HR';
    SID    SERIAL# LAST_CALL_ET
 
    542      40259            0   
   7277      51382          946

Now he knows from last_call_et column,he wants to kill the session (7277,51382).So he can execute the kill_session command as

exec sys.kill_session( 7277,51382 );
PLSQL completed successfully

This will killed the session. Now if we execute any sql on killed session ,it will throw error

SQL> select  * from dual;
 select  * from dual
 *
 ERROR at line 1:
 ORA-00028: your session has been killed

These all steps can be done with Oracle Sql developer tool also. I hope you like this content on How to give oracle kill own session privilege to the developer for increase in productivity and reduce the wastage of time

Related Articles
Active Session History :Check out about Active Session History ,how it is configured,how to find performance bottleneck using ASH, ASH report generation,ASH queries
Oracle Database Health check :check out important Oracle Database Health check for troubleshooting problems, unusual behavior in oracle database
Scripts to check locks in oracle database : Scripts to check locks in oracle database, unlock oracle table, find session holding oracle table locks, check lock on package
How to check temp tablespace in Oracle : check out How to check temp tablespace in Oracle, How to resize the tempfile, how to drop the tempfile,How to check the temp usage by Session
Query to get session details in oracle : Check out How to find session details in Oracle ,how to get the sql text from sid in oracle,how to find sql query using sql_id in oracle

External Resource
Terminating Sessions and Cleaning Up Processes

Related Posts

Leave a Comment Cancel Reply