Infrastructure at your Service

Oracle Blockchain Tables

With Oracle Database 20c/21c the new feature Oracle Blockchain Tables has been introduced.

Blockchain Tables enable Oracle Database users to create tamper-resistant data management without distributing a ledger across multiple parties.

Database security can be improved by using Blockchain Tables to avoid user fraud and administrator fraud as well.

One of the main characteristics of Oracle Blockchain Tables is that you can only append data. Table rows are chained using a cryptographic hashing approach.

In addition, to avoid administrator or identity fraud, rows can optionally be signed with PKI (public key infrastructure) based on the user’s private key.

Use cases can be a centralized storage of compliance data, audit trail or clinical trial.

Let’s have a look how it works.

Creating an Oracle Blockchain Table:
Quite easy, I’ve used Oracle Database 20.3


select version_full from v$instance;
VERSION_FULL     
-----------------
20.3.0.0.0

CREATE BLOCKCHAIN TABLE bank_ledger (bank VARCHAR2(128), deposit_date DATE, deposit_amount NUMBER)
         NO DROP UNTIL 31 DAYS IDLE
         NO DELETE LOCKED
         HASHING USING "SHA2_512" VERSION "v1";
Error report -
ORA-05729: blockchain table cannot be created in root container

select name, pdb from v$services;

alter session set container = pdb1;

CREATE BLOCKCHAIN TABLE bank_ledger (bank VARCHAR2(128), deposit_date DATE, deposit_amount NUMBER)
         NO DROP UNTIL 31 DAYS IDLE
         NO DELETE LOCKED
         HASHING USING "SHA2_512" VERSION "v1";
Blockchain TABLE created.

Changing retention period on Blockchain Tables:
The table was created with a retention time of “31 DAYS IDLE”, can we reset that value?


ALTER TABLE bank_ledger NO DROP UNTIL 16 DAYS IDLE; 
Error report - 
ORA-05732: retention value cannot be lowered 

ALTER TABLE bank_ledger NO DROP UNTIL 42 days idle; 
Table BANK_LEDGER altered.

Appending Data in Oracle Blockchain Tables:
That’s working fine.


SELECT user_name, distinguished_name, 
          UTL_RAW.LENGTH(certificate_guid) CERT_GUID_LEN, 
          DBMS_LOB.GETLENGTH(certificate) CERT_LEN 
          FROM DBA_CERTIFICATES ORDER BY user_name; 
no rows selected
 
desc bank_ledger 
Name Null? Type 
------------------------------------ 
BANK VARCHAR2(128) 
DEPOSIT_DATE 
DATE 
DEPOSIT_AMOUNT NUMBER 

select * from bank_ledger; 
no rows selected
... 
1 row inserted. 
1 row inserted. 
1 row inserted.

BANK             DEPOSIT_           DEPOSIT_AMOUNT 
-------------------------------------------------- 
UBS              01.01.20           444000000 
Credit Suisse    02.02.20           22000000 
Vontobel         03.03.20           1000000

DML and DDL on Oracle Blockchain Tables:
Let’s try to change some data.


update bank_ledger set deposit_amount=10000 where bank like 'UBS';
Error starting at line : 1 in command -
update bank_ledger set deposit_amount=10000 where bank like 'UBS'
Error at Command Line : 1 Column : 8
Error report -
SQL Error: ORA-05715: operation not allowed on the blockchain table

delete from bank_ledger where bank like 'UBS';
Error starting at line : 1 in command -
delete from bank_ledger where bank like 'UBS'
Error at Command Line : 1 Column : 13
Error report -
SQL Error: ORA-05715: operation not allowed on the blockchain table

drop table bank_ledger;
Error starting at line : 1 in command -
drop table bank_ledger
Error report -
ORA-05723: drop blockchain table BANK_LEDGER not allowed

Copying data from an Oracle Blockchain Table:
Ok, we can’t change data in the original table, let’s try to copy it.


create tablespace bank_data;
Tablespace BANK_DATA created.

CREATE BLOCKCHAIN TABLE bad_bank_ledger (bank VARCHAR2(128), deposit_date DATE, deposit_amount NUMBER) 
         NO DROP UNTIL 31 DAYS IDLE
         NO DELETE LOCKED
         HASHING USING "SHA2_512" VERSION "v1"
         tablespace bank_data;
Blockchain TABLE created.

insert into bad_bank_ledger select * from bank_ledger;
Error starting at line : 1 in command -
insert into bad_bank_ledger select * from bank_ledger
Error at Command Line : 1 Column : 13
Error report -
SQL Error: ORA-05715: operation not allowed on the blockchain table

Alternative actions on Oracle Blockchain Tables:
Can we move tablespaces or try to replace tables?


insert into bad_bank_ledger values ('Vader', '09-09-2099', '999999999');
insert into bad_bank_ledger values ('Blofeld', '07-07-1977', '7777777');
insert into bad_bank_ledger values ('Lecter', '08-08-1988', '888888');

1 row inserted.
1 row inserted.
1 row inserted.

select * from bad_bank_ledger;
BANK                                   DEPOSIT_ DEPOSIT_AMOUNT
----------------------------------------------- --------------
Vader                                  09.09.99      999999999
Blofeld                                07.07.77        7777777
Lecter                                 08.08.88         888888

create table new_bad_bank_ledger as select * from bad_bank_ledger;
Table NEW_BAD_BANK_LEDGER created.

update new_bad_bank_ledger set deposit_amount = 666666 where bank like 'Blofeld';
1 row updated.
commit;
commit complete.

