2020年3月17日火曜日

RS256で作成したJWTを検証する

以前に書いたこちらの記事の継続です。

JWTは作成できたので、今度は検証する方法を実装してみます。以前の記事のJavaコードには署名(sign)しか実装がありませんので、検証(verify)を追加しました。
import java.math.BigInteger; 
import java.util.Base64;
import java.security.KeyFactory;
import java.security.Signature;
import java.security.PublicKey;
import java.security.PrivateKey;
import java.security.SignatureException;
import java.security.NoSuchAlgorithmException;
import java.security.spec.RSAPrivateCrtKeySpec;
import java.security.spec.X509EncodedKeySpec;

import sun.security.util.DerInputStream;
import sun.security.util.DerValue;
  
public class SHA256withRSA { 
  /**
   * RSASSA-PKCS1-v1_5 with SHA-256によるデジタル署名の生成
   *
   * @param header    BASE64エンコード済みのJWTヘッダー
   * @param payload   BASE64エンコード済みのJWTペイロード
   * @param key       PKCS#1形式でのRSA秘密鍵(BEGIN/END行なし、改行なし)
   * @return デジタル署名 - BASE64エンコード済み
   */
  public static String sign(String header, String payload, String key)
  throws Exception
  {
    /* header and payload are both encoded in base64 */
    byte[] data = new String(header + "." + payload).getBytes("UTF-8");

    /* get PrivateKey instance from PKCS#1 */
    byte[] pkdata = Base64.getDecoder().decode(key);
    DerInputStream derReader = new DerInputStream(pkdata);
    DerValue[] seq = derReader.getSequence(0);
    // skip version seq[0];
    BigInteger modulus = seq[1].getBigInteger();
    BigInteger publicExp = seq[2].getBigInteger();
    BigInteger privateExp = seq[3].getBigInteger();
    BigInteger prime1 = seq[4].getBigInteger();
    BigInteger prime2 = seq[5].getBigInteger();
    BigInteger exp1 = seq[6].getBigInteger();
    BigInteger exp2 = seq[7].getBigInteger();
    BigInteger crtCoef = seq[8].getBigInteger();

    RSAPrivateCrtKeySpec keySpec =
      new RSAPrivateCrtKeySpec(modulus, publicExp, privateExp, prime1, prime2, exp1, exp2, crtCoef);
    KeyFactory keyFactory = KeyFactory.getInstance("RSA");
    PrivateKey privateKey = keyFactory.generatePrivate(keySpec);

    /* creating the object of Signature then sign */
    Signature sr = Signature.getInstance("SHA256withRSA"); 
    sr.initSign(privateKey);
    sr.update(data);
    byte[] bytes = sr.sign();
   
    /* return signature in Base64 */ 
    return Base64.getEncoder().encodeToString(bytes);
  }

  /**
   * RSASSA-PKCS1-v1_5 with SHA-256によるデジタル署名の検証
   *
   * @param header    BASE64エンコード済みのJWTヘッダー
   * @param payload   BASE64エンコード済みのJWTペイロード
   * @param sig       BASE64エンコードされた電子署名
   * @param key       RSA公開鍵(BEGIN/END行なし、改行なし)
   * @return 検証結果
   */
  public static boolean verify(String header, String payload, String sig, String key)
  throws Exception
  {
    /* header and payload are both encoded in base64 */
    byte[] data = new String(header + "." + payload).getBytes("UTF-8");
    /* get signature in binary representation. */
    byte[] dsig = Base64.getDecoder().decode(sig);

    /* get PublicKey instance from X.509 */
    byte[] pkdata = Base64.getDecoder().decode(key);
    X509EncodedKeySpec keySpec = new X509EncodedKeySpec(pkdata);
    KeyFactory keyFactory = KeyFactory.getInstance("RSA");
    PublicKey publicKey = keyFactory.generatePublic(keySpec);

    /* creating the object of Signature then verify */
    Signature sr = Signature.getInstance("SHA256withRSA");
    sr.initVerify(publicKey);
    sr.update(data);
    return sr.verify(dsig);
  }
}
データベースにロードするのは、前回とまったく同じloadjavaコマンドを使用します。
loadjava -force -verbose -resolve -u myworkspace/**********@localhost/service.world SHA256withRSA.java
追加したメソッドのラッパーとなるファンクションを定義します。
create or replace function sha256withrsa_verify
(
  header      varchar2,
  payload     varchar2,
  signature   varchar2,
  public_key  varchar2
) return boolean
as
language java name 'SHA256withRSA.verify (java.lang.String, java.lang.String, java.lang.String, java.lang.String) return boolean';
/
RSA公開鍵はこちらの記事で紹介したのと、同じ方法で生成します。まずはキー・ペアを生成し、
mkdir -p ~/.oci && openssl genrsa -out ~/.oci/poa_oci_api_key.pem 2048
それから公開鍵を取り出します。
openssl rsa -pubout -in ~/.oci/poa_oci_api_key.pem -out ~/.oci/poa_oci_api_key_public.pem
実際のデータの部分だけを一行にして取り出せるように、こちらの記事で紹介したスクリプトも以下に紹介しておきます。
#!/bin/sh

while read l
do
   test ${l:0:1} != "-" && /bin/echo -n $l
done < ~/.oci/poa_oci_api_key_public.pem
echo
これで、データベース側の準備はできたので、JWTの検証処理を行ってみます。検証には以下のPL/SQLコードを使用しました。
set lines 1000
set serveroutput on
declare
  l_public    varchar2(32767) := 'MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMII***一部省略***dgaetswIDAQAB';
  l_jwt       varchar2(32767) := 'eyJhbGciOiJSUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJzcWxwbHVzIiwic3ViIjoiVEVTVFVTRVIiLCJhdWQiOiJBUEVYIiwiaWF0***一部省略***hLzvCH7iy9OxZMXlZ9qN_doIqjV0Q';
  l_username  varchar2(32);
  l_header_json json_object_t;
  l_header_len  number;
  l_header_base64 varchar2(400);
  l_payload_len number;
  l_payload_json json_object_t;
  l_payload_base64 varchar2(800);
  l_hmac varchar2(1000);

  -- Base64のデコード
  function from_base64(t in varchar2) return varchar2 is
  begin
    return utl_raw.cast_to_varchar2(utl_encode.base64_decode(utl_raw.cast_to_raw(t)));
  end from_base64;

  -- Base64のエンコード
  function to_base64(t in varchar2) return varchar2 is
    l_base64 varchar2(32767);
  begin
    l_base64 := utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(t)));
    l_base64 := replace(l_base64, chr(13)||chr(10), '');
    return l_base64;
  end to_base64;
begin
  /* トークンの内容を印刷 */
  l_header_len     := instr(l_jwt, '.', 1);
  l_header_base64  := substr(l_jwt, 1, l_header_len - 1);
  l_header_json    := json_object_t.parse(from_base64(l_header_base64));
  dbms_output.put_line('Header = ' || l_header_json.to_string);
  l_jwt := substr(l_jwt, l_header_len + 1);
  l_payload_len    := instr(l_jwt, '.', 1);
  l_payload_base64 := substr(l_jwt, 1, l_payload_len - 1);
  l_payload_json   := json_object_t.parse(from_base64(l_payload_base64));
  dbms_output.put_line('Payload = ' || l_payload_json.to_string);
  l_username := l_payload_json.get_string('sub');
  dbms_output.put_line('Username = ' || l_username);
  l_hmac := substr(l_jwt, l_payload_len + 1);
  dbms_output.put_line('Signature = ' || l_hmac);
 
  /* シグネチャの検証をする。 */
  l_hmac := trim(translate(l_hmac, '-_ ', '+/='));
  if SHA256withRSA_verify(l_header_base64, l_payload_base64, l_hmac, l_public) then
    dbms_output.put_line('Signature verified successfully.');
  else
    dbms_output.put_line('Signature verify failed.');
  end if;
