RE: [Q] privilege allow user kill session???

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Wed, 26 Sep 2001 10:08:42 -0700
Message-ID: <F001.0039974A.20010926093034@fatcity.com>

>>From: Jacques Kilchoer
>>
>>Write a procedure that kills the relevant session(s) and grant the user
>>execute access to that procedure.
>>
>-----Original Message-----
>From: ef 8454 [mailto:ef8454_at_hotmail.com]
>
>Thank you for the answer.  Do you have sample program?

 

The OWNER of the procedure will need the following privileges granted DIRECTLY, NOT via a role. "select on sys.v_$session" or "select any table" "alter system"

This procedure will allow you to kill any session that has the same username as your current username. The user EXECUTING the procedure will need some way to find sid and serial# for their various sessions, to know which one to kill. (For Oracle versions that don't support execute immediate, change the procedure to use dbms_sql)

create procedure kill_your_session (in_sid in sys.v_$session.sid%type,

                                    in_serial# in sys.v_$session.serial#%type)

as
   row_count pls_integer ;
begin

   select count (*)
   into row_count
   from v$session
   where username = user and sid = in_sid and serial# = in_serial# ;
   if row_count > 0
   then
      execute immediate 'alter system kill session ''' ||
        to_char (in_sid) || ', ' || to_char (in_serial#) || '''' ;
   end if ;

end ;
/ Received on Wed Sep 26 2001 - 12:08:42 CDT