Invoking privilege to a user without giving ALTER SYSTEM directly

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

Popular posts from this blog

Restoring Master, Model and MSDB from Netbackup

Oracle Job Operations