この記事からの継続です。いまのところOracle Database 21cに実装されているブロックチェーン表は、分散型台帳として使用するための分散の部分がまだ実装されていません(私個人の見解あり、所属先の見解ではありません)。
この状態ではブロックチェーン表で何ができるのか、イメージを掴むのが難しいように思います。そこで、同一インスタンス内ですが、異なるワークスペース(Oracle Databaseとして見るとスキーマ)を作成して、その間でブロックチェーン表を共用してみます。
Oracle APEX側では、YROLEワークスペースとDROLEワークスペースを作成します。この間で、以前の記事で作成した表BC_HOUSEHOLD_EXPENSESを共用します。
ワークスペースは管理サービスのワークスペースの作成を呼び出して追加作成します。
ワークスペースを作成する際に、データベース・ユーザー（スキーマ）をそれぞれ、YROLE、DROLEとして作成します。
パスワードは２重入力をしていないので、打ち間違いに要注意です。ワークスペースのログインが拒否された場合、ここで指定したつもりのパスワードが違っていることがあります。その場合は、SQL Developer WebにADMINで接続し、alter user ... identified by パスワードとして、データベース・ユーザーのパスワードを再設定します。alter user文を実行したときに、以下のエラーが出力されず、そのままパスワードの変更が受付けられるようであれば、ワークスペースの作成時に指定したパスワードは指定したはずの文字列とは違っています。
また、アプリケーション・ビルダーへのログイン時の認証には、データベース・ユーザーのパスワードが使用されますが、作成したアプリケーションの認証スキームがApplication Expressアカウント(これがデフォルト)である場合、そのパスワードもユーザーとグループの管理に含まれるユーザーの編集から変更する必要があります。(この開発ユーザーの扱いの違いはAutonomous Database限定です)。
次に、opensslを使って、YROLEとDROLE、それぞれの秘密鍵と証明書を生成します。使用したコマンドを列記します。この中で使用するのは、YROLEの秘密鍵private-yrole.pem、証明書cert-yrole.crt、DROLEの秘密鍵private-drole.pem、証明書cert-drole.crtです。
openssl genrsa -out private-yrole.pem 2048
openssl req -new -days 365 -key private-yrole.pem -out cert-yrole.csr
openssl x509 -in cert-yrole.csr -out cert-yrole.crt -req -signkey private-yrole.pem -days 365
openssl genrsa -out private-drole.pem 2048
openssl req -new -days 365 -key private-drole.pem -out cert-drole.csr
openssl x509 -in cert-drole.csr -out cert-drole.crt -req -signkey private-drole.pem -days 365
証明書のDistinguished Nameに含まれる値で、最低限、Common NameとEmail Addressはそれぞれ異なる値を指定します。yroleまたはdroleといった名前を含めておくと、後でALL_CERTIFICATESビューを検索してGUIDを見つけやすくなるでしょう。
SQL Developer WebにADMINユーザーでログインし、YROLEおよびDROLEに必要な権限を割り与えます。オーナー名の部分は表BC_HOUSEHOLD_EXPENSESを保持するスキーマ名に置き換えます（通常は表BC_HOUSEHOLD_EXPENSESを作成したときのAPEXのワークスペース名と同一です)。
grant execute on dbms_crypto to drole;
grant execute on dbms_crypto to yrole;
grant all on オーナー名.bc_household_expenses to drole;
grant all on オーナー名.bc_household_expenses to yrole;
作成したワークスペースYROLEにサインインし、表BC_HOUSEHOLD_EXPENSESも使うアプリケーションを開発します。
YROLEで表BC_HOUSEHOLD_EXPENSESを扱いやすくするよう、こちらの記事で作成したビューBC_HOUSEHOLD_EXPENSES_Vを作ります。オーナー名の部分は、表BC_HOUSEHOLD_EXPENSESのオーナーに変更します。
create view bc_household_expenses_v
as
select expense_id, place_date, jt.*
from オーナー名.bc_household_expenses,
json_table(flex_column, '$'
COLUMNS(
item varchar2(80) path '$.item',
category varchar2(200) path '$.category',
income number path '$.income',
expense number path '$.expense',
note varchar2(255) path '$.note'
)
) as jt;
クイックSQLを呼び出し、表MY_EXPENSESを以下の定義で作成します。
my_expenses
place_date
item vc80
category vc200
income num
expense num
note vc255
アプリケーションの名前を私の出納帳と指定して、アプリケーションの作成を実行します。作成した表MY_EXPENSESを扱う対話モード・レポートとフォームが作成されます。
アプリケーションが作成されます。対話モード・レポートを開いて、いくつかデータを投入してみます。
データを投入すると、レポートにも表示されます。
その前に、表BC_HOUSEHOLD_EXPENSESの内容を表示するフォームを作成します。
スクリプトに含めるオブジェクトとして、BC_HOUSEHOLD_EXPENSES_V(VIEW)、MY_EXPENSES(TABLE)、SIGN_BC_HOUSEHOLD_EXPENSES(PROCEDURE)の３つを選択し、次へ進みます。
インストール・スクリプトが追加されます。これで、エクスポートしたアプリケーションのインポートと同時に、必要とされるデータベース・オブジェクトも作成されます。
ドラッグ・アンド・ドロップの領域に、先ほどエクスポートされたファイルを指定し、ファイル・タイプとしては、データベース・アプリケーション、ページまたはコンポーネントのエクスポートを選択します。次に進みます。
これでDROLEワークスペースへのアプリケーションのインポートは完了しました。
insert into bc_household_expenses(place_date, flex_column) values(sysdate, '{ "item": "水", "category":"食費", "expense": 100 }');
ページ作成ウィザードを起動し、コンポーネントのフォームを選択します。
次にフォームを選びます。
ページ名を共同経費、ページ・モードはモーダル・ダイアログを指定します。ページ番号は4番になっているはずです。そうでない場合は4番に変更します。
ページ・モードがモーダル・ダイアログなので、ナビゲーション・メニュー・エントリは不要(このページとナビゲーション・メニュー・エントリを関連づけない）です。
表/ビューの名前として、BC_HOUSEHOLD_EXPENSES_V(ビュー)を選択します。
主キーとしてEXPENSE_ID(Number)を選択し、フォームのページを作成します。これでページが完成しました。
次に対話モード・レポートを更新します。対話モード・レポートのソースを以下のSQLに変更します。先ほど作成したフォームのページは４番であることを前提としています。
select
ID,
PLACE_DATE,
ITEM,
CATEGORY,
INCOME,
EXPENSE,
NOTE,
apex_page.get_url(p_page => 3, p_items => 'P3_ID', p_values => ID) as link
from MY_EXPENSES
union
select
expense_id id,
place_date,
item,
category,
income,
expense,
note,
apex_page.get_url(p_page => 4, p_items => 'P4_EXPENSE_ID', p_values => expense_id) as link
from bc_household_expenses_v
列IDとLINKは、非表示列として設定します。
対話モード・レポートのAttributesを開き、リンクのリンク列をカスタム・ターゲットへのリンク、ターゲットを#LINK#とします。表BC_HOUSEHOLD_EXPENSESと表MY_EXPENSESで開くフォームの切り替えは、対話モード・レポートのSQLのapex_page.get_urlファンクションの引数p_pageの指定を変えることで行っています。
これで対話モード・レポートに、表BC_HOUSEHOLD_EXPENSESと表MY_EXPENSESの内容が両方とも表示されます。また、編集アイコンをクリックすることで、それぞれが(異なる)フォームで表示されます。
現時点では、このアプリケーションから表BC_HOUSEHOLD_EXPENSESという共同の出納帳に書き込む機能がありません。これを追加します。
カテゴリが光熱費、食費、給与である場合、表MY_EXPENSESではなく、表BC_HOUSEHOLD_EXPENSESに書き込むことにします。
このスキーマに証明書を登録します。生成した証明書cert-yrole.crtをHTTPS経由で取得できる場所に配置しておきます。以下の例ではcert-yrole.crtを登録しています。
declare
l_cert blob;
l_cert_guid raw(16);
begin
l_cert := apex_web_service.make_rest_request_b(
p_url => 'https://どこかにファイルを配置します/cert-yrole.crt',
p_http_method => 'GET'
);
dbms_user_certs.add_certificate(l_cert, l_cert_guid);
dbms_output.put_line('Certificate GUID = ' || l_cert_guid);
end;
次にSQLコマンドから、書き込んだ行に署名を付加するプロシージャを定義します。これは、こちらの記事に記載されているsign_bc_household_expnesesとほぼ同じです。prvkeyおよびcert_guidはYROLE向けに置き換えます。表BC_HOUSEHOLD_EXPENSESのオーナーも置き換えます。
create or replace procedure sign_bc_household_expenses
(p_expense_id in number)
as
-- sign row
l_inst_id binary_integer;
l_chain_id binary_integer;
l_sequence_no binary_integer;
l_row_data blob;
-- RSA sign
l_raw raw(2000);
prvkey varchar2(2000) := 'YROLEの秘密鍵';
sign_raw raw(2000);
sType PLS_INTEGER := DBMS_CRYPTO.SIGN_SHA512_RSA;
kType PLS_INTEGER := DBMS_CRYPTO.KEY_TYPE_RSA;
cert_guid raw(16) := hextoraw('YROLEの証明書のGUID');
begin
-- sign row
select orabctab_inst_id$, orabctab_chain_id$, orabctab_seq_num$
into l_inst_id, l_chain_id, l_sequence_no
from <オーナー名>.bc_household_expenses where expense_id = p_expense_id;
dbms_blockchain_table.get_bytes_for_row_signature(
'<オーナー名>',
'BC_HOUSEHOLD_EXPENSES',
l_inst_id,
l_chain_id,
l_sequence_no,
1,
l_row_data
);
l_raw := dbms_lob.substr(l_row_data, dbms_lob.getlength(l_row_data));
sign_raw := dbms_crypto.sign
(
src => l_raw,
prv_key => utl_i18n.string_to_raw(prvkey, 'AL32UTF8'),
pubkey_alg => kType,
sign_alg => sType
);
dbms_blockchain_table.sign_row(
'<オーナー名>',
'BC_HOUSEHOLD_EXPENSES',
l_inst_id,
l_chain_id,
l_sequence_no,
NULL,
sign_raw,
cert_guid,
DBMS_BLOCKCHAIN_TABLE.SIGN_ALGO_RSA_SHA2_512
);
end sign_bc_household_expenses;
以下のPL/SQLスクリプトで、表BC_HOUSEHOLD_EXPENESESへの書き込みを確認します。
declare
l_expense_id number;
begin
begin
insert into apexdev.bc_household_expenses(place_date, flex_column)
values(sysdate, '{ "item": "豆腐", "category": "食費", "expense": 128 }')
returning expense_id into l_expense_id;
dbms_output.put_line('EXPENSE_ID = ' || l_expense_id);
commit;
end;
sign_bc_household_expenses(l_expense_id);
end;
表示されるEXPENSE_IDを使って、ORABCTAB_SIGNATURE$, ORABCTAB_SIGNATURE_ALG$, ORABCTAB_SIGNATURE_CERT$を確認します。
select lengthb(orabctab_signature$), orabctab_signature_alg$, orabctab_signature_cert$
from
apexdev.bc_household_expenses where expense_id = <EXPENSE_ID>;
データを書き込むフォームにプロセスを追加します。最初に既存のプロセスが、カテゴリ(P3_CATEGORY)が光熱費:食費:給与である場合に呼び出されない(タイプはアイテムはコロンで区切られたリストに含まれない)ように、サーバー側の条件を設定します。
次に、表BC_HOUSEHOLD_EXPENSESに行を追加するプロセスを追加します。
プロセスの名前を共同出納帳への書き込みとし、タイプはフォーム - 行の自動処理(DML)、フォーム・リージョンはMy Expenseを選びます。ターゲット・タイプはPL/SQL Code、失われた更新の防止はOFFです。
設定するPL/SQLコードは以下です。オーナー名の部分は表BC_HOUSEHOLD_EXPENSESのオーナー名に変更します。
declare
l_expense_id number;
l_flex_column json;
l_jo json_object_t;
begin
case :APEX$ROW_STATUS
when 'C' then
begin
l_jo := json_object_t();
if :P3_ITEM is not null then
l_jo.put('item', :P3_ITEM);
end if;
l_jo.put('category', :P3_CATEGORY);
if :P3_INCOME is not null then
l_jo.put('income', :P3_INCOME);
end if;
if :P3_EXPENSE is not null then
l_jo.put('expense', :P3_EXPENSE);
end if;
if :P3_NOTE is not null then
l_jo.put('note', :P3_NOTE);
end if;
l_flex_column := l_jo.to_json;
insert into オーナー名.bc_household_expenses(place_date, flex_column)
values(sysdate, l_flex_column)
returning expense_id into l_expense_id;
commit;
end;
sign_bc_household_expenses(l_expense_id);
end case;
end;
表MY_EXPENSESへの書き込みとは反対に、カテゴリが光熱費、食費、給与の場合に呼び出されるよう、サーバー側の条件を設定します。ボタン押下時はCREATE、タイプはアイテムはコロンで区切られたリストに含まれる、アイテムはP3_CATEGORY、リストは光熱費:食費:給与です。
これでYROLEワークスペースでのアプリケーションは完成とします。
次にDROLEワークスペースに同様のアプリケーションを作成します。同じ作業を繰り返すのを避けるため、今まで作成したアプリケーションをエクスポートして、それをDROLEワークスペースにインポートします。
作成した表、ビュー、プロシージャも同時にエクスポート/インポートするように、インストール・スクリプトを追加します。
サポートするオブジェクトを開きます。
インストール・スクリプトを開きます。
作成をクリックします。
データベース・オブジェクトから作成を選択します。
作成されるスクリプトの名前をmyobjectsとし、オブジェクト・タイプはすべて選択し、次に進みます。
生成されたDDLを確認して、作成をクリックします。
インストール・スクリプトが追加されます。これで、エクスポートしたアプリケーションのインポートと同時に、必要とされるデータベース・オブジェクトも作成されます。
続けて、アプリケーションをエクスポートします。
エスクポートを選択します。
サポートするオブジェクトの定義をエスクポートがはい、になっていることを確認し、エクスポートを実行します。この設定はデフォルトではい、で、その他の設定もデフォルトのままで十分です。
エクスポートを実行するとfアプリケーション番号.sqlという名称のSQLファイルがダウンロードされます。
YROLEワークスペースからサインアウトし、DROLEワークスペースへサインインします。
SQLワークショップのSQLコマンドより、証明書の追加を行います。追加する証明書はcert-drole.crtです。
declare
l_cert blob;
l_cert_guid raw(16);
begin
l_cert := apex_web_service.make_rest_request_b(
p_url => 'https://どこかにファイルを配置します/cert-drole.crt',
p_http_method => 'GET'
);
dbms_user_certs.add_certificate(l_cert, l_cert_guid);
dbms_output.put_line('Certificate GUID = ' || l_cert_guid);
end;
出力されたGUIDは記録しておきます。
次に、アプリケーションをインポートします。
ドラッグ・アンド・ドロップの領域に、先ほどエクスポートされたファイルを指定し、ファイル・タイプとしては、データベース・アプリケーション、ページまたはコンポーネントのエクスポートを選択します。次に進みます。
インポートされたスクリプトからアプリケーションをインストールするため、次へ進みます。
別のアプリケーションとしてインストールに、新規アプリケーションIDを自動割り当て、を選択し、アプリケーションのインストールを実行します。
サポートするオブジェクトのインストールをONにし、次へ進みます。
インストールを実行します。
インストールが成功し、アプリケーションのサポートしているオブジェクトがインストールされました。と表示されていれば、アプリケーションのエクスポートに含まれている３つのデータベース・オブジェクトも正常に作成されています。
インストール・サマリーからも確認できます。
インポートしたプロシージャSIGN_BC_HOUSEHOLD_EXPENSESにはYROLEの秘密鍵と証明書のGUIDが設定されているので、これをDROLEの秘密鍵、証明書のGUIDに置き換えます。
SQLワークショップのオブジェクト・ブラウザよりプロシージャを選んで、SIGN_BC_HOUSEHOLD_EXPENSESのコードを開きます。コードのprvkeyとcert_guidの部分を更新し、保存してコンパイルを実行します。
インポートしたアプリケーションを実行します。表BC_HOUSEHOLD_EXPENSESの内容はリストされていますが、YROLEスキーマに存在するMY_EXPENSES表の内容はリストされていません。
共同の経費となるガスの費用を、カテゴリを光熱費として入力します。
個人経費(光熱費、食費、給与以外)も入力します。
これで、YROLE、DROLEのそれぞれのアプケーションで、共同の出納帳である表BC_HOUSEHOLD_EXPENSESを扱いつつ、自前の表MY_EXPENSESを使うアプリケーションを開発することができました。
このようにして、結局のところ、何が嬉しいのか、というのはあるかもしれません。
共同の出納帳である表BC_HOUSEHOLD_EXPENSESへ、誰が書き込んだのかというのを以下のSQLにて確認できます。DBA_CERTIFICATESビューの権限がないと実行できないので、APEXのワークスペースからではなく、SQL Developer WebにADMINユーザーでサインインして実行します。
select
t.expense_id,
t.orabctab_inst_id$, t.orabctab_chain_id$, orabctab_seq_num$,
c.distinguished_name
from apexdev.bc_household_expenses t join dba_certificates c
on t.orabctab_signature_cert$ = certificate_guid
order by t.orabctab_inst_id$, t.orabctab_chain_id$, orabctab_seq_num$
以下のような結果が得られます。
データベースによって以下が保証されている状態で、表を共有できるだけといえば、その通りかな？とも思います。
- 書き込まれたデータを改ざんできない（更新できない、削除できない）
- 誰が書き込んだのか特定できる
しかし、同様の仕組みを実装するのは並大抵の努力ではできないですし、この仕組みがあることを前提とすることで開発が容易になったり、今までは実現が難しかったアプリケーションも開発できるようになるのではないかという印象を持っています。
簡単なものでも動くアプリケーションを作ることで、イメージを掴むきっかけになったのであれば幸いです。
追記
最後にあれ？と思ったことをいくつか。
その１
証明書の追加はDBMS_USER_CERTS.ADD_CERTIFICATEで行います。追加した証明書は基本的には、ADD_CERTIFICATEしたユーザーにしか見えません。それでもDBMS_BOCKCHAIN_TABLE.VERIFY_ROWSは(別のユーザーから見える証明書を使って)検証が成功します。署名を付けるのはそれぞれのデータベース・ユーザーが行うことですが、署名を検証するのはデータベース・システムの仕事、ということなのかと思いました。
その２
例えば、以下の３行を続けて、表BC_HOUSEHOLD_EXPENSES表にデータを書き込んだとします。
commit;
insert into bc_household_expenses(place_date, flex_column) values(sysdate, '{ "item": "水", "category":"食費", "expense": 99 }');
commit;
insert into bc_household_expenses(place_date, flex_column) values(sysdate, '{ "item": "水", "category":"食費", "expense": 98 }');
commit;
それぞれ、主キーとなるEXPENSE_IDとして41, 42, 43が割り振られたとすると、次のように署名を付けることができます。
begin
sign_bc_household_expenses(43);end;
begin
sign_bc_household_expenses(42);end;
begin
sign_bc_household_expenses(41);end;
つまり、署名は行のハッシュ値には含まれていないようです。一旦、行に署名をすると以降、置き換えることはできないようになっているので、それ自体は問題ではないでしょう。思うに、別のインスタンスとデータを交換する際に署名(DBMS_BLOCKCHAIN_TABLE.SIGN_ROW)および検証(DBMS_BLOCKCHAIN_TABLE.VERIFY_ROWS)が使われ、今回のようにアプリケーションから呼び出すような使い方をするものではないのだと思います。もし、一般の開発者が署名をつけるコードを呼び出すとすると、秘密鍵を管理する必要もありますし、それは相当に荷が重い作業です。
その３
データベースのトランザクションとの関連で、２つのセッションA, Bがあるとして、以下のような順序で書き込みをしたとします。
セッションB:insert into bc_household_expenses(place_date, flex_column) values(sysdate, '{ "item": "炭酸水", "category":"食費", "expense": 101 }');
セッションA:insert into bc_household_expenses(place_date, flex_column) values(sysdate, '{ "item": "水", "category":"食費", "expense": 102 }');
セッションB:insert into bc_household_expenses(place_date, flex_column) values(sysdate, '{ "item": "炭酸水", "category":"食費", "expense": 103 }');
セッションB: commit;
セッションA: commit;
セッションBのcommit時にハッシュを計算するとしたら、未コミットのセッションAのデータが読める必要があるし、一体、どのようにしているのかと疑問だったのですが、別のトランザクションではチェーンが異なって(ORABCTAB_CHAIN_ID$が違う)いました。ブロックチェーン表に書き込まれる行すべてが一本のチェーンになる、といった扱いではないようです。
その４
DBMS_USER_CERTS.DROP_CERTIFICATEにて登録済みの証明書をドロップすると、同一の証明書を再度登録してもGUIDは異なるので、それまで署名された行の検証は不可能になります。それはそうなのですが、迂闊なことはできないなと思いました。
追記の追記
以下で同じことでは？といわれると、単一インスタンス内では、確かにあまり変わりが無いかなと思います。
grant select, insert on bc_household_expenses to yrole; grant select, insert on bc_household_expenses to drole;
分散の部分が実装されれば、今はOracle Databaseのサポート対象から外れたマルチマスター・レプリケーションの機能が、より実用的な形で帰ってくるという印象を持っています。レプリケーションの参加者が適切に振舞うことを要求していませんし(信頼は暗号鍵に依存する)、競合解消も合理的に行われると期待できます。
完
