Immutable tables and blockchain tables hands-on

Preamble

Immutable tables and blockchain tables are 21c new features that have been backported to 19c with those Release Update (RU):

Immutable Tables 19.11
Blockchain Tables 19.10

Immutable table and blockchain tables are free in all database editions. Even if through the game of Release Update (RU) Oracle has made blockchain tables available before immutable tables, blockchain tables is an extension of immutable table.

From official documentation:

Immutable tables are read-only tables that prevent unauthorized data modifications by insiders and accidental data modifications resulting from human errors.

Blockchain tables are insert-only tables that organize rows into a number of chains. Each row in a chain, except the first row, is chained to the previous row in the chain by using a cryptographic hash.

Blockchain tables are a step further from immutable tables to prevent unauthorized table modifications from stolen credentials as well as modifications from outside the database and/or using security breaches not yet patched. The subject that obviously comes to mind with blockchain tables is cryptographic money like bitcoin where each digital money transaction is stored in a blockchain table to prevent modification by an attacker of past transactions.

In my opinion the features are not yet 100% mature in 19c, even with 19.12, as it is quite easy to generate an ORA-00600 when playing with them.

My testing has been done on a 19c (19.12) Enterprise Edition release running on bare metal server running RHEL 7.9 with 12 cores and 64GB of memory.

Immutable tables

To use them in your 19.12 (pluggable) database you need to change the COMPATIBLE parameter:

ORA-00406: COMPATIBLE parameter needs TO be 19.11.0.0.0 OR greater
ORA-00722: Feature "Immutable table"

This is funny because in the COMPATIBLE official documentation Oracle says:

The value of the COMPATIBLE parameter should not be changed for a Release Update (RU) or Release Update Revision (RUR). For example, assume you are running Oracle Database 19c and the value of COMPATIBLE is 19.0.0. You then apply Oracle Database Release Update 19.6.0.0.0. Do not set the value of COMPATIBLE to 19.6.0; leave it set to 19.0.0.

So changed on my CDB and bounced the instance:

SQL> ALTER SYSTEM SET compatible='19.12.0' scope=spfile;
 
SYSTEM altered.

You create an immutable table with below command. The minimum number of days after which a row can be deleted after its insertion is 16 days:

SQL> CREATE immutable TABLE test01(id NUMBER, descr VARCHAR2(20)) no DROP until 1 days idle no DELETE until 16 days after INSERT;
 
TABLE created.
 
SQL> SELECT row_retention, row_retention_locked, table_inactivity_retention FROM dba_immutable_tables WHERE table_name = 'TEST01';
 
ROW_RETENTION ROW TABLE_INACTIVITY_RETENTION
------------- --- --------------------------
           16 NO                           1

Be very carreful with the parameters you use as below command is creating an immutable table where rows can never be deleted (really ?) and that can never be dropped (at least I’m still searching how I could):

SQL> CREATE immutable TABLE test01(id NUMBER, descr VARCHAR2(20)) no DROP no DELETE;
 
TABLE created.
 
SQL> col table_name FOR a10
SQL> SELECT table_name, row_retention, row_retention_locked, table_inactivity_retention FROM user_immutable_tables;
 
TABLE_NAME ROW_RETENTION ROW TABLE_INACTIVITY_RETENTION
---------- ------------- --- --------------------------
TEST01                   NO

The drop period inactivity cannot be decreased:

SQL> ALTER TABLE test01 no DROP until 0 days idle;
ALTER TABLE test01 no DROP until 0 days idle
*
ERROR AT line 1:
ORA-05732: retention VALUE cannot be lowered

The period after which rows can be deleted can also not be decreased:

SQL> ALTER TABLE test01 no DELETE until 17 days after INSERT;
 
TABLE altered.
 
SQL> ALTER TABLE test01 no DELETE until 16 days after INSERT;
ALTER TABLE test01 no DELETE until 16 days after INSERT
*
ERROR AT line 1:
ORA-05732: retention VALUE cannot be lowered

You can also lock the row retention period with:

SQL> ALTER TABLE test01 no DELETE until 17 days after INSERT locked;
 
TABLE altered.
 
SQL> SELECT table_name, row_retention, row_retention_locked, table_inactivity_retention FROM user_immutable_tables;
 
