9.4 BLOCKCHAIN_TABLE

This command uses an extension of the DBMS_BLOCKCHAIN_TABLE PL/SQL package. A blockchain table is an append-only table designed for centralized blockchain applications. V2 blockchain tables support schema evolution, delegate signatures, and countersignatures in addition to the functionality found in V1 blockchain tables. Blockchain tables support only DER encoding for X.509 certificates, not PEM encoding.

Syntax

blockchain_table|bl [add_interval_partitioning|addintpartition] |
[countersign_row|countersign] | desc | [delete_expired_rows|del] |
get_bytes_for_row_hash | get_bytes_for_row_signature | get_digest |
get_signed_digest | [sign_row|sign] |
[sign_row_with_countersignature|signandcountersign] | verify_rows | verify_table | verify_user_chains

The following table provides a description of each sub-command.

Sub-Command Description
add_interval_partitioning | addintpartition Adds interval partitioning to an existing, non-partitioned, V1 or V2 blockchain table.
countersign_row | countersign Procures a countersignature on a specified row in a blockchain table.The countersignature is produced by signing the row data content using the table owner's private key stored in the database wallet.
desc Describes the details of the blockchain table.
delete_expired_rows | del Deletes some or all expired rows in the blockchain table.
get_bytes_for_row_hash Returns in row_data the bytes for the particular row identified (a series of meta-data-value, column-data-value pairs in column position order) followed by the hash for the previous row in the chain in the data format specified.
get_bytes_for_row_signature Returns the bytes used to compute a user signature, a delegate signature, or a countersignature. For a user signature or a delegate signature, the command returns in row_data the bytes in the hash in the row without any metadata.
get_digest Generates and returns a cryptographic hash of the digest for user-specified rows or for a specified blockchain table.
get_signed_digest Generates and returns the signed digest for user-specified rows (if specified) in a blockchain table using the table owner's private key stored in the database wallet. The particular rows in the digest are specified by the selector parameter. The default value of this field is NULL. The signed_bytes, signed_row_indexes, and schema_certificate_guid are also returned.
sign_row | sign Provides a signature by the current user on the row content of a previously inserted row.
sign_row_with_countersignature | signandcountersign Enables the user to request a countersignature from the database. The countersignature is produced by signing the row data content using the table owner's private key stored in the database wallet. This command is an extension of the SIGN_ROW command
verify_rows Verifies all rows on all applicable system chains for the integrity of the hash column value for rows created in the range of low_timestamp to high_timestamp. Row signatures can be verified as an option.
verify_table Verifies all rows whose creation-time falls between the minimum value for the row-creation time from begin_bytes_file and the maximum value for row-creation time from end_bytes_file, and returns the number of successfully verified rows.
verify_user_chains Verifies rows of one or more user chains when the user chains feature is enabled on the blockchain table.

Example

The following example shows different ways to select rows for operations in a blockchain table.

  • Use one of the following commands to select a row using positional parameters (instance id, chain id, sequence id).

    bl countersign_row -tab <TABLE_NAME> -inst <INSTANCE_ID> -ch <CHAIN_ID> -seq  <SEQUENCE_ID> -countersignalgo <ALGORITHM> -cscg <CERTIFICATE_GUID> -bytes_file  <PATH_TO_FILE>/countersignBytes.txt -csig <COUNTERSIGNATURE_VARIABLE>

    bl get_bytes_for_row_hash -tab <TABLE_NAME> -inst <INSTANCE_ID> -ch <CHAIN_ID> -seq <SEQUENCE_ID> -uchain <USER_CHAIN>
    bl get_bytes_for_row_signature -tab <TABLE_NAME> -inst  <INSTANCE_ID> -ch <CHAIN_ID> -seq <SEQUENCE_ID> -row_data_file <PATH_TO_FILE>/test2.txt
    bl sign_row -tab <TABLE_NAME> -inst <INSTANCE_ID> -ch <CHAIN_ID> -seq <SEQUENCE_ID> -row_hash <ROW_HASH> -signature_file <PATH_TO_FILE>/u1r1_sign.dat -cert_guid <CERTIFICATE_GUID> -algo <ALGORITHM>
    bl signandcountersign -tab <TABLE_NAME> -inst <INSTANCE_ID> -ch <CHAIN_ID> -seq <SEQUENCE_ID> -row_hash <ROW_HASH> -signature_file <PATH_TO_FILE>/u1r11_sign.dat -cert_guid <CERTIFICATE_GUID> -algo <ALGORITHM>

  • Use one of the following commands to select a row using key column parameters.

    blockchain_table countersign_row -tab <TABLE_NAME> -kc1name <COLUMN_NAME> -kc1val <COLUMN_VALUE> -countersignalgo <ALGORITHM> -cscg <CERTIFICATE_GUID> -bytes_file <PATH_TO_FILE>/countersignBytes.txt -csig <COUNTERSIGNATURE_VARIABLE>
    bl get_bytes_for_row_hash -table_name <TABLE_NAME> -kc1name <COLUMN_NAME> -kc1val <COLUMN_VALUE>
    bl get_bytes_for_row_signature -tab <TABLE_NAME> -kc1name <COLUMN_NAME> -kc1val <COLUMN_VALUE> -row_data_file <PATH_TO_FILE>/u1r1.dat
    bl sign_row -tab <TABLE_NAME> -kc1name <COLUMN_NAME> -kc1val <COLUMN_VALUE> -signature_file <PATH_TO_FILE>/u1r2_sign.dat -cert_guid <CERTIFICATE_GUID> -algo <ALGORITHM>
    bl signandcountersign -tab  <TABLE_NAME> -kc1name <COLUMN_NAME> -kc1val <COLUMN_VALUE> -signature_file <PATH_TO_FILE>/u1r12_sign.dat -cert_guid <CERTIFICATE_GUID> -algo <ALGORITHM>

