1. データベース管理者のための主要20c新機能の学習
  2. データベース管理者のための主要20c新機能の学習
  3. セキュリティ・ソリューション
  4. セキュリティ
  5. Oracle Blockchain Table
  6. 演習: ブロックチェーン表および行の管理

演習: ブロックチェーン表および行の管理

この演習では、Oracleブロックチェーン表を作成、変更および削除する方法を示します。

  1. 新しい演習を開始する前に、演習の環境の推奨事項を参照してください。
  2. PDB20のHR.EMPLOYEESに関する現在および過去のトランザクションの改ざん防止レジャーを管理するために、AUDITOR.LEDGER_EMPという名前のブロックチェーン表を作成します。ブロックチェーン表AUDITOR.LEDGER_EMPの行は一切削除できません。さらに、このブロックチェーン表は、非アクティブな状態で31日経過した後にのみ削除できます。
    1. 表の作成を開始する前に、/home/oracle/labs/M104781GC10/setup_user.shシェル・スクリプトを実行します。
      $ cd /home/oracle/labs/M104781GC10
$ /home/oracle/labs/M104781GC10/setup_user.sh
SQL*Plus: Release 20.0.0.0.0 - Production on Mon Mar 9 05:34:10 2020
Version 20.2.0.0.0

Copyright (c) 1982, 2020, Oracle.  All rights reserved.


Connected to:
Oracle Database 20c Enterprise Edition Release 20.0.0.0.0 - Production
Version 20.2.0.0.0


specify password for HR as parameter 1:

specify default tablespeace for HR as parameter 2:

specify temporary tablespace for HR as parameter 3:

specify log path as parameter 4:


PL/SQL procedure successfully completed.
...
SQL> Disconnected from Oracle Database 20c Enterprise Edition Release 20.0.0.0.0 - Production
Version 20.2.0.0.0

SQL*Plus: Release 20.0.0.0.0 - Production on Mon Mar 9 05:34:16 2020
Version 20.2.0.0.0

Copyright (c) 1982, 2020, Oracle.  All rights reserved.


Connected to:
Oracle Database 20c Enterprise Edition Release 20.0.0.0.0 - Production
Version 20.2.0.0.0
SQL> DROP USER auditor CASCADE;
DROP USER auditor CASCADE
          *
ERROR at line 1:
ORA-01918: user 'AUDITOR' does not exist


SQL> ALTER SYSTEM SET db_create_file_dest='/home/oracle/labs';

System altered.

SQL>
SQL> DROP TABLESPACE ledgertbs INCLUDING CONTENTS AND DATAFILES cascade constraints;
DROP TABLESPACE ledgertbs INCLUDING CONTENTS AND DATAFILES cascade constraints
*
ERROR at line 1:
ORA-00959: tablespace 'LEDGERTBS' does not exist

SQL> CREATE TABLESPACE ledgertbs;

Tablespace created.

SQL> CREATE USER auditor identified by password DEFAULT TABLESPACE ledgertbs;

User created.

SQL> GRANT create session, create table, unlimited tablespace TO auditor;

Grant succeeded.

SQL> GRANT execute ON sys.dbms_blockchain_table TO auditor;

Grant succeeded.

SQL> GRANT select ON hr.employees TO auditor;

Grant succeeded.

SQL>
SQL> exit
Disconnected from Oracle Database 20c Enterprise Edition Release 20.0.0.0.0 - Production
Version 20.2.0.0.0
$
    2. AUDITOR.LEDGER_EMPという名前のブロックチェーン表を作成します。
      $ sqlplus auditor@PDB20
SQL*Plus: Release 20.0.0.0.0 - Production on Mon Mar 9 05:37:25 2020
Version 20.2.0.0.0

Copyright (c) 1982, 2020, Oracle.  All rights reserved.

Enter password: password

SQL> CREATE BLOCKCHAIN TABLE ledger_emp (employee_id NUMBER, salary NUMBER);
CREATE BLOCKCHAIN TABLE ledger_emp (employee_id NUMBER, salary NUMBER)
                                                       *
