Now and then I find a project, typically with an application behind and typically an old one. Its typically been crafted including functions to list and mark oracle users through its interface, which behind the scenes generates and SQL statement like ALTER SYSTEM KILL SESSION 'sid, serial#'. So whats the problem ?
You could argue that! an ALTER SYSTEM priv is much better, than granting the DBA role - and thats correct. However giving powerfull privs to applications or developers, can be problematic. Sooner or later, you can end up in a situation, where they actually use the power granted to them, and then it can be difficult and expensive to correct again.
In a case like with the ALTER SYSTEM i personally prefer to encapsulate, the functionality in a package/procedure if possible.
[workday:XO:/home/oracle/kbirch] sqlplus / as sysdba
SQL> grant alter system to system;
SQL> connect system
SQL> CREATE OR REPLACE procedure kill_session(i_sid in number default 0, i_serial in number default 0) as
v_username v$session.USERNAME%TYPE;
v_systembruger number;
begin
-- Take a look at the combination SID/SERIAL to determine if its been killed before
begin
SELECT username
into v_username
FROM v$session
WHERE username is not null
and Status Not In ('KILLED','SNIPED')
and sid=i_sid
and serial#=i_serial;
exception
when no_data_found then
RAISE_APPLICATION_ERROR(-20001,
'Error in Kill_Session : Sid/Serial can not be found or session has status Killed/Sniped.');
end;
-- Dont kill any of the system users
-- (CTXSYS,DBSNMP,DIP,EXFSYS,LMS,OUTLN,SYS,SYSTEM,TSMSYS,XDB)
begin
SELECT 1
into v_systembruger
from dba_users
where username=v_username
and username in ('CTXSYS','DBSNMP','DIP','EXFSYS','LMS','OUTLN','SYS','SYSTEM','TSMSYS','XDB');
RAISE_APPLICATION_ERROR(-20002,
'Error in Kill_Session : session belongs to a system user '||v_username||'.' );
exception
when no_data_found then
execute immediate 'alter system kill session ' || i_sid || ',' || i_serial || ';
end;
end;
/
SQL> grant execute on kill_session to PUBLIC;
SQL> create PUBLIC synonym kill_session for kill_session;
From here you can execute from SQL*Plus or from your application.
SQL> connect <username>
SQL> exec kill_session(<sid>,<serial#>);
Published by: Karsten Birch-Johansen in
Add to del.icio.us
Digg this
Blink it







