7 Replies Latest reply: Apr 18, 2013 7:29 AM by EdStevens RSS

    Killing particular session coming from particular host

    AmitE.
      Hi All,

      I am trying to create a scheduled job which kills a session established from a particular host with a set of DB schemas. I have written below procedure and scheduled it in DB scheduler. Its working as expected but just would like know if there are any better ways of doing it -

      ================================================
      DECLARE

      LV_STATEMENT varchar2(4000);
      LV_UPDATE varchar2(4000);
      LV_SID number;
      LV_SERIAL number;
      LV_INSTID number;
      LV_FLAG number;

      begin
      insert into session_kill (sid, serial#, inst_id, osuser, username, logon_time, program, machine, kill_flag)
      select sid, serial#, inst_id, osuser, username, logon_time, program, machine,'N' from gv$session where machine in ('WORKGROUP\P181VWCERT01','P182VWCERT01')
      and username in (select * from APPSUSERS) and status in ('ACTIVE','INACTIVE');
      --APPSUSERS contains the schemas to be checked.
      commit;
      select count(*) into LV_FLAG from session_kill where kill_flag='N';
      if LV_FLAG > 0 then
      for i in 1..LV_FLAG LOOP
      select sid into lv_sid from session_kill where kill_flag='N' and rownum=1;
      select serial# into lv_serial from session_kill where kill_flag='N' and rownum=1;
      select inst_id into lv_instid from session_kill where kill_flag='N' and rownum=1;
      LV_STATEMENT := 'alter system kill session '''||lv_sid||','||lv_serial||',@'||lv_instid||'''';
      LV_UPDATE := 'update session_kill set kill_flag=''Y'' where sid='||lv_sid||' and serial#='||lv_serial||' ';
      -- dbms_output.put_line(LV_STATEMENT);
      EXECUTE IMMEDIATE LV_STATEMENT;
      EXECUTE IMMEDIATE LV_UPDATE;
      commit;
      --dbms_output.put_line(LV_UPDATE);
      END LOOP;
      end if;
      --EXCEPTION
      --WHEN OTHERS THEN
      --ROLLBACK;
      end;
      ===================================================================

      Any suggestions are highly appreciated.

      Regards,
      Amit
        • 1. Re: Killing particular session coming from particular host
          Osama_Mustafa
          what about sqlnet.ora adding TCP.EXCLUDED_NODES
          check
          http://osamamustafa.blogspot.com/2012/06/limit-access-to-your-database.html
          • 2. Re: Killing particular session coming from particular host
            Antonio Navarro
            Maybe create a profile limiting CPU use, or more advanced using DRM.

            HTH
            Antonio NAVARRO RABANO
            • 3. Re: Killing particular session coming from particular host
              AmitE.
              Thanks Mustafa,

              Actually i dont want to kill all the sessions coming from these nodes. The procedure will kill only those sessions connecting to database with usernames specified in APPSUSERS table. This table contains some of the DB schemas which should not be used from particular nodes.

              Regards,
              Amit
              • 4. Re: Killing particular session coming from particular host
                EdStevens
                Amit E. wrote:
                Thanks Mustafa,

                Actually i dont want to kill all the sessions coming from these nodes. The procedure will kill only those sessions connecting to database with usernames specified in APPSUSERS table. This table contains some of the DB schemas which should not be used from particular nodes.

                Regards,
                Amit
                If you want suggestions for the best solution it would be better if you stated the business problem to be solved, rather than describing some pre-conceived (and often ill-conceived) technical solution.

                So from what you've said so far, you want to kill certain sessions simply based on what account was used to connect and what machine they are coming from. But your perceived solution actually allows them to connect and run for some period of time until such time as your scheduled procedure identifies them and kills them. So you are going to actually let them in and possibly do whatever damage you fear in the time between their logging on and your scheduled process runs. Have I got that right?

                Also, why would you want to allow a certain user to connect from machine A but not from machine B?
                • 5. Re: Killing particular session coming from particular host
                  AmitE.
                  Hi EdStevens,

                  Here is the problem to be solved -

                  Our Application team members know the db application schema passwords to connect to the database. The connections to the database can only be made using certain machines (called jumphosts where tools like sqlplus, toad, sqldeveloper are installed) and application servers. The application team members can not logon to the application servers because they do not have OS logon credentials on these application servers.
                  By right the app team members should use their own DB IDs (which have limited access) to connect to the database from jumphosts but if someone logs in using application schema accounts we want to terminate this session.
                  We have scheduled the pl/sql block given in my first update in database scheduler to run every 10 seconds so if someones connects to database from jumphost using Application schema accounts his/her session gets killed. so within 10 secods its highly unlikely to perform the actions which they should not. I hope that I have answered your question "why would you want to allow a certain user to connect from machine A but not from machine B?"
                  If at all there is any need to logon using Application database schema account we disable this job during that time.

                  I know that easiest solution for this is to change application schema account but lets keep that aside as there is different story for not changing the passwords.

                  Regards,
                  Amit
                  • 6. Re: Killing particular session coming from particular host
                    DK2010
                    Hi,

                    Why not you go for the Trigger Which can only trigger when your condition satisfied rather then monitoring always
                    • 7. Re: Killing particular session coming from particular host
                      EdStevens
                      Amit E. wrote:
                      Hi EdStevens,

                      Here is the problem to be solved -

                      Our Application team members know the db application schema passwords to connect to the database. The connections to the database can only be made using certain machines (called jumphosts where tools like sqlplus, toad, sqldeveloper are installed) and application servers. The application team members can not logon to the application servers because they do not have OS logon credentials on these application servers.
                      By right the app team members should use their own DB IDs (which have limited access) to connect to the database from jumphosts but if someone logs in using application schema accounts we want to terminate this session.
                      We have scheduled the pl/sql block given in my first update in database scheduler to run every 10 seconds so if someones connects to database from jumphost using Application schema accounts his/her session gets killed. so within 10 secods its highly unlikely to perform the actions which they should not. I hope that I have answered your question "why would you want to allow a certain user to connect from machine A but not from machine B?"
                      If at all there is any need to logon using Application database schema account we disable this job during that time.

                      I know that easiest solution for this is to change application schema account but lets keep that aside as there is different story for not changing the passwords.

                      Regards,
                      Amit
                      As DK2010 said, this would be better done via triggers. Then you don't have to have a job start every few seconds, and they never get in at all. The trigger will have to identify what machine they are coming from (hostname? IP address?) . Rather than keeping up with all of the hosnames or IP address to block, I'd just code the list of acceptable addresses, and block anything else. And I wouldn't hard-code that in the procedure, but put the list of acceptable hosts in a lookup table - with update access (and possibly even read access, though that may be hard to do given the proliferation of SELECT ANY TABLE privileges) to that table limited to a short list of DBAs.