How to give non-DBA user kill session privileges ?

Sometimes developers want to have ability to kill their own sessions.
Apparently it is not safe to let a non-DBA user to have a right to execute the “ALTER SYSTEM” command.

connect scott/XXXXX

select 
     sid,  
     serial#,  
     username,  
     machine,  
     program ,  
     (select sql_text  from v$sql b where b.sql_id=a.sql_id and rownum <= 1 ) current_sql_str,
     (select sql_text  from v$sql b where b.sql_id=a.prev_sql_id and rownum <= 1 ) previous_sql_str
from v$session  a
--   where 
--       username = 'SCOTT'
---  and event like '%enq%'
--   and machine like '%WORKGROUP%'
order by username, machine, sid  ;


SID     SERIAL#    USERNAME     MACHINE              PROGRAM               CURRENT_SQL_STR                          PREVIOUS_SQL_STR
4241    64469       SCOTT       DESKTOP-MONITOR      SQL Developer         select * from TB_SALE_ACCEPT_HIS        select count(*) from   TB_SALE_ACCEPT_HIS 
2573    42393       SALEAP      IK_APP_SER           JDBC Thin Client                                              select value$ from props$ where name = 'GLOBAL_DB_NAME'


  

begin
sys.sp_kill_dev_session( 4241 , 64469  )  ;
end ;
/

How to safely grant ALTER SYSTEM KILL SESSION privileg to non-DBA users

SQL> connect /as sysdba
Connected.



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;
/

Procedure created.


SQL> grant execute on sys.sp_kill_dev_session  to scott;


SQL> grant select on sys.v_$session to scott ;
 

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