Oracle blockchain list, a centralized blockchain platform

Mr. Tony without haircut 2021-05-15 15:50:15 阅读数:460

本文一共[544]字,预计阅读时长:1分钟~
oracle blockchain list centralized blockchain

 Block linked list

Hello everyone ! I only talk about technology, not haircut Tony teacher .

Oracle 21c A very powerful new feature has been added : Native blocklist (Blockchain Table).Oracle Blockchain list is a tamper resistant list , You can only insert data , Both table level and row level retention periods are provided . All the rows in the blockchain list form a data link , Each row stores the hash value of the current data and the previous hash value .

Oracle Blockchain technology can effectively prevent database fraud , Taking advantage of the tamper proof features of blockchain , Users can trade for Finance 、 Chain of supervision 、 Statutory preservation 、 hosted services 、 Audit log and centralized general ledger in many other scenarios provide security protection .

This article will show you how to create and use Oracle Block linked list , And related precautions . If you think the article is useful , Welcome comments 、 give the thumbs-up 、 recommend

Oracle The blocklist function can also be used in Oracle 19.10 Used in version , But it needs to be applied patch 32431413 Patch , And will COMPATIBLE Parameter set to 19.10. from Oracle 19.11 There is no need to apply a patch at the beginning of the release .

Create blockchain list

We can use CREATE BLOCKCHAIN TABLE Command to create a blockchain list , Three options can be specified at the same time .

among ,NO DROP Clause determines when the blockchain list is allowed to be deleted , If there is no data in the table, it can be deleted . Unlike the original blockchain list , from Oracle 19.11 and Oracle 21.3 Start NO DROP Clause can also prevent the passage of DROP USER … CASCADE Command to delete the blockchain list .

NO DROP [ UNTIL number DAYS IDLE ]
  • NO DROP, Table deletion is not allowed . Use this option carefully when creating test tables .
  • NO DROP UNTIL number DAYS IDLE, Table deletion is not allowed , No new data row is inserted until the specified number of days . It can be set to 0 perhaps 1 God .

NO DELETE Clause determines the retention period of data , Only data that has existed for more than this period can be deleted .

NO DELETE { [ LOCKED ] | (UNTIL number DAYS AFTER INSERT [ LOCKED ]) }
  • NO DELETE, Data is kept forever . Although not specified LOCKED keyword , But that doesn't mean it can be used ALTER TABLE Order to modify the retention period , Because the retention period can only be increased , Can't reduce .
  • NO DELETE LOCKED, Data is kept forever , and NO DELETE equally .
  • NO DELETE UNTIL number DAYS AFTER INSERT, Data cannot be deleted until it exists for at least a specified number of days , have access to ALTER TABLE Order an increase in the retention period . The minimum retention period 16 God .
  • NO DELETE UNTIL number DAYS AFTER INSERT LOCKED, Data cannot be deleted until it exists for at least a specified number of days , Out of commission ALTER TABLE Order an increase in the retention period . The minimum retention period 16 God .

HASHING Clause is used to specify the blockchain hash algorithm and data format , The current version can only use fixed values , Other settings may be allowed in the future .

HASHING USING sha2_512 VERSION v1

Here's a complete example of creating a blockchain list :

--drop table bct_t1 purge;
create blockchain table bct_t1 (
id number,
fruit varchar2(20),
quantity number,
created_date date,
constraint bct_t1_pk primary key (id)
)
no drop until 0 days idle
no delete until 16 days after insert
hashing using "SHA2_512" version "v1";

When learning blockchain lists , Be careful not to set too long a retention period , Otherwise, it will take a long time to delete the test table .

Inquire about USER_TAB_COLS View can see that the database has added some invisible fields for us .