9.4.1 add_interval_partitioning

Adds interval partitioning to an existing, non-partitioned, V1 or V2 blockchain table.

Syntax

blockchain_table|bl add_interval_partitioning|addintpartition {OPTIONS}

Options

Option Description
Required
-table_name|-tab <table_name> Specifies a name for the blockchain table. The name can be preceded by the respective schema name. To specify a case-sensitive schema or table name, enclose the entire name in double quotes and then enclose the individual names in double, double quotes.

Note:

Case sensitive, embedded white space table and schema name should be entered as """Alp_ha"".""Be $a""". The parser parses these values as "Alp_ha"."Be $a".
-interval_number|-intnum <interval_number> Specifies the interval number that determines how often the database creates partitions for the blockchain table.
-interval_frequency|-intfreq <interval_frequency> Specifies the interval frequency that determines how frequently the database creates partitions for blockchain tables within a specified interval set by the interval_number setting. Supported values are YEAR, MONTH, DAY, HOUR, MINUTE.
-first_high_timestamp|-firsthigh <first_high_timestamp> Specifies a timestamp that determines the upper boundary of the first partition in the blockchain table.

Example

Partition the BCTAB1 blockchain table with a daily frequency, beginning on January 23, 2024, at 12:47:29. 

bl addintpartition -tab "u1.""bctab1""" -intnum 1 -intfreq "DAY" -firsthigh "23-JAN-24 12.47.29.182463000"

9.4.2 countersign_row

Procures a countersignature on a specified row in a blockchain table.The countersignature is produced by signing the row data content using the table owner's private key stored in the database wallet.

Syntax

blockchain_table|bl countersign_row|countersign {OPTIONS}

Options

Option Description
Required
-table_name|-tab <table_name> Specifies a name for the blockchain table. The name can be preceded by the respective schema name. To specify a case-sensitive schema or table name, enclose the entire name in double quotes and then enclose the individual names in double, double quotes.

Note:

Case-sensitive, embedded white space, table and schema name must be entered as """Alp_ha"".""Be $a""". The parser parses these values as "Alp_ha"."Be $a".
-instance_id|-inst <instance_id> Specifies the instance that inserted the row. If specified, this limits the operation to rows inserted by the given instance.
-chain_id|-ch <chain_id> Specifies the chain containing the row. If specified, this limits operation to rows assigned to the specified chain. By default, there are 32 chains in each instance, and they are numbered from 0 to 31.
-sequence_id|-seq <sequence_id> Specifies the position of the row on the specified chain.
Optional
-keycol1_name|-kc1name <keycol1_name> Specifies the name of the key column.
-keycol1_value|-kc1val <keycol1_value> Specifies the value of the key column.
-keycol2_name|-kc2name <keycol2_name> Specifies the name of the second column in a composite key.
-keycol2_value|-kc2val <keycol2_value> Specifies the value of the second column in a composite key.
-keycol3_name|-kc3name <keycol3_name> Specifies the name of the third column in a composite key.
-keycol3_value|-kc3val <keycol3_value> Specifies the value of the third column in a composite key.
-countersignature_algorithm|-countersignalgo <countersignature_algorithm> Specifies the cryptographic hash algorithm to use for the countersignature. The default value of this field is DBMS_BLOCKCHAIN_TABLE.SIGN_ALGO_DEFAULT. If specified, the parameter must be one of the following acceptable string constants:
  • RSA_SHA2_256
  • RSA_SHA2_384
  • RSA_SHA2_512
-bytes_file <bytes_file> Specifies the name of the file that contains the signed countersignature bytes.
-countersignature|-csig <countersignature> Specifies the digital signature on the bytes returned in bytes_file. This is an OUT parameter option, used for storing the output value of the command in a bind variable.
-countersign_cert_guid|-cscg <countersign_cert_guid> Specifies a unique identifier for the certificate of the blockchain table owner stored in the database that may be used to verify the countersignature. This is an OUT parameter option, used for storing the output value of the command in a bind variable.
-content_version <content_version> ('V2_DIGEST') Specifies the version of the data contents and layout that are used as input to the countersignature algorithm. Only V2_DIGEST is supported in this release.

Example

Countersign the row in the BCTAB1 blockchain table where COL1 equals 200. 

bl countersign -tab u1.bctab1 -kc1name "COL1" -kc1val "200"

9.4.3 desc

Describes the details of the blockchain table.

Syntax

blockchain_table|bl desc {OPTIONS}

Option

Option Description
Required
-table_name|-tab <table_name> Specifies a name for the blockchain table. The name can be preceded by the respective schema name. To specify a case-sensitive schema or table name, enclose the entire name in double quotes and then enclose the individual names in double, double quotes.

Note:

Case-sensitive, embedded white space, table and schema name should be entered as"""Alp_ha"".""Be $a""". The parser parses these values as "Alp_ha"."Be $a".

9.4.4 delete_expired_rows

Deletes some or all expired rows in the blockchain table.

Syntax

blockchain_table|bl delete_expired_rows|del {OPTIONS}

Options

