Wednesday, January 29, 2014

How to give privilege to kill session without giving 'alter system' privilege

Step 1 : Create the procedure with sys user
create or replace procedure kill_session
(pn_sid number
,pn_serial number)
as
lv_user varchar2(30);
begin
select username into lv_user from v$session where sid = pn_sid and serial# = pn_serial;
if lv_user is not null and lv_user  in ('DBR') then
execute immediate 'alter system kill session '''||pn_sid||','||pn_serial||'''';
else
raise_application_error(-20000,'Attempt to kill protected system session has been blocked.');
end if;
end;
/

Procedure created.

you can modify the clause lv_user  in  accordingly to suit your need.
Above package allows user DBR to kill sessions for only DBR user.

Step 2: grant execute privilege to user
SQL> grant execute on sys.kill_session to dbr;

Grant succeeded.

Step 3: create a synonym the user
SQL> create synonym dbr.kill_session for sys.kill_session;

Synonym created.



No comments:

Post a Comment