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> <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.