4月3日にOracleでPLEASE句もどきを実現する

1. はじめに

エイプリル・フールのネタとして DB 界隈で話題になっている PLEASE 句を Oracle でも実現してみようという話です。

時代に即したMySQﾚの新機能：PLEASE句 - sakaikの日々雑感～(T)編

オープンソースでない Oracle では SQL パーサーを弄ることは難しいので、飽くまで「PLEASE句もどき」ですが…

2. 準備

まず、DB ユーザーとテーブルを作ります。

-- ユーザーの作成
conn sys/????????@PDB as sysdba
create user app_user identified by ???????? quota unlimited on users;
grant create session, create table to app_user;

-- テーブルの作成
conn app_user/????????@PDB
create table members (
	member_id   number(3) primary key,
	member_name varchar2(20)
);
insert into members values (1, '田中');
insert into members values (2, '山田');
insert into members values (3, '高橋');
commit;

3. PLEASE 句もどきの実現

本記事ではファイングレイン監査のイベントハンドラーを用いて実現します。

まずは、監査ユーザーの作成です。

conn sys/????????@PDB as sysdba
create user audit_user identified by ???????? quota unlimited on users;
grant create session, create procedure to audit_user;
grant execute on dbms_fga to audit_user;
grant execute on dbms_lock to audit_user;

/* please */ というコメントを付与せずにクエリを実行した際に3秒待たせるイベントハンドラー（PL/SQL のプロシージャー）を作成します。

conn audit_user/????????@PDB
create or replace procedure sleep_if_rude (
	schema_name varchar2,
	table_name  varchar2,
	policy_name varchar2
)
as
begin
	-- 失礼な奴からのクエリは３秒待たせる。
	if sys_context('USERENV','CURRENT_SQL') not like '%/* please */%'
	then
		dbms_lock.sleep(3);
	end if;
end;
/

最後に、作成済みのテーブルにイベントハンドラーを呼び出す FGA ポリシーを追加します（今回は SELECT だけ対象にします）。

conn audit_user/????????@PDB
execute dbms_fga.add_policy( -
	object_schema   => 'APP_USER', -
	object_name     => 'MEMBERS', -
	policy_name     => 'please_policy', -
	handler_schema  => 'AUDIT_USER', -
	handler_module  => 'SLEEP_IF_RUDE', -
	statement_types => 'SELECT' -
);

4. 動作確認

SQL> conn app_user/????????@PDB
接続されました。
SQL> set timing on
SQL> select * from members;

 MEMBER_ID MEMBER_NAME
---------- --------------------
         1 田中
         2 山田
         3 高橋

経過: 00:00:03.09
SQL> select /* please */ * from members;

 MEMBER_ID MEMBER_NAME
---------- --------------------
         1 田中
         2 山田
         3 高橋

経過: 00:00:00.01
SQL>

``/* please */```を付与していないクエリはちゃんと3秒遅くなりました。