一 问题描述

想给某个普通用户授予杀掉自己会话的权限

二 解决办法

2.1 用sys用户创建杀会话的存储过程

create or replace procedure scott_p_kill_session

( v_sid number, v_serial number )

as

v_varchar2 varchar2(100);

begin

if v_sid is not null and v_serial is not null then

    for c in (select /*+ rule */

               s.username, s.inst_id, s.sid, s.serial#

                from gv$session s

               where

                s.sid = v_sid

                 and s.serial# = v_serial

                 and s.username='SCOTT'

                 ) loop

      dbms_output.put_line('alter system kill session ''' || c.sid || ',' ||

                             c.serial# || ',@' || c.inst_id ||

                             ''' immediate');

        execute immediate 'alter system kill session ''' || c.sid || ',' ||

                          c.serial# || ',@' || c.inst_id || ''' immediate';  

      end loop;

  else

    raise_application_error(-30000,

                            'Wrong number or types of arguments in call to ''kill_block_session''.');

  end if;   

end;

2.2 给该普通用户授权调用刚创建的存储过程

grant execute on scott_p_kill_session to scott;

2.3 杀会话示例

--用普通用户登录该会话所在数据库节点杀会话

--语法

exec sys.scott_p_kill_session(会话号,序列号);

--示例

exec sys.scott_p_kill_session(46,51);

--本篇文章参考自

oracle-权限-授予普通用户kill session权限-阿里云开发者社区

并做了些许改动。