Oracle | DBMS_BLOCKCHAIN

Oracle DBMS_BLOCKCHAIN_TABLE Version 23c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

Purpose

A blockchain table is an append-only table designed for centralized blockchain applications.

In an Oracle Blockchain Table, peers are database users who trust the database to maintain a tamper-resistant ledger which is implemented as a blockchain table,

A blockchain table is defined and managed by the application. Existing applications can protect against fraud without requiring a new infrastructure or programming model. And, although transaction throughput is lower than for a standard heap 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

AUTHID CURRENT_USER

Constants

Name	Data Type	Value
General
SIGN_ALGO_RSA_SHA2_256	NUMBER	1
SIGN_ALGO_RSA_SHA2_384	NUMBER	2
SIGN_ALGO_RSA_SHA2_512	NUMBER	3
SIGN_ALGO_DEFAULT	NUMBER	3

Dependencies

ALL_OBJECTS	DBMS_LOB	NLS_DATABASE_PARAMETERS
ALL_USERS	DBMS_UTILITY	ORABCTAB_ROW_ARRAY_T
DBMS_AQADM_LIB	DUAL	UTL_I18N
DBMS_ASSERT	KUPM$MCP	UTL_RAW
DBMS_BLOCKCHAIN_TABLE_LIB

Documented Yes: Packages and Types Reference

Exceptions

Error Code	Reason
ORA-05720	<table_name> is not a blockchain table

First Available 20c

Pragma PRAGMA SUPPLEMENTAL_LOG_DATA(default, NONE);

Security Model Owned by SYS with EXECUTE granted to PUBLIC

Source {ORACLE_HOME}/rdbms/admin/dbmsbctab.sql
{ORACLE_HOME}/rdbms/admin/prvtbctab.plb

Subprograms

ADD_INTERVAL_PARTITIONING	IMPORT_DROPPED
BLOCKCHAIN_DML_NOREPL	IMPORT_EPOCH
COUNTERSIGN_ROW	RAISE_ORA_ERROR
COUNTERSIGN_ROW_SPECIFIED_BY_KEY_COLUMNS	SIGN_ROW
DELETE_EXPIRED_ROWS	SIGN_ROW_SPECIFIED_BY_KEY_COLUMNS
GET_BYTES_FOR_ROW_HASH	SIGN_ROW_SPECIFIED_BY_KEY_COLUMNS _WITH_COUNTERSIGNATURE
GET_BYTES_FOR_ROW_HASH_SPECIFIED_BY_KEY _COLUMNS	SIGN_ROW_WITH_COUNTERSIGNATURE
GET_BYTES_FOR_ROW_SIGNATURE	VERIFY_ROWS
GET_BYTES_FOR_ROW_SIGNATURE_SPECIFIED_BY _KEY_COLUMNS	VERIFY_TABLE_BLOCKCHAIN
GET_SIGNED_BLOCKCHAIN_DIGEST	VERIFY_USER_BLOCKCHAIN_ROWS
IMPORT_CHAIN

ADD_INTERVAL_PARTITIONING (new 23c)

Adds internal partitioning to an existing, non-partitioned, V1 or V2 blockchain table dbms_blockchain_table.add_interval_partitioning( schema_name IN VARCHAR2, table_name IN VARCHAR2, interval_number IN NUMBER, interval_frequency IN VARCHAR2, first_high_timestamp IN TIMESTAMP); PRAGMA SUPPLEMENTAL_LOG_DATA(add_interval_partitioning, AUTO_WITH_COMMIT);

