Site icon Sean Stacey – for What it's Worth

Introduction to Oracle Blockchain tables

First introduced in Oracle Database 20c, Blockchain Tables deliver an easy to use tamper-resistant table that is built upon the principles of the blockchain model. The tables are insert-only and immutable while still being able to participate in transactions with other ordinary (heap-organized) Oracle tables and best of all, they can be indexed and partitioned and operated upon with regular SQL.

Note: Blockchain Tables provide a Centralized Ledger solution. If you are looking for a Distributed Ledger solution then the Oracle Blockchain Platform is what you are looking for: https://www.oracle.com/blockchain/

I have divided this post into the following three sections:

   1.  An architectural dive into the components of the tables  
   2.  A quick demo on getting started with Blockchain Tables
   3.  A few days later, deleting rows from the table

You can find out more about Blockchain tables in the online Oracle documentation here: https://docs.oracle.com/en/database/oracle/oracle-database/20/admin/managing-tables.html#GUID-E7151628-AF04-48D4-9CB4-F72417AFC391


Part 1. An architectural dive into the components of the tables

Structurally speaking, Blockchain tables differ from ordinary Oracle tables by chaining each row with the previous row using a SHA-512 hashing algorithm. The hash value can then be verified by all participants in a transaction. In addition to the row contents being hashed, there are several hidden columns that contain additional information related to the transaction, such as the user number, the instance id (useful for running on a RAC cluster), the timestamp for the row insertion, etc

Here are the additional 10 hidden columns that are appended to every row in a Blockchain table:


Column Name                Hidden  Datatype                       Length 
-------------------------- ------- ----------------------------- ------- 
ORABCTAB_HASH$             YES     RAW                              2000 
ORABCTAB_SIGNATURE$        YES     RAW                              2000 
ORABCTAB_SIGNATURE_ALG$    YES     NUMBER                             22 
ORABCTAB_SIGNATURE_CERT$   YES     RAW                                16 
ORABCTAB_USER_NUMBER$      YES     NUMBER                             22 
ORABCTAB_CREATION_TIME$    YES     TIMESTAMP(6) WITH TIME ZONE        13 
ORABCTAB_SPARE$            YES     RAW                              2000 
ORABCTAB_CHAIN_ID$         YES     NUMBER                             22 
ORABCTAB_INST_ID$          YES     NUMBER                             22 
ORABCTAB_SEQ_NUM$          YES     NUMBER                             22

Note: I have provided the query used to see these columns in section 2 below.

The first column in the list: “ORABCTAB_HASH$” contains a hash of the data from the row combined with the hash contents from the previous row, in other words its a chain of the row with the previous row. Oracle also provides a package called dbms_blockchain_table that includes a number of procedures to verify and administer the contents of the Blockchain table. We’ll take a look at these in the next sections.

Additional information on the internals of the Blockchain Tables including the hidden column descriptions can be found in the online documentation. I noticed the link in the online documentation is broken so I have included two links here: https://docs.oracle.com/en/database/oracle/oracle-database/20/admin/managing-tables.html#GUID-E7151628-AF04-48D4-9CB4-F72417AFC391
–or–
https://docs.oracle.com/en/database/oracle/oracle-database/20/admin/managing-tables.html Section 19.17 Managing Blockchain Tables

Note 1. There is a special hash value when the first row is inserted
Note 2. cannot reduce the No of days idle but can increase the value. Unless you specify “LOCKED” in which case you can NEVER MODIFY the number of days idle before deletion.

Hidden columns
https://docs.oracle.com/en/database/oracle/oracle-database/20/admin/managing-tables.html#GUID-02BD6C35-E1E9-41B7-BD56-68C7A667B51F

Part 2. A quick demo on getting started with Blockchain Tables

In this example I will create a Blockchain table called I_OWE_YOU :

1. Our first step is to create our blockchain table


create BLOCKCHAIN table I_OWE_YOU  
  ( txn_id number(12)   primary key, 
    payer varchar2(20), 
    requestor varchar2(20),
    amount number(8,2),
    timestamp date )
