This discussion is archived
12 Replies Latest reply: Oct 16, 2013 2:06 PM by rp0428 RSS

ALTER SYSTEM KILL SESSION privilege

4071 Newbie
Currently Being Moderated
Hi All,

Is there any possibility to use this command from a without having DBA priviileges, what is the privilege to be grantd to the ordinary users to execute ALTER SYSTEM KILL SESSION... statement..

Thanks in advance
  • 1. Re: ALTER SYSTEM KILL SESSION privilege
    Laurent Schneider Oracle ACE
    Currently Being Moderated
    you can grant "ALTER SYSTEM"
  • 2. Re: ALTER SYSTEM KILL SESSION privilege
    358102 Newbie
    Currently Being Moderated
    Please exercise a lot of caution when granting the privilege to users especially on a production machine.
    That could cause a lot of problems. I would not grant such a privilege to any user on a production machine.
  • 3. Re: ALTER SYSTEM KILL SESSION privilege
    403551 Oracle ACE Director
    Currently Being Moderated
    SQL> GRANT ALTER SYSTEM TO <username>;
    
    Grant succeeded.
    
    SQL>
    Joel Pérez
    http://otn.oracle.com/experts
  • 4. Re: ALTER SYSTEM KILL SESSION privilege
    422218 Newbie
    Currently Being Moderated
    Create a packaged procedure which kills sessions, and grant execute priviliges on that.
  • 5. Re: ALTER SYSTEM KILL SESSION privilege
    Russ Cannon Newbie
    Currently Being Moderated
    I would not grant alter system to any user. The procedure method is the way to go, but make sure the procedure restricts which sessions can be killed.

    Kind regards,
    Russ Cannon
  • 6. Re: ALTER SYSTEM KILL SESSION privilege
    403551 Oracle ACE Director
    Currently Being Moderated
    The procedure proposed by Rusell would be more or less so:
    SQL> create or replace procedure kill_session
      2  ( v_sid number, v_serial number )
      3  as
      4  v_varchar2 varchar2(100);
      5  begin
      6  execute immediate 'ALTER SYSTEM KILL SESSION '''
      7  || v_sid || ',' || v_serial || '''';
      8  end;
      9  /
    
    Procedure created.
    
    SQL> select username, sid, serial# from v$session;
    
    USERNAME                              SID    SERIAL#
    ------------------------------ ---------- ----------
                                          147       5078
    SYS                                   148       6161
                                          151       6769
                                          156          1
                                          158          1
                                          159          1
    REPOS_OWNER                           161      14502
                                          163          1
                                          164          1
                                          165          1
                                          166          1
    
    USERNAME                              SID    SERIAL#
    ------------------------------ ---------- ----------
                                          167          1
                                          168          1
                                          169          1
                                          170          1
    
    15 rows selected.
    
    SQL>
    SQL> exec kill_session(161,14502);
    
    PL/SQL procedure successfully completed.
    
    SQL>
    Joel Pérez
    http://otn.oracle.com/experts
  • 7. Re: ALTER SYSTEM KILL SESSION privilege
    403551 Oracle ACE Director
    Currently Being Moderated
    and for granting the privilege to execute the procedure would be so:
    SQL> grant execute on kill_session to <username>;
    
    Grant succeeded.
    
    SQL>
    Joel Pérez
    http://otn.oracle.com/experts
  • 8. Re: ALTER SYSTEM KILL SESSION privilege
    Russ Cannon Newbie
    Currently Being Moderated
    DANGER! DANGER Will Robinson!

    There must be a check to prevent users from killing privileged DBA sessions:
    create or replace procedure kill_session
     (pn_sid    number
     ,pn_serial number)
    as
     lv_user varchar2(30);
    begin
     select username into lv_user from v$session where sid = pn_sid and serial# = pn_serial;
     if lv_user is not null and lv_user not in ('SYS','SYSTEM') then
      execute immediate 'alter system kill session '''||pn_sid||','||pn_serial||'''';
     else
      raise_application_error(-20000,'Attempt to kill protected system session has been blocked.');
     end if;
    end;
    /
    Note: The check for LV_USER IS NOT NULL is not strictly necessary because KILL SESSION will not kill non-user sessions, but I include it so as to raise my own error for those as well.

    You should add any DBA accounts that you have to the list of privileged users ('SYS','SYSTEM','MYDBA',etc.).

    For this code to work, you will have to create a direct select grant on sys.v_$session to the owner of this procedure as follows:
    grant select on v_$session to <owner>
    Where <owner> is the schema that owns the above procedure. This has to be a direct grant and not through a role.

    This does what you want and prevents users from clobbering DBA sessions. It also obviates the need for granting the dangerous 'alter system' privilege to non-DBAs.

    Kind regards,
    Russ Cannon
  • 9. Re: ALTER SYSTEM KILL SESSION privilege
    Laurent Schneider Oracle ACE
    Currently Being Moderated
    Hi,
    as you can read, most of the dba's here would not advise you to do that.

    On the one hand, "ALTER SYSTEM" privilege is MUCH SAFER then granting "DBA", the privileged user could not read/drop tables/tablespaces/database. So if you are granting this to a "Junior DBA", it is in my opinion acceptable.

    On the other hand, if you grant this to an applicative user, it means the security design of your database is bad. I would in this case oppose myself to such permissive access to the database. If it is needed to kill other sessions, find out why other sessions must be kill ! If the sessions are just "hanging" when middleware died, then watch out for "SQLNET.EXPIRE_TIME" in sqlnet.ora. If you want to prevent user from accessing the databases, maybe there is a way to "lock" the tables, or to make your transaction read-only, and also teach the users to make appropriate selection (like always selecting where txn_date < trunc(sysdate) in some reporting dbs).

    Kind regards
    Laurent Schneider
  • 10. Re: ALTER SYSTEM KILL SESSION privilege
    SweAnderline Explorer
    Currently Being Moderated
    So I guess you have to store the procedure in a dba account and add grant execution to it ? But executing this procedure would be like writing the command by your self - thus not making it possible because the user does not have the grant(alter system) to execute it ? Or am I wrong
  • 11. Re: ALTER SYSTEM KILL SESSION privilege
    jbaslercsa Newbie
    Currently Being Moderated

    Is it feasible to make your script compatible to kill a session across RAC nodes in 11i+ by making it compatible with the syntax:

         SQL> alter system disconnect session '323,519,@1' immediate;

         ?

     

    As it is I get this when I attempt:

         SQL> exec sys.kill_session(326,167,@1);
         BEGIN sys.kill_session(326,167,@1); END;

                                   *
         ERROR at line 1:
         ORA-06550: line 1, column 32:
         PLS-00103: Encountered the symbol "@" when expecting one of the following:
         ( - + case mod new not null <an identifier>
         <a double-quoted delimited-identifier> <a bind variable>
         continue avg count current exists max min prior sql stddev
         sum variance execute forall merge time timestamp interval
         date <a string literal with character set specification>
         <a number> <a single-quoted SQL string> pipe
         <an alternatively-quoted string literal with character set specification>
         <an alternatively

     

    When I do the old-fashioned way I get success:
         SQL> alter system disconnect session '326,167,@1' immediate;

         System altered.

  • 12. Re: ALTER SYSTEM KILL SESSION privilege
    rp0428 Guru
    Currently Being Moderated

    HIJACKED 9 YEAR OLD THREAD!

     

    Please don't hijack another user's thread for your own question.

    Is it feasible to make your script compatible to kill a session across RAC nodes in 11i+ by making it compatible with the syntax:

    Besides that the thread is NINE years old!

     

    If you have a question or issue create a new thread and provide the particulars of your use case. In your new thread you can add a link to this old thread if you think it contains info that will be useful to someone trying to help you.