1. PL/SQL Packages and Types Reference
  2. DBMS_BLOCKCHAIN_TABLE

34 DBMS_BLOCKCHAIN_TABLE

A blockchain table is an append-only table designed for centralized blockchain applications. The DBMS_BLOCKCHAIN_TABLE package allows you do the following: delete rows in a blockchain table that are beyond the row retention defined for the blockchain table; get the bytes that are input to the cryptographic hash for a row so you can verify the hash in the row; sign a row you inserted into a blockchain table after the row is added to a chain in the blockchain table; and have the database verify the hashes on some or all rows in a blockchain table. Blockchain tables support only DER encoding for X.509 certificates, not PEM encoding.

This chapter contains the following topics:

See Also:

34.1 DBMS_BLOCKCHAIN_TABLE Overview

In Oracle Blockchain Table, peers are database users who trust the database to maintain a tamper-resistant ledger.

The ledger is implemented as a blockchain table, which is defined and managed by the application. Existing applications can protect against fraud without requiring a new infrastructure or programming model. Although transaction throughput is lower than for a standard table, performance for a blockchain table is better than for a decentralized blockchain.

The DBMS_BLOCKCHAIN_TABLE package lets you do the following:
  • delete rows in a blockchain table that are beyond the row retention defined for the blockchain table
  • get the bytes that are input to the signature algorithm so you can sign a row you inserted into the blockchain table
  • get the bytes that are input to the cryptographic hash for a row so you can verify the hash in the row
  • sign a row you inserted into a blockchain table after the row is added to a chain in the blockchain table
  • have the database verify the hashes and signatures on some or all rows in a blockchain table.

34.2 DBMS_BLOCKCHAIN_TABLE Security Model

The DBMS_BLOCKCHAIN_TABLE package is owned by SYS and is installed as part of database installation. The routines in the package are run with invokers' rights (run with the privileges of the current user). Thus any user with select privileges on the blockchain table should be able to validate the row contents of that table.

Any user with delete privileges on the blockchain table can delete rows beyond the retention period defined for the blockchain table.

A user that inserted a row into the blockchain table can add a digital signature to the row after the row is added to a chain in the blockchain table.

34.3 Summary of DBMS_BLOCKCHAIN_TABLE Subprograms

The DBMS_BLOCKCHAIN_TABLE package uses DELETE_EXPIRED_ROWS, GET_BYTES_FOR_ROW_HASH, GET_BYTES_FOR_ROW_SIGNATURE, SIGN_ROW, and VERIFY_ROWS subprograms to perform various functions.

Table 34-1 DBMS_BLOCKCHAIN_TABLE Package Subprograms

Subprogram Description
DELETE_EXPIRED_ROWS Procedure Deletes rows outside the retention window created before before_timestamp if the time stamp is specified; otherwise, deletes all rows outside the retention window.
GET_BYTES_FOR_ROW_HASH Procedure Returns in row_data the bytes (series of {meta-data-value} {column-data-value} in column position order} for the particular row identified, followed by the hash (in data format) for previous row in the chain, in the data format supported.
GET_BYTES_FOR_ROW_SIGNATURE Procedure The bytes returned are the bytes in the row hash. No metadata is included.
SIGN_ROW Procedure This procedure can be used by the current user to provide a signature on row content of a previously inserted row. The user who inserted a row into a blockchain table is the only user that can sign the row.
VERIFY_ROWS Procedure Verifies all rows on all applicable chains for integrity of HASH column value for rows created in the range of LOW_TIMESTAMP to HIGH_TIMESTAMP. Row signatures can be verified as an option.

34.3.1 DELETE_EXPIRED_ROWS Procedure

This procedure deletes rows outside the retention window created before_timestamp if the time stamp is specified; otherwise, deletes all rows outside the retention window. The number of rows deleted is returned in number_of_rows_deleted parameter.

Syntax