end;
/
exit;
l_publicには、RSA公開鍵を一行で指定します。l_jwtにはJWTをこれも一行で指定します。実行した結果は以下のようになります。
Header = {"alg":"RS256","typ":"JWT"}
Payload = {"iss":"sqlplus","sub":"TESTUSER","aud":"APEX","iat":1584430294,"exp":1584430304}
Username = TESTUSER
Signature = AJE1R2m_-8OTZLocdNaOfa5UF3EirLIsYkpD***一部省略***t6mSYFPshK3km_X9jNXQfHHP9As-2HnnrOSFMN9A
Signature verified successfully.
これで、Oracle APEXにて(正確にはOracle Databaseにて)RS256を使ったJWTの作成と検証の両方ができるようになっています。

2020年3月14日土曜日

RS256を使ったJWTを作成する

以前にこちらの記事にて、APEX_JWTパッケージを使わずにJWTを生成する方法について紹介しました。APEX_JWTパッケージでは、JSON Webアルゴリズムとして、HMAC SHA-256 ("HS256")のみがサポートされていて、RSASSA-PKCS1-v1_5 with SHA-256 ("RS256")には対応していません。RS256を指定したJWTを生成するために、APEX_JWTパッケージが持つ機能の一部であっても、活用する方法はありませんでした。

以下に、RS256をデジタル署名としたJWTを生成する方法を記載します。Oracle Databaseはいくつかの暗号処理が実装されていますが、RSA公開鍵暗号方式については未実装です。そのため、デジタル署名の生成については、データベースに組み込みのJavaにて実装します。

RSAの公開鍵/秘密鍵はこちらの記事で紹介したのと、同じ方法で生成します。
mkdir -p ~/.oci && openssl genrsa -out ~/.oci/poa_oci_api_key.pem 2048
実際のデータの部分だけを一行にして取り出せるように、こちらの記事で紹介したスクリプトも以下に紹介しておきます。
#!/bin/sh

while read l
do
   test ${l:0:1} != "-" && /bin/echo -n $l
done < ~/.oci/poa_oci_api_key.pem
echo

デジタル署名のJavaの実装をデータベースに登録する


デジタル署名を行うJavaの実装を、ファイル名SHA256withRSA.javaとして用意しました。
import java.math.BigInteger; 
import java.util.Base64;
import java.security.KeyFactory;
import java.security.Signature;
import java.security.PrivateKey;
import java.security.SignatureException;
import java.security.NoSuchAlgorithmException;
import java.security.spec.RSAPrivateCrtKeySpec;

import sun.security.util.DerInputStream;
import sun.security.util.DerValue;
  
