Blogs

Block Chain & Immutable Tables

In this ever changing database world,  its good to have few things which are constant, tamper proof and can validate the authenticity of an original transaction. This is where “Blockchain” tables and “Immutable” tables come to the fore.

They provide a resilient infrastructure which allows to store the transactions which cannot be modified or deleted hence keeping a trail of everything that spawned from the first insert into that table.

 

Some of the common use cases are  :

1. Storing compliance data
2. Accounting / ledger tables in financial instruments
3. Audit information
4. Payments/ funds transfer/ Digital currency maintenance.

and a lot more.

Blockchain and Immutable tables both work on similar principles of providing access to only Inserts but no Delete/Merge/Update*. The blockchain tables go a step further in terms of security and interlink the rows. The new rows are always inserted at the end of the chain and carry the previous rows hash signature.  Where as in the immutable tables, the new rows don’t carry the previous rows signature.

In this documentation we will see how to create, maintain and test the features of blockchain table.

 

Blockchain tables

Along with the generic columns specified during the table creation time, database internally associates a few additional columns to keep trace of the hash values, systimestamp , sequence number, chain number and host of other data to keep the sanctity of transactions and their order of insertion.

SQL> CREATE BLOCKCHAIN TABLE RND.EMP (employee_id NUMBER, salary NUMBER)
                    NO DROP UNTIL 31 DAYS IDLE
                    NO DELETE LOCKED
                    HASHING USING "SHA2_512" VERSION "V1";
Table created.

* *The NO DROP, NO DELETE, HASHING USING, and VERSION clauses are mandatory
** The rows cannot be deleted from the table unless it crosses the retention period. We can alter to     only increase the retention value but not lower.
** The “LOCKED” clause will not allow any alteration to the table , other values
NO DELETE { [ LOCKED ] | (UNTIL number DAYS AFTER INSERT [ LOCKED ]) }
NO DROP [ UNTIL number DAYS IDLE ]

This is the very reason, the architecture of the table should be solid and created after careful consideration of all the application needs.

 

Syntax of Immutable

It’s the same as blockchain table without the “Hashing feature”

CREATE IMMUTABLE TABLE RND.EMP_IMMUTABLE (employee_id NUMBER, salary NUMBER)
                    NO DROP UNTIL 31 DAYS IDLE
                    NO DELETE LOCKED;

For all purpose of testing , we will use only blockchain table in the this documentation.

SQL> desc RND.EMP
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPLOYEE_ID                                        NUMBER
 SALARY                                             NUMBER

The list of all additional rows populated for the table.

SQL> SELECT internal_column_id "Col ID",
        SUBSTR(column_name,1,30) "Column Name",
  SUBSTR(data_type,1,30) "Data Type", data_length "Data Length"
              FROM   dba_tab_cols
  WHERE  table_name = 'EMP' and owner='RND' ORDER BY internal_column_id;

    Col ID Column Name              Data Type                    Data Length
---------- ------------------------ ---------------------------- -----------
         1 EMPLOYEE_ID              NUMBER                                22
         2 SALARY                   NUMBER                                22
         3 ORABCTAB_INST_ID$        NUMBER                                22
         4 ORABCTAB_CHAIN_ID$       NUMBER                                22
         5 ORABCTAB_SEQ_NUM$        NUMBER                                22
         6 ORABCTAB_CREATION_TIME$  TIMESTAMP(6) WITH TIME ZONE           13
         7 ORABCTAB_USER_NUMBER$    NUMBER                                22
         8 ORABCTAB_HASH$           RAW                                 2000
         9 ORABCTAB_SIGNATURE$      RAW                                 2000
        10 ORABCTAB_SIGNATURE_ALG$  NUMBER                                22
        11 ORABCTAB_SIGNATURE_CERT$ RAW                                   16
        12 ORABCTAB_SPARE$          RAW                                 2000

12 rows selected.

 

Inserting data into the table

