Oracle授予普通用户kill session权限的方法
<div id="navCategory"><h5 class="catalogue">目录</h5><ul class="first_class_ul"><li><a href="#_label0">1. 授予alter system权限</a></li><li><a href="#_label1">2. 创建存储过程</a></li><ul class="second_class_ul"><li><a href="#_lab2_1_0">创建审计表</a></li><li><a href="#_lab2_1_1">创建存储过程</a></li></ul></ul></div><p>开发A在测试环境操作时有时会遇到阻塞问题，需要找DBA帮忙查看阻塞会话及kill session，后来觉得太麻烦想要个kill会话的权限，查了下Oracle授予普通用户kill session权限的方法。</p><p class="maodian"><a name="_label0"></a></p><h2>1. 授予alter system权限</h2>
<p>官方文档查到，kill session需要ALTER SYSTEM权限，但是这个权限非常大，不能直接给</p>
<div class="jb51code"><pre class="brush:sql;">GRANT ALTER SYSTEM TO <username>;</pre></div>
<p class="maodian"><a name="_label1"></a></p><h2>2. 创建存储过程</h2>
<p>后来查到了可以自己创建存储过程实现，简单的实现方法如下：</p>
<div class="jb51code"><pre class="brush:sql;">-- sys执行
create or replace procedure kill_session
( v_sid number, v_serial number )
as
v_varchar2 varchar2(100);
begin
execute immediate 'ALTER SYSTEM KILL SESSION '''
|| v_sid || ',' || v_serial || '''';
end;
/
-- 授权：
grant execute on kill_session to username;
-- 普通用户使用：
exec sys.kill_session(161,14502);</pre></div>
<p>还能加各种限制条件，例如写入日志，记录是谁在什么时候发起的、kill了谁，视需要而定。</p>
<p class="maodian"><a name="_lab2_1_0"></a></p><h3>创建审计表</h3>
<div class="jb51code"><pre class="brush:sql;">CREATE TABLE action_audit (
id NUMBER GENERATED ALWAYS AS IDENTITY,
operator_name VARCHAR2(50) NOT NULL,
action_time TIMESTAMP NOT NULL,
session_id NUMBER(10) NOT NULL,
serial_id NUMBER(10) NOT NULL,
sql_id VARCHAR2(13),
CONSTRAINT action_audit_pk PRIMARY KEY (id)
);</pre></div>
<p class="maodian"><a name="_lab2_1_1"></a></p><h3>创建存储过程</h3>
<div class="jb51code"><pre class="brush:sql;">CREATE OR REPLACE PROCEDURE kill_session (
p_session_id NUMBER,
p_serial_id NUMBER
) AS
v_sql_id VARCHAR2(13);
BEGIN
SELECT s.sql_id INTO v_sql_id FROM v$session s WHERE s.sid = p_session_id AND s.serial# = p_serial_id;
EXECUTE IMMEDIATE 'ALTER SYSTEM KILL SESSION ''' || p_session_id || ',' || p_serial_id || ''' IMMEDIATE';
INSERT INTO action_audit (
operator_name,
action_time,
session_id,
serial_id,
sql_id
) VALUES (
sys_context('userenv','os_user'),
CURRENT_TIMESTAMP,
p_session_id,
p_serial_id,
v_sql_id
);
commit;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20001, 'Error in killing session: ' || SQLERRM);
END;
/</pre></div>
<p>运行方法同上~</p>
頁:
[1]