CREATE MASK

オンライン編集

CREATE MASK ステートメントは、現行サーバーで列アクセス制御の列マスクを作成します。列マスクは、指定された列について戻される値を指定します。

呼び出し

このステートメントは、アプリケーション・プログラムに組み込むことも、あるいは対話式に実行することもできます。これは、動的に準備できる実行可能ステートメントです。

許可

ステートメントの許可 ID には、セキュリティー管理者権限が必要です。管理権限 (Administrative authority)を参照してください。

構文

説明

OR REPLACE

列マスクの定義が現行のサーバー上に存在している場合に、その列マスクの定義を置換するために指定します。既存の定義は、新しい定義がカタログ内で置換される前に、効率的にドロップされます。

マスク名

列アクセス制御の列マスクの名前を指定します。暗黙的または明示的修飾子も含め、この名前は、現行サーバーに既に存在している列マスクまたは行の許可と同じ名前にすることはできません。 mask-name は QIBM で始まってはなりません。

SQL 名が指定されている場合、マスクは、暗黙的または明示的修飾子で指定しているスキーマ内に作成されます。

システム名が指定されている場合、マスクは、修飾子で指定しているスキーマ内に作成されます。修飾されておらず、デフォルトのスキーマがない場合、マスクは table-name と同じスキーマに作成されます。

mask-name のスキーマ名は、table-name のスキーマ名と同じでなければなりません。

テーブル名: 列マスクを作成する対象の表を指定します。この名前は、現行サーバーに存在する表を示すものでなければなりません。宣言済み一時表、 QTEMP 内の表、分散表、ビュー、論理ファイル、メンバー別名、読み取りトリガーのあるファイル、またはカタログ表を示すものであってはなりません。
相関名 (correlation-name): 表を指定するために case-expression 内で使用できる相関名を指定します。

FOR COLUMN 列名: マスクを適用する列を指定します。表の列を示す非修飾名でなければなりません。この列にはマスクが既に存在していてはなりません。

RETURN ケース式

列について戻す値を決定するために評価される CASE 式を指定します。 CASE 式の結果が、行の列値の位置に返されます。 CASE 式の結果のデータ・タイプ、長さ、ヌル属性、および CCSID は、列のデータ・タイプと互換でなければなりません。列が NULL 値を許容しない場合、CASE 式の結果は NULL 値にはなりません。データ・タイプの互換性について詳しくは、割り当ておよび比較を参照してください。 column-name のデータ・タイプがユーザー定義のデータ・タイプである場合、 CASE 式の結果のデータ・タイプは同じユーザー定義のタイプでなければなりません。 CASE 式で参照されるオブジェクトは、現行サーバーに存在していなければなりません。 CASE 式では以下を参照してはなりません。

列マスクが定義されている表
宣言済み一時表
変数 (ホスト変数、SQL 変数、SQL パラメーター、またはトリガー遷移変数)
パラメーター・マーカー
NOT SECURED として定義されているユーザー定義関数
非 deterministic 関数¹ 、または外部アクションを持つ関数
列マスクを定義する表を参照する RRN、RID、HASHED_VALUE、DATAPARTITIONNAME、DATAPARTITIONNUM、 DBPARTITIONNAME、または DBPARTITIONNUM 関数
OLAP 指定
ROW CHANGE 式
シーケンス参照
A * または name.* select 節における
QTEMP 内の表
メンバー別名
分散表
読み取りトリガーのあるファイル
複数フォーマット論理ファイル
リモート・オブジェクト
上記のいずれかを含んでいるビュー

ENABLE または DISABLE

列アクセス制御のための列マスクを使用可能または使用不可にすることを指定します。

DISABLE: 列アクセス制御のための列マスクを使用不可に設定することを指定します。表の列アクセス制御が活動化されているかどうかに関係なく、列マスクは無効なままになります。これはデフォルト値。
ENABLE: 列アクセス制御で列マスクを有効にするように指定します。この表の列アクセス制御が現在活動化されていない場合、列マスクは、表の列アクセス制御が活動化されると有効になります。表の列アクセス制御が現在活動化されている場合、列マスクは直ちに有効になります。

ノート

前提条件: マスクを作成するには、 IBM® Advanced Data Security がインストールされている必要があります。

照会への列マスクの影響: 有効な列マスクを適用しても、ステートメント内の他の節 (WHERE、GROUP BY、HAVING、SELECT DISTINCT、ORDER BY など) の操作が妨げられることはありません。最終結果表に戻される行は同じですが、結果行の値が列マスクによってマスクされている可能性があります。このため、sort-key 式が含まれている ORDER BY 文節にマスクされた列も指定されている場合、順序は列の元の値に基づいており、最終結果表のマスクされた値にはこの順序が反映されていないことがあります。同様に、マスクされた値には、SELECT DISTINCT ステートメントにより適用される固有性が反映されない可能性があります。マスクされた列が式に組み込まれている場合、式評価が可能になる前に列マスクが列に適用されるので、式の結果が異なることがあります。例えば、列 SSN の列マスクにより、集約関数 COUNT(DISTINCT SSN) の結果が変更されることがあります。これは、DISTINCT 操作がマスクされた値に対して実行されるためです。ただし、照会の中の式が、列マスク定義で列値をマスクするために使用された式と同じ場合、式の結果は変化しない可能性があります。例えば、照会の中の式が 'XXX-XX-' || SUBSTR(SSN, 8, 4) であり、これと同じ式が列マスク定義にも指定されているとします。この特定の例では、照会から式を削除して、同じ式が 2 回評価されるのを回避できます。