Option Description
Required
-table_name|-tab <table_name> Specifies a name for the blockchain table. The name can be preceded by the respective schema name. To specify a case-sensitive schema or table name, enclose the entire name in double quotes and then enclose the individual names in double, double quotes.

Note:

Case-sensitive, embedded white space, table and schema name should be entered as:"""Alp_ha"".""Be $a""". The parser parses these values as: "Alp_ha"."Be $a".
Optional
-before_timestamp|-before <before_timestamp> Specifies a timestamp to determine deletion of expired rows. Set this parameter according to the NLS_TIMESTAMP_FORMAT or NLS_TIMESTAMP_TZ_FORMAT. The default value for this parameter is NULL.
  • If the parameter value is NULL, all expired rows in the table are deleted.
  • If the parameter value is not NULL and is older than the timestamp calculated based on the current time and the row retention period, then rows with timestamps earlier than the parameter value are deleted.
  • If the parameter value is not NULL and is more recent than the timestamp calculated based on the current time and row retention period, the calculated timestamp is used, resulting in the deletion of all expired rows.
-rowcount <rowcount> Requests the number of rows deleted. This is an OUT parameter option, used for storing the output value of the command in a bind variable.

Example

Delete expired rows from the BCTAB1 blockchain table where expiry occurred before January 23, 2024, at 11:00 AM, and store the number of deleted rows in the bind variable row_count. 

blockchain_table delete_expired_rows -table_name """bcta _b1""" -before_timestamp 23-JAN-23 11.00.00 AM -rowcount ":count1"

9.4.5 get_bytes_for_row_hash

Returns in row_data the bytes for the particular row identified (a series of meta-data-value, column-data-value pairs in column position order) followed by the hash for the previous row in the chain in the data format specified.

Syntax

blockchain_table|bl get_bytes_for_row_hash {OPTIONS}

Options

Option Description
Required
-table_name|-tab <table_name> Specifies a name for the blockchain table. The name can be preceded by the respective schema name. To specify a case-sensitive schema or table name, enclose the entire name in double quotes and then enclose the individual names in double, double quotes.

Note:

Case-sensitive, embedded white space, table and schema name should be entered as:"""Alp_ha"".""Be $a""". The parser parses these values as: "Alp_ha"."Be $a".
-instance_id|-inst <instance_id> Specifies the instance that inserted the row. If specified, this limits the operation to rows inserted by the given instance.
-chain_id|-ch <chain_id> Specifies the chain containing the row. If specified, this limits operation to rows assigned to the specified chain. By default, there are 32 chains in each instance, and they are numbered from 0 to 31.
-sequence_id|-seq <sequence_id> Specifies the position of the row on the specified chain.
Optional
-data_format|-df <data_format> Specifies the version of the data layout for the hash in the specified row. Must be 1 in this release. By default, the value of this option is set as 1.
-row_data_file <row_data_file> Specifies the file in which the generated row_data_bytes are saved.
-user_chain|-uchain <user_chain> Specifies the name of the user chain when the bytes for the cryptographic hash on the user chain are desired. If the option is not specified, the system chain's cryptographic hash bytes are assumed as the desired output.
-keycol1_name|-kc1name <keycol1_name> Specifies the name of the key column.
-keycol1_value|-kc1val <keycol1_value> Specifies the value of the key column.
-keycol2_name|-kc2name <keycol2_name> Specifies the name of the second column in a composite key.
-keycol2_value|-kc2val <keycol2_value> Specifies the value of the second column in a composite key.
-keycol3_name|-kc3name <keycol3_name> Specifies the name of the third column in a composite key.
-keycol3_value|-kc3val <keycol3_value> Specifies the value of the third column in a composite key.
-pdb_guid <pdb_guid> Specifies the identifier of the pluggable database that inserted the row for a V2 blockchain table. If specified, restricts attention to system chains inserted by the specified pluggable database. It must be NULL for a V1 blockchain table.

Examples

Retrieve the row hash bytes for the row in the BCTAB1 blockchain table identified by INSTANCE_ID = 1, CHAIN_ID = 27, and SEQUENCE_ID = 2. 

blockchain_table get_bytes_for_row_hash -tab u1.bcTAB1 -inst 1 -ch 27 -seq 2

9.4.6 get_bytes_for_row_signature

Returns the bytes used to compute a user signature, a delegate signature, or a countersignature.

For a user signature or a delegate signature, the command 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. For a countersignature, the routine returns in row_data the bytes that are input to the digital signature algorithm that computes a countersignature on the row.

Syntax

blockchain_table|bl get_bytes_for_row_signature {OPTIONS}

Options

Option Description
Required
-table_name|-tab [table_name] Specifies a name for the blockchain table. The name can be preceded by the respective schema name. To specify a case-sensitive schema or table name, enclose the entire name in double quotes and then enclose the individual names in double, double quotes.

Note:

Case-sensitive, embedded white space, table and schema name should be entered as:"""Alp_ha"".""Be $a""". The parser parses these values as: "Alp_ha"."Be $a".
One of the following: instance_id or chain_id and sequence_id or keycol1_name and keycol1_value
-instance_id|-inst [instance_id] Specifies the instance that inserted the row. If specified, this limits the operation to rows inserted by the given instance.
   
