Contents
In this ever-changing database world, it’s good to have a few constant things, tamper proof, and validate the authenticity of an original transaction. This is where “Blockchain” tables and “Immutable” tables come to the fore.
They provide a resilient infrastructure that allows storing the transactions that 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 :
- Storing compliance data
- Accounting/ledger tables in financial instruments
- Audit information
- 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. Whereas 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 the blockchain table.
Blockchain Tables
Along with the generic columns specified during the table creation time, the database internally associates a few additional columns to keep track of the hash values, sys timestamp, 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 the 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 purposes of testing, we will use only the blockchain table in 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 that will fall outside of the retention window by using Oracle-provided 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 is 0 because there are no values that satisfy the delete condition based on the retention clause.
Drop Table
As for the drop, 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 additional 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.