DBMS_BLOCKCHAIN_TABLE.DELETE_EXPIRED_ROWS(
   schema_name 		 IN VARCHAR2,
   table_name 	         IN VARCHAR2, 
   before_timestamp 	   IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,
   number_of_rows_deleted     OUT NUMBER);

Parameters

Table 34-2 DELETE_EXPIRED_ROWS Procedure Parameters

Parameter Description

schema_name

 The name of the schema.
table_name The name of the blockchain table.
before_timestamp

The end time for the range of rows deleted by the procedure, subject to the row retention time currently associated with the blockchain table.

This is an optional parameter. The default value is NULL.

number_of_rows_deleted The count of the number of rows deleted.

34.3.2 GET_BYTES_FOR_ROW_HASH Procedure

This procedure returns the bytes in column_data that the database hashed to get the hash value for the row identified by parameters instance_id, chain_id, and sequence_id. These bytes are a concatenation of metadata and data bytes for each column of the table in column position order, followed by the hash value for the previous row in the chain.

Syntax

DBMS_BLOCKCHAIN_TABLE.GET_BYTES_FOR_ROW_HASH(
   schema_name 		 IN VARCHAR2,
   table_name 	         IN VARCHAR2, 
   instance_id 		 IN NUMBER, 
   chain_id                  IN NUMBER,
   sequence_id 	        IN NUMBER,
   data_format 	        IN NUMBER, 
   row_data 	           IN OUT BLOB);

Parameters

Table 34-3 GET_BYTES_FOR_ROW_HASH Procedure Parameters

Parameter Description
schema_name The name of the schema.
table_name The name of the blockchain table.
instance_id

The instance that inserted the row.

Valid values are 1, 2, and so on.

chain_id

The chain containing the row. There are 32 chains in each instance, and they are numbered from 0 to 31.
sequence_id The position of the row on the specified chain.
data_format The version of the data layout for the hash in the specified row. Must be 1 in this release.
row_data The bytes for the specified row in the specified data format that can be input to the cryptographic hash function to verify the value of the hash in the row. Any bytes in the BLOB are overwritten.

Usage Notes

All parameters are required input parameters.

The metadata bytes for a column are 20 bytes that encode the blockchain algorithm version used to hash the row, the column position, the column data type, whether the column value is NULL, and the actual length of the column value in bytes.

The column data bytes are the actual bytes representing the column value on disk for non-character columns. For character columns, the values are normalized to specific character sets. For CHAR and NCHAR columns, blank trimming is also done.

Few metadata bytes are reserved for future use.

34.3.3 GET_BYTES_FOR_ROW_SIGNATURE Procedure

The routine returns in row_data the bytes in the hash in the row without any metadata. No other columns are involved either in the row or in the previous row.

Syntax

DBMS_BLOCKCHAIN_TABLE.GET_BYTES_FOR_ROW_SIGNATURE(
   schema_name 		 IN VARCHAR2,
   table_name 	         IN VARCHAR2, 
   instance_id 		 IN NUMBER, 
   chain_id 		    IN NUMBER,
   sequence_id 	        IN NUMBER,
   data_format 	        IN NUMBER, 
   row_data                   IN OUT BLOB);

Parameters

Table 34-4 GET_BYTES_FOR_ROW_SIGNATURE Procedure Parameters

Parameter Description

schema_name

 The name of the schema.
table_name The name of the blockchain table.
instance_id

The instance on which the row was inserted.

Valid values are 1, 2, and so on.

chain_id

The chain on which the row was inserted. There are 32 chains in each instance, and they are numbered from 0 to 31.
sequence_id The position of the row on the chain.
data_format The format of the data in row_data. The value must be 1 in the DB20c release.
row_data A sequence of bytes that must be signed.

Usage Notes

All parameters are required input parameters.

34.3.4 SIGN_ROW Procedure

This procedure can be used by the current user to provide a signature on row content of a previously inserted row. The transaction that inserted the row into the blockchain table must have committed before the SIGN_ROW procedure is called.