TABLE_NAME ROW_RETENTION ROW TABLE_INACTIVITY_RETENTION
---------- ------------- --- --------------------------
TEST01                17 YES                          1
 
SQL> ALTER TABLE test01 no DELETE until 18 days after INSERT;
ALTER TABLE test01 no DELETE until 18 days after INSERT
*
ERROR AT line 1:
ORA-05731: blockchain OR immutable TABLE TEST01 cannot be altered

For testing as expected:

SQL> INSERT INTO test01 VALUES(1, 'One');
 
1 ROW created.
 
SQL> COMMIT;
 
COMMIT complete.
 
SQL> SELECT * FROM test01;
 
        ID DESCR
---------- --------------------
         1 One
 
SQL> SET lines 200
SQL> col orabctab_creation_time$ FOR a40
SQL> SELECT orabctab_creation_time$, orabctab_user_number$, a.* FROM test01 a;
 
ORABCTAB_CREATION_TIME$                  ORABCTAB_USER_NUMBER$         ID DESCR
---------------------------------------- --------------------- ---------- --------------------
20-OCT-21 10.17.40.761213 AM +00:00                        116          1 One
 
SQL> DELETE FROM test01 WHERE id=1;
DELETE FROM test01 WHERE id=1
            *
ERROR AT line 1:
ORA-05715: operation NOT allowed ON the blockchain OR immutable TABLE

Last but not least you have the DBMS_IMMUTABLE_TABLE package to automatically delete candidates rows that are older than a given date:

SET serveroutput ON SIZE 9999
DECLARE
  nb_rows NUMBER;
BEGIN
  dbms_immutable_table.delete_expired_rows(schema_name => 'TEST01', table_name => 'TEST01', before_timestamp => systimestamp-1, number_of_rows_deleted => nb_rows);
  dbms_output.put_line('Number of rows deleted: ' || nb_rows);
END;
/
 
NUMBER OF ROWS deleted: 0
 
PL/SQL PROCEDURE successfully completed.

Blockchain tables

Basic testing

The syntax to create a blockchain table is very similar to immutable tables, except that there is a hashing algorithm that is apparently not (yet) possible to change:

SQL> CREATE blockchain TABLE TRANSACTION(source_iban VARCHAR2(32), target_iban VARCHAR2(32), deposit_date TIMESTAMP, deposit_amount NUMBER)
no DROP until 0 days idle
no DELETE until 16 days after INSERT
hashing USING "sha2_512" version "v1";
 
TABLE created.
 
SQL> SET lines 200
SQL> col table_name FOR a15
SQL> SELECT * FROM user_blockchain_tables;
 
TABLE_NAME      ROW_RETENTION ROW TABLE_INACTIVITY_RETENTION HASH_ALG
--------------- ------------- --- -------------------------- --------
TRANSACTION                16 NO                           0 SHA2_512

The table has more than 10 hidden columns on top of the one you have declared. Those columns contain, for example, hash of the row, sequence of the row in the chain, signature of the row and so on…

Inserting a row is as transparent as for a normal table:

SQL> INSERT INTO TRANSACTION VALUES('FR7630001007941234567890185', 'FR1420041010050500013M02606', SYSTIMESTAMP, 100);
 
1 ROW created.
 
SQL> COMMIT;
 
COMMIT complete.

If you try to select few of the available hidden columns. The hash column is one of the blockchain tables key column. For now the signature column is empty because I have not yet signed my insertions:

SQL> SET lines 200
SQL> col orabctab_hash$ FOR a20
SQL> col orabctab_signature$ FOR a20
SQL> SELECT orabctab_inst_id$,orabctab_chain_id$,orabctab_seq_num$,orabctab_hash$,orabctab_signature$,orabctab_creation_time$ FROM TRANSACTION;
 
ORABCTAB_INST_ID$ ORABCTAB_CHAIN_ID$ ORABCTAB_SEQ_NUM$ ORABCTAB_HASH$       ORABCTAB_SIGNATURE$  ORABCTAB_CREATION_TIME$
----------------- ------------------ ----------------- -------------------- -------------------- ----------------------------------------
                1                 16                 1 87EECD3739AF7DA12A02                      20-OCT-21 04.41.11.120982 PM +00:00
                                                       76A3ED85707E5463B249
                                                       0470515B30AD4EC00FCD
                                                       259A4EDA7A66763E8C23
                                                       2F436481F47098C4CF63
                                                       B294FDB95746D37DD171
                                                       C9A298CF

