作成日：2025/04/13　更新日：2025/04/13

概要

ユーザにalter system権限を渡さずにkillする仕組みを作成する。

詳細

ユーザにalter system権限を渡さずにkillするプロシージャのサンプル。 具体的には、alter system権限を持つユーザにセッションをkillするプロシージャを作成し、そのプロシージャの実行権限のみ渡しalter system権限を持たないユーザがセッションの切断を可能とする。

前提及びポイント

  • プロシージャの所有者はTESTユーザとする
  • TEST2ユーザにプロシージャの実行権限のみ付与する。alter system権限は付与しない
  • AUTHID DEFINER指定によりプロシージャ実行時の権限はプロシージャを所有するユーザ(TEST)の権限で動くようにする。TESTユーザはALTER SYSTEMやV$SESSIONの実行権限を持つようにする
  • プロシージャを実行するとTEST2ユーザでログインしているセッションをすべてkill sessionする

プログラム

--権限付与

sqlplus /@pdb1 as sysdba
create user TEST identified by test;
create user TEST2 identified by test;
GRANT dba to TEST;
GRANT connect to TEST2;
GRANT alter system to TEST;
GRANT SELECT ON SYS.V_$SESSION TO TEST;

--プロシージャ作成
sqlplus TEST/test@pdb1
CREATE OR REPLACE PROCEDURE TEST.KILL_TEST2_SESSIONS
AUTHID DEFINER
AS
  CURSOR session_cursor IS
    SELECT sid, serial#
    FROM v$session
    WHERE username = 'TEST2';
BEGIN
  FOR rec IN session_cursor LOOP
    BEGIN
      EXECUTE IMMEDIATE 'ALTER SYSTEM KILL SESSION ''' || rec.sid || ',' || rec.serial# || ''' IMMEDIATE';
      DBMS_OUTPUT.PUT_LINE('セッション SID: ' || rec.sid || ', シリアル#: ' || rec.serial# || ' を強制終了しました。');
    EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('セッション SID: ' || rec.sid || ', シリアル#: ' || rec.serial# || ' の強制終了に失敗しました。エラー: ' || SQLERRM);
    END;
  END LOOP;
  COMMIT;
END;
/
grant execute on TEST.KILL_TEST2_SESSIONS  to test2;


-- 実行
sqlplus TEST2/test@pdb1
SET SERVEROUTPUT ON;
exec TEST.KILL_TEST2_SESSIONS;
★ORACLE案件承ります