| 限制用户只能KILL本用户的会话

限制用户只能KILL本用户的会话

作者：OoNiceDream【转载时请务必以超链接形式标明文章原始出处和作者信息】
链接：http://www.dbaroad.me/archives/2009/04/restrict_user_kill_session.html

在yangtingkun的BLOG上看到了“限制用户只能KILL本用户会话”的文章
正好有这个需求，收藏了一下。文中使用了两个存储过程来实现：

CREATE OR REPLACE PROCEDURE P_KILL_SESSION(P_USER IN VARCHAR2,
                                           P_SID  IN VARCHAR2) AS
  V_SQL VARCHAR2(32767);
BEGIN
  SELECT 'ALTER SYSTEM KILL SESSION ''' || SID || ',' || SERIAL# || ''''
    INTO V_SQL
    FROM V$SESSION
   WHERE USERNAME = P_USER
     AND SID = P_SID;
  EXECUTE IMMEDIATE V_SQL;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RAISE_APPLICATION_ERROR(-20001,
                            'SID： ' || P_SID ||
                            ' DOES NOT EXISTS, OR THE SESSION USER IS NOT ' ||
                            P_USER);
END;
 
CREATE OR REPLACE PROCEDURE P_KILL_USER_SESSION(P_SID IN NUMBER) AUTHID CURRENT_USER AS
  V_USERNAME VARCHAR2(30);
  V_SID      NUMBER;
BEGIN
  SELECT SYS_CONTEXT('USERENV', 'SESSION_USER'),
         SYS_CONTEXT('USERENV', 'SID')
    INTO V_USERNAME, V_SID
    FROM DUAL;
  IF P_SID != V_SID THEN
    P_KILL_SESSION(V_USERNAME, P_SID);
  ELSE
    RAISE_APPLICATION_ERROR(-20000, 'CAN NOT KILL CURRENT SESSION!');
  END IF;
END;

测试时发现，9i中SYS_CONTEXT没有SID这个参数，为10G新增：

SYS@TADBA> select * from v$version
  2  where rownum&lt;2;
 
BANNER
---------------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
 
SYS@TADBA> select SYS_CONTEXT('USERENV', 'SID') from dual;
select SYS_CONTEXT('USERENV', 'SID') from dual
*
ERROR 位于第 1 行:
ORA-02003: invalid USERENV parameter
 
 
SYS@TADBA> select SYS_CONTEXT('USERENV', 'SESSION_USER') from dual;
 
SYS_CONTEXT('USERENV','SESSION_USER')
---------------------------------------------------------------------
SYS
 
10G中：
SYS@TSDB1> select * from v$version
  2  where rownum&lt;2;
 
BANNER
-----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
 
SYS@TSDB1> select SYS_CONTEXT('USERENV', 'SID') from dual;
 
SYS_CONTEXT('USERENV','SID')
-----------------------------------------------------------------
5695

改用了：

  SELECT MAX(SID)
    INTO V_SID
    FROM V$MYSTAT;

另外，打算增加一点审计信息，记录杀会话的时间，杀会话者的主机名、IP、OS用户名及被杀会话的相关信息，防止扯皮发生嘛：

create table KILL_SESSION_MSG tablespace PERFSTAT as
select SYSDATE K_TIME,
       SYS_CONTEXT('USERENV', 'OS_USER') K_OS_USER,
       SYS_CONTEXT('USERENV', 'HOST') K_HOST,
       SYS_CONTEXT('USERENV', 'IP_ADDRESS') K_IP,
       'sid: ' || sid || '||username: ' || username || '||OSUSER: ' ||
       OSUSER || '||HASH_VALUE: ' || SQL_HASH_VALUE || '||logon_time: ' ||
       logon_time || '||MACHINE: ' || MACHINE "Message"
  from v$session where 1 < 0;
 
create public synonym KILL_SESSION_MSG for sys.KILL_SESSION_MSG;
 
grant select,insert on KILL_SESSION_MSG to public;

修改后如下：

CREATE OR REPLACE PROCEDURE P_KILL_SESSION(P_USER IN VARCHAR2,
                                           P_SID  IN VARCHAR2) AS
  V_SQL VARCHAR2(32767);
