Re: "kill" equivalent for ORACLE?
Date: 1996/04/16
Message-ID: <3173C769.7EB2_at_CAPITAL.GE.COM>#1/1
badri wrote:
>
> I want to provide an interface that would allow
> a user to kill their own oracle session, but not those of the others.
> If I grant 'alter system' privilege, it allows a user to
> kill any other user's process.
> Any thoughts would be greatly appreciated,
>
> thanks a lot,
>
> -Badri
We wrote a procedure which kills all other sessions for a user (other than their current one). Our applications call it when the user connects to be sure any orphan sessions are removed. The procedure is owned by a system userid which has the ALTER SYSTEM privilege. Any user can call it to kill THEIR other sessions. The code follows:
create procedure spr_s_remove_users as
/*
This procedure kills all other sessions for the calling user.
Note that the owner of this procedure must have been explicitly
granted the system privilege ALTER SYSTEM.
*/
id1 varchar2(30);
c1 integer;
sid2 number;
serial2 number;
cursor c2 is select sid, serial# from v$session
where username=user
and sid <> (select sid from v$session where
userenv('SESSIONID') = audsid);
return1 integer;
begin
open c2;
loop
fetch c2 into sid2,serial2;
exit when c2%notfound;
c1:=dbms_sql.open_cursor;
dbms_sql.parse(c1,'alter system kill session ''' || to_char(sid2) || ','
|| to_char(serial2) || '''',dbms_sql.v7);
return1 := dbms_sql.execute(c1);
dbms_sql.close_cursor(c1);
end loop;
end;
Hope this helps.
-- Roger Harris Oracle DBA GE Credit Corp Danbury, CTReceived on Tue Apr 16 1996 - 00:00:00 CEST