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.
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