Allow a user to kill certain sessions without giving them the alter system privilege

This procedure will give the user the ability to kill most Oracle sessions without giving them the alter system privilege.

This procedure will allow you to give a user the ability to kill any session other than SYS, SYSTEM, DBSNMP, REPADMIN, OUTLN and the background processes without giving them the alter system privilege.

Here are the administrative instructions to install and run the kill_session procedure:

1. Connect as sys. Compile/create the procedure.

2. Grant the user the ability to execute the procedure:

grant  execute on sys.kill_session to <username>;

3. Create a synonym for the sys.kill_session:

create synonym <username>.kill_session for sys.kill_session; 

4. Optional testing. Then you can execute the procedure.

5. Connect to the database with the user you set up the grant for:

set serveroutput on size 1000000
execute kill_session('<LIST|<SID value>>); 

To see output from the execute command, you must issue the set serveroutput on, before executing the procedure. The kill_session procedure takes one parameter. It can either be:

  • LIST - Lists the SID, DB user, O/S user, application that you can kill. List is case insensitive.
  • <SID> The SID number that you get from the list. This user will be killed.

When LIST is the parameter you will receive output like this:

SID, DB User, O/S user, Application 
===================================== 
15, TEST, Administrator, sqlplus.exe 

When <SID> is the parameter you will receive output like this:

User TEST with SID 15 was killed successfully. 
The O/S Process ID to kill is 2407. 

Note: If the application that started the session is still running, you can either exit the application or go out to the UNIX side and kill it. We have provided you the PID.

We have implemented error handling to account for incorrect parameters. Either way, you must pass the procedure one and only one parameter or it will error out. If you enter in an invalid SID like ABC123 or abc, you will get this exception:

Invalid parameter. 
Syntax: execute kill_session('<LIST|<SID value>>'); 

If you enter in a SID that does not exist or a SID that belongs to SYS, SYSTEM, DBSNMP,REPADMIN, OUTLN, you will get this exception:

<SID ENTERED> is not a valid SID.  Please try another one. 

Here's the code:

create or replace procedure kill_session(p_1 in varchar2) 
is 

v_1      varchar2(64) := upper(p_1); 
v_sid    varchar2(254) := null; 
v_sid2   v$session.sid%TYPE := 0; 
v_sn     v$session.serial#%TYPE := 0; 
cid      integer := 0; 
v_pid    v$process.spid%TYPE := 0; 
v_sqlstr varchar2(254) := null; 
v_usr    v$session.username%TYPE := null; 
rows_processed integer := 0; 

BEGIN 

if v_1 = 'LIST' then 

   dbms_output.put_line('SID, DB User, O/S user, Application' || chr(13) || '====================================='); 
   
   for v_sid in (select sid,username, osuser, program from V$SESSION where type != 'BACKGROUND' and username not in ('SYS', 'SYSTEM', 'DBSNMP', 'REPADMIN', 'OUTLN') and status != 'KILLED' and sid != (select sid from V$SESSION where audsid = (select userenv('sessionid') from dual)))

   loop 
        dbms_output.put_line(v_sid.sid || ', ' || v_sid.username || ', ' || v_sid.osuser || ', ' || v_sid.program); 
   end loop; 

elsif v_1 is null or v_1 = '' then 

   raise VALUE_ERROR; 

else 

   v_sid2 := to_number(v_1); 

   select serial#,username into v_sn,v_usr from V$SESSION 
   where type != 'BACKGROUND' 
   and username not in ('SYS', 'SYSTEM', 'DBSNMP', 'REPADMIN', 'OUTLN') 
   and status != 'KILLED' 
   and sid != (select sid from V$SESSION 
   where audsid = (select userenv('sessionid') from dual)) 
   and sid = v_sid2;
  
   if SQL%FOUND then 
      select spid into v_pid from v$session s, v$process p 
      where s.username = v_usr and sid = v_sid2 and s.paddr = p.addr;

      v_sqlstr := 'ALTER SYSTEM KILL SESSION ''' || v_sid2 || ',' || v_sn || ''''; 

      cid := Dbms_Sql.Open_Cursor; 

      Dbms_Sql.parse(cid, v_sqlstr, Dbms_Sql.Native); 

      rows_processed := dbms_sql.execute(cid); 

      DBMS_SQL.CLOSE_CURSOR(cid); 

      dbms_output.put_line('User ' || v_usr || ' with SID ' || v_sid2 || ' was killed successfully.' || chr(13) || 'The O/S Process ID to kill is ' || v_pid || '.');

   end if; 

end if; 

exception 
   when NO_DATA_FOUND then 
      dbms_output.put_line(v_1 || ' is not a valid SID.  Please try another one.'); 
   when INVALID_NUMBER or VALUE_ERROR then 
      dbms_output.put_line('Invalid parameter.' || chr(13) || 'Syntax: execute kill_session(''<LIST|<SID value>>'');'); 
   when others then 
      dbms_output.put_line('kill_session() encountered error ' || to_char(SQLCODE) || '.  Please try again.'); 

END; 
/ 

Reader Feedback

Mona E. writes: I can't get this procedure to work. The list function does not list the session:

SQL> exec kill_session ('LIST 23'); 
Invalid parameter.
Syntax: execute kill_session('>'); PL/SQL procedure successfully completed. 

Gaurav J. writes: This procedure can be customised to give users the facility (and restrict them) to kill sessions started from their own terminal and for their own username, using the userenv fuction.

For More Information

  • What do you think about this tip? E-mail the Editor at tdichiara@techtarget.com with your feedback.
  • The Best Oracle Web Links: tips, tutorials, scripts, and more.
  • Have an Oracle tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
  • Ask your technical Oracle questions--or help out your peers by answering them--in our live discussion forums.
  • Check out our Ask the Experts feature: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.

This was first published in May 2002

Dig deeper on Oracle DBA jobs, training and certification

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close