【DB笔试面试701】在Oracle中，如何让普通用户可以杀掉自己用户的会话？

小麦苗DBA宝典

发布于 2019-12-17 11:34:12

1.2K0

发布于 2019-12-17 11:34:12

文章被收录于专栏：小麦苗的DB宝专栏小麦苗的DB宝专栏

♣

题目部分

在Oracle中，如何让普通用户可以杀掉自己用户的会话？

♣

答案部分

普通用户想要杀掉会话必须要具有ALTER SYSTEM的权限，但是由于该权限过大，用户可能使用该权限错杀其他用户的会话，所以，有没有其它办法可以实现该功能呢？该类问题也是DBA工作中常遇到的问题，下面作者给出一种解决方案。

首先，可以创建一个查询自己会话信息的视图，将该视图创建公共同义词，然后创建一个存储过程，该存储过程实现杀掉会话的需要，最后将该存储过程的执行权限赋权给PUBLIC即可解决这个问题。

代码实现过程如下所示：

CREATE OR REPLACE VIEW VW_MYOWNERSESSION_LHR AS
SELECT * FROM V$SESSION WHERE USERNAME = USER;
CREATE OR REPLACE PUBLIC SYNONYM SYN_MYOWNERSESSION_LHR FOR SYS.VW_MYOWNERSESSION_LHR;

创建存储过程用于杀掉会话：

CREATE OR REPLACE PROCEDURE PRO_KILL_MYOWN_SESSION_LHR(P_INST    IN NUMBER,
                                                       P_SID     IN NUMBER,
                                                       P_SERIAL# IN NUMBER) IS
  V_IGNORE  PLS_INTEGER;
  V_VERSION VARCHAR2(10);
  V_INST_ID NUMBER;
BEGIN
  SELECT COUNT(*)
    INTO V_IGNORE
    FROM GV$SESSION D
   WHERE USERNAME = USER
     AND SID = P_SID
     AND SERIAL# = P_SERIAL#
     AND D.INST_ID = P_INST;

  SELECT SUBSTR(V.VERSION, 1, INSTR(V.VERSION, '.') - 1), V.INSTANCE_NUMBER
    INTO V_VERSION, V_INST_ID
    FROM V$INSTANCE V;

  IF (V_IGNORE = 1) THEN

    IF (V_VERSION = '10' AND V_INST_ID <> P_INST) THEN
      RAISE_APPLICATION_ERROR(-20001,
                              'Please connect to 【INSTANCE:' || P_INST ||
                              '】,then retry！');
    ELSIF (V_VERSION = '10' AND V_INST_ID = P_INST) THEN
      EXECUTE IMMEDIATE 'ALTER SYSTEM DISCONNECT SESSION ''' || P_SID || ',' ||
                        P_SERIAL# || ''' IMMEDIATE';
    ELSE
      EXECUTE IMMEDIATE 'ALTER SYSTEM DISCONNECT SESSION ''' || P_SID || ',' ||
                        P_SERIAL# || ',@' || P_INST || ''' IMMEDIATE';
    END IF;
  ELSE
    RAISE_APPLICATION_ERROR(-20002,
                            'You do not own session ''' || P_SID || ',' ||
                            P_SERIAL# ||',@' || P_INST || '''');
  END IF;
END PRO_KILL_MYOWN_SESSION_LHR;
/

CREATE OR REPLACE PUBLIC SYNONYM PRO_KILL_SESSION_LHR FOR SYS.PRO_KILL_MYOWN_SESSION_LHR;
GRANT SELECT ON SYN_MYOWNERSESSION_LHR TO PUBLIC;
GRANT EXECUTE ON PRO_KILL_SESSION_LHR TO PUBLIC;

使用方法如下所示：

SELECT USERENV('INSTANCE'),USERENV('SID') FROM DUAL;
SELECT V.INST_ID, SID,SERIAL#,PADDR,STATUS FROM SYN_MYOWNERSESSION_LHR V WHERE SID=1008 AND V.INST_ID=1 ;--假设上一步查询出来的SID为1008，实例号为1
EXEC PRO_KILL_SESSION_LHR(1,1008,35038);--假设上一步查询出来的SERIAL#为35038

使用示例如下所示：

使用SYS用户杀PMON进程的会话：

SYS@lhrdb21> SELECT A.SID,A.SERIAL#,USERENV('INSTANCE'),USERNAME FROM V$SESSION A WHERE A.PROGRAM LIKE '%PMON%';
       SID    SERIAL# USERENV('INSTANCE') USERNAME
---------- ---------- ------------------- ------------------------------
       125          1                   1
SYS@lhrdb21> EXEC PRO_KILL_SESSION_LHR(1,125,1);
BEGIN PRO_KILL_SESSION_LHR(1,125,1); END;
*
ERROR at line 1:
ORA-20002: You do not own session '125,1,@1'
ORA-06512: at "SYS.PRO_KILL_MYOWN_SESSION_LHR", line 36
ORA-06512: at line 1

由于系统进程的用户名为空，所以，避免了误杀系统进程。

使用SYS用户杀普通用户的会话如下所示：

SYS@lhrdb21> SELECT A.SID,A.SERIAL#,USERENV('INSTANCE'),USERNAME FROM V$SESSION A WHERE USERNAME='LHR';
       SID    SERIAL# USERENV('INSTANCE') USERNAME
---------- ---------- ------------------- ------------------------------
        79      16453                   1 LHR
SYS@lhrdb21>  EXEC PRO_KILL_SESSION_LHR(1,79,16453);
BEGIN PRO_KILL_SESSION_LHR(1,79,16453); END;
*
ERROR at line 1:
ORA-20002: You do not own session '79,16453,@1'
ORA-06512: at "SYS.PRO_KILL_MYOWN_SESSION_LHR", line 36
ORA-06512: at line 1
SYS@lhrdb21> conn lhr/lhr
Connected.
LHR@lhrdb21> EXEC PRO_KILL_SESSION_LHR(1,79,16453);
PL/SQL procedure successfully completed.

由于79会话属于LHR用户，所以，避免了误杀其它用户的会话，当使用LHR用户的时候，可以正常杀掉会话。

使用LHR用户杀其它用户的会话：

LHR@lhrdb21> SELECT A.SID,A.SERIAL#,USERENV('INSTANCE'),USERNAME FROM V$SESSION A WHERE USERNAME='LHRTEST';
       SID    SERIAL# USERENV('INSTANCE') USERNAME
---------- ---------- ------------------- ------------------------------
       142      12947                   1 LHRTEST
LHR@lhrdb21> EXEC PRO_KILL_SESSION_LHR(1,142,12947);
BEGIN PRO_KILL_SESSION_LHR(1,142,12947); END;
*
ERROR at line 1:
ORA-20002: You do not own session '142,12947,@1'
ORA-06512: at "SYS.PRO_KILL_MYOWN_SESSION_LHR", line 36
ORA-06512: at line 1

普通用户LHR也不能杀掉其它用户LHRTEST的会话。

& 说明：

有关KILL SESSION的更多内容可以参考我的BLOG：http://blog.itpub.net/26736162/viewspace-2121019和http://blog.itpub.net/26736162/viewspace-2121020