BEGIN
  SELECT 'ALTER SYSTEM KILL SESSION ''' || SID || ',' || SERIAL# || ''''
    INTO V_SQL
    FROM V$SESSION
   WHERE USERNAME = P_USER
     AND SID = P_SID
     AND TYPE = 'USER';
  EXECUTE IMMEDIATE V_SQL;
  EXECUTE IMMEDIATE 'alter session set nls_date_format="YYYY-MM-DD HH24:MI:SS"';
  INSERT INTO KILL_SESSION_MSG
  select SYSDATE K_TIME,
       SYS_CONTEXT('USERENV', 'OS_USER') K_OS_USER,
       SYS_CONTEXT('USERENV', 'HOST') K_HOST,
       SYS_CONTEXT('USERENV', 'IP_ADDRESS') K_IP,
       'sid: ' || sid || '||username: ' || username || '||OSUSER: ' ||
       OSUSER || '||HASH_VALUE: ' || SQL_HASH_VALUE || '||logon_time: ' ||
       logon_time || '||MACHINE: ' || MACHINE "Message"
  from v$session
  WHERE USERNAME = P_USER
     AND SID = P_SID
     AND TYPE = 'USER';
  COMMIT;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RAISE_APPLICATION_ERROR(-20001,
                            'SID#: ' || P_SID ||
                            ' DOES NOT EXISTS, OR THE SESSION USER IS NOT ' ||
                            P_USER);
END;
 
 
CREATE OR REPLACE PROCEDURE P_KILL_USER_SESSION(P_SID IN NUMBER) AUTHID CURRENT_USER AS
  V_USERNAME VARCHAR2(30);
  V_SID      NUMBER;
BEGIN
  SELECT SYS_CONTEXT('USERENV', 'SESSION_USER')
    INTO V_USERNAME
    FROM DUAL;
  SELECT MAX(SID)
    INTO V_SID
    FROM V$MYSTAT;
  IF P_SID != V_SID THEN
    P_KILL_SESSION(V_USERNAME, P_SID);
  ELSE
    RAISE_APPLICATION_ERROR(-20000, 'CAN NOT KILL CURRENT SESSION!');
  END IF;
END;
 
create public synonym P_KILL_USER_SESSION for sys.P_KILL_USER_SESSION;

测试：
会话一：

SQL> show user
USER is "A1"
SQL> select max(sid) from v$mystat;
 
  MAX(SID)
----------
        17
 
SQL>

会话二：

SQL>show user
USER 为"A1"
SQL>select max(sid) from v$mystat;
 
  MAX(SID)
----------
        24
 
SQL>exec p_kill_user_session(24);
BEGIN p_kill_user_session(24); END;
 
*
ERROR 位于第 1 行:
ORA-20000: CAN NOT KILL CURRENT SESSION!
ORA-06512: at "SYS.P_KILL_USER_SESSION", line 14
ORA-06512: at line 1
 
 
SQL>exec p_kill_user_session(17);
 
PL/SQL 过程已成功完成。
 
SQL>select * from KILL_SESSION_MSG;
 
K_TIME              K_OS_USER  K_HOST     K_IP       Message
------------------- ---------- ---------- ---------- ------------------------------
2009-04-01 22:39:15 NiceDream  WORKGROUP\ 134.98.83. sid: 17||username: A1||OSUSER:
                               OONICEDREA 5           oracle||HASH_VALUE: 140102237
                               M                     4||logon_time: 2009-04-01 22:3
                                                     8:17||MACHINE: erdb-p01

会话一：

SQL> select * from dual;
select * from dual
*
ERROR at line 1:
ORA-00028: your session has been killed

--- The End ---

关键字: 基础知识

上一篇 DML操作记录在UNDO中的信息

实例2上语句长时间等待某个块下一篇

喜欢DBARoad的文章，那就通过 RSS Feed 功能订阅阅读吧！

才1个评论

1楼
四月 7th, 2009 at 16:22 Douttirllusia 说:
Great site this http://www.dbaroad.me and I am really pleased to see you have what I am actually looking for here and this this post is exactly what I am interested in. I shall be pleased to become a regular visitor

[回复]

公告牌:

当前位置: DBARoad > 基础知识 > 文章正文

限制用户只能KILL本用户的会话

相关文章

随机文章

才1个评论

我要评论

相关文章

最新文章

订阅

站内搜索