列マスクと SQL定義との競合 : 列マスクは、使用される可能性のあるすべてのコンテキストを知らなくても、スタンドアロンオブジェクトとして作成されます。最終結果表の列の値をマスクするために、 Db2®によって列マスクの定義が照会にマージされます。列マスクの定義がステートメントのコンテキストに取り込まれると、ステートメント内の特定の SQL セマンティクスと競合する可能性があります。したがって、特定の状況では、ステートメントと列マスクの適用方法の組み合わせによってはエラーが戻されることがあります。この状況が発生した場合は、ステートメントを変更するか、または列マスクの削除または異なる定義での再作成を行う必要があります。

列マスクとヌル列: 列が NULL 可能でない場合、その列の列マスクの定義は、ほとんどの場合、列の NULL 値を考慮しません。外部結合の NULL 埋め込み表の場合、表の列アクセス制御がアクティブにされた後で、最終結果表の列値が NULL になる可能性があります。列マスクが NULL 値をマスクできるようにするために、表が外部結合の NULL 埋め込み表である場合、 Db2 は、最初の WHEN 節として「WHEN target-column IS NULL THEN NULL」を列マスク定義に追加します。これにより、NULL 値が常に NULL 値として強制的にマスクされます。 NULL 可能列の場合、これにより、NULL 値を他の値としてマスクすることができなくなります。例 4 に、この追加される WHEN 文節を示します。

データ変更 SQL ステートメントでの列マスク値: INSERT、UPDATE、および MERGE では、新しい行の値を導出するときに列が参照されている場合に、その列に対して有効な列マスクがあれば、マスクされた値が新しい値を導出するために使用されます。オブジェクト表でも列アクセス制御がアクティブになっている場合、新しい値を導出するために適用される列マスクは、定数や式ではなく、列自体を戻す必要があります。列マスクを列に適用した結果が列自体にならない場合、新しい値を挿入または更新に使用できず、エラーが戻されます。新しい値を得るために列マスクを適用するときに使用されるルールは、照会の最終結果表の同じルールに従います。挿入可能性と更新可能性に影響する列マスクの使用方法については、データ変更ステートメントを参照してください。

列マスクとトリガー遷移変数: OLD ROW および OLD TABLE 遷移変数の値に、マスクされた値が含まれることはありません。

SET 遷移変数 割り当てステートメントは、マスクされたデータを変数に割り当てることができます。列に違反チェック制約が存在しない場合、マスクされたデータが表の列で挿入または更新され、エラーは発行されません。

列アクセス制御がアクティブになる前に作成された列マスク: CREATE MASK ステートメントは独立したステートメントであり、これを使用して、表の列アクセス制御がアクティブになる前に列アクセス制御マスクを作成することができます。この場合の唯一の要件は、表と列がマスクの作成前に存在していることです。 1 つの表に対して複数の列マスクを作成できますが、1 つの列に設定できるマスクは 1 つのみです。マスクの定義は DB2 カタログに格納されます。マスクの作成対象の表への従属関係と、定義で参照されるその他のオブジェクトへの従属関係が記録されます。列マスクの作成時には、列アクセス制御に対してその列マスクが有効または無効であるかどうかを指定できます。有効に設定されている列マスクは、ACTIVATE COLUMN ACCESS CONTROL 文節が指定された ALTER TABLE ステートメントを使用して表の列アクセス制御がアクティブになった後で、初めて有効になります。表の列アクセス制御がアクティブになっても、無効な列マスクは引き続き無効です。 ALTER MASK ステートメントを使用して、ENABLE と DISABLE を切り替えることができます。表の列アクセス制御をアクティブにした後で、データ操作ステートメントで表が参照されると、この表に対して作成された有効な列マスクがすべて DB2 により暗黙に適用され、これにより照会の最終結果表で参照されている列に戻される値がマスクされるか、またはデータ変更ステートメントで使用される新しい値が決定します。

行アクセス制御がアクティブになった後で作成された列マスク: 有効に設定された列マスクは、コミットされるとすぐに有効になります。したがって、データ操作ステートメントで表が参照されている場合、有効な列マスクはすべて、DB2 によりそのステートメントに暗黙に適用されます。表の列アクセス制御がアクティブになっても、無効な列マスクは引き続き無効です。

列アクセス制御または行アクセス制御が適用されている表が列マスク定義で参照される場合、カスケード効果はない: 列マスク定義が、行アクセス制御または列アクセス制御が現在適用されている表と列を参照することがあります。列マスクの作成対象の表がデータ操作ステートメントで参照される場合、このような表と列のアクセス制御は無視されます。

例

