Blockchain Tables in Oracle Database 19c: Time Travel and Wormholes Part 1

Blockchain tables, now available in Oracle database 19c with a later RU and a higher COMPATIBLE parameter value, can act as an electronic ledger and verify that a series of transactions happened in a specific time period and prevent deletions or modifications to the table. The syntax for creating a blockchain table includes options for when the table can be dropped, how soon previous transactions can be deleted, and what encryption algorithm is used on each row of the table. However, with certain row retention options there can be premature and undetectable data loss in the table.

A Vulnerability Path

My discovery about a potential weakness in blockchain table management started when I was trying the various syntax options. Here’s the DDL and some INSERTs to the blockchain table:

create BLOCKCHAIN table bt_descriptions
   (id           number,
    description  varchar2(100))
no drop until 10 days idle
no delete until 16 days after insert
hashing using SHA2_512 version v1;

insert into bt_descriptions values(1,'This is row #1');
insert into bt_descriptions values(2,'This is row #2');
insert into bt_descriptions values(3,'This is row #3');
commit;

16 days was the minimum value allowed for INSERT retention. In addition, I couldn’t drop the table for at least 10 days. Not surprisingly, I got this error message when I tried to delete one of the rows:

I also realized that I had to instead use the DELETE_EXPIRED_ROWS procedure in the DBMS_BLOCKCHAIN_TABLE package to remove expired rows. Which would not expire for 16 more days!

No Time Like the Present

What other options do I have to remove those rows from the table? I could drop and recreate the database, but that’s a bit drastic. There might be other important tables in the database. So far, it appeared that blockchain tables worked as advertised.

Time Tunnel

In this case, I realized that there is an inherent weakness in blockchain tables when you use the NO DELETE UNTIL clause. What if I advanced the system clock at the OS level more than 16 days? It’s not implausible that an unscrupulous system administrator would help out an unscrupulous database administrator with this plan, so I tried this out. After I shut down the database, I checked the system clock:

[root@ol7db19c ~]# date
Sat Sep 30 21:28:59 CDT 2023

After disabling the “chron” services, and setting the system date and time, it now looked like it was over 20 days later:

[oracle@ol7db19c ~]$ date
Sat Oct 21 20:51:00 CDT 2023

What about the database? It started up fine. And using Oracle’s blockchain API, I was able to delete the “expired” rows:

Back to the Future

Now that the rows were gone, my nefarious plot would not be complete until I set the operating system’s clock back to “today”. After shutting down the database, I re-enable the “chron” services and set the clock back to “now”:

[oracle@ol7db19c ~]$ date
Sun Oct  1 21:19:43 CDT 2023

After the database started up again, the moment of truth had arrived. Was the blockchain table empty, corrupted, or something else?

It was a roundabout method, but fortunately or unfortunately it worked!

What About the Alert Log?

The database alert log might have some clues as to the changes to the clock, and it did, but there were no alerts or warnings regarding the sudden time shifts.

After setting the clock back to current:

Lessons Learned

Security and auditing at the OS level would make this hard to implement in a true production environment.

From the database perspective, I could prevent these “early” deletes by making one change to the CREATE BLOCKCHAIN TABLE statement and change this clause:

no delete until 16 days after insert

to this:

no delete

In other words, rows in the table cannot be deleted unless the entire database is dropped. The same caveats apply to the no drop until 10 days idle clause — if I set that to just no drop instead, I’ll get this error message when trying to drop the table:

Error report -
ORA-05723: drop blockchain or immutable table BT_DESCRIPTIONS not allowed
05723. 0000 -  "drop blockchain or immutable table %s not allowed"
*Cause:    A blockchain or immutable table was created without the drop option.
*Action:   No action required.

What about the just-released Oracle Database 23c? Are there any new blockchain table features? And I fully expect an Exadata angle to blockchain tables. Stay tuned for Part 2!