8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Blockchain Table Enhancements in Oracle Database 23c
Blockchain tables were first introduced in Oracle 21c, and backported to Oracle 19c. This article demonstrates the enhancements to blockchain tables in Oracle 23c. There are some fundamental changes to blockchain tables in Oracle 23c, so some of the previous article will be replicated here.
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.
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.
- Create a Blockchain Table
- Alter a Blockchain Table
- Blocked DML and DDL Operations
- Add/Drop Columns
- User Chains
- Row Versions
- Control Long Idle Retention Times
- DBMS_BLOCKCHAIN_TABLE Package
- Flashback Data Archives (FDA) using Blockchain Tables
- Manage Certificates
- Sign Rows
- Countersignature and Delegate Signer
- Considerations
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 ]
NO DROP
: The table can't be dropped. Be careful about using this setting during testing.NO DROP UNTIL number DAYS IDLE
: The table can't dropped until there have been no new rows inserted for the specified number of days. You may prefer to use 0 or 1 as the number of days during testing this functionality.
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 ]) }
NO DELETE
: Each row is retained forever. The absence of theLOCKED
keyword implies the setting can be changed with theALTER TABLE
command, but it can't. Retention periods can only be increased.NO DELETE LOCKED
: Same asNO DELETE
.NO DELETE UNTIL number DAYS AFTER INSERT
: Each row is protected from deletion for the specified number of days, but this setting can be increased using theALTER TABLE
command. Minimum 16 days.NO DELETE UNTIL number DAYS AFTER INSERT LOCKED
: Each row is protected from deletion for the specified number of days, and this setting can't be changed using theALTER TABLE
command. Minimum 16 days.
In the initial release the blockchain hash and data format clause was fixed. In Oracle 23c we have the option of using the original "V1" version, or the new "V2" version, which supports additional functionality.
HASHING USING sha2_512 VERSION v2
The following example creates two tables. One of each version.
drop table if exists 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"; drop table if exists bct_t2 purge; create blockchain table bct_t2 ( id number, fruit varchar2(20), quantity number, created_date date, constraint bct_t2_pk primary key (id) ) no drop until 0 days idle no delete until 16 days after insert hashing using "SHA2_512" version "v2";
Checking the USER_TAB_COLS
view shows us several invisible columns have been added to our column list. The hidden columns are described here. Notice the "V2" table has twice the number of hidden columns compared to the "V1" table.
set linesize 120 pagesize 50 column column_name format a45 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 20 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> select internal_column_id, column_name, data_type, data_length, hidden_column from user_tab_cols where table_name = 'BCT_T2' order by internal_column_id; INTERNAL_COLUMN_ID COLUMN_NAME DATA_TYPE DATA_LENGTH HIDDEN_COLUMN ------------------ --------------------------------------------- --------------------------- ----------- ------------- 1 ID NUMBER 22 NO 2 FRUIT VARCHAR2 20 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 1000 YES 14 ORABCTAB_SPARE$ RAW 2000 YES 15 ORABCTAB_PDB_GUID$ RAW 2000 YES 16 ORABCTAB_ROW_VERSION$ NUMBER 22 YES 17 ORABCTAB_LAST_ROW_VERSION_NUMBER$ RAW 1 YES 18 ORABCTAB_USER_CHAIN_HASH$ RAW 2000 YES 19 ORABCTAB_DELEGATE_SIGNATURE$ RAW 2000 YES 20 ORABCTAB_DELEGATE_SIGNATURE_ALG$ NUMBER 22 YES 21 ORABCTAB_DELEGATE_SIGNATURE_CERT$ RAW 1000 YES 22 ORABCTAB_DELEGATE_USER_NUMBER$ NUMBER 22 YES 23 ORABCTAB_COUNTERSIGNATURE$ RAW 2000 YES 24 ORABCTAB_COUNTERSIGNATURE_ALG$ NUMBER 22 YES 25 ORABCTAB_COUNTERSIGNATURE_CERT$ RAW 1000 YES 26 ORABCTAB_COUNTERSIGNATURE_ROW_FORMAT_VERSION$ VARCHAR2 4000 YES 27 ORABCTAB_COUNTERSIGNATURE_ROW_FORMAT_FLAG$ NUMBER 22 YES 28 ORABCTAB_TS$ TIMESTAMP(6) 13 YES 28 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 table_name format a10 column row_retention format 9999999999999 column row_retention_locked format a20 column table_inactivity_retention format 9999999999999999999999999 column hash_algorithm format a14 column table_version format a14 select table_name, row_retention, row_retention_locked, table_inactivity_retention, hash_algorithm, table_version from user_blockchain_tables where table_name like 'BCT_T%' order by 1; TABLE_NAME ROW_RETENTION ROW_RETENTION_LOCKED TABLE_INACTIVITY_RETENTION HASH_ALGORITHM TABLE_VERSION ---------- -------------- -------------------- -------------------------- -------------- -------------- BCT_T1 16 NO 0 SHA2_512 V1 BCT_T2 16 NO 0 SHA2_512 V2 SQL>
Alter a Blockchain Table
The following operations perform the same on "V1" and "V2" blockchain tables.
The NO DROP
clause can be altered using the ALTER TABLE
command, as long as the retention period is not reduced. Be careful not to set it too large when testing.
alter table bct_t1 no drop until 1 days idle;
Regardless of the current drop delay setting, an attempt to switch to the maximum value of NO DROP
causes an error. This is because of the BLOCKCHAIN_TABLE_RETENTION_THRESHOLD
parameter discussed below.
alter table bct_t1 no drop; * ERROR at line 1: ORA-05807: Blockchain or immutable table "TESTUSER1"."BCT_T1" cannot have idle retention greater than 16 days. SQL>
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>
We can set the row retention to NO DELETE
, which means rows will be kept forever. Remember, we can't reduce this value once is it set, so take care using such a long retention period.
alter table bct_t1 no delete;
We'll drop and recreate the "V1" blockchain table. We've not inserted any rows into it yet, so despite the retention times the following command works.
drop table if exists 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";
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_t2 (id, fruit, quantity, created_date) values (1, 'apple', 20, sysdate); 1 row inserted. SQL> commit; Commit complete. SQL> -- UPDATE update bct_t2 set quantity = 10 where id = 1; * ERROR at line 1: ORA-05715: operation not allowed on the blockchain or immutable table SQL> -- DELETE delete from bct_t2 where id = 1; * ERROR at line 1: ORA-05715: operation not allowed on the blockchain or immutable 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_t2; * ERROR at line 1: ORA-05715: operation not allowed on the blockchain or immutable table SQL>
Extending existing columns is fine.
alter table bct_t1 modify (fruit varchar2(25)); Table altered. SQL>
Add/Drop Columns
In a "V1" blockchain table adding new columns or dropping existing columns is not allowed.
-- Add column alter table bct_t1 add (additional_info varchar2(50)); * ERROR at line 1: ORA-05715: operation not allowed on the blockchain or immutable table SQL> -- Drop column. alter table bct_t1 drop column quantity; * ERROR at line 1: ORA-05715: operation not allowed on the blockchain or immutable table SQL>
In a "V2" blockchain table we can add new columns and drop existing columns. The dropped columns are marked as hidden, rather than actually being dropped.
-- Add column alter table bct_t2 add (additional_info varchar2(50)); Table altered. SQL> -- Drop column. alter table bct_t2 drop column additional_info; Table altered. SQL>
User Chains
In the previous releases blockchain tables only supported up to 32 system generated chains per instance, with rows being assigned to chains at random. In Oracle 23c we can create user chains using up to three columns to define the chains. Each unique combination of values represents a separate chain.
In the following example we use the WITH USER CHAIN
clause, resulting in a separate chain for each unique value of the FRUIT
column.
drop table if exists bct_uc purge; create blockchain table bct_uc ( id number, fruit varchar2(20), quantity number, created_date date, constraint bct_uc_pk primary key (id) ) no drop until 0 days idle no delete until 16 days after insert hashing using "SHA2_512" with user chain fruit_chain (fruit) version "v2";
The USER_BLOCKCHAIN_TABLE_CHAINS
view allows us to display information about user chains associated with a table. A chain is not created until rows are inserted. We insert some data and check the USER_BLOCKCHAIN_TABLE_CHAINS
view.
insert into bct_uc (id, fruit, quantity, created_date) values (1, 'apple', 20, sysdate), (2, 'orange', 10, sysdate), (3, 'apple', 30, sysdate), (4, 'orange', 35, sysdate); commit; select table_name, chain_id from user_blockchain_table_chains; TABLE_NAME CHAIN_ID -------------------- ---------- BCT_UC 9 SQL>
Row Versions
We can't update rows in a blockchain table, so we have to make changes by inserting new rows. Blockchain table row versions allow us to track the sequence of inserts for related rows over time, so we can check the latest version of a specific row. This is done by adding the WITH ROW VERSION [AND USER CHAIN]
clause to the blockchain table, specifying a list of up to three columns that are used to determine if rows are related.
In the example below we use the WITH ROW VERSION AND USER CHAIN
clause based on the FRUIT
column.
drop table if exists bct_rv purge; create blockchain table bct_rv ( id number, fruit varchar2(20), quantity number, created_date date, constraint bct_rv_pk primary key (id) ) no drop until 0 days idle no delete until 16 days after insert hashing using "SHA2_512" with row version and user chain fruit_chain (fruit) version "v2";
We insert some data.
insert into bct_rv (id, fruit, quantity, created_date) values (1, 'apple', 20, sysdate), (2, 'orange', 10, sysdate), (3, 'apple', 30, sysdate), (4, 'orange', 35, sysdate); commit;
We can see all the entries in the blockchain table.
select * from bct_rv; ID FRUIT QUANTITY CREATED_D ---------- -------------------- ---------- --------- 1 apple 20 07-JUL-23 2 orange 10 07-JUL-23 3 apple 30 07-JUL-23 4 orange 35 07-JUL-23 SQL>
When we create a blockchain table with a user chain, we automatically get a view created with "_LAST$
" appended to the table name. When we query this view we see only the latest row version for each unique value in the chain.
select * from bct_rv_last$; ID FRUIT QUANTITY CREATED_D ---------- -------------------- ---------- --------- 3 apple 30 07-JUL-23 4 orange 35 07-JUL-23 SQL>
Control Long Idle Retention Times
In Oracle 23c the maximum idle retention time for the table is controlled by the BLOCKCHAIN_TABLE_RETENTION_THRESHOLD
parameter.
show parameter blockchain_table_retention_threshold NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ blockchain_table_retention_threshold integer 16 SQL>
This means we can't set the NO DROP
clause to a value longer than 16 days.
drop table if exists bct_t3 purge; create blockchain table bct_t3 ( id number, fruit varchar2(20), quantity number, created_date date, constraint bct_t3_pk primary key (id) ) no drop until 32 days idle no delete until 16 days after insert hashing using "SHA2_512" version "v2"; * ERROR at line 1: ORA-05807: Blockchain or immutable table "TESTUSER1"."BCT_T3" cannot have idle retention greater than 16 days. SQL>
We can either increase this setting, or grant the user the TABLE RETENTION
privilege, which allows the user to ignore this limit.
conn sys/SysPassword1@//localhost:1521/freepdb1 as sysdba grant table retention to testuser1; conn testuser1/testuser1@//localhost:1521/freepdb1
Now we can ignore the limit.
drop table if exists bct_t3 purge; create blockchain table bct_t3 ( id number, fruit varchar2(20), quantity number, created_date date, constraint bct_t3_pk primary key (id) ) no drop until 32 days idle no delete until 16 days after insert hashing using "SHA2_512" version "v2"; Table created. 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 admin.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>
In Oracle 23c we can verify rows for a specific chain.
Flashback Data Archives (FDA) using Blockchain Tables
In Oracle 23c we can create flashback data archives (FDA) as block blockchain tables by adding the BLOCKCHAIN
keyword to the FLASHBACK ARCHIVE
clause. This gives additional assurance the flashback data archive has not been tampered with.
First we set up a new test user.
conn sys/SysPassword1@//localhost:1521/freepdb1 as sysdba drop user if exists testuser2 cascade; create user testuser2 identified by testuser2; grant db_developer_role to testuser2;
We create a new tablespace and a flashback data archive using the tablespace.
create tablespace fda_ts datafile size 1m autoextend on next 1m; alter user testuser2 quota unlimited on fda_ts; create flashback archive default fda_1year tablespace fda_ts quota 10g retention 1 year;
We apply some additional grants to the test user.
grant flashback archive on fda_1year to testuser2; grant flashback archive administer to testuser2; grant execute on dbms_flashback_archive to testuser2; grant create any context to testuser2;
We can now connect to the test user and create a table with a blockchain flashback data archive. Notice the addition of the BLOCKCHAIN
keyword.
conn testuser2/testuser2@//localhost:1521/freepdb1 drop table if exists t1 purge; create table t1 ( id number, description varchar2(50), constraint t1_pk primary key (id) ) blockchain flashback archive fda_1year;
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.
Countersignature and Delegate Signer
Oracle 23c includes the ability to request a countersignature at the time of signing a row. It also allows a delegate signer, who can sign the rows on behalf of the primary user. Here are the links to the documentation on these features.
Considerations
There are a number of things to consider when using blockchain tables.
- Many of the issues associated with blockchain tables in the previous releases have been resolved in Oracle 23c.
- Blockchain tables are slower than conventional tables, due to the extra work associated with them.
- Blockchain tables can be indexed and partitioned in the normal manner.
- There are some restrictions associated with data pump against blockchain tables, described here.
- There are a number of general restrictions associated with blockchain tables, described here.
- Oracle recommend saving the current hash and the corresponding sequence number for each chain in the instance somewhere outside of the database. This allows you to compare your recorded values to those in the table for extra assurance.
- In data guard environments, Oracle recommend maximum protection mode or maximum availability mode when working with blockchain tables.
- User certificates can be added to the database using the
ADD_CERTIFICATE
procedure in theDBMS_USER_CERTS
package, and applied to existing rows using theSIGN_ROW
procedure in theDBMS_BLOCKCHAIN_TABLE
package.
I guess the main question should be, why would you use a blockchain table?
- If you need an insert-only tamper proof table in your application generally, this could be the solution.
- If you want to take advantage of the trust associated with blockchain in a centralized manner, rather than having multiple client applications having to manage blockchains individually, using a blockchain table allows you to centralise that trust.
- You can add the trust associated with blockchain to existing applications without having to worry about recoding them.
For more information see:
- Managing Blockchain Tables
- DBMS_BLOCKCHAIN_TABLE
- DBMS_USER_CERTS
- DBA_CERTIFICATES
- ALL_BLOCKCHAIN_TABLES
- CREATE TABLE
- Blockchain Tables in Oracle Database 21c (part 2)
- Blockchain Tables in Oracle Database 21c
Hope this helps. Regards Tim...