Procedure to kill a session

On the Oracle-L mailing list a user requested a procedure to allow a developer to kill his own process. This contributed quite a few comments which I will list below and I posted a method that I use when I have the same requirement.

To create the stored procedure logon as a sysdba through sqlplus and explicitly grant the required rights to system and then create the procedure.

grant alter system to system;
grant select on sys.v_$session to system;

create or replace procedure system.killsession (killsid IN VARCHAR2)
as
killserial   varchar2(20);

begin
killserial:='none';
select serial#
into killserial
from sys.v_$session
where sid = killsid
and username = 'WES';

execute immediate 'alter system kill session ''' || to_char (killsid) || ', ' || to_char (killserial) || '''' ;

EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('The SID ' || killsid || ' does not exist or cannot be killed');

end ;


Grant execute on the procedure to the user
For example, userxxx needs access to kill sessions so you would type the following:-
grant execute on system.killsession to userxxx;
The user needs to know the sid of the session being killed. For example, if a user needs to kill a session with a SID of 140 they would type:-
set serveroutput on
exec system.killsession ('140');
If they get the following error then there is no SID with a username of WES currently connected:-
The SID 140 does not exist or cannot be killed
  The various thoughts on allowing such a procedure centred on two strands, security and practical considerations
 
Security issues

Why do they need to kill jobs, what is causing the issue

What will developers want next

Sarbanes-Oxley considerations

Practical

How do you know the developer will kill one of his own processes and not a co-worker (think apps or sysadm job)

Can you use resource profiles to limit CPU usage

Use a standby to allow unrestricted resource usage

My view is that in certain development environments, for certain users it is reasonable to allow them to kill a process that is running rogue and as such I am happy to provide such a facility. This would not be allowed either unrestricted or in any sort of controlled environment such as production and pre-production.