「DB2」カテゴリーのアーカイブ

DB2 Express-C 10.5 FP1(10.5.1)がリリースされました。   Leave a comment

DB2 Express-C 10.5 FP1(10.5.1)がリリースされました。

DB2 Express-C 10.5がリリースされた当初、Text Searchの機能が利用できなかったので、あきらめていましたが、今回のリリースで、Text Searchの機能を利用できるようになったので良かったです。Text Searchがインストールできない件を日本語のサイトで検索しても、検索できなかったことに対して残念に思っていました。全文検索機能のText Searchは、非常に便利なのにもかかわらず、注目されていないようで、すごくがっかりです。XMLのデータに対しても、全文検索機能を利用できます。

下記フォーラムは、Text Searchがインストールできない件での質疑応答があります。(英語サイトです。)
DB2 Express-C 10.5: Text Search component is not installed

参照
DB2 for LUW V10.5 FP1 公開のお知らせ(DM-13-024)

okule による DB2 への投稿 (10月 12, 2013)

DB2 Express-C 10.1.2がリリースされました。   Leave a comment

DB2 Express-C 10.1.2がリリースされました。

参照
無料のDB2 Express-cが10.1 FP2に更新。Mac OS X版のDB2もv10.1に更新!

okule による DB2 への投稿 (12月 19, 2012)

DB2のLBACみたいなことをpureXMLを使用して実現する   Leave a comment

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

図 1. 会社組織図

図 2. 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>

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

2.行に対するアクセス制御
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を返します。

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

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 
        ()
  ')

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

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

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 
        ()
  ')

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

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

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

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

図 10. 列に対して、ロールのルールを適用する場合で、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

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

図 12. 列に対して、ロールのルールを適用する場合で、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

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

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

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

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.

okule による DB2, pureXML への投稿 (12月 19, 2012)

タグ: ,