ERROR at line 1:
ORA-00905: missing keyword

      注意:

      CREATE BLOCKCHAIN TABLE文に追加の属性が必要であることに注目します。

      NO DROP、NO DELETE、HASHING USINGおよびVERSION句は必須です。 

      SQL> CREATE BLOCKCHAIN TABLE ledger_emp (employee_id NUMBER, salary NUMBER)
                     NO DROP UNTIL 31 DAYS IDLE
                     NO DELETE LOCKED
                     HASHING USING "SHA2_512" VERSION "v1";

Table created.

SQL>
    3. 該当するデータ・ディクショナリ・ビューのブロックチェーン表に設定されている属性を確認します。
      SQL> SELECT row_retention, row_retention_locked, 
                     table_inactivity_retention, hash_algorithm  
              FROM   user_blockchain_tables 
              WHERE  table_name='LEDGER_EMP';

ROW_RETENTION ROW TABLE_INACTIVITY_RETENTION HASH_ALG
------------- --- -------------------------- --------
              YES                         31 SHA2_512

SQL>
    4. 表の説明を表示します。
      SQL> DESC ledger_emp
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPLOYEE_ID                                        NUMBER
 SALARY                                             NUMBER

SQL>

      注意:

      表示可能な列のみが説明に表示されていることに注目します。
    5. USER_TAB_COLSビューを使用して、ユーザー番号やユーザー署名などの内部情報の格納に使用されるすべての内部列名を表示します。
      SQL> COL "Data Length" FORMAT 9999
SQL> COL "Column Name" FORMAT A24
SQL> COL "Data Type" FORMAT A28
SQL> SELECT internal_column_id "Col ID", SUBSTR(column_name,1,30) "Column Name", 
                     SUBSTR(data_type,1,30) "Data Type", data_length "Data Length"
              FROM   user_tab_cols       
              WHERE  table_name = 'LEDGER_EMP' ORDER BY internal_column_id;

    Col ID Column Name              Data Type                    Data Length
---------- ------------------------ ---------------------------- -----------
         1 EMPLOYEE_ID              NUMBER                                22
         2 SALARY                   NUMBER                                22
         3 ORABCTAB_INST_ID$        NUMBER                                22
         4 ORABCTAB_CHAIN_ID$       NUMBER                                22
         5 ORABCTAB_SEQ_NUM$        NUMBER                                22
         6 ORABCTAB_CREATION_TIME$  TIMESTAMP(6) WITH TIME ZONE           13
         7 ORABCTAB_USER_NUMBER$    NUMBER                                22
         8 ORABCTAB_HASH$           RAW                                 2000
         9 ORABCTAB_SIGNATURE$      RAW                                 2000
        10 ORABCTAB_SIGNATURE_ALG$  NUMBER                                22
        11 ORABCTAB_SIGNATURE_CERT$ RAW                                   16
        12 ORABCTAB_SPARE$          RAW                                 2000

12 rows selected.

SQL>
  3. 監査アプリケーションが実行するかのように、ブロックチェーン表に行を挿入します。
    1. ブロックチェーン表に最初の行を挿入します。
      SQL> INSERT INTO ledger_emp VALUES (106,12000);

1 row created.

SQL> COMMIT;

Commit complete.

SQL>
    2. チェーンの最初の行の内部値を表示します。
      SQL> COL "Chain date" FORMAT A17
SQL> COL "Chain ID" FORMAT 99999999
SQL> COL "Seq Num" FORMAT 99999999
SQL> COL "User Num" FORMAT 9999999
SQL> COL "Chain HASH" FORMAT 99999999999999
SQL> SELECT ORABCTAB_CHAIN_ID$ "Chain ID", ORABCTAB_SEQ_NUM$ "Seq Num",
            to_char(ORABCTAB_CREATION_TIME$,'dd-Mon-YYYY hh-mi') "Chain date",
            ORABCTAB_USER_NUMBER$ "User Num", ORABCTAB_HASH$ "Chain HASH"
     FROM   ledger_emp;

 Chain ID   Seq Num Chain date        User Num
--------- --------- ----------------- --------
Chain HASH
--------------------------------------------------------------------------------
       14         1 06-Apr-2020 12-26      119
5812238B734B019EE553FF8A7FF573A14CFA1076AB312517047368D600984CFAB001FA1FF2C98B13
9AB03DDCCF8F6C14ADF16FFD678756572F102D43420E69B3

SQL>
    3. HRとして接続し、監査アプリケーションが実行するかのように、ブロックチェーン表に行を挿入します。まず、表に対するINSERT権限をHRに付与します。
      SQL> GRANT insert ON ledger_emp TO hr;

