create or replace procedure kill_session( p_sid in varchar2,
                                    p_serial# in varchar2)
is
cursor_name     pls_integer default dbms_sql.open_cursor;
ignore          pls_integer;
BEGIN
select count(*) into ignore
from V$session
where username = USER
 and sid = p_sid
 and serial# = p_serial# ;
if ( ignore = 1 )
then
  dbms_sql.parse(cursor_name,
                'alter system kill session '''
                          ||p_sid||','||p_serial#||'''',
                 dbms_sql.native);
  ignore := dbms_sql.execute(cursor_name);
else
  raise_application_error( -20001,
                         'You do not own this session ''' ||
                          p_sid || ',' || p_serial# ||
                         '''' );
end if;
END;
/
Now I have created synonym under that user who wants this privilege
create or replace synonym USERNAME.KILL_OWN_SESSION
for SYS.KILL_SESSION;
Now I connect with that username and try to kill one session of SYS and other session
of its own.
SQL> exec sys.kill_session(217,18696);
PL/SQL procedure successfully completed.
SQL>
SQL> exec sys.kill_session(7,17322);
BEGIN sys.kill_session(7,17322); END;
*
ERROR at line 1:
ORA-20001: You do not own session '7,17322'
ORA-06512: at "SYS.KILL_SESSION", line 21
ORA-06512: at line 1
 
Comments
Post a Comment