If I insert new rows, the tuple made of ORABCTAB_INST_ID$, ORABCTAB_CHAIN_ID$ and ORABCTAB_SEQ_NUM$ uniquely identifies a row in the blockchain table:

SQL> INSERT INTO TRANSACTION VALUES('FR3214508000703188446194H85','FR3930003000704342572143H02', SYSTIMESTAMP, 150);
 
1 ROW created.
 
SQL> INSERT INTO TRANSACTION VALUES('FR9017569000404619456186I04','FR4912739000306676995962C37', SYSTIMESTAMP, 250);
 
1 ROW created.
 
SQL> COMMIT;
 
COMMIT complete.
 
SQL> SET lines 200
SQL> SELECT orabctab_inst_id$,orabctab_chain_id$,orabctab_seq_num$ FROM TRANSACTION;
 
ORABCTAB_INST_ID$ ORABCTAB_CHAIN_ID$ ORABCTAB_SEQ_NUM$
----------------- ------------------ -----------------
                1                 16                 1
                1                 21                 1
                1                 21                 2

Remark:
The chain id is apparently evolving as time pass.

All the rows are chained as in below schema:

Obviously, by design, you cannot alter the content of the table and you have the same properties as for immutable tables when you try to alter or change the retention period:

SQL> UPDATE TRANSACTION SET deposit_amount=200 WHERE source_iban='FR3214508000703188446194H85';
UPDATE TRANSACTION SET deposit_amount=200 WHERE source_iban='FR3214508000703188446194H85'
       *
ERROR AT line 1:
ORA-05715: operation NOT allowed ON the blockchain OR immutable TABLE

The DBMS_BLOCKCHAIN_TABLE has procedures to verify the itegrity of your blockchain table, for exmaple:

SQL> SET serveroutput ON SIZE 9999
SQL>
DECLARE
  verify_rows NUMBER;
BEGIN
  DBMS_BLOCKCHAIN_TABLE.VERIFY_ROWS('test01','transaction', NULL, NULL, NULL, NULL, verify_rows, FALSE);
  DBMS_OUTPUT.PUT_LINE('Number of rows verified in table: ' || verify_rows);
END;
/
 
NUMBER OF ROWS verified IN TABLE: 3
 
PL/SQL PROCEDURE successfully completed.

Signing rows

In case a user credential got stolen you can also sign the inserted rows in blockchain tables using client private key that is never shared to the database. As such even if a thief try to insert rows in your blockchain table using a comprise user account he will lack the private client certificate and will not be able to sign rows. The Oracle official documentation is really not good at all and lots of steps are not well explained. I have finally succeeded to do sign rows using even blog post not written in English (so that I cannot share) where I have been able at least to understand the code.

Let’s start by creating the root CA certificate, obviously with openSSL:

[oracle@server01 ~]$ mkdir certs
[oracle@server01 ~]$ cd certs
[oracle@server01 certs]$ openssl genrsa -out MyRootCA.key 4096
Generating RSA private key, 4096 bit long modulus
.....++
.....................................++
e is 65537 (0x10001)
[oracle@server01 certs]$ openssl req -x509 -new -nodes -key MyRootCA.key -sha512 -days 1024 -out MyRootCA.pem
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [XX]:CH
State or Province Name (full name) []:Geneva
Locality Name (eg, city) [Default City]:Geneva
Organization Name (eg, company) [Default Company Ltd]:MyCompany
Organizational Unit Name (eg, section) []:DIT
Common Name (eg, your name or your server's hostname) []:MyServer
Email Address []:yannick.jaquier@gmail.com

Create a client certificate signed by our freshly generated root CA:

[oracle@server01 certs]$ openssl genrsa -out MyClient.key 4096
Generating RSA private key, 4096 bit long modulus
..........................++
.............................++
e is 65537 (0x10001)
[oracle@server01 certs]$ openssl req -new -key MyClient.key -out MyClient.csr
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [XX]:CH
State or Province Name (full name) []:Geneva
Locality Name (eg, city) [Default City]:Geneva
Organization Name (eg, company) [Default Company Ltd]:MyCompany
Organizational Unit Name (eg, section) []:DIT
Common Name (eg, your name or your server's hostname) []:MyServer
Email Address []:yannick.jaquier@gmail.com
 
Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:
[oracle@server01 certs]$ openssl x509 -req -in MyClient.csr -CA MyRootCA.pem -CAkey MyRootCA.key -CAcreateserial -out MyClient.pem -days 365 -sha512
Signature ok
subject=/C=CH/ST=Geneva/L=Geneva/O=MyCompany/OU=DIT/CN=MyServer/emailAddress=yannick.jaquier@gmail.com
Getting CA Private Key

So we now get:

[oracle@server01 certs]$ ll
total 24
-rw-r----- 1 oracle dba 1752 Oct 28 15:19 MyClient.csr
-rw-r----- 1 oracle dba 3243 Oct 28 15:18 MyClient.key
-rw-r----- 1 oracle dba 2004 Oct 28 15:19 MyClient.pem
-rw-r----- 1 oracle dba 3243 Oct 28 15:16 MyRootCA.key
-rw-r----- 1 oracle dba 2122 Oct 28 15:17 MyRootCA.pem
-rw-r----- 1 oracle dba   17 Oct 28 15:19 MyRootCA.srl

Load the public certificate inside the database with (the write privilege on directory is to write row signature file), you need to note down the certificate id you will later use:

SQL> CREATE OR REPLACE DIRECTORY certs AS '/home/oracle/certs';
 
DIRECTORY created.
 
SQL> GRANT read, WRITE ON DIRECTORY certs TO test01;
 
GRANT succeeded.
 
SQL> SET serveroutput ON SIZE 9999
SQL>
DECLARE
  FILE       BFILE;
	buffer     BLOB;
	amount     NUMBER := 32767;
	cert_id  RAW(16);
BEGIN
	FILE := BFILENAME('CERTS', 'MyClient.pem');
	DBMS_LOB.FILEOPEN(FILE);
	DBMS_LOB.READ(FILE, amount, 1, buffer);
	DBMS_LOB.FILECLOSE(FILE);
	DBMS_USER_CERTS.ADD_CERTIFICATE(buffer, cert_id);
	DBMS_OUTPUT.PUT_LINE('Certificate ID = ' || cert_id);
END;
/
 
Certificate ID = 38AE484868150D34451AFA8B99E4871E
 
PL/SQL PROCEDURE successfully completed.
 
SQL> SET lines 200
SQL> col user_name FOR a10
SQL> col distinguished_name FOR a50
SQL> SELECT user_name, distinguished_name, UTL_RAW.LENGTH(certificate_id) cert_id_len, DBMS_LOB.GETLENGTH(certificate) cert_len FROM user_certificates;
 
USER_NAME  DISTINGUISHED_NAME                                 CERT_ID_LEN   CERT_LEN
---------- -------------------------------------------------- ----------- ----------
TEST01     EMAIL=yannick.jaquier@gmail.com,CN=MyServer,OU=DIT          16       2004
           ,O=MyCompany,L=Geneva,ST=Geneva,C=CH
 
SQL> SELECT certificate_id FROM user_certificates;
 
CERTIFICATE_ID
--------------------------------
38AE484868150D34451AFA8B99E4871E

Drop it with:

SQL> EXEC DBMS_USER_CERTS.DROP_CERTIFICATE('38AE484868150D34451AFA8B99E4871E');
 
PL/SQL PROCEDURE successfully completed.

Generate a signature file (signature.dat) for one row of your blockchain table:

SQL>
DECLARE
  row_data        BLOB;
  buffer          RAW(4000);
  inst_id         BINARY_INTEGER;
  chain_id        BINARY_INTEGER;
  sequence_no     BINARY_INTEGER;
  row_len         BINARY_INTEGER;
  l_file          UTL_FILE.FILE_TYPE;
BEGIN
  SELECT ORABCTAB_INST_ID$, ORABCTAB_CHAIN_ID$, ORABCTAB_SEQ_NUM$ INTO inst_id, chain_id, sequence_no FROM transaction
  WHERE ORABCTAB_INST_ID$=1 and ORABCTAB_CHAIN_ID$=16 and ORABCTAB_SEQ_NUM$=1;
  DBMS_BLOCKCHAIN_TABLE.GET_BYTES_FOR_ROW_SIGNATURE('test01','transaction', inst_id, chain_id, sequence_no, 1, row_data);
  row_len := DBMS_LOB.GETLENGTH(row_data);
  DBMS_LOB.READ(row_data, row_len, 1, buffer);
  l_file := UTL_FILE.fopen('CERTS','signature.dat','wb', 32767);
  UTL_FILE.put_raw(l_file, buffer, TRUE);
  UTL_FILE.fclose(l_file);
END;
/
 
PL/SQL procedure successfully completed.

Now I have this signature row file in the CERTS directory that I must sign with my client certificate using openSSL. For the sake of testing I have my client private key (MyClient.key) in same directory as my other certificates but clearly those private client certificates must not be on the database server and kept in a safe place:

[oracle@server01 certs]$ ll signature.dat
-rw-r----- 1 oracle dba 64 Oct 28 15:25 signature.dat
[oracle@server01 certs]$ openssl dgst -sha512 -sign MyClient.key -out row_signature.dat signature.dat

Finally sign the row with this row signature file (do not forget the commit as the PL/SQL procedure does not do it, wasted a bit of time on this):

SQL>
DECLARE
  inst_id BINARY_INTEGER;
  chain_id BINARY_INTEGER;
  sequence_no BINARY_INTEGER;
  FILE BFILE;
  amount NUMBER := 2000;
  signature RAW(2000);
  cert_id RAW (16) := HEXTORAW('38AE484868150D34451AFA8B99E4871E');
BEGIN
  SELECT ORABCTAB_INST_ID$, ORABCTAB_CHAIN_ID$, ORABCTAB_SEQ_NUM$ INTO inst_id, chain_id, sequence_no FROM TRANSACTION
  WHERE ORABCTAB_INST_ID$=1 AND ORABCTAB_CHAIN_ID$=16 AND ORABCTAB_SEQ_NUM$=1;
  FILE := BFILENAME('CERTS', 'row_signature.dat');
  DBMS_LOB.FILEOPEN(FILE);
  dbms_lob.READ(FILE, amount, 1, signature);
  dbms_lob.FILECLOSE(FILE);
  DBMS_BLOCKCHAIN_TABLE.SIGN_ROW('test01','transaction', inst_id, chain_id, sequence_no, NULL, signature, cert_id, DBMS_BLOCKCHAIN_TABLE.SIGN_ALGO_RSA_SHA2_512);
END;
/
 
PL/SQL PROCEDURE successfully completed.
 
SQL> COMMIT;
 
COMMIT complete.

Now if you check the ORABCTAB_SIGNATURE$ hidden columns of your blockchain table you will see that the row has been signed:

SQL> SET lines 200
SQL> SET pages 1000
SQL> SELECT orabctab_inst_id$, orabctab_chain_id$, orabctab_seq_num$, orabctab_signature$ FROM TRANSACTION;
 
ORABCTAB_INST_ID$ ORABCTAB_CHAIN_ID$ ORABCTAB_SEQ_NUM$ ORABCTAB_SIGNATURE$
----------------- ------------------ ----------------- ------------------------------
                1                 16                 1 AB2BA40938A89152F24A753DE5A925
                                                       C16263403C2B1F0675D9D2767A3AEB
                                                       27078473F96CAD89D47D33670B2038
                                                       7A0BDB2144A33FEF1F7E2BDDACE352
                                                       5DD4E5CEF44E20C5656A7DE3647D88
                                                       58020801A5C32F14ED1E306CDCC672
                                                       78567152E46772B10F1693F48E7C48
                                                       8BEF2BA7EBAF6D9F71CF07E1031B0D
                                                       1038D4E4D98150F
 
                1                 21                 1
                1                 21                 2

IT World

Immutable tables and blockchain tables hands-on

Table of contents

Preamble

Immutable tables

Blockchain tables

Basic testing

Signing rows

References

About Post Author

Yannick Jaquier

Related Posts:

Leave a Reply Cancel Reply