select * from new_bad_bank_ledger;
BANK                                   DEPOSIT_ DEPOSIT_AMOUNT
----------------------------------------------- --------------
Vader                                  09.09.99      999999999
Blofeld                                07.07.77         666666
Lecter                                 08.08.88         888888

drop table bad_bank_ledger;
Error starting at line : 1 in command -
drop table bad_bank_ledger
Error report -
ORA-05723: drop blockchain table BAD_BANK_LEDGER not allowed

drop tablespace bank_data INCLUDING CONTENTS and datafiles;
Error starting at line : 1 in command -
drop tablespace bank_data INCLUDING CONTENTS and datafiles
Error report -
ORA-00604: error occurred at recursive SQL level 1
ORA-05723: drop blockchain table BAD_BANK_LEDGER not allowed
00604. 00000 -  "error occurred at recursive SQL level %s"
*Cause:    An error occurred while processing a recursive SQL statement
           (a statement applying to internal dictionary tables).
*Action:   If the situation described in the next error on the stack
           can be corrected, do so; otherwise contact Oracle Support.

Move or Compress on Oracle Blockchain Table:
Table operations are forbidden in either case.


alter table bank_ledger move tablespace bank_data COMPRESS;
Error starting at line : 1 in command -
alter table bank_ledger move tablespace bank_data COMPRESS
Error report -
ORA-05715: operation not allowed on the blockchain table

alter table bank_ledger move tablespace bank_data;
Error starting at line : 1 in command -
alter table bank_ledger move tablespace bank_data
Error report -
ORA-05715: operation not allowed on the blockchain table

Hidden Columns in Oracle Blockchain Tables:
Every row is identified by hidden attributes.


col table_name for a40
set lin 999
set pages 100

SELECT * FROM user_blockchain_tables;
desc bank_ledger
SELECT column_name, hidden_column FROM user_tab_cols WHERE table_name='BANK_LEDGER';

TABLE_NAME                         ROW_RETENTION ROW TABLE_INACTIVITY_RETENTION HASH_ALG
------------------------------------------------ --- -------------------------- --------
BANK_LEDGER                                  YES                             42 SHA2_512
BAD_BANK_LEDGER                              YES                             31 SHA2_512

Name           Null? Type          
-------------- ----- ------------- 
BANK                 VARCHAR2(128) 
DEPOSIT_DATE         DATE          
DEPOSIT_AMOUNT       NUMBER        

COLUMN_NAME                            HID
-------------------------------------- ---
ORABCTAB_SIGNATURE$                    YES
ORABCTAB_SIGNATURE_ALG$                YES
ORABCTAB_SIGNATURE_CERT$               YES
ORABCTAB_SPARE$                        YES
BANK                                   NO 
DEPOSIT_DATE                           NO 
DEPOSIT_AMOUNT                         NO 
ORABCTAB_INST_ID$                      YES
ORABCTAB_CHAIN_ID$                     YES
ORABCTAB_SEQ_NUM$                      YES
ORABCTAB_CREATION_TIME$                YES
ORABCTAB_USER_NUMBER$                  YES
ORABCTAB_HASH$                         YES
13 rows selected. 

set colinvisible on
desc bank_ledger
Name                                 Null? Type                        
------------------------------------ ----- --------------------------- 
BANK                                       VARCHAR2(128)               
DEPOSIT_DATE                               DATE                        
DEPOSIT_AMOUNT                             NUMBER                      
ORABCTAB_SPARE$ (INVISIBLE)                RAW(2000 BYTE)              
ORABCTAB_SIGNATURE_ALG$ (INVISIBLE)        NUMBER                      
ORABCTAB_SIGNATURE$ (INVISIBLE)            RAW(2000 BYTE)              
ORABCTAB_HASH$ (INVISIBLE)                 RAW(2000 BYTE)              
ORABCTAB_SIGNATURE_CERT$ (INVISIBLE)       RAW(16 BYTE)                
ORABCTAB_CHAIN_ID$ (INVISIBLE)             NUMBER                      
ORABCTAB_SEQ_NUM$ (INVISIBLE)              NUMBER                      
ORABCTAB_CREATION_TIME$ (INVISIBLE)        TIMESTAMP(6) WITH TIME ZONE 
ORABCTAB_USER_NUMBER$ (INVISIBLE)          NUMBER                      
ORABCTAB_INST_ID$ (INVISIBLE)              NUMBER    

set lin 999
set pages 100
col bank for a40

select bank, deposit_date, orabctab_creation_time$ from bank_ledger;
BANK                                     DEPOSIT_ ORABCTAB_CREATION_TIME$        
---------------------------------------- -------- -------------------------------
UBS                                      01.01.20 25.09.20 13:17:03.946615000 GMT
Credit Suisse                            02.02.20 25.09.20 13:17:03.951545000 GMT
Vontobel                                 03.03.20 25.09.20 13:17:03.952064000 GMT

We see that it is not possible to modify an Oracle Blockchain Table on database level. To avoid manipulations from users with root access there are several possibilities to protect data, e.g. by transferring cryptographic hashes and user signatures systematically to external vaults which would enable you to recover data against the most disaster scenarios.

Resources:

https://www.oracle.com/blockchain/#blockchain-platform-tab

https://docs.oracle.com/en/cloud/paas/blockchain-cloud/user/create-rich-history-database.html#GUID-266145A1-EF3A-4917-B174-C50D4DB1A0E3

https://docs.oracle.com/en/database/oracle/oracle-database/21/nfcon/details-oracle-blockchain-table-282449857.html

https://docs.oracle.com/en/database/oracle/oracle-database/21/admin/managing-tables.html#GUID-43470B0C-DE4A-4640-9278-B066901C3926

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Christian Weinfurtner
Christian Weinfurtner

Consultant