-chain_id|-ch [chain_id] Specifies the chain containing the row. If specified, this limits operation to rows assigned to the specified chain. By default, there are 32 chains in each instance, and they are numbered from 0 to 31.
-sequence_id|-seq <sequence_id> Specifies the position of the row on the specified chain.
-keycol1_name|-kc1name <keycol1_name> Specifies the name of the key column.
-keycol1_value|-kc1val <keycol1_value> Specifies the value of the key column.
Optional
-data_format|-df <data_format> Specifies the version of the data layout for the hash in the specified row. Must be 1 in this release. By default, the value of this option is set as 1.
-row_data_file <row_data_file> Specifies the file in which the generated row_data_bytes are saved.
-keycol2_name|-kc2name <keycol2_name> Specifies the name of the second column in a composite key.
-keycol2_value|-kc2val <keycol2_value> Specifies the value of the second column in a composite key.
-keycol3_name|-kc3name <keycol3_name> Specifies the name of the third column in a composite key.
-keycol3_value|-kc3val <keycol3_value> Specifies the value of the third column in a composite key.
-pdb_guid <pdb_guid> Specifies the identifier of the pluggable database that inserted the row for a V2 blockchain table. If specified, restricts attention to system chains inserted by the specified pluggable database. It must be NULL for a V1 blockchain table.
-type <type> The valid values are USER, DELEGATE, and COUNTERSIGNATURE. DELEGATE and USER may be used interchangeably. The default value is USER.

Examples

Retrieve the row signature bytes for the row in the BCTAB1 blockchain table where COL1 = 100, and store the output in the row data file u1r1.dat. 

bl get_bytes_for_row_signature -tab u1.bctab1 -kc1name "COL1" -kc1val "100" -row_data_file <PATH_TO_FILE>/u1r1.dat

9.4.7 get_digest

Generates and returns a cryptographic hash of the digest for user-specified rows or for a specified blockchain table.

Syntax

blockchain_table|bl get_digest {OPTIONS}

Options

Option Description
Required
-table_name|-tab <table_name> Specifies a name for the blockchain table. The name can be preceded by the respective schema name. To specify a case-sensitive schema or table name, enclose the entire name in double quotes and then enclose the individual names in double, double quotes.

Note:

Case sensitive, embedded white space table and schema name should be entered as """Alp_ha"".""Be $a""". The parser parses these values as "Alp_ha"."Be $a".
-digest <digest> Specifies the output variable containing the hexadecimal representation of the digest. This is an OUT parameter option, used for storing the output value of the command in a bind variable.
Optional
-selector <selector> Specifies the condition: A WHERE clause without the WHERE keyword.
-selector_file <selector_file> Specifies the name of the file which stores the condition, a WHERE clause without the WHERE keyword.
-bytes_file <bytes_file> Specifies the name of the file that contains the digest.
-digest_file <digest_file> Specifies the name of the file that contains the binary representation of the digest. The digest value contains a header followed by an array of row-info.
-row_data_file <row_data_file> Specifies the file in which the generated row_data_bytes are saved.
-row_indexes_file <row_indexes_file> Specifies the name of the file that contains the JSON representation of the type ORABCTAB_ROW_ARRAY_T.
-algorithm|-algo <algorithm> Specifies the cryptographic hash algorithm to use. The parameter must be one of the following constants:
  • SHA2_256
  • SHA2_384
  • SHA2_512

Examples

Generate a digest for the rows in the BCTAB1 blockchain table where COL1 = 100 or COL1 = 400, and store the digest in the file dig.txt. 

blockchain_table get_digest -tab u1.bctab1 -selector "COL1=100 OR COL1=400" -digest_file <PATH_TO_FILE>/dig.txt

9.4.8 get_signed_digest

Generates and returns the signed digest for user-specified rows (if specified) in a blockchain table using the table owner's private key stored in the database wallet.

The particular rows in the digest are specified by the selector parameter. The default value of this field is NULL. The signed_bytes, signed_row_indexes, and schema_certificate_guid are also returned.

Syntax

blockchain_table|bl get_signed_digest {OPTIONS}

Options

Option Description
Required
-table_name|-tab <table_name> Specifies a name for the blockchain table. The name can be preceded by the respective schema name. To specify a case-sensitive schema or table name, enclose the entire name in double quotes and then enclose the individual names in double, double quotes.

Note:

Case sensitive, embedded white space table and schema name should be entered as """Alp_ha"".""Be $a""". The parser parses these values as "Alp_ha"."Be $a".
-digest <digest> Specifies the output variable that contains the hexadecimal representation of the signed digest. This is an OUT parameter option, used for storing the output value of the command in a bind variable.
-digest_file <digest_file> Specifies the name of the file that contains the binary representation of the signed digest.
Optional
-selector <selector> Specifies the condition: A WHERE clause without the WHERE keyword.
-selector_file <selector_file> Specifies the name of the file which stores the condition, a WHERE clause without the WHERE keyword.
-bytes_file <bytes_file> Specifies the name of the file that contains the digest that is signed.
-row_data_file <row_data_file> Specifies the file in which the generated row_data_bytes are saved.
-row_indexes_file <row_indexes_file> Specifies the name of the file that contains the JSON representation of the type ORABCTAB_ROW_ARRAY_T.
-cert_guid|-cg <cert_guid> Specifies the Global Unique Identifier (GUID) for the certificate. This is an OUT parameter option, used for storing the output value of the command in a bind variable.
-algorithm|-algo <algorithm> Specifies the cryptographic hash algorithm to use. The default value of this field is DBMS_BLOCKCHAIN_TABLE.SIGN_ALGO_DEFAULT. If specified, the parameter must be one of the following package constants:
  • RSA_SHA2_256
  • RSA_SHA2_384
  • RSA_SHA2_512