NO DROP UNTIL 16 DAYS IDLE 
NO DELETE UNTIL 16 DAYS AFTER INSERT 
HASHING USING "SHA2_512" VERSION "V1" ;

The three additional clauses in the table definition are-

NO DROP UNTIL 16 DAYS IDLE – this configures the table so that it cannot be dropped until the newest row in the table is less than the number of specified days.
NO DELETE LOCKED – this blocks rows from ever being deleted from the table.
NO DELETE UNTIL 16 DAYS AFTER INSERT – this sets an idle time before rows can be deleted from the table
HASHING USING “SHA2_512” VERSION “V1” – are mandatory clauses that cannot be changed as of now.

We will also insert some sample data to get started with.


insert into I_OWE_YOU values (1, 'Mark', 'Lee', 50.00, sysdate) ;
insert into I_OWE_YOU values (2, 'Anne', 'Harry', 15.00, sysdate) ;
insert into I_OWE_YOU values (3, 'Charlie', 'Mary', 750.00, sysdate) ;
insert into I_OWE_YOU values (4, 'Mary', 'Sean', 20.00, sysdate ) ; 
commit ;

 

Let’s see the rows we’ve inserted…


SQL> select * from I_OWE_YOU ;

   TXN_ID  PAYER        REQUESTOR        AMOUNT TIMESTAMP
---------- ------------ ------------ ---------- ---------
         1 Mark         Lee                  50 17-NOV-20
         2 Anne         Harry                15 17-NOV-20
         3 Charlie      Mary                750 17-NOV-20
         4 Mary         Sean                 20 17-NOV-20

You will have noticed that there does not appear anything special about this table. This is good news for existing applications and users who do not need to modify the data.

2. Immutable table operations

Let’s take a look at what happens if you do try to delete or update a row in the table:

SQL> delete from I_OWE_YOU
  2  where TXN_ID = 2
  3  /
delete from I_OWE_YOU
            *
ERROR at line 1:
ORA-05715: operation not allowed on the blockchain table

SQL> update I_OWE_YOU
  2  set REQUESTOR = 'Mike'
  3  where TXN_ID = 2
  4  /
update I_OWE_YOU
       *
ERROR at line 1:
ORA-05715: operation not allowed on the blockchain table

The same restrictions apply to trying to modify the table itself:

SQL> drop table I_OWE_YOU ;
drop table I_OWE_YOU
           *
ERROR at line 1:
ORA-05723: drop blockchain table I_OWE_YOU not allowed 

SQL> truncate table I_OWE_YOU ;
truncate table I_OWE_YOU
               *
ERROR at line 1:
ORA-05715: operation not allowed on the blockchain table

Just as an FYI, it is possible to copy the table using a Create Table as Select (CTAS) operation, but it will simply create an ordinary heap-organized table without the additional Blockchain safeguards in place.

3. Blockchain table meta data

So we’ve seen the table is tamper-resistent, now lets look at some of the meta data for the Blockchain table. We can start by looking at the table using normal methods, you will notice there are no special flags to indicate that this is a Blockchain Table:


SQL> select * from user_tables where table_name = 'I_OWE_YOU' ;

But do not despair… there is however a new view to display Blockchain Table information:


SQL> select * from all_blockchain_tables 
  2  where table_name = 'I_OWE_YOU'
  3  /


SCHEMA_NAME TABLE_NAME ROW_RETENTION ROW TABLE_INACTIVITY_RETENTION HASH_ALG
----------- ---------- ------------- --- -------------------------- --------
SEAN        I_OWE_YOU                YES                         16 SHA2_512

SQL>

To take this one step further, what if we want to view the hidden columns of the table? This can be easily done with the following query:


SQL> SELECT column_id,
  2         column_name,
  3         hidden_column,
  4         data_type,
  5         data_length
  6  FROM   all_tab_cols
  7  WHERE  table_name = 'I_OWE_YOU'
  8  ORDER BY column_id
  9  /

