8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 21c » Here

Blockchain Tables in Oracle Database 21c

A blockchain table is a tamper-proof, insert-only table with an associated table-level and row-level retention period. Rows are organised into chains, with each row containing a hash of the data contained in the row, and the hash of the previous rows data.

This feature has been backported to Oracle 19.10, but also requires patch 32431413 and the COMPATIBLE parameter set to 19.10.0 or later. From 19.11 the additional patch is not required. There have been significant changes introduced to blockchain tables in Oracle 23ai, which you can read about here.

When learning about blockchain tables, be careful not to set excessively long retention periods, or you will have to wait a long time to drop your test tables.

Related articles.

Create a Blockchain Table

In addition to adding the BLOCKCHAIN keyword to the CREATE TABLE command, there are three blockchain clauses.

The NO DROP clause determines how long the table is protected from being dropped. If the table has no rows it can still be dropped. Unlike the initial releases of blockchain tables, in (19.11 and 21.3) the NO DROP clause also prevents the table being dropped via a DROP USER ... CASCADE command.

NO DROP [ UNTIL number DAYS IDLE ]

The NO DELETE clause determines the retention period. How long each row will be protected from deletion.

NO DELETE { [ LOCKED ] | (UNTIL number DAYS AFTER INSERT [ LOCKED ]) }

The blockchain hash and data format clause is fixed in the current release. It looks like it will allow alternative hashing algorithms in future releases.

HASHING USING sha2_512 VERSION v1

Putting it all together gives us something like the following.

--drop table bct_t1 purge;

create blockchain table bct_t1 (
  id            number,
  fruit         varchar2(20),
  quantity      number,
  created_date  date,
  constraint bct_t1_pk primary key (id)
)
no drop until 0 days idle
no delete until 16 days after insert
hashing using "SHA2_512" version "v1";

Checking the USER_TAB_COLS view shows us several invisible columns have been added to our column list. The hidden columns are described here.

set linesize 120 pagesize 50
column column_name format a30
column data_type format a27
column hidden_column format a13

select internal_column_id,
       column_name,
       data_type,
       data_length,
       hidden_column
FROM   user_tab_cols       
WHERE  table_name = 'BCT_T1'
ORDER BY internal_column_id;

INTERNAL_COLUMN_ID COLUMN_NAME                    DATA_TYPE                   DATA_LENGTH HIDDEN_COLUMN
------------------ ------------------------------ --------------------------- ----------- -------------
                 1 ID                             NUMBER                               22 NO
                 2 FRUIT                          VARCHAR2                             25 NO
                 3 QUANTITY                       NUMBER                               22 NO
                 4 CREATED_DATE                   DATE                                  7 NO
                 5 ORABCTAB_INST_ID$              NUMBER                               22 YES
                 6 ORABCTAB_CHAIN_ID$             NUMBER                               22 YES
                 7 ORABCTAB_SEQ_NUM$              NUMBER                               22 YES
                 8 ORABCTAB_CREATION_TIME$        TIMESTAMP(6) WITH TIME ZONE          13 YES
                 9 ORABCTAB_USER_NUMBER$          NUMBER                               22 YES
                10 ORABCTAB_HASH$                 RAW                                2000 YES
                11 ORABCTAB_SIGNATURE$            RAW                                2000 YES
                12 ORABCTAB_SIGNATURE_ALG$        NUMBER                               22 YES
                13 ORABCTAB_SIGNATURE_CERT$       RAW                                  16 YES
                14 ORABCTAB_SPARE$                RAW                                2000 YES

14 rows selected.

SQL>

The {CDB|DBA|ALL|USER}_BLOCKCHAIN_TABLES views display information about blockchain tables. It's a view over the SYS.BLOCKCHAIN_TABLE$ table.

column row_retention format a13
column row_retention_locked format a20
column table_inactivity_retention format a26
column hash_algorithm format a14

SELECT row_retention,
       row_retention_locked, 
       table_inactivity_retention,
       hash_algorithm  
FROM   user_blockchain_tables 
WHERE  table_name = 'BCT_T1';

ROW_RETENTION ROW_RETENTION_LOCKED TABLE_INACTIVITY_RETENTION HASH_ALGORITHM
------------- -------------------- -------------------------- --------------
           16 NO                                            0 SHA2_512

SQL>

Alter a Blockchain Table

The documentation suggests the NO DROP clause can be altered using the ALTER TABLE command, as long as the retention period is not reduced. At the time of writing this doesn't seem to work for tables that were initially created with NO DROP UNTIL 0 DAYS IDLE, as all values of days return an error. We currently have a retention period of 0 days for the table. In the following example we try to change it to 100 days, which gives an error. The command is syntactically correct, so I assume this is a bug in this release update. This is still broken in 19.12, but works correctly in 21.3.

