Thursday, March 2, 2017

User privilege for kill session

1) please create in sys ( login as sysdba)
-------------------------------------------------
create or replace procedure sys.kill_session
(
p_sid NUMBER
,p_serial# NUMBER
) AS
m_sql VARCHAR2(1000);
m_loginusername VARCHAR2(30);
m_killusername VARCHAR2(30);
m_schemaname varchar2(30);
BEGIN
SELECT a.USERNAME,
a.SCHEMANAME
INTO m_killusername,
m_schemaname
FROM v$session a
WHERE a.sid = p_sid
AND a.serial# = p_serial#;
select user
into m_loginusername
from dual;
IF m_loginusername = m_killusername AND
m_schemaname NOT IN ('SYS', 'SYSTEM')
THEN
m_sql := 'ALTER SYSTEM KILL SESSION ' || '''' || p_sid || ',' ||
p_serial# || '''' || ' IMMEDIATE';
EXECUTE IMMEDIATE m_sql;
dbms_output.put_line('Session Killed ');
ELSE
dbms_output.put_line(' Login User Name ' || m_loginusername ||
' User Session to Kill ' ||m_killusername);
dbms_output.put_line('Cannot kill session of other users or Admin users');
raise_application_error(-20101,'Cannot kill session of other users or Admin users or self');
END IF;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Error in Kill Session.' ||SQLERRM(SQLCODE));
END kill_session;
------------------------------------------------------
2) grant execute on kill_seesion to suh;
3) grant select on v_$session to suh;
4) login as suh/suh
5) select a.sid, a.serial#
from v$session a
where username = 'SUH'
/
6) exec kill_session ( sid,serial#)
7) exec kill_session(18,3320);

No comments:

Post a Comment