COLUMN_ID COLUMN_NAME              HIDDEN DATA_TYPE                  DATA_LENGTH
--------- ------------------------ ------ -------------------------- -----------
        1 TXN_ID                   NO     NUMBER                              22
        2 PAYER                    NO     VARCHAR2                            60
        3 REQUESTOR                NO     VARCHAR2                            60
        4 AMOUNT                   NO     NUMBER                              22
        5 TIMESTAMP                NO     DATE                                 7
          ORABCTAB_HASH$           YES    RAW                               2000
          ORABCTAB_SIGNATURE$      YES    RAW                               2000
          ORABCTAB_SIGNATURE_ALG$  YES    NUMBER                              22
          ORABCTAB_SIGNATURE_CERT$ YES    RAW                                 16
          ORABCTAB_USER_NUMBER$    YES    NUMBER                              22
          ORABCTAB_CREATION_TIME$  YES    TIMESTAMP(6) WITH TIME ZONE         13
          ORABCTAB_SPARE$          YES    RAW                               2000
          ORABCTAB_CHAIN_ID$       YES    NUMBER                              22
          ORABCTAB_INST_ID$        YES    NUMBER                              22
          ORABCTAB_SEQ_NUM$        YES    NUMBER                              22

15 rows selected.

We can also see the internal data associated with new rows


SELECT ORABCTAB_CHAIN_ID$ "Chain ID",
     ORABCTAB_SEQ_NUM$ "Sequence Number",
     to_char(ORABCTAB_CREATION_TIME$, 'DD-MON-YYYY hh:mi:ss') "Date Inserted",
     ORABCTAB_USER_NUMBER$ "User Num",
     ORABCTAB_INST_ID$ "Instance Id",
     ORABCTAB_HASH$ "Chain HASH"
FROM I_OWE_YOU ;

NOTE: If data is inserted but not yet committed the hidden columns for those rows will have an entry of (null).

4. Verifying Blockchain table row contents

The next powerful feature of Blockchain Tables is the ability to “verify” the row contents or transactions. It is important to have confidence in the integrity of the data to know that the entries have not been tampered with.


-- Demo whether the table has been tampered with...
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
   2	     verified_rows NUMBER := 0;
   3     BEGIN
   4	     DBMS_BLOCKCHAIN_TABLE.VERIFY_ROWS('SEAN', 'I_OWE_YOU', number_of_rows_verified=> verified_rows) ;
   5	     DBMS_OUTPUT.PUT_LINE('Number of rows verified = ' || verified_rows) ;
   6     END ;
   7  /

Number of rows verified = 4

PL/SQL procedure successfully completed.

The value of being able to verify or audit the contents of the Blockchain table is best illustrated when there are multiple participants involved in the ledger transactions. Blockchain tables allow multiple users to verify or audit the transaction integrity quite simply.

BUT before we get started, let’s login as a new user: Mary and insert some more data…


connect mary@pdb1
insert into SEAN.I_OWE_YOU values (5, 'Christian', 'Anuj', 10.00, sysdate ) ; insert into SEAN.I_OWE_YOU values (6, 'Harry', 'Jose', 30.00, sysdate ) ; commit ; SQL> select * from SEAN.I_OWE_YOU 2 / TXN_ID PAYER REQUESTOR AMOUNT TIMESTAMP ------ ---------- ---------- ------ --------- 1 Mark Lee 50 17-NOV-20 2 Anne Harry 15 17-NOV-20 3 Charlie Mary 750 17-NOV-20 4 Mary Sean 20 17-NOV-20 5 Christian Anuj 10 19-NOV-20 6 Harry Jose 30 19-NOV-20 6 rows selected.

<p>&nbsp;<br></p>

5. Now let’s run our query to see the hidden columns…

SQLDeveloper does a nice job of displaying the wide width hash column. But to make this a bit more user-friendly, I have modified the statement to include the username, dropped the instance_id and trimmed the hash column as follows:


