XMLを使いこなそう   okulejp

1．説明
DB2のLBACは便利ですが、それと同じようなことを、pureXMLで実現する方法を説明します。
ロールの構造は、下記のような組織構造をとるものとします。説明のため単純化してありますが、すきなだけ数だけ階層化できます。商品情報テーブルのROLE列には、そのデータを表示できるロールが設定されています。商品情報のロールに、東京支店の社員が設定されていた場合は、そのデータを見ることができるのは、東京支店の社員、東京支店長、社長です。商品情報のロールに、千葉支店の社員が設定されていた場合は、そのデータを見ることができるのは、千葉支店の社員、千葉支店長、社長です。XML形式の会社組織のデータは、SECURITY_ROLE_TREEテーブルのCONTENT列に格納されています。

図 １. 会社組織図

図 ２. XML形式の会社組織

<roles>
  <role id="k1" name="社長">
      <role id="k1_1" name="東京支店長">
        <role id="k1_1_1" name="社員"></role>
      </role>
      <role id="k1_2" name="千葉支店長">
        <role id="k1_2_1" name="社員">
        </role>
      </role>
  </role>
</roles>

図 ３. 商品情報(PRODUCT_INFO)テーブルの全部のデータ

２．行に対するアクセス制御
11行目の$MYROLEは、6行目のMYROLEを示します。11行名の$CONTENT変数は、SECURITY_ROLE_TREEテーブルのCONTENT列を示します。13行目の$ROLE変数は、PRODUCT_INFOテーブルのROLE列を示します。このように、pureXMLの便利なところは、DB2により列名と同じ名前の変数が、自動的に作成されことです。6行目のMYROLEは、WEBアプリケーションのプログラムで直下書きになります。(「’k_1_1’」の部分が該当。ログイン時に、ログインユーザのロールを、セッション情報に保持するなどすれば、プログラムで取得することは、容易なはずです。)
「descendant-or-self」軸の利用が肝です。13行のif文で、自分自身のロールが、商品情報のロールと同じか、または、子孫方向に商品情報のロールがあれば、1の値を持つシーケンスを返し、そうでなければ、空シーケンスを返します。またXMLEXISTS関数は、与えられた式が空シーケンスであればFALSEを、それ以外はTRUEを返します。

図 ４. 行に対して、ロールのルールを適用する場合。(東京支店の社員の場合。)

SELECT PI.ID, PI.NAME, PI.PRICE, PI.ROLE 
FROM 
  PRODUCT_INFO PI, 
  SECURITY_ROLE_TREE SRT,
  ( 
    SELECT 'k1_1_1' AS MYROLE 
    FROM SYSIBM.SYSDUMMY1
  ) SD  