alter table bct_t1 no drop until 100 days idle;

Error report -
ORA-05732: retention value cannot be lowered

SQL>

This command will work on tables created with NO DROP UNTIL 1 DAYS IDLE or higher.

Regardless of the current drop delay setting, an attempt to switch to the maximum value of NO DROP causes an ORA-00600 error. This is still broken in 19.12, but works correctly in 21.3.

alter table bct_t1 no drop;

Error starting at line : 1 in command -
alter table bct_t1 no drop
Error report -
ORA-00600: internal error code, arguments: [atbbctable_1], [0], [], [], [], [], [], [], [], [], [], []

This is a problem, as I would expect most people to want to play it safe by starting with a zero day delay, then upping the value later once they are happy with their setup. Starting on day one with a NO DROP seems very risky, as the only way to remove the table is to drop the whole schema.

Assuming it was not defined as locked, the NO DELETE clause can be modified using the ALTER TABLE command, as long as the retention period is not reduced. We currently have a row retention period of 16 days. In the example below we increase that value to 32. When we subsequently attempt to lower the value to 16 it gives an error.

-- Increase to 32 days.
alter table bct_t1 no delete until 32 days after insert;

Table BCT_T1 altered.

SQL>


-- Decrease to 16 days (fail).
alter table bct_t1 no delete until 16 days after insert;

Error report -
ORA-05732: retention value cannot be lowered

SQL>

In the current release, attempting to set the row retention to NO DELETE, which is an increase in the retention period, results in an ORA-00600 error. I assume this is a bug in the current release update. This is still broken in 19.12, but works correctly in 21.3.

alter table bct_t1 no delete;

Error report -
ORA-00600: internal error code, arguments: [atbbctable_1], [0], [], [], [], [], [], [], [], [], [], []

Blocked DML and DDL Operations

As you would expect for an insert-only table, all DML and DDL operations that would result in row data being amended or deleted are prevented for a blockchain table.

The following example shows a successful insert, then some unsuccessful DML statements.

-- INSERT
insert into bct_t1 (id, fruit, quantity, created_date ) values (1, 'apple', 20, sysdate);

1 row inserted.

SQL> commit;

Commit complete.

SQL>


-- UPDATE
update bct_t1 set quantity = 10 where id = 1;

Error report -
SQL Error: ORA-05715: operation not allowed on the blockchain table

SQL>


-- DELETE
delete from bct_t1 where id = 1;

Error report -
SQL Error: ORA-05715: operation not allowed on the blockchain table

SQL>

Some DDL statements that could alter the contents of the data are also prevented. Here is an example of the TRUNCATE statement.

truncate table bct_t1;

Error report -
ORA-05715: operation not allowed on the blockchain table

SQL>

Extending existing columns is fine, but adding new columns or dropping existing columns is not allowed.

-- Extend column.
alter table bct_t1 modify (fruit varchar2(25));

Table BCT_T1 altered.

SQL>


-- Add column
alter table bct_t1 add (additional_info varchar2(50));

Error report -
ORA-05715: operation not allowed on the blockchain table

SQL>


-- Drop column.
alter table bct_t1 drop column quantity;

Error report -
ORA-05715: operation not allowed on the blockchain table

SQL>

DBMS_BLOCKCHAIN_TABLE Package

The DBMS_BLOCKCHAIN_TABLE package is used for maintenance of blockchain tables.

The DELETE_EXPIRED_ROWS procedure removes any rows that are beyond the retention period. They can't be removed using a normal DELETE statement.

set serveroutput on
declare
  l_rows  number;
begin
  dbms_blockchain_table.delete_expired_rows(
    schema_name            => 'testuser1',
    table_name             => 'bct_t1',
    before_timestamp       => null,
    number_of_rows_deleted => l_rows);

  dbms_output.put_line('Rows Deleted=' || l_rows);
end;
/
Rows Deleted=0

PL/SQL procedure successfully completed.

SQL>

Alternatively, we can limit the deletion by date. The rows will only be deleted if they are outside the retention period, and match the date criteria.

set serveroutput on
declare
  l_rows  number;
begin
  dbms_blockchain_table.delete_expired_rows(
    schema_name            => 'testuser1',
    table_name             => 'bct_t1',
    before_timestamp       => systimestamp - 60,
    number_of_rows_deleted => l_rows);

  dbms_output.put_line('Rows Deleted=' || l_rows);
end;
/
Rows Deleted=0

PL/SQL procedure successfully completed.

SQL>

The VERIFY_ROWS procedure checks the rows in the table have a consistent hash, and signature if used.

set serveroutput on
declare
  l_rows      number;
  l_verified  number;
