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:
![Thumbnail [60x60]](https://www.dbi-services.com/blog/wp-content/uploads/2022/10/CWE_web-min-scaled.jpg)
![Thumbnail [90x90]](https://www.dbi-services.com/blog/wp-content/uploads/2022/08/DWE_web-min-scaled.jpg)
![Thumbnail [90x90]](https://www.dbi-services.com/blog/wp-content/uploads/2022/08/JDU_web-min-scaled.jpg)