WHERE 
  XMLEXISTS('
    let $myRolePosition := $CONTENT//*[@id=$MYROLE] 
    return 
      if($myRolePosition/descendant-or-self::*[@id = $ROLE]) then 
        (1)
      else 
        ()
  ')

図 ５. 結果(千葉支店の社員のデータが表示されません。)

図 ６. 行に対して、ロールのルールを適用する場合。(社長の場合。)

SELECT PI.ID, PI.NAME, PI.PRICE, PI.ROLE 
FROM 
  PRODUCT_INFO PI, 
  SECURITY_ROLE_TREE SRT,
  ( 
    SELECT 'k1' AS MYROLE 
    FROM SYSIBM.SYSDUMMY1
  ) SD  
WHERE 
  XMLEXISTS('
    let $myRolePosition := $CONTENT//*[@id=$MYROLE] 
    return 
      if($myRolePosition/descendant-or-self::*[@id = $ROLE]) then 
        (1)
      else 
        ()
  ')

図 ７. 結果(すべてのデータが表示されます。)

３．列に対するアクセス制御
図 ８. 列に対して、ロールのルールを適用する場合で、「*****」を返す場合。(東京支店の社員の場合。)

SELECT PI.ID, PI.NAME, XMLCAST( XMLQUERY('
    let $myRolePosition := $CONTENT//*[@id=$MYROLE] 
    return 
      if($myRolePosition/descendant-or-self::*[@id = $ROLE]) then 
        $PRICE
      else 
        "*****"
  ') AS VARCHAR(9)), PI.ROLE  
FROM 
  PRODUCT_INFO PI, 
  SECURITY_ROLE_TREE SRT,
  ( 
    SELECT 'k1_1_1' AS MYROLE 
    FROM SYSIBM.SYSDUMMY1
  ) SD

図 ９. 結果(千葉支店の社員のデータがマスキングされています。)

図 １０. 列に対して、ロールのルールを適用する場合で、NULLを返す場合。(東京支店の社員の場合。)

SELECT PI.ID, PI.NAME, XMLCAST( XMLQUERY('
    let $myRolePosition := $CONTENT//*[@id=$MYROLE] 
    return 
      if($myRolePosition/descendant-or-self::*[@id = $ROLE]) then 
        $PRICE
      else 
        ()
  ') AS VARCHAR(9)), PI.ROLE  
FROM 
  PRODUCT_INFO PI, 
  SECURITY_ROLE_TREE SRT,
  ( 
    SELECT 'k1_1_1' AS MYROLE 
    FROM SYSIBM.SYSDUMMY1
  ) SD

図 １１. 結果(千葉支店の社員のデータがNULLになっています。)

図 １２. 列に対して、ロールのルールを適用する場合で、NULLを返す場合。(社長の場合。)

SELECT PI.ID, PI.NAME, XMLCAST( XMLQUERY('
    let $myRolePosition := $CONTENT//*[@id=$MYROLE] 
    return 
      if($myRolePosition/descendant-or-self::*[@id = $ROLE]) then 
        $PRICE
      else 
        ()
  ') AS VARCHAR(9)), PI.ROLE  
FROM 
  PRODUCT_INFO PI, 
  SECURITY_ROLE_TREE SRT,
  ( 
    SELECT 'k1' AS MYROLE 
    FROM SYSIBM.SYSDUMMY1
  ) SD

図 １３. 結果(マスキングされていません。)

４．総評
複雑な組織の階層構造に対応したアクセス制御機能が、DB2 pureXMLを使用することで、いとも簡単に実現できてしまう。この記事を読んだ、読者のみなさんの中で、これをヒントにして、もっといいアイディアが浮かぶかもしれません。そう思わせるほど、DB2 pureXMLには、大きな可能性を秘めていると思います。この方法は、WEBアプリケーションからのアプローチです。いいかえれば、ROLEの定義をデータベースで行うのか、WEBアプリケーションで行うかの違いです。

５．付録
ここで使用した、テーブル定義とデータです。

CREATE TABLE PRODUCT_INFO (
  ID INT NOT NULL,
  NAME VARCHAR(50),
  PRICE INT NOT NULL,
  ROLE VARCHAR(10) NOT NULL 
);

ALTER TABLE PRODUCT_INFO ADD CONSTRAINT PK_ID
 PRIMARY KEY(ID);

INSERT INTO PRODUCT_INFO (ID, NAME, PRICE, ROLE) VALUES (1, 'ブルーレイ', 120000, 'k1_1_1');

INSERT INTO PRODUCT_INFO (ID, NAME, PRICE, ROLE) VALUES (2, 'ビデオカメラ', 60000, 'k1_2_1');

CREATE TABLE SECURITY_ROLE_TREE (
  CONTENT XML NOT NULL 
);


INSERT INTO SECURITY_ROLE_TREE (CONTENT) 
VALUES (
'
<roles>
  <role id="k1" name="社長">
      <role id="k1_1" name="東京支店長">
        <role id="k1_1_1" name="社員"></role>
      </role>
      <role id="k1_2" name="千葉支店長">
        <role id="k1_2_1" name="社員">
        </role>
      </role>
  </role>
</roles>
');

©中條勝徳 and okulejp.com, 2012.