Oracle

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 (‘ERMANNKARA’) 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 ERMANNKARA to kill sessions for only ERMANNKARA user.

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

Grant succeeded.

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

Synonym created.

DEMO:

ermannkara

Advertisements
Standard

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s