♣
题目部分
在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的会话。