Syntax

DBMS_BLOCKCHAIN_TABLE.SIGN_ROW(
   schema_name               IN VARCHAR2,
   table_name                IN VARCHAR2,
   instance_id               IN NUMBER, 
   chain_id 		   IN NUMBER,
   sequence_id               IN NUMBER,
   hash                      IN RAW DEFAULT NULL,
   signature                 IN RAW,
   certificate_guid          IN RAW,
   signature_algo            IN NUMBER);

Parameters

Table 34-5 SIGN_ROW Procedure Parameters

Parameter Description

schema_name

 The name of the schema.
table_name The name of the blockchain table.
instance_id The instance on which the row was inserted.
chain_id

The chain containing the row to be signed. There are 32 chains in each instance, and they are numbered from 0 to 31.
sequence_id

The position of the row on the chain.

Valid values are 1, 2, and so on.

hash If non-NULL, the expected value of the hash in the row to be signed. If NULL, the hash in the row to be signed is not checked.
signature The user's digital signature on the hash value stored in the row.
certificate_guid A unique identifier for the certificate stored in the database that may be used to verify the digital signature.
signature_algo The algorithm used to create the digital signature. The algorithm must be one of the following constants defined in the DBMS_BLOCKCHAIN_TABLE package:
  • SIGN_ALGO_RSA_SHA2_256
  • SIGN_ALGO_RSA_SHA2_384
  • SIGN_ALGO_RSA_SHA2_512

Note:

For information on hidden columns in blockchain tables, see Hidden Columns in Blockchain Tables

Usage Notes

  • All parameters are required input parameters except for hash.
  • The database will verify that:
    • the current user’s obj# matches the user# hidden column value (ensures that the user owns the row)
    • the user has insert privileges for the blockchain table ‘schema_name’.’table_name’
    • the hash (if provided) matches the hash column content for the row
    • the signature column value for the specific row identified by ‘instance_id’, ‘chain_id’, and ‘sequence_id’ is NULL
    • if the verification succeeds, the signature value is stored for the row.

34.3.5 VERIFY_ROWS Procedure

Verifies all rows on all applicable chains for integrity of HASH column value and optionally the SIGNATURE column value for rows created in the range of low_timestamp to high_timestamp. An appropriate exception is thrown if the integrity of chains is compromised.

Syntax

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);

Parameters

Table 34-6 VERIFY_ROWS Procedure Parameters

Parameter Description
schema_name The name of the schema.
table_name The name of the blockchain table.
low_timestamp

If specified, the low end of the time range for verifying rows.

The default value is NULL.

high_timestamp

If specified, the high end of the time range for verifying rows.

The default value is NULL.

instance_id If specified, restricts row verification to rows inserted on the specified instance.
chain_id If specified, restricts row verification to rows on the specified chain. There are 32 chains in each instance, and they are numbered from 0 to 31.
number_of_rows_verified The number of rows verified.
verify_signature

If verify_signature is TRUE, both the hash on each row and any signature on the row are verified. If verify_signature is FALSE, only the hash on each row is verified.

The default value is TRUE.

Usage Notes

  • The hash on the first element in the time range for verifying rows in a chain is verified only if its sequence number is 1.
  • schema_name and table_name are required input parameters
  • All others input parameters are optional, with the following exceptions:
    • If chain_id is specified, instance_id must be specified
    • Valid values for instance_id are 1, 2, … etc.
    • If neither instance_id, nor chain_id is specified, then it implies *all* chains. If only instance_id is specified, then it implies *all* chains on that instance. If both are specified, it implies the specific chain provided by the combination.

    • If both low_timestamp and high_timestamp are specified, then high_timestamp must be later than low_timestamp.

      If low_timestamp is not specified, then the range is the oldest row in the blockchain to high_timestamp.

      If high_timestamp is not specified then the range is low_timestamp to the timestamp of the last row inserted in the table.