データベース・セキュリティの活用(7) - 仮想プライベート・データベース

テスト用に作成したAPEXアプリケーションは、意図的にSQLインジェクションに脆弱にしています。仮想プライベート・データベースを構成することにより、APEXアプリケーションへ変更を変更せずに（認証スキームは変更します）SQLインジェクションを防いでみます。

認証スキームの変更

仮想プライベート・データベースによる表HR.EMPの保護を実装するにあたり、作成済みのAPEXアプリケーションを従業員によって認証するように変更します。

create table auth_users as select empno, ename, deptno from hr.emp;

view raw seminar200825-create_auth_users.sql hosted with ❤ by GitHub

最初に認証に使用する表AUTH_USERSを表HR.USERSより作成します。

APEXのSQLワークショップのSQLコマンドより実行します。

テスト用アプリケーションの共有コンポーネントの認証スキームを開き、作成を実行します。

スキームの作成として、ギャラリからの事前構成済スキームに基づくを選択し、次に進みます。

名前を従業員による認証とし、スキーム・タイプとしてカスタムを選択します。ソースのPL/SQLコードに、下記のファンクションauth_employees_onlyを記述します。

	function auth_employees_only (
	p_username in varchar2,
	p_password in varchar2 )
	return boolean
	is
	l number;
	begin
	select 1 into l from auth_users where ename = p_username;
	return true;
	exception
	when others then
	return false;
	end;

view raw seminar200825-auth_employees_only.sql hosted with ❤ by GitHub

認証ファンクション名にauth_employees_onlyを指定します。ユーザー名が従業員名に一致しているとサインインに成功します。パスワードの検証は行いません。従業員名は大文字なので、ユーザー名も大文字で入力する必要があります。

認証スキームの作成をクリックします。

作成された認証スキーム従業員による認証は、作成後よりカレント・スキームになります。

アプリケーションを実行し、サインインの確認を行います。ユーザーAPEXDEVでサインインしたままの場合は、一旦サインアウトします。

ユーザー名として大文字でSCOTTと入力し、サインインをクリックします。パスワードは検証に使用されません。

左上のメニューを確認します。サインインしたユーザー名scottが小文字で表示されています。

以上で認証スキームは作成は完了です。

ユーザーVPDADMINの作成

仮想プライベート・データベースを構成するユーザーVPDADMINを作成します。データベース・アクションよりデータベース・ユーザーを開きます。ユーザーの作成をクリックします。

ユーザー名としてVPDADMINを指定します。パスワードを設定し、WebアクセスをONに設定します。ユーザーの作成をクリックします。

ユーザーVPDADMINが作成されます。WebアクセスをONとしているため、データベース・アクションにVPDADMINとして接続することができます。

ユーザーVPDADMINが仮想プライベート・データベースを構成するために必要な権限を割り当てます。データベース・アクションのSQLを開き、以下を実行します。

	grant create any context to vpdadmin;
	grant execute on dbms_rls to vpdadmin;
	grant execute on dbms_session to vpdadmin;
	grant create procedure to vpdadmin;
	grant select on apexdev.auth_users to vpdadmin;

view raw seminar210825-vpdadmin-privs.sql hosted with ❤ by GitHub

ユーザーVPDADMINの作成は以上で完了です。データベース・アクションからサインアウトします。

仮想プライベート・データベースの構成

仮想プライベート・データベースの構成作業を行います。データベース・アクションにユーザーVPDADMINにてサインインします。開発のSQLを開きます。

最初にアプリケーション・コンテキストEMP_DEPT_CTXを作成します。このアプリケーション・コンテキストを操作するパッケージはEMP_DEPT_CTX_PKGとします。

create or replace context emp_dept_ctx using emp_dept_ctx_pkg;

view raw seminar210825-create_app_context.sql hosted with ❤ by GitHub

作成されたアプリケーション・コンテキストはビューDBA_CONTEXTより参照できます。(ビューの参照権限が必要です。)

続いてパッケージEMP_DEPT_CTX_PKGとそのパッケージ本体を作成します。次のスクリプトを実行します。アプリケーション・コンテキストにはサインインしたユーザーの従業員番号empnoと部門番号deptnoが保持されます。作成されたパッケージはユーザーAPEXDEVから実行できるよう、実行権限を与えます。

	create or replace package emp_dept_ctx_pkg is
	procedure init;
	procedure clear;
	end;
	/
	create or replace package body emp_dept_ctx_pkg is
	procedure init
	is
	l_empno apexdev.auth_users.empno%type;
	l_deptno apexdev.auth_users.deptno%type;
	begin
	select empno, deptno into l_empno, l_deptno
	from apexdev.auth_users
	where ename = sys_context('APEX$SESSION', 'APP_USER');
	dbms_session.set_context('emp_dept_ctx','empno',l_empno);
	dbms_session.set_context('emp_dept_ctx','deptno',l_deptno);
	exception
	when others then
	-- empty app context
	null;
	end init;
	procedure clear
	is
	begin
	dbms_session.clear_context('emp_dept_ctx');
	end clear;
	end emp_dept_ctx_pkg;
	/
	grant execute on emp_dept_ctx_pkg to apexdev;

