DB2」タグのアーカイブ

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)

タグ: ,

EclipseLinkにおける、DB2の拡張クライアント情報の設定方法   Leave a comment

EclipseLinkを利用した、DB2の拡張クライアント情報の設定方法がなかったので、設定方法をブログに載せます。

1.SessionEventAdapterの実装
postAcquireConnectionメソッドは、DBコネクションを取得した後に呼び出されます。preReleaseConnectionメソッドは、DBコネクションを開放するときに呼び出されます。postAcquireConnectionメソッドの24~26行目で、拡張クライアント情報を設定しています。preReleaseConnectionの39~41行目で、拡張クライアント情報を初期化しています。

図 1. SessionEventAdapterの実装例(CustomSessionEventAdapter.java)

package com.wordpress.okulejp.sesstion;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.Map;
import java.util.logging.Logger;
import javax.faces.context.FacesContext;
import javax.servlet.http.HttpServletRequest;
import org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor;
import org.eclipse.persistence.sessions.SessionEvent;
import org.eclipse.persistence.sessions.SessionEventAdapter;

public class CustomSessionEventAdapter extends SessionEventAdapter {
  
  @Override
  public void postAcquireConnection(SessionEvent event) {
    Logger logger = Logger.getLogger(this.getClass().getName());
    logger.fine("postAcquireConnection start");
    try {
      FacesContext fc =  FacesContext.getCurrentInstance();
      Map<String,Object> sessionMap = fc.getExternalContext().getSessionMap();
      Connection con= ((DatabaseAccessor) event.getResult()).getConnection();
      HttpServletRequest request = (HttpServletRequest) fc.getExternalContext().getRequest();
      con.setClientInfo("ApplicationName", (String) sessionMap.get("ApplicationName"));
      con.setClientInfo("ClientUser", request.getRemoteUser());
      con.setClientInfo("ClientHostname", request.getRemoteAddr());
    } catch(SQLException ex) {
      throw new RuntimeException(ex.getMessage());
    }
    logger.fine("postAcquireConnection end");
  }
    
  @Override
  public void preReleaseConnection(SessionEvent event) {
    Logger logger = Logger.getLogger(this.getClass().getName());
    logger.fine("preReleaseConnection start");
    try {
      java.sql.Connection con= ((DatabaseAccessor) event.getResult()).getConnection();
      con.setClientInfo("ApplicationName", null);
      con.setClientInfo("ClientUser", null);
      con.setClientInfo("ClientHostname", null);
    } catch(SQLException ex) {
      throw new RuntimeException(ex.getMessage());
    }
    logger.fine("preReleaseConnection end");
  }
    
}

2.persistence.xmlの設定
9行目で、実装したSessionEventAdapterを呼び出す設定をしています。

図 2. persistence.xml

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.0" xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
             xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd">
  <persistence-unit name="ngsPU" transaction-type="JTA">
    <provider>org.eclipse.persistence.jpa.PersistenceProvider</provider>
    <jta-data-source>jdbc/db2</jta-data-source>
    <exclude-unlisted-classes>false</exclude-unlisted-classes>
    <properties>
        <property name="eclipselink.session-event-listener" value="com.wordpress.okulejp.sesstion.CustomSessionEventAdapter"/>
        <property name="eclipselink.logging.level" value="FINE"/>
        <property name="eclipselink.logging.level.sql" value="FINE"/>
        <property name="eclipselink.logging.parameters" value="true"/>
    </properties>
  </persistence-unit>
</persistence>

3.DB2監査ログ
12~14行目で、CustomSessionEventAdapter.javaで設定した拡張クライアント情報が出力されています。またDB2監査ログの設定しだいで、42~52行目のようにSQLパラメータの出力も可能です。
(※注意10,13行目で、IPアドレスを「*」でマスキングしてありますが、実際に監査ログには値が出力されます。)

図 3. DB2監査ログ出力結果

timestamp=2012-12-19-10.50.32.423000;
  category=EXECUTE;
  audit event=STATEMENT;
  event correlator=55;
  event status=0;
  database=MYDB;
  userid=OKULE;
  authid=ADMIN;
  session authid=ADMIN;
  application id=192.168.*.*.54300.121219013706;
  application name=db2jcc_application;
  client userid=test_id;
  client workstation name=192.168.*.*;
  client application name=test_appName;
  package schema=OKULE;
  package name=SYSSH200;
  package section=1;
  local transaction id=0xd7064d0000000000;
  global transaction id=0x0000000000000000000000000000000000000000;
  uow id=25;
  activity id=1;
  statement invocation id=0;
  statement nesting level=0;
  activity type=WRITE_DML;
  statement text=UPDATE DB2_AUDIT_TEST SET UPDATE_PROGRAM_ID = ? , UPDATE_USER_ID = ? , UPDATE_TIMESTAMP = SYSDATE WHERE ID = ?;
  statement isolation level=CS;
  Compilation Environment Description
    isolation: CS
    query optimization: 5
    min dec div 3: NO
    degree: 1    
    SQL rules: DB2
    refresh age: +00000000000000.000000
    resolution timestamp: 2012-12-19-10.50.30.000000
    federated asynchrony: 0
    temporal business time: 0000-00-00-00.00.00.000000000000
    temporal system time: 0000-00-00-00.00.00.000000000000
    schema: OKULE   
    maintained table type: SYSTEM;
  rows modified=1;
  rows returned=0;
  value index = 1
    type = VARCHAR 
    data = MA0304;
    extended indicator = 0;
  value index = 2
    type = VARCHAR 
    data = admin;
    extended indicator = 0;
  value index = 3
    type = VARCHAR 
    data = k3;
    extended indicator = 0;
  local start time=2012-12-19-10.50.30.772729;

4.参考URL
IBM Data Server Driver for JDBC and SQLJ によるクライアント情報プロパティーのサポート
Java Persistence API (JPA) Extensions Reference for EclipseLink session-event-listener

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

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

タグ: ,

DB2 Express-C 10.1 pureXML 実行計画の変更点   Leave a comment

Figure 1.実行計画 DB2 v9.7.5
explan

Figure 2.実行計画 DB2 v10.1.0
explan

DB2 Express-C 9.7.5では、fn:starts-withを使用した場合、pureXML専用のインデックススキャンであるXISCANは使用されてなかったが、DB2 Express-C 10.1.0では使用されるようになった。前方検索するときに、XISCANが実行されることを強く望んでいたので、大変喜ばしいことである。理由は、当然インデックス索引を使用されないと、100万レコードみたいな大量のXML文書の検索のときに時間がとてもかかるからである。

Figure 3. SQL

SELECT * FROM OKULE.CLIENTS
WHERE xmlexists(
  '$c//phone/*[fn:starts-with(.,"5" )]' 
  passing OKULE.CLIENTS.CONTACT AS "c" 
)

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

okule による DB2, pureXML, XML, XQuery への投稿 (7月 18, 2012)

タグ: , , ,