set linesize 120 pagesize 50
column column_name format a30
column data_type format a27
column hidden_column format a13
select internal_column_id,
column_name,
data_type,
data_length,
hidden_column
FROM user_tab_cols
WHERE table_name = 'BCT_T1'
ORDER BY internal_column_id;
INTERNAL_COLUMN_ID COLUMN_NAME DATA_TYPE DATA_LENGTH HIDDEN_COLUMN
------------------ ------------------------------ --------------------------- ----------- -------------
1 ID NUMBER 22 NO
2 FRUIT VARCHAR2 25 NO
3 QUANTITY NUMBER 22 NO
4 CREATED_DATE DATE 7 NO
5 ORABCTAB_INST_ID$ NUMBER 22 YES
6 ORABCTAB_CHAIN_ID$ NUMBER 22 YES
7 ORABCTAB_SEQ_NUM$ NUMBER 22 YES
8 ORABCTAB_CREATION_TIME$ TIMESTAMP(6) WITH TIME ZONE 13 YES
9 ORABCTAB_USER_NUMBER$ NUMBER 22 YES
10 ORABCTAB_HASH$ RAW 2000 YES
11 ORABCTAB_SIGNATURE$ RAW 2000 YES
12 ORABCTAB_SIGNATURE_ALG$ NUMBER 22 YES
13 ORABCTAB_SIGNATURE_CERT$ RAW 16 YES
14 ORABCTAB_SPARE$ RAW 2000 YES
14 rows selected.

{CDB|DBA|ALL|USER}_BLOCKCHAIN_TABLES The view contains information about the blockchain list , They are based on SYS.BLOCKCHAIN_TABLE$ View of the system table .

column row_retention format a13
column row_retention_locked format a20
column table_inactivity_retention format a26
column hash_algorithm format a14
SELECT row_retention,
row_retention_locked,
table_inactivity_retention,
hash_algorithm
FROM user_blockchain_tables
WHERE table_name = 'BCT_T1';
ROW_RETENTION ROW_RETENTION_LOCKED TABLE_INACTIVITY_RETENTION HASH_ALGORITHM
------------- -------------------- -------------------------- --------------
16 NO 0 SHA2_512

Modify block list

Official documents tell us that as long as we don't reduce the retention period , You can use ALTER TABLE Command to change NO DROP Clause . But for now, if we're going to NO DROP UNTIL 0 DAYS IDLE Change to a longer term , The database will return an error .

alter table bct_t1 no drop until 100 days idle;
Error report -
ORA-05732: retention value cannot be lowered

There is no problem with the above grammar , It could be a part of the system bug. If you create a table with NO DROP UNTIL 1 DAYS IDLE There's no problem getting other deadlines .

No matter what the current retention period is , If you will NO DROP If it is changed to permanent reservation, it will be returned ORA-00600 error :

alter table bct_t1 no drop;
Error starting at line : 1 in command -
alter table bct_t1 no drop
Error report -
ORA-00600: internal error code, arguments: [atbbctable_1], [0], [], [], [], [], [], [], [], [], [], []

It could be a problem , Because most people may want to start with a retention period of 0 One day, I began to try , Then increase the retention period . From the retention period of 1 The beginning of the day may lead to certain risks , Because the only way to delete a test table is to delete the entire pattern .

If not specified LOCKED Options , We can use ALTER TABLE Command to change NO DELETE Clause , Of course, only the retention period can be increased . The current data retention period of our test table is 16 God , Now let's change it to 32 God :

-- Add to 32 God 
alter table bct_t1 no delete until 32 days after insert;
Table BCT_T1 altered.
-- Reduced to 16 Time of day returns an error 
alter table bct_t1 no delete until 16 days after insert;
Error report -
ORA-05732: retention value cannot be lowered

In the current version , If you try to change the data retention period to NO DELETE( Increase the retention period ) Will lead to ORA-00600 error , It should also be a bug.

alter table bct_t1 no delete;
Error report -
ORA-00600: internal error code, arguments: [atbbctable_1], [0], [], [], [], [], [], [], [], [], [], []

prevent DML and DDL sentence

Blockchain lists only support data insertion , All that lead to data modification or deletion DML and DDL Statements return errors . for example :