exec dbms_blockchain_table.add_interval_partitioning(???

BLOCKCHAIN_DML_NOREPL (new 23c)

Undocumented dbms_blockchain_table.blockchain_dml_norepl; PRAGMA SUPPLEMENTAL_LOG_DATA(blockchain_dml_norepl, MANUAL);

exec dbms_blockchain_table.blockchain_dml_norepl;

COUNTERSIGN_ROW (new 23c)

Procures a countersignature on a specified row in a blockchain table dbms_blockchain_table.countersign_row( schema_name IN VARCHAR2, table_name IN VARCHAR2, instance_id IN NUMBER, chain_id IN NUMBER, sequence_id IN NUMBER, countersignature_algo IN NUMBER DEFAULT SIGN_ALGO_DEFAULT, countersignature_signed_bytes IN OUT BLOB, countersignature OUT RAW, countersignature_certificate_guid OUT RAW, countersignature_content_version IN VARCHAR2 DEFAULT 'V2_DIGEST'); PRAGMA SUPPLEMENTAL_LOG_DATA(countersign_row, AUTO_WITH_COMMIT);

TBD

COUNTERSIGN_ROW_SPECIFIED_BY_KEY_COLUMNS (new 23c)

Uses, at most, 3 user columns, to identify exactly one blockchain table row and procures a countersign for that row dbms_blockchain_table.countersign_row_specified_by_key_columns( schema_name IN VARCHAR2, table_name IN VARCHAR2, keycol1_name IN VARCHAR2, keycol1_value IN VARCHAR2, keycol2_name IN VARCHAR2 DEFAULT NULL, keycol2_value IN VARCHAR2 DEFAULT NULL, keycol3_name IN VARCHAR2 DEFAULT NULL, keycol3_value IN VARCHAR2 DEFAULT NULL, countersignature_algo IN NUMBER DEFAULT SIGN_ALGO_DEFAULT, countersignature_signed_bytes IN OUT BLOB, countersignature OUT RAW, countersignature_certificate_guid OUT RAW, countersignature_content_version IN VARCHAR2 DEFAULT 'V2_DIGEST'); PRAGMA SUPPLEMENTAL_LOG_DATA(countersign_row_specified_by_key_columns, AUTO_WITH_COMMIT);

TBD

DELETE_EXPIRED_ROWS

Deletes rows outside the retention window created before before_timestamp if the time stamp is specified; otherwise, deletes all rows outside the retention window. 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); PRAGMA SUPPLEMENTAL_LOG_DATA(delete_expired_rows, NONE);

DECLARE

       rowsDel NUMBER;

      BEGIN

        dbms_blockchain_table.delete_expired_rows('UWCLASS', 'LEDGER', NULL, rowsDel);

        dbms_output.put_line(TO_CHAR(rowsDel) || ' rows deleted');

      END;

      /

      8 rows deleted

GET_BYTES_FOR_ROW_HASH (new 23c parameter)

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. 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, chain_name IN VARCHAR2 DEFAULT NULL); PRAGMA SUPPLEMENTAL_LOG_DATA(get_bytes_for_row_hash, READ_ONLY);

TBD

GET_BYTES_FOR_ROW_HASH_SPECIFIED_BY_KEY_COLUMNS (new 23c)

Uses at most 3 columns to uniquely identify a row dbms_blockchain_table.get_bytes_for_row_hash_specified_by_key_columns( schema_name IN VARCHAR2, table_name IN VARCHAR2, data_format IN NUMBER, row_data IN OUT BLOB, chain_name IN VARCHAR2 DEFAULT NULL, keycol1_name IN VARCHAR2, keycol1_value IN VARCHAR2, keycol2_name IN VARCHAR2 DEFAULT NULL, keycol2_value IN VARCHAR2 DEFAULT NULL, keycol3_name IN VARCHAR2 DEFAULT NULL, keycol3_value IN VARCHAR2 DEFAULT NULL); PRAGMA SUPPLEMENTAL_LOG_DATA(get_bytes_for_row_hash_specified_by_key_columns, READ_ONLY);

TBD

GET_BYTES_FOR_ROW_SIGNATURE

Bytes returned are the bytes in the row hash. No metadata is included. 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); PRAGMA SUPPLEMENTAL_LOG_DATA(get_bytes_for_row_hash, READ_ONLY);

GET_BYTES_FOR_ROW_SIGNATURE_SPECIFIED_BY _KEY_COLUMNS (new 23c)