Examples

Generate a signed digest for the BCTAB1 blockchain table and store the digest in the bind variable hex. 

blockchain_table get_signed_digest -table_name u1.bctab1 -digest ":hex"

9.4.9 sign_row

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

Syntax

blockchain_table|bl sign_row|sign {OPTIONS}

Options

Option Description
Required
-table_name|-tab <table_name> Specifies a name for the blockchain table. The name can be preceded by the respective schema name. To specify a case-sensitive schema or table name, enclose the entire name in double quotes and then enclose the individual names in double, double quotes.

Note:

Case sensitive, embedded white space table and schema name should be entered as """Alp_ha"".""Be $a""". The parser parses these values as: "Alp_ha"."Be $a".
-cert_guid|-cg <cert_guid> Specifies the Global Unique Identifier (GUID) for the certificate.
-instance_id|-inst <instance_id> Specifies the instance that inserted the row. If specified, this limits the operation to rows inserted by the given instance.
-chain_id|-ch <chain_id> Specifies the chain containing the row. If specified, this limits operation to rows assigned to the specified chain. By default, there are 32 chains in each instance, and they are numbered from 0 to 31.
-sequence_id|-seq <sequence_id> Specifies the position of the row on the specified chain.
-signature|-sig <signature> Specifies the bind variable which holds the hexadecimal representation of the user's digital signature on the hash value stored in the row.
-algorithm|-algo <algorithm> Specifies the algorithm used to create the digital signature. The parameter must be one of the following acceptable string constants:
  • RSA_SHA2_256
  • RSA_SHA2_384
  • RSA_SHA2_512
Optional
-row_hash <row_hash> Specifies the expected value of the hash in the row to be signed, if non-Null. If NULL, the hash in the row to be signed is not checked. Default value is NULL.
-signature_file <signature_file> Specifies the file name which holds the binary representation of the user's digital signature on the hash value stored in the row.
-private_key_for_signature|-prvtkey <private_key_for_signature> Specifies the private key file used to generate a signature using the signature bytes for a particular row. The generated signature is further used for signing a particular row. The private key is cleared out from memory upon signature generation.
-wallet_path|-wallet <wallet_path> Specifies the location of the Wallet. If `private_key_for_signature` is not provided, the private key is extracted and used from the wallet at the specified path.
-wallet_password|-walletpw <wallet_password> Specifies the password for accessing the local wallet. The password is cleared upon use and is not retained. If a local wallet password is not provided, a prompt appears for interactive entry.
-wallet_private_key_alias|-walletpvtkeyalias <wallet_private_key_alias> Specifies the private key for signature generation. If not specified, the system tries to retrieve the private key associated with the provided CERT_GUID from the local wallet.
-wallet_private_key_password|-walletpvtkeypw <wallet_private_key_password> Specifies the password required to access the private key stored in the specified wallet_path.
-keycol1_name|-kc1name <keycol1_name> Specifies the name of the key column.
-keycol1_value|-kc1val <keycol1_value> Specifies the value of the key column.
-keycol2_name|-kc2name <keycol2_name> Specifies the name of the second column in a composite key.
-keycol2_value|-kc2val <keycol2_value> Specifies the value of the second column in a composite key.
-keycol3_name|-kc3name <keycol3_name> Specifies the name of the third column in a composite key.
-keycol3_value|-kc3val <keycol3_value> Specifies the value of the third column in a composite key.
-data_format|-df <data_format> Specifies the version of the data layout for the hash in the specified row. Must be 1 in this release. By default, the value of this option is set as 1.
-pdb_guid <pdb_guid> Specifies the identifier of the pluggable database that inserted the row for a V2 blockchain table. It must be NULL for a V1 blockchain table.
-type <type> The valid values for type are USER and DELEGATE. These values may be used interchangeably. The default value is USER.

Examples

Sign the row in the BCTAB1 blockchain table identified by the bind variables INSTANCE_ID, CHAIN_ID, and SEQUENCE_ID, using the certificate ID in the bind variable CERTID, the row hash in ROWHASH, and the RSA_SHA2_512 algorithm. 

blockchain_table sign_row -tab u1.bctab1 -inst ":instid" -ch ":chainid" -seq ":seqid" -row_hash  ":rowhash" -sig ":signature" -cg ":certid" -algo  "RSA_SHA2_512"

9.4.10 sign_row_with_countersignature

Enables the user to request a countersignature from the database.

This command is an extension of the SIGN_ROW command. The countersignature is produced by signing the row data content using the table owner's private key stored in the database wallet. A row in a blockchain table can be countersigned only if the row belongs to the current epoch for the blockchain table.

Syntax

blockchain_table|bl sign_row_with_countersignature|signandcountersign {OPTIONS}

Options

Option Description
Required
-table_name|-tab <table_name> Specifies a name for the blockchain table. The name can be preceded by the respective schema name. To specify a case-sensitive schema or table name, enclose the entire name in double quotes and then enclose the individual names in double, double quotes.

Note:

