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.
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.