Which has the higher priority in your organization: Deploying a new database or securing the ones you already have?
Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
Be sure to view the full listing of monographs in Morgan's Library
Purpose
Blockchain tables are intended to provide an extremely tamper resistant means of storing relational data in a form wherein it can be accessed using SQL.
Blockchain tables intended for use in an environment where it is required that the ledger be distributed to multiple organizations/locations, but clearly that distribution is possible if one considers integration with data distribution tools such as Data Guard and GoldenGate.
Data Dictionary Objects
ALL_SEGMENTS
CDB_TAB_COLS
DBA_TABLES
ALL_TAB_COLS
CDB_TABLES
USER_SEGMENTS
ALL_TABLES
DBA_SEGMENTS
USER_TAB_COLS
CDB_SEGMENTS
DBA_TAB_COLS
USER_TABLES
First Available
20c
System Privileges
ALTER ANY TABLE
CREATE ANY TABLE
DROP ANY TABLE
ALTER TABLE
CREATE TABLE
DROP TABLE
General (new 20c)
Block Change table with NO DELETE LOCKED
CREATE BLOCKCHAIN TABLE <schema_name>.<table_name(
<column_name> <column_data_type>)
NO DROP [UNTIL <integer> DAYS IDLE]
NO DELETE LOCKED
HASHING USING "<hashing_algorithm>" VERSION "<version_number>"
[sharing_clause]
[memoptimize_clause]
[relational_properties];
CREATE BLOCKCHAIN TABLE ledger1(
tx_id INTEGER,
tx_date DATE,
tx_value NUMBER(10,2))
TABLESPACE uwdata
NO DROP UNTIL 31 DAYS IDLE
NO DELETE LOCKED
HASHING USING "SHA2_512" VERSION "v1";
Block Change table with NO DELETE UNTIL
CREATE BLOCKCHAIN TABLE <schema_name>.<table_name(
<column_name> <column_data_type>)
NO DROP [UNTIL <integer> DAYS IDLE]
NO DELETE UNTIL <integer> DAYS AFTER INSERT [LOCKED]
HASHING USING "<hashing_algorithm>" VERSION "<version_number>"
[sharing_clause]
[memoptimize_clause]
[relational_properties];
CREATE BLOCKCHAIN TABLE ledger2(
tx_id INTEGER,
tx_date DATE,
tx_value NUMBER(10,2))
TABLESPACE uwdata
NO DROP UNTIL 31 DAYS IDLE
NO DELETE UNTIL 90 DAYS AFTER INSERT
HASHING USING "SHA2_512" VERSION "v1";
Block Chain table with range partitioning
CREATE BLOCKCHAIN TABLE <schema_name>.<table_name(
<column_name> <column_data_type>)
NO DROP UNTIL
NO DELETE LOCKED
HASHING USING "<hashing_algorithm>" VERSION "<version_number>"
PARTITION BY ....;
CREATE BLOCKCHAIN TABLE
ledger_partitioned(
tx_id NUMBER,
tx_date DATE,
grantor VARCHAR2(60),
grantee VARCHAR2(60))
NO DROP UNTIL 60 DAYS IDLE
NO DELETE UNTIL 31 DAYS AFTER INSERT
HASHING USING "SHA2_512" VERSION "v1"
PARTITION BY RANGE(trans_date)
(
PARTITION p1 VALUES LESS THAN (TO_DATE('01-01-2018','dd-mm-yyyy')),
PARTITION p2 VALUES LESS THAN (TO_DATE('01-01-2019','dd-mm-yyyy')),
PARTITION p3 VALUES LESS THAN (TO_DATE('01-01-2020','dd-mm-yyyy')),
PARTITION p4 VALUES LESS THAN (TO_DATE('01-01-2021','dd-mm-yyyy')));
Block Change Tracking Table Demos
conn / as sysdba
SQL> CREATE BLOCKCHAIN TABLE ledger (
2 tx_id INTEGER,
3 tx_date DATE,
4 tx_value NUMBER(10,2))
5 NO DROP UNTIL 31 DAYS IDLE
6 NO DELETE LOCKED
7 HASHING USING "SHA2_512" VERSION "v1";
CREATE BLOCKCHAIN TABLE ledger (txn_id INTEGER, tx_date DATE, tx_value NUMBER(10,2))
*
ERROR at line 1:
ORA-05729: blockchain table cannot be created in root container
SQL> CREATE BLOCKCHAIN TABLE ledger(
2 tx_id INTEGER,
3 tx_date DATE,
4 tx_value NUMBER(10,2))
5 NO DROP UNTIL 31 DAYS IDLE
6 NO DELETE LOCKED
7 HASHING USING "SHA2_512" VERSION "v1";
Table created.
SQL> desc ledger Name Null? Type
----------------------------- -------- --------------------
TX_ID NUMBER(38)
TX_DATE DATE
TX_VALUE NUMBER(10,2)
SQL> SELECT column_id, column_name, data_type, hidden_column, virtual_column, user_generated
2 FROM user_tab_cols
3* ORDER BY user_generated, column_id;
COLUMN_ID COLUMN_NAME DATA_TYPE HID VIR USE
---------- ------------------------- ---------------------------- --- --- ---
ORABCTAB_SIGNATURE$ RAW YES NO NO
ORABCTAB_SIGNATURE_ALG$ NUMBER YES NO NO
ORABCTAB_SIGNATURE_CERT$ RAW YES NO NO
ORABCTAB_SPARE$ RAW YES NO NO
ORABCTAB_USER_NUMBER$ NUMBER YES NO NO
ORABCTAB_CREATION_TIME$ TIMESTAMP(6) WITH TIME ZONE YES NO NO
ORABCTAB_HASH$ RAW YES NO NO
ORABCTAB_INST_ID$ NUMBER YES NO NO
ORABCTAB_CHAIN_ID$ NUMBER YES NO NO
ORABCTAB_SEQ_NUM$ NUMBER YES NO NO 1 TX_ID NUMBER NO NO YES
2 TX_DATE DATE NO NO YES
3 TX_VALUE NUMBER NO NO YES
col ORABCTAB_CREATION_TIME$ format a40
col ORABCTAB_HASH$ format a130
SQL> SELECT orabctab_user_number$, orabctab_creation_time$, orabctab_inst_id$
2 FROM ledger;