Case sensitive, embedded white space table and schema name should be entered as """Alp_ha"".""Be $a""". The parser parses these values as "Alp_ha"."Be $a".
One of the following is required: instance_id, or chain_id and sequence_id, or keycol1_name and keycol1_value
-instance_id|-inst <instance_id> Specifies the instance that inserted the row. If specified, this limits the operation to rows inserted by the given instance.
-chain_id|-ch <chain_id> Specifies the chain containing the row. If specified, this limits operation to rows assigned to the specified chain. By default, there are 32 chains in each instance, and they are numbered from 0 to 31.
-sequence_id|-seq <sequence_id> Specifies the position of the row on the specified chain.
-keycol1_name|-kc1name <keycol1_name> Specifies the name of the key column.
-keycol1_value|-kc1val <keycol1_value> Specifies the value of the key column.
One of the following: signature, or signature_file, or private_key_for_signature, or wallet_path
-signature|-sig <signature> Specifies the bind variable which holds the hexadecimal representation of the user's digital signature on the hash value stored in the row.
-signature_file <signature_file> Specifies the file name which holds the binary representation of the user's digital signature on the hash value stored in the row.
-private_key_for_signature|-prvtkey <private_key_for_signature> Specifies the private key file used to generate signature using the signature bytes for a particular row. The generated signature is further used for signing a particular row. The private key is cleared out from memory upon signature generation.
-wallet_path|-wallet <wallet_path> Specifies the location of the local wallet.
-cert_guid|-cg <cert_guid> Specifies the Global Unique Identifier (GUID) for the certificate.
-algorithm|-algo <algorithm> Specifies the algorithm used to create the digital signature. The parameter must be one of the following acceptable string constants:
  • RSA_SHA2_256
  • RSA_SHA2_384
  • RSA_SHA2_512
Optional
-row_hash <row_hash> Specifies the expected value of the hash in the row to be signed, if non-Null. If NULL, the hash in the row to be signed is not checked. Default value is NULL.
-wallet_password|-walletpw <wallet_password> Specifies the password for accessing the local wallet. The password is cleared upon use and is not retained. If a local wallet password is not provided, a prompt appears for interactive entry.
-wallet_private_key_alias|-walletpvtkeyalias <wallet_private_key_alias> Specifies the private key for signature generation. If not specified, the system tries to retrieve the private key associated with the provided CERT_GUID from the local wallet.
-wallet_private_key_password|-walletpvtkeypw <wallet_private_key_password> Specifies the password required to access the private key stored in the specified wallet_path.
-keycol2_name|-kc2name <keycol2_name> Specifies the name of the second column in a composite key.
-keycol2_value|-kc2val <keycol2_value> Specifies the value of the second column in a composite key.
-keycol3_name|-kc3name <keycol3_name> Specifies the name of the third column in a composite key.
-keycol3_value|-kc3val <keycol3_value> Specifies the value of the third column in a composite key.
-data_format|-df <data_format> Specifies the version of the data layout for the hash in the specified row. Must be 1 in this release. By default, the value of this option is set as 1.
-type <type> The valid values for type are USER and DELEGATE. These values may be used interchangeably. The default value is USER.
-countersignature_algorithm|-countersignalgo <countersignature_algorithm> Specifies the cryptographic hash algorithm to use for the countersignature. The default value of this field is DBMS_BLOCKCHAIN_TABLE.SIGN_ALGO_DEFAULT. If specified, the parameter must be one of the following acceptable string constants:
  • RSA_SHA2_256
  • RSA_SHA2_384
  • RSA_SHA2_512
-bytes_file <bytes_file> Specifies the name of the file that contains the signed countersignature bytes.
-countersignature|-csig <countersignature> Specifies the digital signature on the bytes returned in bytes_file. This is an OUT parameter option, used for storing the output value of the command in a bind variable.
-countersign_cert_guid|-cscg <countersign_cert_guid> Specifies a unique identifier for the certificate of the blockchain table owner stored in the database that may be used to verify the countersignature. This is an OUT parameter option, used for storing the output value of the command in a bind variable.
-content_version <content_version> Specifies the version of the data contents and layout that are used as input to the countersignature algorithm. Only V2_DIGEST is supported in this release.
-pdb_guid <pdb_guid> Specifies the identifier of the pluggable database that inserted the row for a V2 blockchain table. It must be NULL for a V1 blockchain table.

Examples

  • Sign and countersign the row in the U1.BCTAB1 blockchain table identified by the bind variables :INST_ID, :CHAIN_ID, and :SEQ_ID, using the row hash :ROWHASH, the certificate ID :CERT_ID2, and the RSA_SHA2_512 algorithm. The signature is generated using the content from the file u1r11_sign.dat. 

    bl signandcountersign -tab u1.bctab1 -inst ":inst_id" -ch ":chain_id" -seq ":seq_id" -row_hash ":row_hash" -signature_file
<PATH_TO_FILE>/u1r11_sign.dat -cert_guid ":cert_id2" -algo "RSA_SHA2_512"

  • Perform signature and countersignature on the row in the U1.BCTAB1 blockchain table identified by :INST_ID,:CHAIN_ID, and :SEQ_ID, using the row hash :ROWHASH, signer certificate ID :CERT_ID2, and returns the countersigner certificate ID :CERT_GUID used for countersignature. The RSA_SHA2_512 algorithm is used for both signing and countersigning. The signature is generated using the file u1r11_sign.dat, and the countersignature is provided in hex format using :CSIG and the binary format using countersignBytes.txt. 

    blockchain_table sign_row_with_countersignature -tab u1.bctab1 -inst ":inst_id" -ch ":chain_id" -seq ":seq_id" -row_hash ":row_hash" -signature_file
