ALTER SYSTEM KILL SESSION…"だけ"の権限付与を定義者権限のプロシージャで実現してみる。(Oracle Database)

ALTER SYSTEM KILL SESSION…によるユーザーセッションの切断は許可したいけど、
ALTER SYSTEM権限の付与(GRANT)は範囲が広過ぎる、権限が強過ぎる。
ALTER SYSTEM KILL SESSION"だけ"を許可したい……てな要件を、
定義者権限(AUTHID DEFINER)のプロシージャで実現してみるやで彡(ﾟ)(ﾟ)

概要

以下のような構成でDBユーザーとストアド・プロシージャを作成します。

DEFUSER … 権限の強いDBユーザー
↓
DEFUSER.PRC_KILL_SESS … KILL SESSIONを行うストアド・プロシージャ、定義者権限(AUTHID DEFINER)で作成
↓
実行権限付与(GRANT EXECUTE…)
↓
EXEUSER ←権限の弱いDBユーザー

DBユーザー作成

権限の強いユーザー(DEFUSER)と弱いユーザー(EXEUSER)をそれぞれ作成します。

CONNECT /AS SYSDBA

-- プロシージャの所有者
CREATE USER DEFUSER IDENTIFIED BY xxxxxxxx
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP;

-- DBAロールとALTER SYSTEM権限を付与
GRANT DBA TO DEFUSER;
GRANT ALTER SYSTEM TO DEFUSER;

User created.
Grant succeeded.
Grant succeeded.

-- KILL SESSIONを実行するユーザー
CREATE USER EXEUSER IDENTIFIED BY xxxxxxxx
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP;

-- CREATE SESSION権限のみ付与
GRANT CREATE SESSION TO EXEUSER;

User created.
Grant succeeded.

ストアド・プロシージャ作成＆権限付与

KILL SESSIONを実行するストアド・プロシージャをDEFUSERスキーマに
作成してEXEUSERに実行権限を付与します。

CONNECT DEFUSER/xxxxxxxx@yyyyyyyy

CREATE OR REPLACE PROCEDURE DEFUSER.PRC_KILL_SESS (
    in_sid    IN NUMBER
  , in_serial IN NUMBER
)
AUTHID DEFINER
IS
BEGIN
  EXECUTE IMMEDIATE 'ALTER SYSTEM KILL SESSION ''' || in_sid || ',' || in_serial || '''';
END;
/

Procedure created.

GRANT EXECUTE ON DEFUSER.PRC_KILL_SESS TO EXEUSER;

Grant succeeded.

EXEUSERでKILL SESSIONの検証

EXEUSERでKILL SESSIONの検証をしてみます。下記のセッションをKILLしてみます。

CONNECT AYSHIBAT/xxxxxxxx@yyyyyyyy

Connected.

SELECT SID, SERIAL# FROM V$SESSION WHERE USERNAME = 'AYSHIBAT';

       SID    SERIAL#
---------- ----------
       775      31102

まずは ALTER SYSTEM KILL SESSION…から。権限が無いので当然失敗します。

CONNECT EXEUSER/xxxxxxxx@yyyyyyyy
Connected.

SHOW USER
USER is "EXEUSER"

ALTER SYSTEM KILL SESSION '775, 31102';
*
ERROR at line 1:
ORA-01031: insufficient privileges

ストアド・プロシージャ(PRC_KILL_SESS)によるKILL。こちらは成功します。

CONNECT EXEUSER/xxxxxxxx@yyyyyyyy
Connected.

SHOW USER
USER is "EXEUSER"

EXEC DEFUSER.PRC_KILL_SESS(775, 31102);
PL/SQL procedure successfully completed.

元のセッション(AYSHIBATユーザ)に戻ってみると……
見事にKILLされています。やったぜ。彡(^)(^)

SELECT SID, SERIAL# FROM V$SESSION WHERE USERNAME = 'AYSHIBAT';
*
ERROR at line 1:
ORA-00028: your session has been killed

SELECT SID, SERIAL# FROM V$SESSION WHERE USERNAME = 'AYSHIBAT';
ERROR at line 1:
ORA-01012: not logged on
Process ID: 4398
Session ID: 775 Serial number: 31102

マニュアルも読みませう彡(ﾟ)(ﾟ)
AUTHID DEFINERでプロシージャ(等)を作成すると、
定義者(今回のケースではDEFUSER)の権限で実行されるってことなんやね。

Oracle Database PL/SQL言語リファレンス 12c リリース2 (12.2)
E72879-03
実行者権限および定義者権限(AUTHIDプロパティ)
https://docs.oracle.com/cd/E82638_01/LNPLS/plsql-subprograms.htm#GUID-41D23DE7-3C07-41CF-962B-F92B696594B5