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

DB宝 2019-12-06

799

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

     
♣答案部分

普通用户想要杀掉会话必须要具有ALTER SYSTEM的权限，但是由于该权限过大，用户可能使用该权限错杀其他用户的会话，所以，有没有其它办法可以实现该功能呢？该类问题也是DBA工作中常遇到的问题，下面作者给出一种解决方案。
首先，可以创建一个查询自己会话信息的视图，将该视图创建公共同义词，然后创建一个存储过程，该存储过程实现杀掉会话的需要，最后将该存储过程的执行权限赋权给PUBLIC即可解决这个问题。
代码实现过程如下所示：
1CREATE OR REPLACE VIEW VW_MYOWNERSESSION_LHR AS
2SELECT * FROM V$SESSION WHERE USERNAME = USER;
3CREATE OR REPLACE PUBLIC SYNONYM SYN_MYOWNERSESSION_LHR FOR SYS.VW_MYOWNERSESSION_LHR;

创建存储过程用于杀掉会话：
 1CREATE OR REPLACE PROCEDURE PRO_KILL_MYOWN_SESSION_LHR(P_INST    IN NUMBER,
 2                                                       P_SID     IN NUMBER,
 3                                                       P_SERIAL# IN NUMBER) IS
 4  V_IGNORE  PLS_INTEGER;
 5  V_VERSION VARCHAR2(10);
 6  V_INST_ID NUMBER;
 7BEGIN
 8  SELECT COUNT(*)
 9    INTO V_IGNORE
10    FROM GV$SESSION D
11   WHERE USERNAME = USER
12     AND SID = P_SID
13     AND SERIAL# = P_SERIAL#
14     AND D.INST_ID = P_INST;
15
16  SELECT SUBSTR(V.VERSION, 1, INSTR(V.VERSION, '.') - 1), V.INSTANCE_NUMBER
17    INTO V_VERSION, V_INST_ID
18    FROM V$INSTANCE V;
19
20  IF (V_IGNORE = 1) THEN
21
22    IF (V_VERSION = '10' AND V_INST_ID <> P_INST) THEN
23      RAISE_APPLICATION_ERROR(-20001,
24                              'Please connect to 【INSTANCE:' || P_INST ||
25                              '】,then retry！');
26    ELSIF (V_VERSION = '10' AND V_INST_ID = P_INST) THEN
27      EXECUTE IMMEDIATE 'ALTER SYSTEM DISCONNECT SESSION ''' || P_SID || ',' ||
28                        P_SERIAL# || ''' IMMEDIATE';
29    ELSE
30      EXECUTE IMMEDIATE 'ALTER SYSTEM DISCONNECT SESSION ''' || P_SID || ',' ||
31                        P_SERIAL# || ',@' || P_INST || ''' IMMEDIATE';
32    END IF;
33  ELSE
34    RAISE_APPLICATION_ERROR(-20002,
35                            'You do not own session ''' || P_SID || ',' ||
36                            P_SERIAL# ||',@' || P_INST || '''');
37  END IF;
38END PRO_KILL_MYOWN_SESSION_LHR;
39/
40
41CREATE OR REPLACE PUBLIC SYNONYM PRO_KILL_SESSION_LHR FOR SYS.PRO_KILL_MYOWN_SESSION_LHR;
42GRANT SELECT ON SYN_MYOWNERSESSION_LHR TO PUBLIC;
43GRANT EXECUTE ON PRO_KILL_SESSION_LHR TO PUBLIC;
44

使用方法如下所示：
1SELECT USERENV('INSTANCE'),USERENV('SID') FROM DUAL;
2SELECT V.INST_ID, SID,SERIAL#,PADDR,STATUS FROM SYN_MYOWNERSESSION_LHR V WHERE SID=1008 AND V.INST_ID=1 ;--假设上一步查询出来的SID为1008，实例号为1
3EXEC PRO_KILL_SESSION_LHR(1,1008,35038);--假设上一步查询出来的SERIAL#为35038

使用示例如下所示：
使用SYS用户杀PMON进程的会话：
 1SYS@lhrdb21> SELECT A.SID,A.SERIAL#,USERENV('INSTANCE'),USERNAME FROM V$SESSION A WHERE A.PROGRAM LIKE '%PMON%';
 2       SID    SERIAL# USERENV('INSTANCE') USERNAME
 3---------- ---------- ------------------- ------------------------------
 4       125          1                   1
 5SYS@lhrdb21> EXEC PRO_KILL_SESSION_LHR(1,125,1);
 6BEGIN PRO_KILL_SESSION_LHR(1,125,1); END;
 7*
 8ERROR at line 1:
 9ORA-20002: You do not own session '125,1,@1'
10ORA-06512: at "SYS.PRO_KILL_MYOWN_SESSION_LHR", line 36
11ORA-06512: at line 1

由于系统进程的用户名为空，所以，避免了误杀系统进程。
使用SYS用户杀普通用户的会话如下所示：
 1SYS@lhrdb21> SELECT A.SID,A.SERIAL#,USERENV('INSTANCE'),USERNAME FROM V$SESSION A WHERE USERNAME='LHR';
 2       SID    SERIAL# USERENV('INSTANCE') USERNAME
 3---------- ---------- ------------------- ------------------------------
 4        79      16453                   1 LHR
 5SYS@lhrdb21>  EXEC PRO_KILL_SESSION_LHR(1,79,16453);
 6BEGIN PRO_KILL_SESSION_LHR(1,79,16453); END;
 7*
 8ERROR at line 1:
 9ORA-20002: You do not own session '79,16453,@1'
10ORA-06512: at "SYS.PRO_KILL_MYOWN_SESSION_LHR", line 36
11ORA-06512: at line 1
12SYS@lhrdb21> conn lhr/lhr
13Connected.
14LHR@lhrdb21> EXEC PRO_KILL_SESSION_LHR(1,79,16453);
15PL/SQL procedure successfully completed.


由于79会话属于LHR用户，所以，避免了误杀其它用户的会话，当使用LHR用户的时候，可以正常杀掉会话。
使用LHR用户杀其它用户的会话：
 1LHR@lhrdb21> SELECT A.SID,A.SERIAL#,USERENV('INSTANCE'),USERNAME FROM V$SESSION A WHERE USERNAME='LHRTEST';
 2       SID    SERIAL# USERENV('INSTANCE') USERNAME
 3---------- ---------- ------------------- ------------------------------
 4       142      12947                   1 LHRTEST
 5LHR@lhrdb21> EXEC PRO_KILL_SESSION_LHR(1,142,12947);
 6BEGIN PRO_KILL_SESSION_LHR(1,142,12947); END;
 7*
 8ERROR at line 1:
 9ORA-20002: You do not own session '142,12947,@1'
10ORA-06512: at "SYS.PRO_KILL_MYOWN_SESSION_LHR", line 36
11ORA-06512: at line 1

普通用户LHR也不能杀掉其它用户LHRTEST的会话。
& 说明：
有关KILL SESSION的更多内容可以参考我的BLOG：http://blog.itpub.net/26736162/viewspace-2121019和http://blog.itpub.net/26736162/viewspace-2121020

本文选自《Oracle程序员面试笔试宝典》，作者：小麦苗