Grant succeeded.

SQL>
    4. HRとして接続し、新しい行を挿入します。
      SQL> CONNECT hr@PDB20
Enter password: password
Connected.
SQL> INSERT INTO  auditor.ledger_emp VALUES (106,24000);

1 row created.

SQL> COMMIT;

Commit complete.

SQL>
    5. AUDITORとして接続し、ブロックチェーン表の行の内部値および外部値を表示します。
      SQL> CONNECT auditor@PDB20
Enter password: password
Connected.
SQL> SELECT ORABCTAB_CHAIN_ID$ "Chain ID", ORABCTAB_SEQ_NUM$ "Seq Num",
               to_char(ORABCTAB_CREATION_TIME$,'dd-Mon-YYYY hh-mi') "Chain date",
               ORABCTAB_USER_NUMBER$ "User Num", ORABCTAB_HASH$ "Chain HASH",
               employee_id, salary
        FROM   ledger_emp;

 Chain ID   Seq Num Chain date        User Num
--------- --------- ----------------- --------
Chain HASH
--------------------------------------------------------------------------------
EMPLOYEE_ID     SALARY
----------- ----------
       14         1 06-Apr-2020 12-26      119
5812238B734B019EE553FF8A7FF573A14CFA1076AB312517047368D600984CFAB001FA1FF2C98B13
9AB03DDCCF8F6C14ADF16FFD678756572F102D43420E69B3
        106      12000

       14         2 06-Apr-2020 12-28      118
BBCDACC41B489DFBD8E28244841411937BD716F987BE750146572C555311E377D6DBA28D392C61E7
D75BA47BFCB3A2F4920A2C149409E89FBA63E10549DF4F47
        106      24000

SQL>

      ユーザー番号が異なることに注目します。この値は、V$SESSION.USER#列と同じ値です。

  4. HRが挿入した行を削除します。
    SQL> DELETE FROM ledger_emp WHERE ORABCTAB_USER_NUMBER$ = 106;
DELETE FROM ledger_emp WHERE ORABCTAB_USER_NUMBER$ = 106
          *
ERROR at line 1:
ORA-05715: operation not allowed on the blockchain table

SQL>

    DML DELETEコマンドでブロックチェーン表の行を削除することはできません。DBMS_BLOCKCHAIN_TABLEパッケージを使用する必要があります。 

    SQL> SET SERVEROUTPUT ON
SQL> DECLARE
   NUMBER_ROWS NUMBER;
BEGIN
   DBMS_BLOCKCHAIN_TABLE.DELETE_EXPIRED_ROWS('AUDITOR','LEDGER_EMP', null, NUMBER_ROWS);
   DBMS_OUTPUT.PUT_LINE('Number of rows deleted=' || NUMBER_ROWS);
END;
/    2    3    4    5    6    7
Number of rows deleted=0

PL/SQL procedure successfully completed.
SQL>

    ブロックチェーン表の行は、DBMS_BLOCKCHAIN_TABLEパッケージの使用によってのみ削除でき、保存期間外の行しか削除できません。このため、行が一切削除されずにプロシージャは正常に完了します。

    インストールされているOracle Databaseリリースが20.0.0の場合、使用するプロシージャはDBMS_BLOCKCHAIN_TABLE.DELETE_ROWSであり、DBMS_BLOCKCHAIN_TABLE.DELETE_EXPIRED_ROWSではありません。

  5. 表を切り捨てます。
    SQL> TRUNCATE TABLE ledger_emp;
TRUNCATE TABLE ledger_emp
               *
ERROR at line 1:
ORA-05715: operation not allowed on the blockchain table

SQL>
  6. 今度は、作成後15日経過するまで行を削除できないように指定します。
    SQL> ALTER TABLE ledger_emp NO DELETE UNTIL 15 DAYS AFTER INSERT;
ALTER TABLE ledger_emp NO DELETE UNTIL 15 DAYS AFTER INSERT
*
ERROR at line 1:
ORA-05731: blockchain table LEDGER_EMP cannot be altered

SQL>

    この属性を変更できないのはなぜですか。

    この表はNO DELETE LOCKED属性を使用して作成しました。LOCKED句は、後で行の保存を変更できないことを示します。

  7. 表を削除します。
    SQL> DROP TABLE ledger_emp;