例 1: 表 EMPLOYEE の列アクセス制御がアクティブになった後で、給与管理部門の Paul は、従業員番号が 123456 の従業員の社会保障番号を確認できます。管理者である Maryは、社会保障番号の最後の 4 文字のみを表示できます。 Peter は給与管理部門に属しておらず、また管理者でもないため、ソーシャル・セキュリティー番号を確認できません。

CREATE MASK SSN_MASK ON EMPLOYEE
   FOR COLUMN SSN RETURN
      CASE
         WHEN (VERIFY_GROUP_FOR_USER(SESSION_USER,'PAYROLL') = 1)
            THEN SSN
         WHEN (VERIFY_GROUP_FOR_USER(SESSION_USER,'MGR') = 1)
            THEN 'XXX-XX-' || SUBSTR(SSN,8,4)
         ELSE NULL
      END
   ENABLE;

COMMIT;

ALTER TABLE EMPLOYEE
   ACTIVATE COLUMN ACCESS CONTROL;

COMMIT;

SELECT SSN FROM EMPLOYEE
   WHERE EMPNO = 123456;

例 2: SELECT ステートメントで、列マスク SSN_MASK に使用されている式と同じ式に列 SSN が組み込まれています。表 EMPLOYEE に対して列アクセス制御をアクティブにした後に、列マスク SSN_MASK が SELECT ステートメントの列 SSN に適用されます。この特定の式の場合、SELECT ステートメントの結果は、すべてのユーザーに対して列アクセス制御をアクティブにする前と同じ結果になります。ユーザーは SELECT ステートメントの式を列 SSN に置き換えることによって、同じ式が 2 回評価されるのを回避できます。

CREATE MASK SSN_MASK ON EMPLOYEE
   FOR COLUMN SSN RETURN
      CASE
         WHEN (1 = 1)
             THEN 'XXX-XX-' || SUBSTR(SSN,8,4)
         ELSE NULL
      END
   ENABLE;

COMMIT;

ALTER TABLE EMPLOYEE
   ACTIVATE COLUMN ACCESS CONTROL;

COMMIT;

SELECT 'XXX-XX-' || SUBSTR(SSN,8,4) FROM EMPLOYEE
   WHERE EMPNO = 123456;

例 3 EMPNO が 123456 の従業員の 5 月のボーナスは $8000、給与は $80000 です。管理者がこの従業員の給与を取り出すときに、NULL 値ではなく給与額が取り出されます。これは、列マスク SALARY_MASK が、列マスク BONUS_MASK が定義されている列 BONUS を参照する場合、カスケード効果が発生しないためです。

CREATE MASK SALARY_MASK ON EMPLOYEE
   FOR COLUMN SALARY RETURN
      CASE
         WHEN (BONUS < 10000)
            THEN SALARY
         ELSE NULL
      END
   ENABLE;

COMMIT;

CREATE MASK BONUS_MASK ON EMPLOYEE
   FOR COLUMN BONUS RETURN
      CASE
         WHEN (BONUS > 5000)
            THEN NULL
         ELSE BONUS
      END
   ENABLE;

COMMIT;

ALTER TABLE EMPLOYEE
   ACTIVATE COLUMN ACCESS CONTROL;

COMMIT;

SELECT SALARY FROM EMPLOYEE
   WHERE EMPNO = 123456;

例 4: この例では、 Db2 が最初の WHEN 文節として「WHEN target-column IS NULL THEN NULL」を列マスク定義に追加してから、列マスク定義をステートメントにマージします。

CREATE TABLE EMPLOYEE (EMPID INT,
                       DEPTID CHAR(8),
                       SALARY DEC(9,2) NOT NULL,
                       BONUS DEC(9,2));

CREATE MASK SALARY_MASK ON EMPLOYEE
   FOR COLUMN SALARY RETURN
      CASE
         WHEN SALARY < 10000
            THEN CAST(SALARY*2 AS DEC(9,2))
         ELSE COALESCE(CAST(SALARY/2 AS DEC(9,2)), BONUS)
      END
   ENABLE;

COMMIT;

CREATE MASK BONUS_MASK ON EMPLOYEE
   FOR COLUMN BONUS RETURN
      CASE
         WHEN BONUS > 1000
            THEN BONUS
         ELSE NULL
      END
   ENABLE;

COMMIT;

ALTER TABLE EMPLOYEE
   ACTIVATE COLUMN ACCESS CONTROL;

COMMIT;

SELECT SALARY FROM DEPT
   LEFT JOIN EMPLOYEE ON DEPTNO = DEPTID;

/* When SALARY_MASK is effectively merged into the above statement,
* 'WHEN SALARY IS NULL THEN NULL' is added by Db2 as the
* first WHEN clause, as follows:
*/

SELECT CASE WHEN SALARY IS NULL THEN NULL
            WHEN SALARY < 10000 THEN CAST(SALARY*2 AS DEC(9,2))
            ELSE COALESCE(CAST(SALARY/2 AS DEC(9,2)), BONUS)
       END SALARY
  FROM DEPT
    LEFT JOIN EMPLOYEE ON DEPTNO = DEPTID;

¹ STATEMENT DETERMINISTIC 関数は許可されていますが、推奨されません。