begin
  select count(*)
  into   l_rows
  from   testuser1.bct_t1;

  dbms_blockchain_table.verify_rows(
    schema_name             => 'testuser1',
    table_name              => 'bct_t1',
    number_of_rows_verified => l_verified);

  dbms_output.put_line('Rows=' || l_rows || '  Verified Rows=' || l_verified);
end;
/
Rows=1  Verified Rows=1

PL/SQL procedure successfully completed.

SQL>

Manage Certificates

The DBMS_USER_CERTS package allows us to manage certificates for use with row signing.

We generated a new self-signed certificate using the following openssl command on a Linux server.

mkdir /home/oracle/my_wallet

openssl req \
  -newkey rsa:2048 -nodes -sha512 \
  -x509 -days 3650 \
  -outform der \
  -keyout /home/oracle/my_wallet/my-bct-test-key.der \
  -out /home/oracle/my_wallet/my-bct-test-cert.der \
  -subj "/C=GB/ST=West Midlands/L=Birmingham/O=Example Company/OU=Devs/CN=Tim Hall/emailAddress=me@example.com"

We create an oracle directory object pointing to the location holding the certificate.

conn sys/SysPassword1@//localhost:1521/freepdb1 as sysdba

create or replace directory cert_dir as '/home/oracle/my_wallet/';
grant read, write on directory cert_dir to testuser1;

We use the contents of the "my-bct-test-key.der" file with the ADD_CERTIFICATE procedure to create a new certificate in the database.

conn testuser1/testuser1@//localhost:1521/freepdb1

set serveroutput on
declare
  l_dir            varchar2(20) := 'CERT_DIR';
  l_file_name      varchar2(20) := 'id_1_signature.dat';

  l_cert           blob;
  l_bfile          bfile;
  l_destoffset     integer := 1;
  l_srcoffset      integer := 1;
  l_cert_id        raw(16);
begin
  dbms_lob.createtemporary(l_cert, false);
  l_bfile := bfilename(l_dir, l_file_name);

  if (dbms_lob.fileexists( l_bfile ) = 1) then
    dbms_lob.fileopen( l_bfile );
    dbms_lob.loadblobfromfile(
      dest_lob    => l_cert,
      src_bfile   => l_bfile,
      amount      => dbms_lob.getlength(l_bfile),
      dest_offset => l_destoffset,
      src_offset  => l_srcoffset
    );
    dbms_lob.fileclose( l_bfile );

    dbms_user_certs.add_certificate(l_cert, l_cert_id);
    dbms_output.put_line('certificate ID: ' || l_cert_id);
  else
    raise_application_error(-20001, 'must create the user certificates first');
  end if;
end;
/
certificate ID: 006EA851A3FE2E89E065000000000001

PL/SQL procedure successfully completed.

SQL>

The certificate is be visible in the {DBA|USER}_CERTIFICATES views.

column certificate_id format a35
column user_name format a10
column distinguished_name format a30

select certificate_id,
       user_name,
       distinguished_name
from   user_certificates;

CERTIFICATE_ID                      USER_NAME  DISTINGUISHED_NAME
----------------------------------- ---------- ------------------------------
006EA851A3FE2E89E065000000000001    TESTUSER1  EMAIL=me@example.com,CN=Tim Ha
                                               ll,OU=Devs,O=Example Company,L
                                               =Birmingham,ST=West Midlands,C
                                               =GB

SQL>

The DROP_CERTIFICATE procedure removes certificates from the database. The CERTIFICATE_ID column from the {DBA|USER}_CERTIFICATES views identifies the certificate to be removed. Don't run this is you want to continue to sign rows.

begin
  dbms_user_certs.drop_certificate(cert_id => '006EA851A3FE2E89E065000000000001');
end;
/

Sign Rows

Once a certificate is loaded into the database, we can use it to sign rows.

We create a test blockchain table and insert a row.

drop table if exists bct_sign purge;

create blockchain table bct_sign (
  id            number,
  fruit         varchar2(20),
  quantity      number,
  created_date  date,
  constraint bct_sign_pk primary key (id)
)
no drop until 0 days idle
no delete until 16 days after insert
hashing using "SHA2_512" version "v2";

insert into bct_sign (id, fruit, quantity, created_date) values (1, 'apple', 20, sysdate);
commit;

We query some of the hidden column values for the row we want to sign, and pass them to the DBMS_BLOCKCHAIN_TABLE.GET_BYTES_FOR_ROW_SIGNATURE procedure to identify the data that is necessary to sign the row. We then write this data out to a file.

declare
  l_dir         varchar2(20) := 'CERT_DIR';
  l_file_name   varchar2(20) := 'id_1_signature.dat';

  l_row_data    blob;
  l_buffer      raw(4000);
  l_inst_id     binary_integer;
  l_chain_id    binary_integer;
  l_seq_num     binary_integer;
  l_row_len     binary_integer;
  l_file        utl_file.file_type; 
