How To Kill Session Without ALTER SYSTEM Authorization

dbtut

5 years ago

Application developers may want to kill long-running queries. However, since application developers do not have ALTER SYSTEM privileges, they cannot execute the kill session command. Also, this authorization should not be given to users who are not dba.

There is a way for application developers to kill sessions without ALTER SYSTEM authorization. We may create a procedure to kill sessions and authorize the user to execute them.

Let’s create the following procedure under the SYS user and authorize the user to execute it.

Create Procedure Script:

CREATE OR REPLACE PROCEDURE kill_session(prm_sid NUMBER,
prm_inst_id NUMBER DEFAULT 1) AS
l_user VARCHAR2(30);
l_serial NUMBER;
l_inst_id NUMBER;
BEGIN
SELECT t.username, t.SERIAL#, t.INST_ID
INTO l_user, l_serial, l_inst_id
FROM gv$session t
WHERE t.sid = prm_sid
AND t.INST_ID = prm_inst_id;
IF l_user IS NOT NULL AND l_user NOT IN ('SYS', 'SYSTEM') THEN
EXECUTE IMMEDIATE 'alter system kill session ''' || prm_sid || ',' ||
l_serial || ',@' || prm_inst_id || '''';
dbms_output.put_line(prm_sid || 'Session was killed.');
ELSE
raise_application_error(-20000, 'System session cannot be killed');
END IF;
END;
/

CREATE OR REPLACE PROCEDURE kill_session(prm_sid NUMBER,

prm_inst_id NUMBER DEFAULT 1) AS

l_user VARCHAR2(30);

l_serial NUMBER;

l_inst_id NUMBER;

BEGIN

SELECT t.username, t.SERIAL#, t.INST_ID

INTO l_user, l_serial, l_inst_id

FROM gv$session t

WHERE t.sid = prm_sid

AND t.INST_ID = prm_inst_id;

IF l_user IS NOT NULL AND l_user NOT IN ('SYS', 'SYSTEM') THEN

EXECUTE IMMEDIATE 'alter system kill session ''' || prm_sid || ',' ||

l_serial || ',@' || prm_inst_id || '''';

dbms_output.put_line(prm_sid || 'Session was killed.');

ELSE

raise_application_error(-20000, 'System session cannot be killed');

END IF;

END;

User Authorization:

grant execute on kill_session to xUser;

1	grant execute on kill_session to xUser;