Uses at most 3 columns to uniquely identify a row dbms_blockchain_table.get_bytes_for_row_signature_specified_by_key_columns( schema_name IN VARCHAR2, table_name IN VARCHAR2, data_format IN NUMBER, row_data IN OUT BLOB, keycol1_name IN VARCHAR2, keycol1_value IN VARCHAR2, keycol2_name IN VARCHAR2 DEFAULT NULL, keycol2_value IN VARCHAR2 DEFAULT NULL, keycol3_name IN VARCHAR2 DEFAULT NULL, keycol3_value IN VARCHAR2 DEFAULT NULL); PRAGMA SUPPLEMENTAL_LOG_DATA(get_bytes_for_row_signature_specified_by_key_columns, READ_ONLY);

TBD

GET_SIGNED_BLOCKCHAIN_DIGEST (new 23c parameters)

Generates the signed digest for a specified blockchain table using the table owner's private key stored in the database wallet dbms_blockchain_table.get_signed_blockchain_digest( schema_name IN VARCHAR2, table_name IN VARCHAR2, signed_bytes IN OUT BLOB, signed_rows_indexes OUT ORABCTAB_ROW_ARRAY_T, schema_certificate_guid OUT RAW, signature_algo IN NUMBER DEFAULT SIGN_ALGO_DEFAULT row_filter IN VARCHAR2 DEFAULT NULL, signed_content_version IN VARCHAR2 DEFAULT 'V1_DIGEST') RETURN RAW; PRAGMA SUPPLEMENTAL_LOG_DATA(get_signed_blockchain_digest, READ_ONLY);

TBD

IMPORT_CHAIN (new 23c parameters)

Undocumented dbms_blockchain_table.import_chain( schema_name IN VARCHAR2, table_name IN VARCHAR2, instance_id IN NUMBER, chain_id IN NUMBER, epoch_num IN NUMBER, hash IN RAW, min_seqnum IN NUMBER, max_seqnum IN NUMBER, last_ctime IN TIMESTAMP WITH TIME ZONE DEFAULT SYSTIMESTAMP, pdb_guid IN RAW DEFAULT NULL); PRAGMA SUPPLEMENTAL_LOG_DATA(import_chain, AUTO_WITH_COMMIT);

TBD

IMPORT_DROPPED (new 23c)

Undocumented dbms_blockchain_table.import_dropped( schema_name IN VARCHAR2, table_name IN VARCHAR2, drop_time IN TIMESTAMP WITH TIME ZONE); PRAGMA SUPPLEMENTAL_LOG_DATA(import_dropped, AUTO_WITH_COMMIT);

TBD

IMPORT_EPOCH (new 23c parameters)

Undocumented dbms_blockchain_table.import_epoch( schema_name IN VARCHAR2, table_name IN VARCHAR2, epoch_num IN NUMBER, reason_id IN NUMBER, pdb_guid IN RAW, hash_algo IN NUMBER, hash_format IN NUMBER, is_last_epoch IN BOOLEAN DEFAULT FALSE, col_pos_max IN NUMBER DEFAULT NULL, col_pos_vec IN RAW DEFAULT NULL); PRAGMA SUPPLEMENTAL_LOG_DATA(import_epoch, AUTO_WITH_COMMIT););

TBD

RAISE_ORA_ERROR (new 23c)

Undocumented dbms_blockchain_table.raise_ora_error( error_code IN PLS_INTEGER, error_arg_1 IN VARCHAR2 DEFAULT NULL, error_arg_2 IN VARCHAR2 DEFAULT NULL, error_arg_3 IN VARCHAR2 DEFAULT NULL, error_arg_4 IN VARCHAR2 DEFAULT NULL, error_arg_5 IN VARCHAR2 DEFAULT NULL); PRAGMA SUPPLEMENTAL_LOG_DATA(raise_ora_error, READ_ONLY);

TBD

SIGN_ROW (new 23c parameter)

Provides 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. dbms_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, delegate IN BOOLEAN DEFAULT FALSE); PRAGMA SUPPLEMENTAL_LOG_DATA(sign_row, AUTO_WITH_COMMIT);

TBD

SIGN_ROW_SPECIFIED_BY_KEY_COLUMNS (new 23c)