SQL> SELECT ORABCTAB_CHAIN_ID$ "Chain ID",
  2         ORABCTAB_SEQ_NUM$ "Sequence Number",
  3	        to_char(ORABCTAB_CREATION_TIME$,'DD-MON-YYYY hh:mi:ss') "Date Inserted",
  4	        ORABCTAB_USER_NUMBER$ "User Num",
  5	        B.username "Inserted By",
  6         substr(ORABCTAB_HASH$,1,20) "Chain Hash"
  7  FROM   SEAN.I_OWE_YOU A,  ALL_USERS B
  8  WHERE  A.ORABCTAB_USER_NUMBER$ = B.USER_ID
  9  /

  Chain ID Sequence Number Date Inserted	       User Num Inserted By        Chain Hash
 --------- --------------- ------------------------- ---------- ------------------ --------------------  
         2               1 19-NOV-2020 08:17:17             108 SEAN               34A8B65BE63A03BC2CAC
         2               2 19-NOV-2020 08:17:17             108 SEAN               328B7C87FCACBD2E79C4
         2               3 19-NOV-2020 08:17:17             108 SEAN               CBE8AED456DB259A648B
         2               4 19-NOV-2020 08:17:17             108 SEAN               6C5B05DC13DFD316FE9C
         2               5 19-NOV-2020 09:41:54             109 MARY               7392DCA4C6320AA1E3D8
         2               6 19-NOV-2020 09:41:54             109 MARY               82AC05BCCDC0716D7B39

6 rows selected.

 

6. Validating the Hash chain

For our final verification we are going to validate the hash chain to ensure that it has not been tampered with. To perform this operation we will use the DBMS_CRYPTO package from the Oracle database. So our first step is to grant execute privilege to the Oracle DBMS_CRYPTO package to our users: Sean and Mary. This operation could very well be performed by someone completely different like an external auditor, in which case you would need to only grant that person the privilege to execute the package.


SQL> grant execute on sys.dbms_crypto to SEAN ;
SQL> grant execute on sys.dbms_crypto to MARY ;

SQL> DECLARE
  2    row_data BLOB;
  3    row_id ROWID;
  4    row_hash RAW(64);
  5    computed_hash RAW(64);
  6    buffer RAW(4000);
  7    inst_id BINARY_INTEGER;
  8    chain_id BINARY_INTEGER;
  9    sequence_no BINARY_INTEGER;
  10 BEGIN
  11   -- Get the row details and hash value of the most recently inserted row with the specified instance ID and chain ID
  12    select max(ORABCTAB_SEQ_NUM$)
  13    into sequence_no
  14    from SEAN.I_OWE_YOU
  15    where ORABCTAB_INST_ID$=1 AND ORABCTAB_CHAIN_ID$=2;
  16    select ORABCTAB_INST_ID$, ORABCTAB_CHAIN_ID$, ORABCTAB_SEQ_NUM$, ORABCTAB_HASH$
  17    into inst_id, chain_id, sequence_no, row_hash
  18    from SEAN.I_OWE_YOU
  19    where ORABCTAB_INST_ID$=1
  20    and ORABCTAB_CHAIN_ID$=2
  21    and ORABCTAB_SEQ_NUM$ = sequence_no;
  22   -- Compute the row hash externally from row column bytes
  23    DBMS_BLOCKCHAIN_TABLE.GET_BYTES_FOR_ROW_HASH('SEAN', 'I_OWE_YOU', inst_id, chain_id, sequence_no, 1, row_data);
  24    computed_hash := DBMS_CRYPTO.HASH(row_data, DBMS_CRYPTO.HASH_SH512);
  25   -- Verify that the row's hash and externally computed hash are same
  26    if UTL_RAW.COMPARE(row_hash, computed_hash) = 0 THEN
  27       DBMS_OUTPUT.PUT_LINE('Hash verification successful');
  28    else
  29       DBMS_OUTPUT.PUT_LINE('Hash verification failed');
  30    END IF;
  31 END;
  32 /

  Hash verification successful

  PL/SQL procedure successfully completed.

NOTE: In our example the ORABCTAB_INST_ID$ value is redundant as this is a single instance database so will always be “1” and the ORABCTAB_CHAIN_ID$ “2” was taken from our previous SQL query

This code was taken from the Oracle Online documentation: https://docs.oracle.com/en/database/oracle/oracle-database/20/admin/managing-tables.html#GUID-6B64C395-434D-4C6C-BBCE-D5157858EEE2

Part 3: A few days later, Deleting rows from the table


Stay tuned for screenshots…. they’re on their way.

Exit mobile version