DROP TABLE ledger_emp
           *
ERROR at line 1:
ORA-05723: drop blockchain table LEDGER_EMP not allowed

SQL>

    注意:

    エラー・メッセージが若干異なることに注目します。前述の2つのコマンドからのエラー・メッセージは、ブロックチェーン表に対して操作を実行できなかったことを示しています。現在のエラー・メッセージは、このLEDGER_EMP表に対してはDROP TABLEを実行できないことを示しています。

    このブロックチェーン表は、非アクティブな状態で31日経過する前に削除できないように作成しました。

  8. 保存期間が短くなるように表の動作を変更します。
    SQL> ALTER TABLE ledger_emp NO DROP UNTIL 1 DAYS IDLE;
ALTER TABLE auditor.ledger_emp NO DROP UNTIL 1 DAYS IDLE
*
ERROR at line 1:
ORA-05732: retention value cannot be lowered

SQL> ALTER TABLE ledger_emp NO DROP UNTIL 40 DAYS IDLE;

Table altered.

SQL>

    保存値の増加のみが可能です。これにより、セキュリティのために保持する必要がある履歴情報は削除できなくなります。

  9. 別のブロックチェーン表AUDITOR.LEDGER_TESTを作成します。行は、挿入後5日経過するまで削除できませんが、行の削除は可能です。さらに、このブロックチェーン表は、非アクティブな状態で1日経過した後にのみ削除できます。
    1. ブロックチェーン表を作成します。
      SQL> CREATE BLOCKCHAIN TABLE auditor.ledger_test (id NUMBER, label VARCHAR2(2))
       NO DROP UNTIL 1 DAYS IDLE
       NO DELETE UNTIL 5 DAYS AFTER INSERT
       HASHING USING "SHA2_512" VERSION "v1";
 2    3    4  CREATE BLOCKCHAIN TABLE auditor.ledger_test (id NUMBER, label VARCHAR2(2))
*
ERROR at line 1:
ORA-05741: minimum retention time too low, should be at least 16 days

SQL> CREATE BLOCKCHAIN TABLE auditor.ledger_test (id NUMBER, label VARCHAR2(2))
       NO DROP UNTIL 16 DAYS IDLE
       NO DELETE UNTIL 16 DAYS AFTER INSERT
       HASHING USING "SHA2_512" VERSION "v1";  

Table created.

SQL>
    2. HRとして接続し、監査アプリケーションが実行するかのように、ブロックチェーン表に行を挿入します。まず、表に対するINSERT権限をHRに付与します。
      SQL> GRANT insert ON auditor.ledger_test TO hr;

Grant succeeded.

SQL>
    3. HRとして接続し、新しい行を挿入します。
      SQL> CONNECT hr@PDB20
Enter password: password
Connected.
SQL> INSERT INTO auditor.ledger_test VALUES (1,'A1');

1 row created.

SQL> COMMIT;

Commit complete.

SQL>
    4. AUDITORとして接続し、挿入された行を表示します。
      SQL> CONNECT auditor@PDB20
Enter password: password
Connected.
SQL> SELECT * FROM auditor.ledger_test;

        ID LA
---------- --
         1 A1

SQL>
  10. 行の内容が引き続き有効であることを定期的に確認します。
    1. DBMS_BLOCKCHAIN_TABLE.VERIFY_ROWSを使用して、行を検証します。
      SQL> CONNECT auditor@PDB20
Enter password: password
Connected.
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
   row_count NUMBER;
   verify_rows NUMBER;
   instance_id NUMBER;
BEGIN
  FOR instance_id IN 1 .. 2 LOOP
    SELECT COUNT(*) INTO row_count FROM auditor.ledger_test WHERE ORABCTAB_INST_ID$=instance_id;
    DBMS_BLOCKCHAIN_TABLE.VERIFY_ROWS('AUDITOR','LEDGER_TEST', NULL, NULL, instance_id, NULL, verify_rows);
    DBMS_OUTPUT.PUT_LINE('Number of rows verified in instance Id '|| instance_id || ' = '|| row_count);
  END LOOP;
END;
/
Number of rows verified in instance Id 1 = 1
Number of rows verified in instance Id 2 = 0

PL/SQL procedure successfully completed.
SQL> EXIT
$

親トピック: Oracle Blockchain Table