view raw seminar210825-create_context_package.sql hosted with ❤ by GitHub

仮想プライベート・データベースのポリシーを作成します。

最初に作成するポリシーは、表EMPの検索範囲をサインインしたユーザーと同じ部門に制限するポリシーemp_in_same_deptnoです。

検索条件を生成するファンクションpred_emp_in_same_deptnoを作成します。

	CREATE OR REPLACE FUNCTION pred_emp_in_same_deptno
	(
	schema_p IN VARCHAR2,
	table_p IN VARCHAR2
	)
	RETURN VARCHAR2
	AS
	pred VARCHAR2(80);
	BEGIN
	pred := q'~deptno = SYS_CONTEXT('emp_dept_ctx','deptno')~';
	RETURN pred;
	END;
	/

view raw seminar200825-pred_emp_in_same_deptno.sql hosted with ❤ by GitHub

続けてポリシーemp_in_same_deptnoを作成します。プロシージャDBMS_RLS.ADD_POLICYを呼び出します。

	begin
	dbms_rls.add_policy(
	object_schema => 'hr'
	, object_name => 'emp'
	, policy_name => 'emp_in_same_deptno'
	, function_schema => 'vpdadmin'
	, policy_function => 'pred_emp_in_same_deptno'
	, statement_types => 'select'
	, policy_type => DBMS_RLS.CONTEXT_SENSITIVE
	, namespace => 'emp_dept_ctx'
	, attribute => 'deptno'
	);
	end;
	/

view raw seminar200825-emp_in_same_deptno.sql hosted with ❤ by GitHub

もうひとつ作成するポリシーは、自分自身がマネージャである従業員の列SALとCOMMのみ参照できるように制限するポリシーemp_is_mgrです。

条件を生成するファンクションはpred_emp_is_mgrです。ファンクションを作成した後にポリシーemp_is_mgrを作成します。

	CREATE OR REPLACE FUNCTION pred_emp_is_mgr
	(
	schema_p IN VARCHAR2,
	table_p IN VARCHAR2
	)
	RETURN VARCHAR2
	AS
	pred VARCHAR2(80);
	BEGIN
	pred := q'~mgr = SYS_CONTEXT('emp_dept_ctx','empno')~';
	RETURN pred;
	END;
	/
	begin
	dbms_rls.add_policy(
	object_schema => 'hr'
	, object_name => 'emp'
	, policy_name => 'emp_is_mgr'
	, function_schema => 'vpdadmin'
	, policy_function => 'pred_emp_is_mgr'
	, statement_types => 'select'
	, policy_type => DBMS_RLS.CONTEXT_SENSITIVE
	, sec_relevant_cols => 'sal,comm'
	, sec_relevant_cols_opt => DBMS_RLS.ALL_ROWS
	, namespace => 'emp_dept_ctx'
	, attribute => 'empno'
	);
	end;
	/

view raw seminar210825-emp_is_mgr.sql hosted with ❤ by GitHub

以上で表HR.EMPを保護するための設定が完了しました。

作成された仮想プライベート・データベースのポリシーはビューALL_POLICIESより参照できます。

APEXアプリケーションの変更

APEXアプリケーションにアプリケーション・コンテキストを操作する設定を追加します。アプリケーション定義属性のセキュティのデータベース・セッションの初期化PL/SQLコードとして

vpdadmin.emp_dept_ctx_pkg.init;

PL/SQLコードのクリーンアップとして

vpdadmin.emp_dept_ctx_pkg.clear;

を指定します。

以上でアプリケーションの修正も完了です。

動作確認

アプリケーションにユーザーSCOTTでサインインし、従業員名として以下を指定してレポートを表示させます。

SCOTT' or '1' = '1

レポートのSQL問合せには変更がなくSQLインジェクションに脆弱なままですが、レポートに表示される従業員はSCOTTと同じ部門の従業員に限定されています。

続いて、以下を指定してレポートを表示します。

SCOTT' UNION SELECT EMPNO, ENAME || ' - ' || SAL || ' - ' || COMM ENAME, JOB, MGR, HIREDATE, DEPTNO FROM HR.EMP WHERE '1'='1

Enameに列SALの値が表示されていますが、SCOTTがマネージャーである従業員ADAMSに限定されています。

ポリシーの削除

後続の作業に影響があるため、作成した仮想プライベート・データベースのポリシーを削除します。

	begin
	dbms_rls.drop_policy(
	object_schema => 'hr'
	, object_name => 'emp'
	, policy_name => 'emp_in_same_deptno'
	);
	dbms_rls.drop_policy(
	object_schema => 'hr'
	, object_name => 'emp'
	, policy_name => 'emp_is_mgr'
	);
	end;
	/

view raw seminar210825-drop_vpd_policies.sql hosted with ❤ by GitHub

続く