SQL> begin
for i in 1..10 loop
INSERT INTO rnd.emp VALUES (i,1000*i);
  end loop;
   commit;
end;      
  7  /

PL/SQL procedure successfully completed.

SQL> select * from rnd.emp;

EMPLOYEE_ID     SALARY
----------- ----------
          1       1000
          2       2000
          3       3000
          4       4000
          5       5000
          6       6000
          7       7000
          8       8000
          9       9000
         10      10000

10 rows selected.

SQL>

Using this query we can see the chain id and

SQL> SELECT ORABCTAB_CHAIN_ID$ "Chain ID", ORABCTAB_SEQ_NUM$ "Seq Num",
            to_char(ORABCTAB_CREATION_TIME$,'dd-Mon-YYYY hh-mi') "Chain date",
  2    3              ORABCTAB_USER_NUMBER$ "User Num", ORABCTAB_HASH$ "Chain HASH"
  4      FROM   RND.EMP;

The value of the user# is the value of user# populated in v$session. If another user with privileges to insert data into the table , then the “User Num” column will have a different value.

SQL> select distinct(user#) from v$session;
     USER#
----------
         0

 

Delete data

As per the laws of the table we cannot perform any action of regular delete and update on the table.

SQL> delete from rnd.emp;
delete from rnd.emp
                *
ERROR at line 1:
ORA-05715: operation not allowed on the blockchain or immutable table

SQL> update rnd.emp set salary=10000000 where employee_id=1;
update rnd.emp set salary=10000000 where employee_id=1
           *
ERROR at line 1:
ORA-05715: operation not allowed on the blockchain or immutable table

SQL>

 

We can only delete the expired rows will fall outside of the retention window and by using oracle provide procedures.

SQL>  SET SERVEROUTPUT ON

DECLARE
  NUMBER_ROWS NUMBER;
BEGIN

  DBMS_BLOCKCHAIN_TABLE.DELETE_EXPIRED_ROWS('RND','EMP', null, NUMBER_ROWS);
  DBMS_OUTPUT.PUT_LINE('Number of rows deleted=' || NUMBER_ROWS);
END;
SQL> SQL>   
Number of rows deleted=0

PL/SQL procedure successfully completed.

The number of rows deleted are 0 because there are no values which satisfy the delete condition based on the retention clause.

 

Drop table

As for the drop, the similar rules apply where we cannot drop the table within the retention period mentioned during the table creation.

SQL> drop table rnd.emp;
drop table rnd.emp
               *
ERROR at line 1:
ORA-05723: drop blockchain or immutable table EMP not allowed

 

Verify the rows in the table 

SQL> DECLARE
  verify_rows NUMBER;
  2    3  BEGIN
  4    DBMS_BLOCKCHAIN_TABLE.VERIFY_ROWS('RND','EMP', NULL, NULL, NULL, NULL, verify_rows, FALSE);
  5    DBMS_OUTPUT.PUT_LINE('Number of rows verified in table: ' || verify_rows);
  6  END;
  7  /
Number of rows verified in table: 10

* * all parameters of the procedure
declare
begin
prompt ' dbms_blockchain_table.verify_rows(
schema_name             IN  VARCHAR2,
table_name              IN  VARCHAR2,
low_timestamp           IN  TIMESTAMP WITH TIME ZONE DEFAULT NULL,
high_timestamp          IN  TIMESTAMP WITH TIME ZONE DEFAULT NULL,
instance_id             IN  NUMBER                   DEFAULT NULL,
chain_id                IN  NUMBER                   DEFAULT NULL,
number_of_rows_verified OUT NUMBER,
verify_signature        IN  BOOLEAN                  DEFAULT TRUE);
PRAGMA SUPPLEMENTAL_LOG_DATA(verify_rows, READ_ONLY) '
end;

As an addition security to the table we can sign the inserted rows using a client private key which is never shared with the database. Using this key we can make sure only authenticated users can insert the data into the table.

More from this Author