f Oracle 非最高權限怎麼砍session ~ 迪貝之家

Oracle 非最高權限怎麼砍session

組織分工下,通常不會把kill session的權限給AP 單位,但有時會緊急接到要砍資料庫連線的需求,不過DBA又不是英英美代只,隨時待命準備幫你砍session,只好寫了一隻store procedure 給AP單位來自行砍session,但這只是應急,奉勸還是要找到根本原因,想辦法去解決問題,那才是做事情的基本道理

create or replace procedure kill_session(sid_in varchar2, serial_num_in varchar2) authid definer
as
  not_allowed  exception;
  killed exception;
  ses_rec v$session%rowtype;
  v_file  UTL_FILE.FILE_TYPE;
  kill_log_file varchar2(100) := '.kill_ses_'||to_char(sysdate,'YYYYMMDD')||'.log';
  current_session_user varchar2(100);
  client_host          varchar2(100);
  client_ip            varchar2(100);
  client_terminal      varchar2(100);
  client_module        varchar2(100);
  msg varchar2(100);
begin


    execute immediate('create or replace directory kill_sess_log_dir as ''/tmp''');
   select * into ses_rec
   from v$session
   where sid = sid_in
   and   serial# = serial_num_in;

 if ses_rec.username  in ( 'SYS','SYSTEM','DBSNMP','SYSMAN') or ses_rec.username is null
   then
       raise not_allowed;
   end if;
 if ses_rec.status = 'KILLED'
   then
       raise killed;
   end if;
 
execute immediate 'alter system kill session ''' || sid_in || ', ' || serial_num_in || '''' ;

   SELECT SYS_CONTEXT ('USERENV', 'SESSION_USER'),
          SYS_CONTEXT('USERENV','HOST'),
  SYS_CONTEXT('USERENV','IP_ADDRESS'),
  SYS_CONTEXT('USERENV','TERMINAL'),
  SYS_CONTEXT('USERENV','MODULE')
  into current_session_user ,
               client_host,
   client_ip ,
   client_terminal ,
   client_module
  FROM DUAL;


 v_file := UTL_FILE.FOPEN(location     => 'KILL_SESS_LOG_DIR',
                           filename     => kill_log_file,
                           open_mode    => 'a',
                           max_linesize => 32767);
msg := to_char(sysdate,'YYYYMMDDHH24MISS')||','||current_session_user||','||client_host||','||client_ip||','||client_terminal ||','||sid_in||','||serial_num_in||','||client_module ;
 utl_file.put_line(v_file,msg);
 utl_file.fclose(v_file);

 exception
    when no_data_found
    then
    dbms_output.put_line('Your input find no session');
   when not_allowed
   then
    dbms_output.put_line('Hey! You are not allowed to kill this session.');
   when killed
   then
    dbms_output.put_line('This session has already been killed.');
   when others
     then
     declare
       error_code  number := SQLCODE;
       error_msg   varchar2(300) := SQLERRM;
      begin
        if error_code != 0 then
             dbms_output.put_line(error_msg);
      if   UTL_FILE.IS_OPEN(v_file) then
       utl_file.fclose(v_file);
  end if;
         end if;
      end;
end;
/