begin
  select orabctab_inst_id$, orabctab_chain_id$, orabctab_seq_num$
  into   l_inst_id, l_chain_id, l_seq_num
  from   bct_sign 
  where  id = 1;
  
  dbms_blockchain_table.get_bytes_for_row_signature(
    schema_name => 'testuser1',
    table_name  => 'bct_sign',
    instance_id => l_inst_id,
    chain_id    => l_chain_id,
    sequence_id => l_seq_num,
    data_format => 1,
    row_data    => l_row_data);
    
  l_row_len := dbms_lob.getlength(l_row_data);
  dbms_lob.read(l_row_data, l_row_len, 1, l_buffer);
  l_file := utl_file.fopen(l_dir,l_file_name,'wb', 32767);
  utl_file.put_raw(l_file, l_buffer, true);
  utl_file.fclose(l_file);
end;
/

We sign the file using the private key for our certificate, and create the "/home/oracle/my_wallet/id_1_signature.dat.sha512" file. The resulting file is our signature.

cd /home/oracle/my_wallet/

openssl dgst -sha512 \
        -sign my-bct-test-key.der \
        -out id_1_signature.dat.sha512 \
        id_1_signature.dat

We can now use the signature to sign the row. The following example gets the information about the row, loads the signature into a blob, and uses it to sign the rows using the SIGN_ROW procedure in the DBMS_BLOCKCHAIN_TABLE package.

declare
  l_dir          varchar2(30) := 'CERT_DIR';
  l_file_name    varchar2(30) := 'id_1_signature.dat.sha512';
  l_cert_id      raw (16) := hextoraw('006EA851A3FE2E89E065000000000001');

  l_inst_id      binary_integer;
  l_chain_id     binary_integer;
  l_sequence_no  binary_integer;
  l_hash         raw(2000);
  l_signature    blob;
  l_bfile        bfile;
  l_destoffset     integer := 1;
  l_srcoffset      integer := 1;
begin
  select orabctab_inst_id$, orabctab_chain_id$, orabctab_seq_num$, orabctab_hash$, orabctab_signature$
  into   l_inst_id, l_chain_id, l_sequence_no, l_hash, l_signature
  from   bct_sign
  where  id = 1;

  if l_signature is not null then
    raise_application_error(-20000, 'The row has already been signed.');
  end if;

  l_bfile := bfilename(l_dir, l_file_name);
  if (dbms_lob.fileexists(l_bfile ) = 1) then
    dbms_lob.createtemporary(l_signature, false);
    dbms_lob.fileopen(l_bfile);
    dbms_lob.loadblobfromfile(
      dest_lob    => l_signature,
      src_bfile   => l_bfile,
      amount      => dbms_lob.getlength(l_bfile),
      dest_offset => l_destoffset,
      src_offset  => l_srcoffset
    );
    dbms_lob.fileclose(l_bfile);

    dbms_blockchain_table.sign_row(
      schema_name      => 'testuser1',
      table_name       => 'bct_sign',
      instance_id      => l_inst_id,
      chain_id         => l_chain_id,
      sequence_id      => l_sequence_no,
      hash             => l_hash,
      signature        => l_signature,
      certificate_guid => l_cert_id,
      signature_algo   => dbms_blockchain_table.sign_algo_rsa_sha2_512);
  else
    raise_application_error(-20001, 'must create the signature first');
  end if;
end;
/

We can see the row has been signed using the following query.

set linesize 120
column signature format a30
column cert_id format a35

select id,
       fruit,
       orabctab_signature$ as signature,
       orabctab_signature_cert$ as cert_id
from   bct_sign
where  id = 1;

        ID FRUIT                SIGNATURE                      CERT_ID
---------- -------------------- ------------------------------ -----------------------------------
         1 apple                240FB4F558B14DD035DBDD5E71012E 006EA851A3FE2E89E065000000000001
                                AB28775A47C7CC2720D6660B4F9F1B
                                DFE8F944FCD55AD7BE67400A82B0A8
                                C00281F405CD53401D85DF6272AE3E
                                1294A02B6901B6693515348482F7D5
                                5F861860FBF66ACB6DFB7C977DC87C
                                3F2C1A9C1ABFE79F30DD6F7AAA155F
                                4F4DCD3B37F6BDD4AEDED784B18E3A
                                0EAB72B23F53403

SQL>

Thanks to Martin Bach and Ulrike Schwinn for helping with the solution for signing rows.

Considerations

There are a number of things to consider when using blockchain tables.

I guess the main question should be, why would you use a blockchain table?

For more information see:

Hope this helps. Regards Tim...

Back to the Top.