前回( https://qiita.com/ora_gonsuke777/items/fc617d04d8bd720f1af7 )からの続き。

Qiita の @tlokweng さんから 12c新機能のCode Based Access Control
なるものの存在を教えて頂きますた彡(ﾟ)(ﾟ)

ALTER SYSTEM KILL SESSION…によるユーザーセッションの切断は許可したいけど、
ALTER SYSTEM権限の付与(GRANT)は範囲が広過ぎる、権限が強過ぎる。。。

ALTER SYSTEM KILL SESSIONだけ許可したい……てな要件を、
今回は実行者権限(AUTHID CURRENT_USER)のプロシージャと、
12c新機能のCode Based Access Controlで実現してみるやで彡(ﾟ)(ﾟ)

概要

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

DEFUSER … 権限の強いDBユーザー
│　↓
│　RL_EXE_KILL_SESS … ALTER SYSTEM権限を保持するロール
│　│
│　(Code Based Access Controlでシステム権限をプロシージャに対してロール経由で付与)
↓　↓
DEFUSER.PRC_KILL_SESS … KILL SESSIONを行うストアド・プロシージャ、実行者権限(AUTHID CURRENT_USER)で作成
↓
実行権限付与(GRANT EXECUTE…)
↓
EXEUSER ←権限の弱いDBユーザー

DBユーザー作成

プロシージャ所有者(DEFUSER)と実行ユーザー(EXEUSER)をそれぞれ作成します。

-- プロシージャの所有者を作成
CONNECT /AS SYSDBA
CREATE USER DEFUSER IDENTIFIED BY xxxxxxxx
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP;

-- DBAロールを付与
GRANT DBA TO DEFUSER;

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

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

Connected.
User created.
Grant succeeded.

User created.
Grant succeeded.

プロシージャ作成

実行者権限(AUTHID CURRENT_USER)のプロシージャを作成します。

-- 実行者権限(AUTHID CURRENT_USER)のKILL SESSIONプロシージャを作成
CONNECT DEFUSER/xxxxxxxx@yyyyyyyy

CREATE OR REPLACE PROCEDURE DEFUSER.PRC_KILL_SESS (
    in_sid    IN NUMBER
  , in_serial IN NUMBER
)
AUTHID CURRENT_USER
IS
BEGIN
  DBMS_OUTPUT.PUT_LINE('Current Schema => ' || SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA'));
  DBMS_OUTPUT.PUT_LINE('Current User => '   || SYS_CONTEXT('USERENV', 'CURRENT_USER'));
  DBMS_OUTPUT.PUT_LINE('Session User => '   || SYS_CONTEXT('USERENV', 'SESSION_USER'));
  EXECUTE IMMEDIATE 'ALTER SYSTEM KILL SESSION ''' || in_sid || ',' || in_serial || '''';
END;
/

Connected.
Procedure created.

ロール作成＆権限付与および Code Based Access Controlによる権限付与

プロシージャ所有者でロールを作成して、
ロール経由でALTER SYSTEM権限をプロシージャに付与します。

ユーザーやロールではなく、プロシージャに権限を付与するところがポイントで、
これが12c新機能のCode Based Access Control、、、と理解したやで。彡(ﾟ)(ﾟ)

CONNECT DEFUSER/xxxxxxxx@yyyyyyyy
-- ロール作成とロールに対するALTER SYSTEM権限の付与
CREATE ROLE RL_EXEC_KILL_SESS;
GRANT ALTER SYSTEM TO RL_EXEC_KILL_SESS;

-- ALTER SYSTEM権限をロール経由でプロシージャに付与(Code Based Access Control)
GRANT RL_EXEC_KILL_SESS TO PROCEDURE DEFUSER.PRC_KILL_SESS;

-- プロシージャの実行権限付与
GRANT EXECUTE ON DEFUSER.PRC_KILL_SESS TO EXEUSER;

Role created.
Grant succeeded.
Grant succeeded.
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#
---------- ----------
       787      62729

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

CONNECT EXEUSER/xxxxxxxx@yyyyyyyy
Connected.

SHOW USER
USER is "EXEUSER"

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

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

CONNECT EXEUSER/xxxxxxxx@yyyyyyyy
Connected.

SHOW USER
USER is "EXEUSER"

SET SERVEROUTPUT ON SIZE 1000000;
EXEC DEFUSER.PRC_KILL_SESS(787, 62729);

Current Schema => EXEUSER
Current User => EXEUSER
Session User => EXEUSER

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: 4160
Session ID: 787 Serial number: 62729

ちなみにALTER SYSTEM権限をロールから剥奪(REVOKE)すると、
実行ユーザーからはKILL SESSIONが実行できなくなります。
Code Based Access Controlが有効に機能していることが解ります彡(ﾟ)(ﾟ)

CONNECT DEFUSER/xxxxxxxx@yyyyyyyy
REVOKE ALTER SYSTEM FROM RL_EXEC_KILL_SESS;

Connected.
Revoke succeeded.


CONNECT EXEUSER/xxxxxxxx@yyyyyyyy
SET SERVEROUTPUT ON SIZE 1000000;
EXEC DEFUSER.PRC_KILL_SESS(798, 56993);

Connected.
Current Schema => EXEUSER
Current User => EXEUSER
Session User => EXEUSER
BEGIN DEFUSER.PRC_KILL_SESS(798, 56993); END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "DEFUSER.PRC_KILL_SESS", line 11
ORA-06512: at line 1

AUTHID DEFINERだと、問答無用で定義者の権限やロールで処理されてしまう所を、
実行者に付与された権限＆ロールできめ細かく制御可能なのが、
この機能(Code Based Access Control)のメリットやね彡(^)(^)
＃今回のケースだと、メリットが全面的には出し辛かったんやけど。。。彡(-)(-)

マニュアルも読みませう彡(ﾟ)(ﾟ)

Oracle Database新機能ガイド 12cリリース1 (12.1)
B71327-05
2.9.2.2 コードベース・セキュリティ
https://docs.oracle.com/cd/E57425_01/121/NEWFT/chapter12101.htm#FEATURENO08676

Oracle Databaseセキュリティ・ガイド 12c リリース1 (12.1)
B71285-10
定義者権限および実行者権限のコード・ベース・アクセス制御の使用
https://docs.oracle.com/cd/E57425_01/121/DBSEG/dr_ir.htm#GUID-45E77E8E-587F-42AF-A163-D814264341E2