<PATH_TO_FILE>/u1r11_sign.dat -cert_guid ":cert_id2" -algo "RSA_SHA2_512" -cscg ":cert_guid" -bytes_file <PATH_TO_FILE>/countersignBytes.txt -csig ":csig"
-countersignalgo "RSA_SHA2_512"

  • Sign and countersign the row in the U1.BCTAB1 blockchain table where COL1 = 1200, using the certificate ID :CERT_ID2 and the RSA_SHA2_512 algorithm. The signature is generated from the file u1r12_sign.dat. 

    bl signandcountersign -tab u1.bctab1 -kc1name "COL1" -kc1val "1200" -signature_file <PATH_TO_FILE>/u1r12_sign.dat -cert_guid ":cert_id2" -algo
"RSA_SHA2_512"

9.4.11 verify_rows

Verifies all rows on all applicable system chains for the integrity of the hash column value for rows created in the range of low_timestamp to high_timestamp. Row signatures can be verified as an option. An appropriate exception is triggered if the integrity of chains is compromised.

Syntax

blockchain_table|bl verify_rows {OPTIONS}

Options

Option Description
Required
-table_name|-tab <table_name> Specifies a name for the blockchain table. The name can be preceded by the respective schema name. To specify a case-sensitive schema or table name, enclose the entire name in double quotes and then enclose the individual names in double, double quotes.

Note:

Case sensitive, embedded white space table and schema name should be entered as """Alp_ha"".""Be $a""". The parser parses these values as "Alp_ha"."Be $a".
Optional
-low_timestamp|-low <low_timestamp> Specifies the low end of the time range. If specified, the low end of the time range is used for verifying rows. The default value is NULL. Use double quotes to enclose the value.

Note:

The low_timestamp should be specified as per the NLS_TIMESTAMP_FORMAT or NLS_TIMESTAMP_TZ_FORMAT.
.
-high_timestamp|-high <high_timestamp> Specifies the high end of the time range. If specified, the high end of the time range is used for verifying rows. The default value is NULL. Use double quotes to enclose the value.

Note:

The high_timestamp should be specified as per the NLS_TIMESTAMP_FORMAT or NLS_TIMESTAMP_TZ_FORMAT.
-instance_id|-inst <instance_id> Specifies the instance that inserted the row. If specified, this limits the operation to rows inserted by the given instance.
-chain_id|-ch <chain_id> Specifies the chain containing the row. If specified, this limits operation to rows assigned to the specified chain. By default, there are 32 chains in each instance, and they are numbered from 0 to 31.
-rowcount <rowcount> Specifies the number of rows verified. This is an OUT parameter option, used for storing the output value of the command in a bind variable.
-skip_user_signature|-skipuser Specifies true or false for SKIP_USER_SIGNATURE. The default value is FALSE.
  • If SKIP_USER_SIGNATURE is specified (set to true), the blockchain verify_rows SQLcl command bypasses the validation of user signature if it is present on any row.
  • If SKIP_USER_SIGNATURE is not specified (set to false), the hash and user signature are both verified for any row if they are present, otherwise only the hash is verified.
-skip_delegate_signature|-skipdlg Specifies true or false for SKIP_DELEGATE_SIGNATURE. The default value is FALSE.
  • If SKIP_DELEGATE_SIGNATURE is specified (set to true), the blockchain verify_rows SQLcl command bypasses the validation of delegate signature if it is present on any row.
  • If SKIP_DELEGATE_SIGNATURE is not specified (set to false), the hash and delegate signature are both verified for any row if they are present, otherwise only the hash is verified.
-skip_countersignature|-skipctr Specifies true or false for SKIP_COUNTERSIGNATURE. The default value is FALSE.
  • If SKIP_COUNTERSIGNATURE is specified (set to true), the blockchain verify_rows SQLcl command bypasses the validation of countersignature if it is present on any row.
  • If SKIP_COUNTERSIGNATURE is not specified (set to false), the hash and countersignature are both verified for any row if they are present, otherwise only the hash is verified.
-pdb_guid <pdb_guid> Specifies the identifier of the pluggable database that inserted the rows for a V2 blockchain table. It must be NULL for a V1 blockchain table.

Examples

Verify the integrity of all rows in the BCTAB1 blockchain table. 

blockchain_table verify_rows -table_name u1.bctab1

9.4.12 verify_table

Verifies signatures and system chains for all rows where the creation time falls between the minimum value for the row-creation time from begin_bytes_file and the maximum value for row-creation time from end_bytes_file. The OUT parameter row count returns the number of successfully verified rows.

Syntax

 blockchain_table|bl verify_table {OPTIONS}

Options

Option Description
Required
-end_bytes_file <end_bytes_file> Specifies a digest populated by a call to either

PL/SQL API's (GET_SIGNED_BLOCKCHAIN_DIGEST, GET_SIGNED_BLOCKCHAIN_DIGEST_FOR_SELECTED_ROWS, GET_BLOCKCHAIN_DIGEST, or GET_BLOCKCHAIN_DIGEST_FOR_SELECTED_ROWS)

or

by executing SQLcl command (blockchain_table get_digest or get_signed_digest command).
-begin_bytes_file <begin_bytes_file> Specifies a digest populated by a call to either

PL/SQL API's (GET_SIGNED_BLOCKCHAIN_DIGEST, GET_SIGNED_BLOCKCHAIN_DIGEST_FOR_SELECTED_ROWS, GET_BLOCKCHAIN_DIGEST, or GET_BLOCKCHAIN_DIGEST_FOR_SELECTED_ROWS)

