How to safely grant ALTER SYSTEM KILL SESSION “privilege” to non-DBA users

The title of the post can be a bit misleading as there is no such Oracle privilege “ALTER SYSTEM KILL SESSION”, and there is just “ALTER SESSION” privilege. Nevertheless, it would be nice to have something similar.

Very often developers want to have ability to kill their own sessions, i.e. sessions started by their personal database users. Apparently it is not safe to let a non-DBA user to have a right to execute the “ALTER SYSTEM” command, so what can be done?

A sys user may create a stored procedure (sp_kill_dev_session) that will only allow killing sessions started by a given user or a set of given users. For example, we can check that the session was started by SCOTT and only in that case allow it to be killed.

CREATE OR REPLACE PROCEDURE sys.sp_kill_dev_session(p_sid NUMBER, p_serial NUMBER)
AS
    v_user VARCHAR2(30);
BEGIN
    SELECT MAX(username)
    INTO v_user
    FROM v$session
    WHERE sid = p_sid
      AND serial# = p_serial;

    IF v_user IN ('SCOTT') THEN --the list can be extended
         EXECUTE IMMEDIATE 'ALTER SYSTEM KILL SESSION ''' || p_sid || ',' || p_serial || '''';
    ELSIF v_user IS NULL THEN
         RAISE_APPLICATION_ERROR(-20001,'Session has Expired or Invalid sid/serial Arguments Passed');
    ELSE
         RAISE_APPLICATION_ERROR(-20002,'Unauthorized Attempt to Kill a Non-Dev Session has been Blocked.');
    END IF;
END sp_kill_dev_session;
/

Procedure created.

Now, all we need to do is to grant EXECUTE privilege to SCOTT:

SQL> GRANT EXECUTE ON sp_kill_api_session TO scott;

Grant succeeded.

Here is how scott can call the procedure from SQL*PLUS:

SQL> exec sys.sp_kill_dev_session(14, 26043)
BEGIN sys.sp_kill_dev_session(14, 26043); END;

*
ERROR at line 1:
ORA-20001: Session has Expired or Invalid sid/serial Arguments Passed
ORA-06512: at "SYS.SP_KILL_DEV_SESSION", line 14
ORA-06512: at line 1

SQL> exec sys.sp_kill_dev_session(14, 26043)

PL/SQL procedure successfully completed.

SQL> exec sys.sp_kill_dev_session(18,19218)
BEGIN sys.sp_kill_dev_session(18,19218); END;

*
ERROR at line 1:
ORA-20002: Unauthorized Attempt to Kill a Non-Dev Session has been Blocked.
ORA-06512: at "SYS.SP_KILL_DEV_SESSION", line 16
ORA-06512: at line 1

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

Advertisements

One thought on “How to safely grant ALTER SYSTEM KILL SESSION “privilege” to non-DBA users

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s