Table of contents
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 |
References
- Why Oracle Implement Blockchain in the Database
- Native Blockchain Tables Extend Oracle Database’s Multi-model Converged Architecture
- Oracle Blockchain Blog
- Random Iban Generator
- Technical Tip: How to generate certificates using OpenSSL