Allows the current user to provide a signature on the row content of a previously inserted row dbms_blockchain_table.sign_row_specified_by_key_columns( schema_name IN VARCHAR2, table_name IN VARCHAR2, hash IN RAW DEFAULT NULL, signature IN RAW, certificate_guid IN RAW, signature_algo IN NUMBER, delegate IN BOOLEAN DEFAULT FALSE, keycol1_name IN VARCHAR2, keycol1_value IN VARCHAR2, keycol2_name IN VARCHAR2 DEFAULT NULL, keycol2_value IN VARCHAR2 DEFAULT NULL, keycol3_name IN VARCHAR2 DEFAULT NULL, keycol3_value IN VARCHAR2 DEFAULT NULL); PRAGMA SUPPLEMENTAL_LOG_DATA(sign_row_specified_by_key_columns, AUTO_WITH_COMMIT);

TBD

SIGN_ROW_SPECIFIED_BY_KEY_COLUMNS_WITH_COUNTERSIGNATURE (new 23c)

Uses at most 3 user columns names and values to uniquely identify a single row to sign and countersign dbms_blockchain_table.sign_row_specified_by_key_columns_with_countersignature( schema_name IN VARCHAR2, table_name IN VARCHAR2, hash IN RAW DEFAULT NULL, signature IN RAW, certificate_guid IN RAW, signature_algo IN NUMBER, delegate IN BOOLEAN DEFAULT FALSE, keycol1_name IN VARCHAR2, keycol1_value IN VARCHAR2, keycol2_name IN VARCHAR2 DEFAULT NULL, keycol2_value IN VARCHAR2 DEFAULT NULL, keycol3_name IN VARCHAR2 DEFAULT NULL, keycol3_value IN VARCHAR2 DEFAULT NULL, countersignature_algo IN NUMBER DEFAULT SIGN_ALGO_DEFAULT, countersignature_signed_bytes IN OUT BLOB, countersignature OUT RAW, countersignature_certificate_guid OUT RAW, countersignature_content_version IN VARCHAR2 DEFAULT 'V2_DIGEST'); PRAGMA SUPPLEMENTAL_LOG_DATA(sign_row_specified_by_key_columns_with_countersignature, AUTO_WITH_COMMIT);

TBD

SIGN_ROW_WITH_COUNTERSIGNATURE (new 23c)

Allows a user to request a countersignature fro the database dbms_blockchain_table.sign_row_with_countersignature( 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, delegate IN BOOLEAN DEFAULT FALSE, countersignature_algo IN NUMBER DEFAULT SIGN_ALGO_DEFAULT, countersignature_signed_bytes IN OUT BLOB, countersignature OUT RAW, countersignature_certificate_guid OUT RAW, countersignature_content_version IN VARCHAR2 DEFAULT 'V2_DIGEST'); PRAGMA SUPPLEMENTAL_LOG_DATA(sign_row_with_countersignature, AUTO_WITH_COMMIT);

TBD

VERIFY_ROWS

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. Optionally verifies row signatures. 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);

TBD

VERIFY_TABLE_BLOCKCHAIN

Verifies all rows with creation times between the minimum value for the row creation time from signed_buffer_previous and the maximum valuefor signed_buffer_latest and returns the number of successfully verified rows dbms_blockchain_table.verify_table_blockchain( signed_bytes_latest IN BLOB, signed_bytes_previous IN BLOB, number_of_rows_verified OUT NUMBER); PRAGMA SUPPLEMENTAL_LOG_DATA(verify_table_blockchain, READ_ONLY);

TBD

VERIFY_USER_BLOCKCHAIN_ROWS (new 23c)

Verifies row of one or more user chains when the user chains feature is enabled on the blockchain table dbms_blockchain_table.verify_user_blockchain_rows( schema_name IN VARCHAR2, table_name IN VARCHAR2, row_version_name IN VARCHAR2, number_of_rows_verified OUT NUMBER, keycol1_value IN VARCHAR2 DEFAULT NULL, keycol2_value IN VARCHAR2 DEFAULT NULL, keycol3_value IN VARCHAR2 DEFAULT NULL, low_timestamp IN TIMESTAMP WITH TIME ZONE DEFAULT NULL, high_timestamp IN TIMESTAMP WITH TIME ZONE DEFAULT NULL, verify_signature IN BOOLEAN DEFAULT TRUE); PRAGMA SUPPLEMENTAL_LOG_DATA(verify_user_blockchain_rows, READ_ONLY);

TBD