专栏首页小麦苗的DB宝专栏【DB笔试面试701】在Oracle中,如何让普通用户可以杀掉自己用户的会话?

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

题目部分

在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

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

本文分享自微信公众号 - DB宝(xiaomaimiaolhr),作者:小麦苗best

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2019-12-06

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 【DB笔试面试638】在Oracle中,文本型字段直方图示例2个。

    这里的ENDPOINT_VALUE值需要去转换,字符‘1’的16进制的dump值为0x31,字符‘6’的16进制的dump值为0x36,

    小麦苗DBA宝典
  • 【DB笔试面试674】在Oracle中,V$SESSION_LONGOPS视图的作用是什么?

    在Oracle 11g之前的版本,长时间运行的SQL可以通过监控V$SESSION_LONGOPS来观察,当某个操作执行时间超过6秒时,就会被记录在V$SESS...

    小麦苗DBA宝典
  • 【DB笔试面试639】在Oracle中,什么是多列统计信息(Extended Statistics)?

    Oracle优化器对于基数值的估算是否准确关系到能否生成最优的执行计划,而基数值估算的准确性又取决于SQL中各个对象的统计信息是否完整、是否能真实反映出对象的数...

    小麦苗DBA宝典
  • 【DB笔试面试674】在Oracle中,V$SESSION_LONGOPS视图的作用是什么?

    在Oracle 11g之前的版本,长时间运行的SQL可以通过监控V$SESSION_LONGOPS来观察,当某个操作执行时间超过6秒时,就会被记录在V$SESS...

    小麦苗DBA宝典
  • 关于inodes占用100%的问题及解决方法

    系统:Centos;一般的Linux系统也可以用这种方法。 情况描述:今天session文件写入时,写入不了,而且连接到服务器上开启服务都开不了,起始以为磁盘空...

    joshua317
  • 那个炒鸡有趣的H5标签 —— <dataList>

    按照whatwg文档所展示的,截至到本文截稿之前,一共有 113 个HTML标签。

    陈大鱼头
  • php JSON数据格式化输出方法

    php 的json_encode能把数组转换为json格式的字符串。字符串没有缩进,中文会转为unicode编码,例如\u975a\u4ed4。人阅读比较困难。...

    joshua317
  • DataTable的AcceptChange方法为什么不能在Update之前?

    本人转载http://www.cnblogs.com/telnet_mike/archive/2011/08/29/2159013.html

    跟着阿笨一起玩NET
  • Android:这是一份全面 & 清晰易懂的Application类使用指南

    即不同的组件(如Activity、Service)都可获得Application对象且都是同一个对象

    Carson.Ho
  • Android:全面解析熟悉而陌生 的 Application 类使用

    前言 Applicaiton类在 Android开发中非常常见,可是你真的了解Applicaiton类吗? 本文将全面解析Applicaiton类,包括特点、方...

    非著名程序员

扫码关注云+社区

领取腾讯云代金券