public class SHA256withRSA { 
  /**
   * RSASSA-PKCS1-v1_5 with SHA-256によるデジタル署名の生成
   *
   * @param header    BASE64エンコード済みのJWTヘッダー
   * @param payload   BASE64エンコード済みのJWTペイロード
   * @param key       PKCS#1形式でのRSA秘密鍵(BEGIN/END行なし、改行なし)
   * @return デジタル署名 - BASE64エンコード済み
   */
  public static String sign(String header, String payload, String key)
  throws Exception
  {
    /* header and payload are both encoded in base64 */
    byte[] data = new String(header + "." + payload).getBytes("UTF-8");

    /* get PrivateKey instance from PKCS#1 */
    byte[] pkdata = Base64.getDecoder().decode(key);
    DerInputStream derReader = new DerInputStream(pkdata);
    DerValue[] seq = derReader.getSequence(0);
    // skip version seq[0];
    BigInteger modulus = seq[1].getBigInteger();
    BigInteger publicExp = seq[2].getBigInteger();
    BigInteger privateExp = seq[3].getBigInteger();
    BigInteger prime1 = seq[4].getBigInteger();
    BigInteger prime2 = seq[5].getBigInteger();
    BigInteger exp1 = seq[6].getBigInteger();
    BigInteger exp2 = seq[7].getBigInteger();
    BigInteger crtCoef = seq[8].getBigInteger();

    RSAPrivateCrtKeySpec keySpec =
      new RSAPrivateCrtKeySpec(modulus, publicExp, privateExp, prime1, prime2, exp1, exp2, crtCoef);
    KeyFactory keyFactory = KeyFactory.getInstance("RSA");
    PrivateKey privateKey = keyFactory.generatePrivate(keySpec);

    // creating the object of Signature 
    Signature sr = Signature.getInstance("SHA256withRSA"); 
    sr.initSign(privateKey);
    sr.update(data);
    byte[] bytes = sr.sign();
   
    /* return signature in Base64 */ 
    return Base64.getEncoder().encodeToString(bytes);
  }
}
これをデータベースにロードします。ロードするスキーマはMYWORKSPACEとします。
loadjava -force -verbose -resolve -u myworkspace/**********@localhost/service.world SHA256withRSA.java
-verboseをオプションがついているので、以下のような出力が行われます。最後のErrorsが0でloadjavaの実行が終了したら、このJavaコードのデータベースへのロードが完了です。
arguments: '-u' 'myworkspace/***@localhost/service.world' '-force' '-verbose' '-resolve' 'SHA256withRSA.java' creating : source SHA256withRSA
loading  : source SHA256withRSA
resolving: source SHA256withRSA
Classes Loaded: 0
Resources Loaded: 0
Sources Loaded: 1
Published Interfaces: 0
Classes generated: 0
Classes skipped: 0
Synonyms Created: 0
Errors: 0

Javaの実装をラップするPL/SQLファンクションを定義する


PL/SQLからファンクションとして呼び出せるよう、Wrapperとなるファンクションを設定します。
create or replace function sha256withrsa_sign
(
  header      varchar2,
  payload     varchar2,
  private_key varchar2
) return varchar2
as
language java name 'SHA256withRSA.sign (java.lang.String, java.lang.String, java.lang.String) return java.lang.String';
/
これで、SHA256WITHRSA_SIGNとしてPL/SQLのコード中からRSAによるデジタル署名を実行できるようになりました。

sun.security.utilというパッケージに含まれるクラスは、Oracle Databaseのデフォルトでは、一般のスキーマからのアクセスに制限が設けられている模様です。ですので、sysやsystemといったDBA権限のあるユーザーにて、これらの実行をMYWORKSPACEにたいして許可する必要がありました。そのために使用したコマンドが以下になります。
dbms_java.grant_permission( 'MYWORKSPACE', 'SYS:java.lang.RuntimePermission', 'accessClassInPackage.sun.security.util', '' );

JWTをPL/SQLコードで生成する


実装したデジタル署名アルゴリズムを指定したうえで、PL/SQLのコードを使ってJWTを生成します。このコード自体は、こちらの記事とほぼ同じものになります。
set lines 1000
set serveroutput on
declare
  l_now       timestamp;
  l_secret    varchar2(32767) := 'MIIEpAIBAAKCAQEA+ARMJiUjN+3kWFckXnxQkbHcbnKxoB46cHyuI+p7f7itiE4x8gJ6A9ML1alo6uCnHn8D0vaDJ/DVzL9whTS8zXJTB/WzGs35DsFrb23RIqrQCQHwCigi/rePzuaco225Rdc6yeMzTPnYhx36Vhhw/wR43oMtd2rBLntK8qOQgC5e8XFA5AxBCY6h0vPlgcF8fV3v+m1MonoC+9htXY8j6hg2pflcu2nTsJes8pysWYqxDjK32Vf2Mo/7SoK21zpjwUQonzllYPmuPguLhJcpGnCHKH2rU2jUBxfQtOirNYmuQ+qyZ4bgbzWj3iwoe584OHUw5BJ00kPiUA5dgaetswIDAQABAoIBAQCVCFgNz+Ei2ZOONgcHs+HiOqN/xvHPinqJX5JwyJUfgYTdVEeU6kIRjD8fS+ZcxVQG8kIFAdZ8KK8AksT1dmRBfAJC1TSj1cNkR3vcgHvcPhI2Z3CrWcT1lK4YoODdH7DmM9gCusD372ZagvwLwjIcO765zs+CY6bj3HiD5x+MtaHIGrrArKFb2rTBSt9HSrSov5lvlHT0GQJGYyfGEKQzcviv8pwflOUv8LlfTHRpOKl+V+mMsT4ljdqzc4cauSrU+nObBZKWJH+vdGZE78E7NnTP4bVQ/Sz0R6JMawL0lfQ2ty4mrceQ/92LpKRyIEHkUYOr5i+ln+DqGzvIbUyhAoGBAP15KZ5DkF4CfD0+I5v00xDmMLwWx0+8f3eBVEznq7SHLQUMQgXLjUtDWfIPzspNOmyn8J9nOhsIP9f92kYxGNcsIC7IoYhGdM1chGeT3yeOxc4pa0t13JpVZPAe5lsD6OCkEn6aH6fCc2XD/I6DvlsbOLYRakz3C+Oety3qRxERAoGBAPp9NeBmQW7pKy6lVy8ZOIcDezi+eOB0YzO3y2D4kt8ZO7w56I/IjwjINggCRlWeKROyaqJHzjs51iIOS9eIQlCnEubNKouyztflvwjI/lQSI+gXS85TVdrQoZn4OyVCkbxzs/MLWQxp6QqF850EwGFQJxzFAav+xF8suI8eqtKDAoGAJvhl6atBmvKO32uaVfQCL2r0uzZVVnEIt/ruVxSXVZ4i0c/cpr9w7c1hGtJLXNca0HHRkPByAo32IuMol/occ5iZKhg2nZkXhCthP/uAJ7jPuK6tjGCGlkPizPgzOnMTbtZK8aQIkeZXj6HeRpsCvUcmJPfwe/zPmyNfznNikcECgYAZqlip3HBZ5+Q3zFMpjUEAfHfUsZd7EBHWC7/3+KA74b8AI0LT8K3PLYxzt5/zR9hXn1FTvV3BLcTmCmb0944r62KBaYYxeCm/b1fqk4WToaQyNKjcxCco23WjbA7LW43JLGXEwmXmYrexD1aNwR3hGrBXP0xTjjAavF9Qssi39wKBgQCahtl16QWS/2Gh65X2jJGjKychgUd1RbZTE7v9gNh4oR+uciFdyoJ5Zbj3mpbKFtA0SFBGTZr86CKYqlO4712XPqSjtnw8Fpc8CnyO9mSxmn1BfSB+8FNdvgUKcDBQTno5PK5KfDNosylhUK7mROHawX/GXAtT2OsNXA451TjNVg==';
  l_username  varchar2(32) := 'TESTUSER';
  l_jwt       varchar2(32767);
  l_jwt_token apex_jwt.t_token;
  l_jwt_t     apex_t_varchar2;

  l_header_json json_object_t;
  l_header_str  varchar2(200);
  l_header_base64 varchar2(400);
  l_payload_json json_object_t;
  l_payload_str  varchar2(200);
  l_payload_base64 varchar2(800);
  l_token varchar2(1000);
  l_hmac varchar2(1000);

  -- Unix時間の取得
  function unixtime(p_timestamp in timestamp)
  return pls_integer
  is
    l_date date;
    l_epoc number;
  begin
    l_date := sys_extract_utc(p_timestamp);
    l_epoc := l_date - date'1970-01-01';
    return l_epoc * 24 * 60 * 60;
  end unixtime;

  -- Base64のデコード
  function from_base64(t in varchar2) return varchar2 is
  begin
    return utl_raw.cast_to_varchar2(utl_encode.base64_decode(utl_raw.cast_to_raw(t)));
  end from_base64;

  -- Base64のエンコード
  function to_base64(t in varchar2) return varchar2 is
    l_base64 varchar2(32767);
  begin
    l_base64 := utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(t)));
    l_base64 := replace(l_base64, chr(13)||chr(10), '');
    return l_base64;
  end to_base64;
begin
  -- 共通で使用する現在時刻
  l_now := current_timestamp;
  dbms_output.put_line('Current Timestamp = ' || l_now || ', unixtime = ' || unixtime(l_now));

  -- ヘッダーを手作業で生成する。 
  dbms_output.put_line('Hand made ==================================');
  l_header_json := json_object_t();
  l_header_json.put('alg','RS256');
  l_header_json.put('typ','JWT');
  l_header_str := l_header_json.to_string();
  l_header_base64 := to_base64(l_header_str);
  dbms_output.put_line('Header    = ' || l_header_str);

  -- ペイロードを手作業で作成する。
  l_payload_json := json_object_t();
  l_payload_json.put('iss','sqlplus');
  l_payload_json.put('sub',l_username);
  l_payload_json.put('aud','APEX');
  l_payload_json.put('iat',unixtime(l_now));
  l_payload_json.put('exp',unixtime(l_now)+10);
  l_payload_str := l_payload_json.to_string();
  l_payload_base64 := to_base64(l_payload_str);
  dbms_output.put_line('Payload   = ' || l_payload_str);

  -- シグネチャを手作業で作成する。
  l_hmac := SHA256withRSA_sign(l_header_base64, l_payload_base64, l_secret);
  l_hmac := trim(translate(l_hmac, '+/=', '-_ '));
  dbms_output.put_line('JWT = ' || l_header_base64 || '.' || l_payload_base64 || '.' || l_hmac);
end;
/

exit;
上記のコードを実行すると以下のような出力がされます。
Current Timestamp = 17-MAR-20 03.48.50.702398 PM, unixtime = 1584427730
Hand made ==================================
Header   = {"alg":"RS256","typ":"JWT"}
Payload   = {"iss":"sqlplus","sub":"TESTUSER","aud":"APEX","iat":1584427730,"exp":1584427740}
JWT = eyJhbGciOiJSUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJzcWxwbHVzIiwic3ViIjoiVEVTVFVTRVIiLCJhdWQiOiJBUEVYIiwiaWF0IjoxNTg0NDI3NzMwLCJleHAiOjE1ODQ0Mjc3NDB9.DqFIbu-a3lD7QVgLjiIraRFUT-0jV7bNrg-wRDrQ9CUSoGxATWx2d43yJ2CrJxgDY-h62ogQgnFmh8vAn54Se7wXii9U9LO7wcclm96KP2PRyve0c1LDYmVt2-2_cGCQqas5J1yDF2E5Twxr4o9oeZcVCBkfjS22cZzz4VoiowvpbJw_HCbh0iU_bXv_l1dqSctRZWNKugaLEcPnERzIoXeceORb5YR8icB8A9z10l750MdsfxEIQAdtuFGYhJkQi4_a2cPGp0adJIt_Y0X2aDGfoz1sKdTbIbbIbcOBStspOVI5v-PCc01yNMyf1jJk-4zoliSmV6GIWyfl5ewawQ
https://jwt.ioにはJWTのデバッガーがありますので、生成されたデータと鍵を入力し、JWTのデジタル署名の検証が可能です。

RSASSA-PKCS1-v1_5 with SHA-256によるデジタル署名の生成についてはこちらの記事、PKCS#1の鍵フォーマットから、秘密鍵を取り出すためにこちらの記事を参照させていただきました。

2020年3月13日金曜日

ページ・アイテムのデフォルト値を設定する3つの方法

フォームが開いたときにページ・アイテムにデフォルト値を設定しておきたい、という要望がありました。パッと思いついた3つの方法について、紹介したいと思います。SQLワークショップのサンプル・データセットに含まれるEMP / DEPTをインストールし、それから作成したアプリケーションを説明に使用します。

レポートから作成をクリックしてフォームを開きます。または、編集アイコンをクリックして、フォームを開きます。JOBの設定はあらかじめ削除しました。


フォームが開きますが、ここのJOBにSALESMANというデフォルト値を設定します。

デフォルトを設定する


最初に作成をクリックしてフォームを開いたときは、もちろん空白です。


ページ・デザイナを開いて、ページ・アイテムP4_JOBデフォルトを設定します。タイプとして静的静的値としてSALESMANを指定します。デフォルト値の設定方法は静的以外にも、アイテムSQL問合せコロンで区切られたリストを戻すSQL問合せPL/SQL式PL/SQLファンクション本体順序といった方法を選ぶことができます。


デフォルトを設定すると、フォームがオープンするときにJobとしてSALESMANが設定されます。



計算を設定する


ページ・アイテムのデフォルトは、新規行の場合のみ設定されます。ですので、最初にあるページで編集をクリックすると、Jobは空白のままです。


ほとんどの場合で「私のやりたいことは、そうじゃないんだ。」となります。このような場合にデフォルト値を設定するには、ページ・アイテムP4_JOBに計算を設定します。P4_JOB上でコンテキスト・メニューを開き(右クリック)、計算の作成を実行します。


計算のプロパティをそれぞれ設定します。実行オプションポイントは、リージョンの前です。ページのレンダリング処理の前にページ・アイテムに値を設定しないと表示されません。計算のプロパティはタイプ静的値静的値SALESMANとします。そして、サーバー側の条件として、タイプアイテムはNULLとし、アイテムP4_JOBにします。P4_JOBに値が無い時だけ、値としてSALESMANを設定します。


この計算が設定されていると、ページ・アイテムのデフォルトの設定は不要になります。

動的アクションを設定する


計算はサーバー・サイドでの処理によるデフォルト値の設定ですが、ブラウザ側でもできます。動的アクションをページ・アイテムP4_JOBに設定します。P4_JOBの上でコンテキスト・メニューを表示させ、動的アクションの作成を実行します。


動的アクションのタイミングとして、イベント変更選択タイプアイテムアイテムP4_JOBを指定します。クライアント側の条件として、タイプアイテムはnullアイテムP4_JOBを指定します。


Trueアクションとして、値の設定を指定します。設定のセクションでは、タイプの設定Static AssignmentSALESMANです。変更イベントの禁止は、この設定には影響しないので、ON/OFFどちらでもかまいません。影響を受ける要素は、値が設定されるコンポーネントのことですので、選択タイプアイテムとして、アイテムP4_JOBを指定します。実行オプション初期化時に実行は必ずONです。これをONにしていないと、フォームが開いた時に値が設定されません。



計算を使ったデフォルトの設定と、(ここで作成した)動的アクションを使ったデフォルトの設定は画面上の動作が異なります。計算の場合はデフォルトを設定するタイミングは、ページがロードされるとき、一度のみですが、動的アクションの場合は、値が変更されるたびに評価されます。ですので、Jobを空白にして、別のページ・アイテムにフォーカスが移ると、またデフォルト値が設定されます。動的アクションのタイミングとしてページのロードを選ぶと動作もまったく同じになるはずです。

2020年3月12日木曜日

APEX_JWTパッケージを使わずJWTを生成する

Oracle APEXに標準でAPEX_JWTという、Java Web Token (RFC 7519)を扱うパッケージが提供されています。ただし、APEX_JWTパッケージは署名アルゴリズムとして、"HS256" - HMAC SHA-256だけをサポートしています。これ以外のJava Web Ticketを作るために、一番追加作業が少ない手順を考えるために、とりあえずAPEX_JWTで作れるJWTをPL/SQLで実装するとどうなるか、というのを書いてみました。
set lines 1000
set serveroutput on
declare
  l_now       timestamp;
  l_secret    varchar2(32) := 'secret!';
  l_username  varchar2(32) := 'TESTUSER';
  l_jwt       varchar2(32767);
  l_jwt_token apex_jwt.t_token;
  l_jwt_t     apex_t_varchar2;

  l_header_json json_object_t;
  l_header_str  varchar2(200);
  l_header_base64 varchar2(400);
  l_payload_json json_object_t;
  l_payload_str  varchar2(200);
  l_payload_base64 varchar2(800);
  l_token varchar2(1000);
  l_hmac varchar2(1000);

  -- Unix時間の取得
  function unixtime(p_timestamp in timestamp)
  return pls_integer
  is
    l_date date;
    l_epoc number;
  begin
    l_date := sys_extract_utc(p_timestamp);
    l_epoc := l_date - date'1970-01-01';
    return l_epoc * 24 * 60 * 60;
  end unixtime;

  -- Base64のデコード
  function from_base64(t in varchar2) return varchar2 is
  begin
    return utl_raw.cast_to_varchar2(utl_encode.base64_decode(utl_raw.cast_to_raw(t)));
  end from_base64;

  -- Base64のエンコード
  function to_base64(t in varchar2) return varchar2 is
    l_base64 varchar2(32767);
  begin
    l_base64 := utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(t)));
    l_base64 := replace(l_base64, chr(13)||chr(10), '');
    return l_base64;
  end to_base64;
begin
  -- 共通で使用する現在時刻
  l_now := current_timestamp;
  dbms_output.put_line('Current Timestamp = ' || l_now || ', unixtime = ' || unixtime(l_now));

  -- APEX_JWTパッケージを使用しJWTの作成
  -- Using Oracle APEX provided package APEX_JWT
  l_jwt := apex_jwt.encode (
    p_iss      => 'sqlplus',
    p_aud      => 'APEX',
    p_sub      => l_username,
    p_iat_ts   => l_now,
    p_exp_sec  => 10,
    p_signature_key => sys.utl_raw.cast_to_raw(l_secret));
  -- 生成したJava Web Token
  l_jwt_t := apex_string.split(l_jwt, '.');
  dbms_output.put_line('APEX_JWT.encode ===========================');
  dbms_output.put_line('Header    = ' || l_jwt_t(1));
  dbms_output.put_line('Payload   = ' || l_jwt_t(2));
  dbms_output.put_line('Signature = ' || l_jwt_t(3));

  -- JWTをデコードし、内容を確認する。
  l_jwt_token := apex_jwt.decode (
    p_value         => l_jwt,
    p_signature_key => sys.utl_raw.cast_to_raw(l_secret) );
  --
  dbms_output.put_line('APEX_JWT.decode  ===========================');
  dbms_output.put_line('Header    = ' || trim(l_jwt_token.header) );
  dbms_output.put_line('Payload   = ' || trim(l_jwt_token.payload) );
  dbms_output.put_line('Signature = ' || trim(l_jwt_token.signature) );

  -- ヘッダーを手作業で生成する。 
  dbms_output.put_line('Hand made ==================================');
  l_header_json := json_object_t();
  l_header_json.put('alg','HS256');
  l_header_json.put('typ','JWT');
  l_header_str := l_header_json.to_string();
  l_header_base64 := to_base64(l_header_str);
  dbms_output.put_line('Header    = ' || l_header_str);
  dbms_output.put_line('Header    = ' || l_header_base64);

  -- ペイロードを手作業で作成する。
  l_payload_json := json_object_t();
  l_payload_json.put('iss','sqlplus');
  l_payload_json.put('sub',l_username);
  l_payload_json.put('aud','APEX');
  l_payload_json.put('iat',unixtime(l_now));
  l_payload_json.put('exp',unixtime(l_now)+10);
  l_payload_str := l_payload_json.to_string();
  l_payload_base64 := to_base64(l_payload_str);
  dbms_output.put_line('Payload   = ' || l_payload_str);
  dbms_output.put_line('Payload   = ' || l_payload_base64);

  -- シグネチャを手作業で作成する。
  l_token := l_header_base64 || '.' || l_payload_base64;
  l_hmac := utl_raw.cast_to_varchar2(utl_encode.base64_encode(dbms_crypto.mac(
     utl_raw.cast_to_raw(l_token),
     dbms_crypto.HMAC_SH256,
     utl_raw.cast_to_raw(l_secret)
  )));
  l_hmac := trim(translate(l_hmac, '+/=', '-_ '));
  dbms_output.put_line('Signature = ' || l_hmac);
end;
/
上記の実行結果は以下のような感じです。時刻が毎回変わるため、同じ結果は生成されないです。
Current Timestamp = 12-MAR-20 04.29.05.616729 PM, unixtime = 1583998145
APEX_JWT.encode ===========================
Header   = eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9
Payload   = eyJpc3MiOiJzcWxwbHVzIiwic3ViIjoiVEVTVFVTRVIiLCJhdWQiOiJBUEVYIiwiaWF0IjoxNTgzOTk4MTQ1LCJleHAiOjE1ODM5OTgxNTV9
Signature = fgjlxE-JLcZdcvU4D_vqP9xX29G8lgC6w4zzifWCPuU
APEX_JWT.decode  ===========================
Header   = {"alg":"HS256","typ":"JWT"}
Payload   = {"iss":"sqlplus","sub":"TESTUSER","aud":"APEX","iat":1583998145,"exp":1583998155}
Signature = fgjlxE-JLcZdcvU4D_vqP9xX29G8lgC6w4zzifWCPuU
Hand made ==================================
Header   = {"alg":"HS256","typ":"JWT"}
Header   = eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9
Payload   = {"iss":"sqlplus","sub":"TESTUSER","aud":"APEX","iat":1583998145,"exp":1583998155}
Payload   = eyJpc3MiOiJzcWxwbHVzIiwic3ViIjoiVEVTVFVTRVIiLCJhdWQiOiJBUEVYIiwiaWF0IjoxNTgzOTk4MTQ1LCJleHAiOjE1ODM5OTgxNTV9
Signature = fgjlxE-JLcZdcvU4D_vqP9xX29G8lgC6w4zzifWCPuU
Base64変換はこちらの記事、Base64の変換結果から改行を除く方法はこちらの記事、Unix時間の取得はこちらの記事、それ以外にはこちらの記事も参考にさせていただきました。ありがたいことです。もちろん、RFC 7519とRFC 4648も。

2020年3月10日火曜日

REST APIで取得したJSON配列を扱う3つの方法

最近、あるサービスとの連携を、そのサービスが提供しているREST APIを使ってやってください、という依頼があったので試してみたことを紹介します。Oracle APEXを使うことで可能な方法としては、以下の3つの方法があるかと思います。
  1. Webソース・モジュールを作成する。
  2. Webソース・モジュールを作成し、APEX_EXEC.OPEN_WEB_SOURCE_QUERYを呼び出し、それを処理するパイプライン表関数を作成する。
  3. APEX_WEB_SERVICE.MAKE_REST_REQUEST_Bを呼び出し、JSON文書の取得とパースを行うパイプライン表関数を作成する。
表示されたレポートは以下のような感じになります。上記3つの方法、全てで同じ表示になります。
 

上から順にAPEXでの扱いが簡単ですが、それぞれ一長一短はあります。JSONをそのまま扱う方が、パイプライン表関数を作るときにわかりやすい(つまり3番が一番簡単)という方もいらっしゃるでしょう。

参考にしたのはCarsten Czarskiによるブログポスト"APEX 18.1 Early Adopter 2: REST Services and PL/SQL"です。

Webソース・モジュールのみによるレポート作成


最初にアプリケーション・ビルダーからワークスペース・ユーティリティを選んで、Web資格証明を開きます。
 

Web資格証明として、認証に必要な情報を登録します。Oracle APEX 19.2では、指定可能な認証タイプは3種類あります。
  • 基本認証
  • OAuth2クライアント資格証明フロー
  • Oracle Cloud Infrastructure (OCI)
基本認証は与えられているクライアントIDまたはユーザー名クライアント・シークレットまたはパスワードから、HTTPの"Authorization: Basic"リクエストヘッダーを生成するものです。

OAuth2クライアント資格証明フローは、OAuth2による認証で使用します。API呼び出しに使用する場合は、トークン・エンドポイントの指定はWebソース・モジュールの設定に含まれます。

Oracle Cloud Infrastructure (OCI)は、その名の通り、Oracle Cloud Infrastructureが提供しているAPIの呼び出しに使用されます。使い方については以前にオブジェクト・ストレージについてブログを記載したときに紹介したことがあります。こちらのエントリです。

今回は基本認証を認証タイプとして指定したWeb資格証明を登録しました。静的識別子は必須の項目になっていませんが、ファンクションAPEX_WEB_SERVICE.MAKE_REST_REQUEST_BによってREST APIを呼び出す際に使われるため、必ず指定します。また、PL/SQLのコード中に記述されるので、日本語や空白といった文字は使わない方が良いでしょう。
 

次にWebソース・モジュールを作成します。アプリケーションの共有コンポーネントにある、データ・ソースのセクションに含まれるWebソース・モジュールを開きます。
 

Webソース・タイプとして4種類選択できます。詳細は画面にある""をクリックすると説明が表示されます。

登録済みのWebソース・モジュールをレポートする画面に作成ボタンがあります。それをクリックし、作成画面に進みます。最初に開いたダイアログで最初からをチェックし、に進むとWebソース・モジュールの登録に必要な情報の入力画面になります。

簡易HTTP以外は、それぞれのサービスが持っている、ページネーション(ヘルプではページ区切りという用語になっています)のサポートが含まれます。REST APIの応答に大量の行が含まれる場合、一度の応答に含まれる行を固定し、複数のリクエストを発行することにより結果全体を取得する、というものです。Oracle REST Data Servicesについては、さらにサーバー側に処理を移譲することが可能になっています。これらの特定の機能を持ったサービス以外は、簡易HTTPを選びます。

Webソース・モジュールの名前とREST APIのURLエンドポイントを指定し、へ進みます。
 

リモート・サーバーベースURLサービスURLパスは、入力したURLエンドポイントから自動的に決定されます。へ進みます。
 

事前に登録してあるWeb資格証明を選択し、検出をクリックします。
 

プレビューされている内容を確認し、Webソースの作成をクリックします。以下の画面は概要のプレビュー画面で、データとデータ・プロファイルの確認が可能です。
 

詳細をクリックし、詳細のプレビュー画面に移ると、データとデータ・プロファイルに加えて、操作、レスポンス本文、レスポンス・ヘッダーの確認も可能です。以下は詳細画面にてデータ・プロファイルを表示させています。行セレクタは選択されたJSON配列のキー値です。JSONに含まれているキー値がOracle Databaseでどのように扱われるか、例えば数値が文字列として扱われていないか、日付が文字列として扱われていないかは、あらかじめ確認しておきましょう。
 

これでWebソース・モジュールの作成が完了しました。

Webソース・モジュールからレポートを作成


この方法はOracle APEXの標準的な作業になります。対話グリッド、対話レポートおよびクラシック・レポートを作成する際に使用するデータ・ソースとして、作成済みのWebソース・モジュールを指定します。
 

APEX_EXEC.OPEN_WEB_SOURCE_QUERYによるレポート


作成したWebソース・モジュールの編集画面を開いて、静的識別子を確認します。扱いやすい名前に変更しても良いでしょう。
 

次にデータベースにタイプをふたつ定義します。ひとつめは行を表すタイプT_OIC_INSTANCE_ROWです。
CREATE OR REPLACE EDITIONABLE TYPE  "T_OIC_INSTANCE_ROW" as object( 
    href          varchar2(400), 
    state         varchar2(80), 
    title         varchar2(200), 
    levels        number, 
    ownedby       varchar2(120), 
    priority      number, 
    createdby     varchar2(120), 
    processdn     varchar2(200), 
    processid     number, 
    createddate   timestamp with time zone, 
    processname   varchar2(80), 
    updateddate   timestamp with time zone, 
    processnumber number);
次に、それを使ったテープルを表すタイプT_OIC_INSTANCE_TABLEです。
CREATE OR REPLACE EDITIONABLE TYPE  "T_OIC_INSTANCE_TABLE" as table of t_oic_instance_row;
Webソース・モジュールを使ったパイプライン表関数は以下になります。Webソース・モジュールの静的識別子として、作成したWebソース・モジュールPROCESS_INSTANCE_LISTAPEX_EXEC.OPEN_WEB_SOURCE_QUERYに与えています。
  
タイプに指定されているカラムと、Webソース・モジュールから取り出した値の定義が正確に一致していること、また、日付については、タイプにはTIMESTAMP WITH TIME ZONEで定義されていて、それに設定する値はget_timestamp_tzで取り出していることを確認します。JSONの日付フォーマットがISO8601に従っている限り、タイムゾーン付き(そして、大抵はUTC)のデータとして取り出されます。ここが間違っていると、何のエラーも出力せず次回のapex_exec.next_rowの結果がfalseになりました。原因を見つけるのはとても大変でした。

パイプライン表関数が作成されていると、レポートには次のSQLを使用できます。
select * from table(get_instance_data)

レポートでの指定箇所は以下になります。

APEX_WEB_SERVICE.MAKE_REST_REQUEST_Bによるレポート


前のセクションで作成した、ふたつのタイプはそのまま流用します。APEX_WEB_SERVICE.MAKE_REST_REQUEST_Bを呼び出して取得したJSON文書は、Oracle Databaseに組み込みのJSONを処理するタイプを使って処理します。JSON_OBJECT_T, JSON_ARRAY_Tは、Oracle Database 12c 12.2より使用可能です。
 
作成したファンクションの使い方は、APEX_EXEC.OPEN_WEB_SOURCE_QUERYによるものとまったく同じです。

ここでも、日付データの取得はJSONの日付表現を考慮して、JSON_OBJECT_T.GET_TIMESTAMPを使用し、TIMESTAMP WITH TIME ZONEとして保存します。

Oracle Database 12c 12.2より以前のバージョンでは、データベース組み込みのJSONの機能を使えなかったりします。そのときはAPEX_JSONというOracle APEXが提供しているパッケージを代わりに使用することができます。また、その場合、JSON文書の取得にはBLOBを返却するAPEX_WEB_SERVICE.MAKE_REST_REQUEST_Bではなく、CLOBを返却するAPEX_WEB_SERVICE.MAKE_REST_REQUESTを使用します。JSON文書をBLOBで受け取って処理するのは、文字コードの変換(UTF-8 <-> UCS-2)が発生しないためCLOBより効率がよく、また、Oracle Database 20cではネイティブのJSON型のサポートが追加されるという発表もあります。興味があるかたは、Chris SaxonのOffice Hour(45:30より)で紹介されていますので、参考にしてみてください。

2020年2月28日金曜日

APEXからOCIオブジェクト・ストレージを操作する(0) - はじめに

OracleのブログにAdrian Pngさんが、Better File Storage in Oracle Cloudとして、Oracle APEXでOCI - Oracle Cloud Infrastructure - のオブジェクト・ストレージを操作するアプリケーションの作り方を寄稿しています。


Adrian PngさんはカナダのInsumという、Oracle APEXやオラクルのパートナーさんのメンバーで、Oracle APEXのコミュニティーへの有功者へこちらのコインを配布している方です。また、このブログポストの謝辞には、こちらもオラクルのパートナーであるAccenture EnkitecのメンバーであるChristoph Ruepprichさんと、オラクルのCarsten Czarskiの2名の名前がリストされています。InsumとAccenture Enkitecの両方ともOracle APEXとOCIの両方を使っているので、こういったOCI上のサービスのAPI呼び出しはニーズとして高いのでしょう。

さて、Oracle APEX 19.2より、Web資格証明のタイプにOracle Cloud Infrastructure (OCI)が追加されていて、これでOracle APEXからOCIのAPI呼び出しが容易になっています。
 

また、Oracle Cloudで提供されているAutonomous Databaseのインスタンスに構成されているOracle APEXも、19.2へ順次アップグレードされています。ですので、Always Freeのサービスとして提供されているAutonomous DatabaseのインスタンスのOracle APEXを使って、上記のアプリケーション作成を行ってみました。

(2022年12月1日追記:Autonomous Database上のOracle APEX 22.2で作業を再度実施し、記事を更新しています。)

元にした記事はOCIやOracle APEXの知識を前提としているところがあります。その部分は補完しつつ、元記事の内容をそのまま使う部分は重複しないようにしたいと思います。

OCIのテナントを取得した直後の状態からアプリケーションが完成するまでの作業を、全部で9本の記事で紹介しています。

  1. APIユーザーの作成では、 準備としてコンパートメント、グループ、ポリシー、ユーザー、それにユーザーにAPIキーを作成しています。
  2. バケットの作成では、OCIオブジェクト・ストレージにバケットを作成し、ファイルをひとつアップロードしています。
  3. ADBの作成からAPEXアプリの準備まででは、Always FreeのAutonomous Databaseをひとつ作成し、Oracle APEXのワークスペースを新規に作成した後、空のアプリケーションを作成しています。
  4. Web資格証明の作成では、APEXにOCIのオブジェクト・ストレージにアクセスするときに使用するWeb資格証明を登録しています。
  5. Web資格証明の作成では、アプリケーションにOCIのオブジェクト・ストレージを操作するために使用するWebソース・モジュールを作成しています。
  6. オブジェクトの一覧表示では、APEXのアプリケーションにOCIオブジェクト・ストレージのバケットを選んで、含まれるオブジェクトの一覧を表示する機能を実装しています。
  7. ファイルのアップロードでは、オブジェクト・ストレージにファイルをアップロードする機能を実装しています。この実装はダイアログ・ページのページの送信(HTTPのPOSTによる呼び出し)時に実行されるプロセスとして機能を実装するという、Oracle APEXで最も一般的な機能の実装手法を使っています。
  8. ファイルのダウンロードでは、オブジェクト・ストレージからオブジェクトをダウンロードする機能を実装しています。この実装は別ページへのリダイレクト(HTTPのGETによる呼び出し)時に処理を行う実装になっています。
  9. ファイルの削除では、オブジェクト・ストレージ内のオブジェクトの削除機能を実装しています。これはリンクに動的アクションを定義して、それからAjaxによってプロセスの呼び出しを行う実装になっています。

OCIオブジェクト・ストレージを操作するアプリケーションの作り方の紹介ですが、処理それぞれに異なるプロセスの呼び出し方を採用していて、参考になります。

続く

APEXからOCIオブジェクト・ストレージを操作する(9) - ファイルの削除

OCIオブジェクト・ストレージ上にあるオブジェクトを削除するリンクを、ホーム画面のレポートに追加します。

元ブログの内容をAPEX 22.2のActionインターフェースを使う実装に変えています。


オブジェクトを削除するリンクの追加


ダウンロード・リンクと同様に、仮想列をリージョンBucket Contentsに追加します。手順は同じです。

仮想列DERIVED$02が作成されるので、その識別タイプとしてプレーン・テキストを指定し、式の書式HTML式に以下を設定します。

カスタム属性data-ationからアクションdelete-objectを呼び出すように定義しています。引数nameとしてオブジェクト名を渡しています。
 

アクションdelete-objectを定義します。以下のコードを、ページ・プロパティのJavaScriptページ・ロード時に実行に記述します。



リージョンBucket Contents詳細静的IDとしてR_BUCKET_CONTENTSを設定します。リフレッシュ対象のリージョンを指定するために使用します。


Ajaxのコールバックを作成するため、左ペインの表示にプロセス・ビューを選んで、Ajaxコールバックの上でコンテキスト・メニューを開き、プロセスの作成を実行します。
 

識別名前としてDELETE_OBJECT(この名前で処理が呼び出されるので必ずDELETE_OBJECTを指定してください)、識別タイプコードを実行ソース位置ローカル・データベースで、PL/SQLコードとして以下を記述します。元のブログのコードは日本語の扱いに不備があったので、その部分を修正しています。


 

これでオブジェクトの削除機能が実装できました。

アプリケーションを実行して、ファイルのアップロード、ダウンロード、削除などを実行してみましょう。

また、元となるブログの内容はここで終了です。

今回作成したアプリケーションのエクスポートを以下に置きました。
https://github.com/ujnak/apexapps/blob/master/exports/apex-with-oci-object-storage.zip

Oracle APEXのアプリケーション作成の参考になれば幸いです。