-- INSERT
insert into bct_t1 (id, fruit, quantity, created_date ) values (1, 'apple', 20, sysdate);
1 row inserted.
SQL> commit;
Commit complete.
-- UPDATE
update bct_t1 set quantity = 10 where id = 1;
Error report -
SQL Error: ORA-05715: operation not allowed on the blockchain table
-- DELETE
delete from bct_t1 where id = 1;
Error report -
SQL Error: ORA-05715: operation not allowed on the blockchain table

What causes data change DDL Statements also return errors , Here is one TRUNCATE Statement example :

truncate table bct_t1;
Error report -
ORA-05715: operation not allowed on the blockchain table

We can extend the length of existing fields , But you can't add fields or delete existing fields :

-- Modify field length 
alter table bct_t1 modify (fruit varchar2(25));
Table BCT_T1 altered.
-- Add fields 
alter table bct_t1 add (additional_info varchar2(50));
Error report -
ORA-05715: operation not allowed on the blockchain table
-- Delete field 
alter table bct_t1 drop column quantity;
Error report -
ORA-05715: operation not allowed on the blockchain table

DBMS_BLOCKCHAIN_TABLE

System package DBMS_BLOCKCHAIN_TABLE Can be used to maintain blockchain lists .

among , stored procedure DELETE_EXPIRED_ROWS It can be used to delete data rows beyond the retention period , This data can't be used normally DELETE Statement to delete .

set serveroutput on
declare
l_rows number;
begin
dbms_blockchain_table.delete_expired_rows(
schema_name => 'admin',
table_name => 'bct_t1',
before_timestamp => null,
number_of_rows_deleted => l_rows);
dbms_output.put_line('Rows Deleted=' || l_rows);
end;
/
Rows Deleted=0
PL/SQL procedure successfully completed.

in addition , We can also add a date limit , Only data rows that exceed the retention period and meet the date requirement are deleted .

set serveroutput on
declare
l_rows number;
begin
dbms_blockchain_table.delete_expired_rows(
schema_name => 'testuser1',
table_name => 'it_t1',
before_timestamp => systimestamp - 60,
number_of_rows_deleted => l_rows);
dbms_output.put_line('Rows Deleted=' || l_rows);
end;
/
Rows Deleted=0
PL/SQL procedure successfully completed.

stored procedure VERIFY_ROWS Can be used to check that data rows have consistent hashes , And user signatures ( If used ).

set serveroutput on
declare
l_rows number;
l_verified number;
begin
select count(*)
into l_rows
from admin.bct_t1;
dbms_blockchain_table.verify_rows(
schema_name => 'admin',
table_name => 'bct_t1',
number_of_rows_verified => l_verified);
dbms_output.put_line('Rows=' || l_rows || ' Verified Rows=' || l_verified);
end;
/
Rows=1 Verified Rows=1
PL/SQL procedure successfully completed.

matters needing attention

Before using blockchain list, you need to consider the following issues :

  • At present, there are still some problems in the function of blockchain list , Some of the features are not exactly what the official documentation describes .
  • The performance of block linked list is worse than ordinary list , Because it needs to do more , For example, calculating hash value .
  • Blockchain lists can support indexing and partitioning just like other tables .
  • The import and export of blockchain list still exist Some restrictions .
  • Block list of Usage restriction .
  • Oracle It is recommended to store the current hash algorithm and corresponding sequence number of each blockchain outside the database , So you can compare the stored values with the data in the table , Provide additional security .
  • stay data guard Configuration in progress ,Oracle It is recommended to use maximum protection mode or maximum high availability mode to synchronize blockchain list .
  • DBMS_USER_CERTS In the package ADD_CERTIFICATE Stored procedures can be used for Add user certificate , And then use DBMS_BLOCKCHAIN_TABLE In the package SIGN_ROW The stored procedure will Apply to existing data rows .
版权声明:本文为[Mr. Tony without haircut]所创,转载请带上原文链接,感谢。 https://netfreeman.com/2021/05/20210515142021417g.html