sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 开发DBA >

【学习笔记】关于Oracle kill my own session学习笔记

时间:2016-11-02 20:45   来源:Oracle研究中心   作者:HTZ   点击:

天萃荷净 kill my own session(without system privilege) ,测试开发要求实现会话能KILL自己用户下的会话的功能,由于KILL SESSION默认只有DBA用户才有权限,并且如果授予alter system权限给用户,那么他可以KILL所有用户的会话,不能满足要求,并且alter system包含的权限太多,生产环境不敢授予这样的权限。

1 创建KILL SESSION存储过程

在创建存储过程之前,先确认存储是否存在,如果存在请更换名字
CREATE OR REPLACE PROCEDURE kill_session (pn_sid NUMBER, pn_serial NUMBER)
AS
   lv_user     VARCHAR2 (40);
   user_kill   VARCHAR2 (40);
BEGIN
   SELECT USER INTO user_kill FROM DUAL;

   SELECT username
     INTO lv_user
     FROM v$session
    WHERE sid = pn_sid AND serial# = pn_serial;

   IF lv_user IS NOT NULL AND lv_user = user_kill
   THEN
      EXECUTE IMMEDIATE
         'alter system kill session ''' || pn_sid || ',' || pn_serial || '''';
   ELSE
      raise_application_error (
         -20000,
         'Attempt to kill  other session.');
   END IF;
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      DBMS_OUTPUT.put_line ('SESSION :' || pn_sid || ' IS NOT FIND;');
END;
/
授予相应的权限
grant execute on kill_session to username;
create public synonym kill_session for sys.kill_session;
grant select on v_$session to username;
grant execute on dbms_output to username;
ser serveroutput on

2 KILL SESSION测试

任意输入SID,Oracle о SERIAL,因为SID不存在,所以会报错
oracleplus.net> exec kill_session(1,1);
SESSION :1 IS NOT FIND;

PL/SQL procedure successfully completed.

会话2执行
oracleplus.net> conn htz/oracle
Connected.
会话1执行
oracleplus.net> exec kill_session(148,70);

PL/SQL procedure successfully completed.
回到会话2执行任意一条SQL语句,提示SESSION已经被KILL
oracleplus.net> select * from dual;
select * from dual
*
ERROR at line 1:
ORA-00028: your session has been killed

下面是测试KILL其它用户的会话
会话2执行
oracleplus.net> conn htz1/oracle
Connected.
会话1执行 ,这里会报KILL其它会话的提示。
oracleplus.net> exec kill_session(148,72);
BEGIN kill_session(148,72); END;

*
ERROR at line 1:
ORA-20000: Attempt to kill other session.
ORA-06512: at "SYS.KILL_SESSION", line 19
ORA-06512: at line 1
本文固定链接: http://www.htz.pw/2014/11/24/kill-my-own-sessionwithout-system-privilege.html | 认真就输

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【学习笔记】关于Oracle kill my own session学习笔记

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/1146.html

Oracle研究中心

关键词:

天萃荷净乳膏

Oracle DG

oracle dataguard调优

oracle dg常用视图