or

by executing SQLcl command (blockchain_table get_digest or get_signed_digest command) before the end_bytes_file is populated.

Optional
-rowcount <rowcount> Specifies the number of rows verified. This is an OUT parameter option, used for storing the output value of the command in a bind variable.
-skip_user_signature|-skipuser Specifies true or false for SKIP_USER_SIGNATURE. The default value is FALSE.
  • If SKIP_USER_SIGNATURE is specified (set to true), the blockchain verify_table SQLcl command bypasses the validation of user signature if it is present on any row.
  • If SKIP_USER_SIGNATURE is not specified (set to false), the hash and user signature are both verified for any row if they are present, otherwise only the hash is verified.
-skip_delegate_signature|-skipdlg Specifies true or false for SKIP_DELEGATE_SIGNATURE. The default value is FALSE.
  • If SKIP_DELEGATE_SIGNATURE is specified (set to true), the blockchain verify_table SQLcl command bypasses the validation of delegate signature if it is present on any row.
  • If SKIP_DELEGATE_SIGNATURE is not specified (set to false), the hash and delegate signature are both verified for any row if they are present, otherwise only the hash is verified.
-skip_countersignature|-skipctr Specifies true or false for SKIP_COUNTERSIGNATURE. The default value is FALSE.
  • If SKIP_COUNTERSIGNATURE is specified (set to true), the blockchain verify_table SQLcl command bypasses the validation of countersignature if it is present on any row.
  • If SKIP_COUNTERSIGNATURE is not specified (set to false), the hash and countersignature are both verified for any row if they are present, otherwise only the hash is verified.

Examples

Verify the digests defined by the end bytes file latest.txt and the begin bytes file prev1.txt, and store the count of verified rows in the bind variable rowcount. 

blockchain_table verify_table -end_bytes_file <PATH_TO_FILE>/latest.txt -begin_bytes_file <PATH_TO_FILE>/prev1.txt -rowcount ":temp"

9.4.13 verify_user_chains

Verifies rows of one or more user chains when the user chains feature is enabled on the blockchain table.

Syntax
blockchain_table|bl verify_user_chains {OPTIONS}

Options

Option Description
Required
-table_name|-tab <table_name> Specifies a name for the blockchain table. The name can be preceded by the respective schema name. To specify a case-sensitive schema or table name, enclose the entire name in double quotes and then enclose the individual names in double, double quotes.

Note:

Case sensitive, embedded white space table and schema name should be entered as : """Alp_ha"".""Be $a""". The parser parses these values as: "Alp_ha"."Be $a".
-user_chain|-uchain <user_chain> Specifies the name of the row version given when the blockchain table was created.
Optional
-rowcount <rowcount> Specifies the number of rows verified. This is an OUT parameter option, used for storing the output value of the command in a bind variable.
-keycol1_value|-kc1val <keycol1_value> Specifies the value of the key column.
-keycol2_value|-kc2val <keycol2_value> Specifies the value of the second column in a composite key.
-keycol3_value|-kc3val <keycol3_value> Specifies the value of the third column in a composite key.
-low_timestamp|-low <low_timestamp> Specifies the low end of the time range. If specified, the low end of the time range is used for verifying rows. The default value is NULL. Use double quotes to enclose the value.

Note:

The low_timestamp should be specified as per NLS_TIMESTAMP_FORMAT or NLS_TIMESTAMP_TZ_FORMAT.
-high_timestamp|-high <high_timestamp> Specifies the high end of the time range. If specified, the high end of the time range is used for verifying rows. The default value is NULL. Use double quotes to enclose the value.

Note:

The high_timestamp should be specified as per NLS_TIMESTAMP_FORMAT or NLS_TIMESTAMP_TZ_FORMAT.
-skip_user_signature|-skipuser Specifies true or false for SKIP_USER_SIGNATURE. The default value is FALSE.
  • If SKIP_USER_SIGNATURE is specified (set to true), the blockchain verify_user_chains SQLcl command bypasses the validation of user signature if it is present on any row.
  • If SKIP_USER_SIGNATURE is not specified (set to false), the hash and user signature are both verified for any row if they are present, otherwise only the hash is verified.
-skip_delegate_signature|-skipdlg Specifies true or false for SKIP_DELEGATE_SIGNATURE. The default value is FALSE.
  • If SKIP_DELEGATE_SIGNATURE is specified (set to true), the blockchain verify_user_chains SQLcl command bypasses the validation of delegate signature if it is present on any row.
  • If SKIP_DELEGATE_SIGNATURE is not specified (set to false), the hash and delegate signature are both verified for any row if they are present, otherwise only the hash is verified.
-skip_countersignature|-skipctr Specifies true or false for SKIP_COUNTERSIGNATURE. The default value is FALSE.
  • If SKIP_COUNTERSIGNATURE is specified (set to true), the blockchain verify_user_chains SQLcl command bypasses the validation of countersignature if it is present on any row.
  • If SKIP_COUNTERSIGNATURE is not specified (set to false), the hash and COUNTERSIGNATURE are both verified for any row if they are present, otherwise only the hash is verified.
-pdb_guid <pdb_guid> Specifies the identifier of the pluggable database that inserted the rows for a V2 blockchain table. It must be NULL for a V1 blockchain table.

Examples

Verify the rows of BCTAB1_COL2 user chain in the BCTAB1 blockchain table. 

bl verify_user_chains -tab u1.bctab1 -uchain "bctab1_col2"