They are here! Initially announced at OOW 2019 and described in my post-OOW blog post, the blockchain tables are now generally available as mentioned in the Introducing Oracle Database 21c post announcing availability of the latest release in the Oracle Cloud Database Service Virtual Machine (for RAC and single instance) and Bare Metal Service (single instance). It's also available in the Autonomous Database Free Tier Service in Ashburn (IAD), Phoenix (PHX), Frankfurt (FRA) and London (LHR) regions. General availability of Oracle Database 21c for on-prem platforms (including Exadata, Linux and Windows) will follow along in 2021, and the Blockchain Tables will also become available in 19c database when 19.10 Release Update (RU) is available in early 2021. So what exactly are the Blockchain Tables and why should you care?
With blockchain tables users get application-transparent protection from fraud by other users. And by signing and verifying data, they can detect provider fraud (trust but verify). While emerging decentralized applications benefit from Oracle Blockchain Platform’s de-centralized trust model, most applications today have a central authority (bank, escrow company, trading exchange, government office, etc.) and with blockchain tables in Oracle Database we can make these applications more secure without the added complexity of changing to a de-centralized model. This is the reason for Oracle Database-native Blockchain Tables. Examples include financial transaction logs, audit trails, regulatory compliance data, financial records covered by SOX-404 controls, legal hold data, centralized chain of custody or provenance information. We can apply blockchain tables to make these applications more secure and their data tamper-resistant.
For the examples cited above, using blockchain tables is much easier than implementing a blockchain network and de-centralized applications. There’s no new infrastructure needed – the feature is delivered as part of Oracle Database. The use of the tables can be transparent to existing applications, and developers can keep current architecture and programming model, using SQL, PL/SQL, JDBC, and other ways to access the tables. Blockchain Tables can also participate in transactions and queries with other tables.
The first pattern is for applications that need to store changeless data, e.g. readings from IoT devices, compliance data, etc. Create and use blockchain tables to store this data directly.
Second case is when an existing application needs a tamper-resistant audit trail of changes made to the regular database tables. You can add an additional Blockchain table and either update the application to insert a record for each update to current tables or create a triggered stored procedure on the current table to capture and log an update into an audit trail maintained in a blockchain table.
Another scenario is related to de-centralized applications using a distributed blockchain network. In some cases these applications need to store large data volumes, e.g., Electronic Health Records (EHR), images, legal agreements or contracts, etc. Due to the large number of messages exchanged by nodes in a distributed network, MB-sized documents or GB-sized images will create a high network load across many connections. The preferred design pattern is to “anchor” (hash and maintain on-chain with a metadata record) these records on the distributed ledger, but store the actual source data off-chain using, for example, tamper-resistant blockchain tables. The combination of tamper-resistance in the tables and additional security of anchoring records on blockchain network makes it even harder to tamper with any data.
Now that we explained the "why", let's talk about the "how".
Oracle Database 21c provides new and modified DDL for blockchain tables and three PL/SQL packages with useful functions. If you'd like to follow this hands-on, go to Oracle Cloud (Database Cloud Service Virtual Machine or Autonomous Database, which you can get in Always Free tier) to spin up your instance and connect to the database with your favorite database client.
CREATE BLOCKCHAIN TABLE is the new DDL provided to define blockchain tables and set some parameters. An example of this would be:
CREATE BLOCKCHAIN TABLE bank_ledger (bank varchar2(128), EOD_deposit NUMBER, ...) \
NO DROP UNTIL 31 DAYS IDLE \
NO DELETE LOCKED \
HASHING USING "sha2_512" VERSION "v1“;
Note that in this release the supported data types are NUMBER, VARCHAR2, RAW, JSON, BLOB, CLOB, DATE formats, and other scalar types. Other types, such as LONG, ADTs, TYPE, varray, OBJECTS, ROWID, BFILE, REF, Collections, etc. are not supported.
You will note three additional clauses in the example above:
When it comes to ALTER TABLE clauses, most are disallowed for the blockchain tables except the ones mentioned above and clauses used when adding constraints.
Blockchain Tables Restrictions
The core principle of data immutability in blockchain is used in the database implementation of the Blockchain tables. When using Blockchain tables users cannot update rows via DML, direct path inserts (INSERT AS SELECT, SQL Loader) or Golden Gate logical replication. In addition, the users cannot do the following:
This helps to protect data in Blockchain tables against basic manipulation and fraud committed by database users, including forgery, rewriting history, etc.
When rows are inserted a special chaining process is used with Oracle-managed hidden columns to ensure tamper-resistance. This refers to a blockchain-derived anti-tampering method of storing cryptographic hashes of previous blocks with the current data. Any alteration of the previous data will cause its hash to change and not match the hash stored with the current data. In Oracle blockchain tables, hash for a new row is generated as SHA2-512 computation over a combination of 1) “Row content” of user and hidden columns of the current row (except its user signature columns) AND 2) the hidden hash column of the previous row. For parallelism and higher performance of row insertion for each table we maintain 32 chains, and in RAC configuration, 32 chains per RAC instance. So technically “previous” row refers to a row with sequence number one less than the current row on the specific chain. The SHA2-512 hash computation is over a concatenated array that contains for each column position a meta-data header and column-byte-value and adds as the last array entry the meta-data and column-byte-value of the “previous” row’s hidden hash column.
This helps to protect the data from tampering at a deeper level if someone managed to bypass database protections and modify the row contents. The integrity of the hash chains can be verified using a function in the PL/SQL packages described below. You can further protect the blockchain tables from admin-level tampering by:
In addition to built-in row chaining via cryptographic hashes, PL/SQL packages described below enable users to register their PKI (X.509) certificates and apply user signatures to row content after the row has been inserted. Signing is done after the row has been inserted and the hash added by the database. But first, the signer's certificate has to be registered in the database. Here's an example of the full sequence of steps:
-- Add user certificate to database, get as output the Certificate ID dbms_user_certs.add_certificate(buffer, cert_id); -- Insert row in to the blockchain table insert into bank_ledger values ('Chase', 1000); -- Commit the row. This causes the crypto hash to be computed on row & linked to previous row commit; -- Get the bytes to be signed. Returns the output in crypto_hash dbms_blockchain_table.get_bytes_for_row_signature('EXAMPLE', 'BANK_LEDGER', 'instance_id', 'chain_id', 'sequence_id', 1, row_hash); -- Application signs the bytes using some trusted library like openssl openssl dgst -sha256 –sign … -- Sign the row (and specify the id of the registered public key certificate) dbms_blockchain_table.sign_row(… 'BANK_LEDGER’…, sequence_no, row_hash, signature, cert_id, dbms_blockchain_table.SIGN_ALGO_RSA_SHA2_256);
This enables non-repudiation property, which protects against users or administrators impersonating others, or claiming that someone else has impersonated them. Of course, maintaining the security of private keys used with openssl command is critical for this to work as is ability to revoke and re-issue certificates if someone's private key has been compromised.
To work with the blockchain tables in PL/SQL we provide three database packages with a range of functions.
Two sets of views are pre-defined to help query and managed metadata information about blockchain tables and user certificates:
These views contain the following columns (names simplified, see full names in the documentation): SCHEMA_NAME (all views except USER), TABLE_NAME, ROW_RETENTION, ROW_RETENTION_LOCKED, TABLE_INACTIVITY_RETENTION, HASH_ALGORITHM, CON_ID (CDB view only)
For user certificates, the following views are defined:
These views contain the following columns: CERTIFICATE_GUID, USER_NAME, DISTINGUISHED_NAME, CERTIFICATE (BLOB), CON_ID (CDB view only).
Blockchain Tables offer two ways of verifying data immutability and detecting tampering. First of these is simply verifying hashes, and optionally signatures if present and specified in the arguments, for a set of rows between two timestamps using the DBMS_BLOCKCHAIN_TABLE.VERIFY_ROWS procedure.
DECLARE verify_rows NUMBER; instance_id NUMBER; BEGIN FOR instance_id IN 1 .. 4 LOOP DBMS_BLOCKCHAIN_TABLE.VERIFY_ROWS('EXAMPLES','BANK_LEDGER', NULL, NULL, instance_id, NULL, verify_rows); DBMS_OUTPUT.PUT_LINE('Number of rows verified in instance Id '|| instance_id || ' = '|| verify_rows); END LOOP; END; / Number of rows verified in instance Id 1 = 3 Number of rows verified in instance Id 2 = 12 Number of rows verified in instance Id 3 = 8 Number of rows verified in instance Id 4 = 10 PL/SQL procedure successfully completed.
The second process enables you to maintain the integrity of blockchain tables by continuously verifying that the blockchain table data has not been compromised. It uses a signed digest for the blockchain table generated by using the DBMS_BLOCKCHAIN_TABLE.GET_SIGNED_BLOCKCHAIN_DIGEST procedure. This returns a BLOB that contains the system generated column content only for the top row in each chain signed by the database schema owner's private key maintained in the database wallets. The signed digests can be generated periodically and should be published or stored outside the database. Then use DBMS_BLOCKCHAIN_TABLE.VERIFY_TABLE_BLOCKCHAIN procedure to verify the integrity of all rows between the times corresponding to the two digests (from the earliest time of the earlier digest to the latest time of the later digest.)
The example of generating the digest is shown below. This example computes the signed digest and generates a signature for the blockchain EXAMPLES.BANK_LEDGER. The signed digest is in binary format and consists of metadata and data of the last rows in each chain. It is stored in signed_bytes. The PL/SQL array version of the signed digest is stored in the output parameter signed_row_array. The GUID of the certificate used to generate the signature is stored in certificate_guid. Note that you must first store the certificate in the database using DBMS_USER_CERTS.ADD_CERTIFICATE procedure.
DECLARE signed_bytes BLOB:=EMPTY_BLOB(); signed_row_array SYS.ORABCTAB_ROW_ARRAY_T; certificate_guid RAW(2000); signature RAW(2000); BEGIN signature := DBMS_BLOCKCHAIN_TABLE.GET_SIGNED_BLOCKCHAIN_DIGEST('EXAMPLES', 'BANK_LEDGER', signed_bytes, signed_row_array, certificate_guid, dbms_blockchain_table.SIGN_ALGO_RSA_SHA2_512); DBMS_OUTPUT.PUT_LINE('Certificate GUID = ' || certificate_guid); DBMS_OUTPUT.PUT_LINE('Signature length = ' || UTL_RAW.LENGTH(signature)); DBMS_OUTPUT.PUT_LINE('Number of chains = ' || signed_row_array.count); DBMS_OUTPUT.PUT_LINE('Signature content buffer length = ' || DBMS_LOB.GETLENGTH(signed_bytes)); END; / Certificate GUID = AF27H7FE3EEA473GE0783FE56A0AFCEB Signature length = 256 Number of chains = 10 Signature content buffer length = 1248 PL/SQL procedure successfully completed.
Now with signed_bytes_T1 and signed_bytes_T2BLOBs you can use DBMS_BLOCKCHAIN_TABLE.VERIFY_TABLE_BLOCKCHAIN procedure as shown in the example below.
DECLARE signed_bytes_T1 BLOB; signed_bytes_T2 BLOB; rows_verified NUMBER; BEGIN DBMS_BLOCKCHAIN_TABLE.VERIFY_TABLE_BLOCKCHAIN(signed_bytes_T2,signed_bytes_T1, rows_verified); dbms_output.put_line('Rows verified = ' || rows_verified); END; / Rows verified = 10 PL/SQL procedure successfully completed.
Oracle Database Blockchain Tables |
Oracle Blockchain Platform |
|
|
Use cases involve
|
Use cases involve
|
To explore the new Native Blockchain Tables feature, make sure you have an Oracle Cloud account and go to cloud.oracle.com to provision Autonomous Database in Always Free tier or a free 30 day trial of database VM on a compute instance. Then connect with your favorite database client and CREATE BLOCKCHAIN TABLE (don't forget the mandatory clauses!) It’s that easy. If you have any issues or questions, support is provided via regular customer support channels and via public forums.
Mark is responsible for Blockchain strategy and products. He focuses on evolving Oracle Blockchain Platform and Oracle Database Blockchain Tables in the cloud and on-premises to meet the needs of customers and partners for scalable, secure, production-ready platform to support blockchain solutions. He helps to guide customers and partners around the world in applying blockchain technology to deliver on key business outcomes – accelerating growth, reducing costs and friction in business ecosystems, reducing risk and fraud, and bringing to market innovative solutions that solve real-world societal challenges. Mark facilitates customers’ journey from rapid experimentation to live production through discovery, sharing industry use cases and best practices, and advising on solution architecture.
Previous Post