Practice: Managing Blockchain Tables and Rows

Overview

This practice shows how to create, alter and drop Oracle blockchain tables.

Before starting any new practice, refer to the Practices Environment recommendations.

Step 1 : Create the blockchain table

Create the AUDITOR user, owner of the blockchain table.


$ cd /home/oracle/labs/M104781GC10
$ /home/oracle/labs/M104781GC10/setup_user.sh
...
specify password for HR as parameter 1:

specify default tablespeace for HR as parameter 2:

specify temporary tablespace for HR as parameter 3:

specify log path as parameter 4:

PL/SQL procedure successfully completed.
...
SQL> 

Connected to:

SQL> DROP USER auditor CASCADE;
DROP USER auditor CASCADE
          *
ERROR at line 1:
ORA-01918: user 'AUDITOR' does not exist

SQL> DROP TABLESPACE ledgertbs INCLUDING CONTENTS AND DATAFILES cascade constraints;
DROP TABLESPACE ledgertbs INCLUDING CONTENTS AND DATAFILES cascade constraints
*
ERROR at line 1:
ORA-00959: tablespace 'LEDGERTBS' does not exist

SQL> CREATE TABLESPACE ledgertbs;

Tablespace created.

SQL> CREATE USER auditor identified by password DEFAULT TABLESPACE ledgertbs;

User created.

SQL> GRANT create session, create table, unlimited tablespace TO auditor;

Grant succeeded.

SQL> GRANT execute ON sys.dbms_blockchain_table TO auditor;

Grant succeeded.

SQL> GRANT select ON hr.employees TO auditor;

Grant succeeded.

SQL>
SQL> exit

$

Create the blockchain table named AUDITOR.LEDGER_EMP that will maintain a tamper-resistant ledger of current and historical transactions about HR.EMPLOYEES in PDB21. Rows can never be deleted in the blockchain table AUDITOR.LEDGER_EMP. Moreover the blockchain table can be dropped only after 31 days of inactivity.


$ sqlplus auditor@PDB21

Enter password: password

SQL> CREATE BLOCKCHAIN TABLE ledger_emp (employee_id NUMBER, salary NUMBER);
CREATE BLOCKCHAIN TABLE ledger_emp (employee_id NUMBER, salary NUMBER)
                                                       *
ERROR at line 1:
ORA-00905: missing keyword

SQL>

Observe that the CREATE BLOCKCHAIN TABLE statement requires additional attributes. The NO DROP, NO DELETE, HASHING USING, and VERSION clauses are mandatory.


SQL> CREATE BLOCKCHAIN TABLE ledger_emp (employee_id NUMBER, salary NUMBER)
                     NO DROP UNTIL 31 DAYS IDLE
                     NO DELETE LOCKED
                     HASHING USING "SHA2_512" VERSION "v1";

Table created.

SQL>

Verify the attributes set for the blockchain table in the appropriate data dictionary view.


SQL> SELECT row_retention, row_retention_locked, 
                     table_inactivity_retention, hash_algorithm  
              FROM   user_blockchain_tables 
              WHERE  table_name='LEDGER_EMP';

ROW_RETENTION ROW TABLE_INACTIVITY_RETENTION HASH_ALG
------------- --- -------------------------- --------
              YES                         31 SHA2_512

SQL>

Show the description of the table.


SQL> DESC ledger_emp
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPLOYEE_ID                                        NUMBER
 SALARY                                             NUMBER

SQL>

Observe that the description displays only the visible columns.

Use the USER_TAB_COLS view to display all internal column names used to store internal information like the users number, the users signature.


SQL> COL "Data Length" FORMAT 9999
SQL> COL "Column Name" FORMAT A24
SQL> COL "Data Type" FORMAT A28
SQL> SELECT internal_column_id "Col ID", SUBSTR(column_name,1,30) "Column Name", 
                     SUBSTR(data_type,1,30) "Data Type", data_length "Data Length"
              FROM   user_tab_cols       
              WHERE  table_name = 'LEDGER_EMP' ORDER BY internal_column_id;

    Col ID Column Name              Data Type                    Data Length
---------- ------------------------ ---------------------------- -----------
         1 EMPLOYEE_ID              NUMBER                                22
         2 SALARY                   NUMBER                                22
         3 ORABCTAB_INST_ID$        NUMBER                                22
         4 ORABCTAB_CHAIN_ID$       NUMBER                                22
         5 ORABCTAB_SEQ_NUM$        NUMBER                                22
         6 ORABCTAB_CREATION_TIME$  TIMESTAMP(6) WITH TIME ZONE           13
         7 ORABCTAB_USER_NUMBER$    NUMBER                                22
         8 ORABCTAB_HASH$           RAW                                 2000
         9 ORABCTAB_SIGNATURE$      RAW                                 2000
        10 ORABCTAB_SIGNATURE_ALG$  NUMBER                                22
        11 ORABCTAB_SIGNATURE_CERT$ RAW                                   16
        12 ORABCTAB_SPARE$          RAW                                 2000

12 rows selected.

SQL>

Step 2 : Insert rows into the blockchain table

Insert a first row into the blockchain table.


SQL> INSERT INTO ledger_emp VALUES (106,12000);

1 row created.

SQL> COMMIT;

Commit complete.

SQL>

Display the internal values of the first row of the chain.


SQL> COL "Chain date" FORMAT A17
SQL> COL "Chain ID" FORMAT 99999999
SQL> COL "Seq Num" FORMAT 99999999
SQL> COL "User Num" FORMAT 9999999
SQL> COL "Chain HASH" FORMAT 99999999999999
SQL> SELECT ORABCTAB_CHAIN_ID$ "Chain ID", ORABCTAB_SEQ_NUM$ "Seq Num",
            to_char(ORABCTAB_CREATION_TIME$,'dd-Mon-YYYY hh-mi') "Chain date",
            ORABCTAB_USER_NUMBER$ "User Num", ORABCTAB_HASH$ "Chain HASH"
     FROM   ledger_emp;

 Chain ID   Seq Num Chain date        User Num
--------- --------- ----------------- --------
Chain HASH
--------------------------------------------------------------------------------
       14         1 06-Apr-2020 12-26      119
5812238B734B019EE553FF8A7FF573A14CFA1076AB312517047368D600984CFAB001FA1FF2C98B13
9AB03DDCCF8F6C14ADF16FFD678756572F102D43420E69B3

SQL>

Connect as HR and insert a row into the blockchain table as if your auditing application would do it. First grant the INSERT privilege on the table to HR.
```
SQL> GRANT insert ON ledger_emp TO hr;

Grant succeeded.

SQL> 
```

Connect as HR and insert a new row.


SQL> CONNECT hr@PDB21
Enter password: password
Connected.
SQL> INSERT INTO  auditor.ledger_emp VALUES (106,24000);

1 row created.

SQL> COMMIT;

Commit complete.

SQL>

Connect as AUDITOR and display the internal and external values of the blockchain table rows.


SQL> CONNECT auditor@PDB21
Enter password: password
Connected.
SQL> SELECT ORABCTAB_CHAIN_ID$ "Chain ID", ORABCTAB_SEQ_NUM$ "Seq Num",
               to_char(ORABCTAB_CREATION_TIME$,'dd-Mon-YYYY hh-mi') "Chain date",
               ORABCTAB_USER_NUMBER$ "User Num", ORABCTAB_HASH$ "Chain HASH",
               employee_id, salary
        FROM   ledger_emp;

 Chain ID   Seq Num Chain date        User Num
--------- --------- ----------------- --------
Chain HASH
--------------------------------------------------------------------------------
EMPLOYEE_ID     SALARY
----------- ----------
       14         1 06-Apr-2020 12-26      119
5812238B734B019EE553FF8A7FF573A14CFA1076AB312517047368D600984CFAB001FA1FF2C98B13
9AB03DDCCF8F6C14ADF16FFD678756572F102D43420E69B3
        106      12000

       14         2 06-Apr-2020 12-28      118
BBCDACC41B489DFBD8E28244841411937BD716F987BE750146572C555311E377D6DBA28D392C61E7
D75BA47BFCB3A2F4920A2C149409E89FBA63E10549DF4F47
        106      24000

SQL>

Observe that the user number is different. This value is the same value as V$SESSION.USER# column.

Step 3 : Delete rows from the blockchain table

Delete the row inserted by HR.


SQL> DELETE FROM ledger_emp WHERE ORABCTAB_USER_NUMBER$ = 119;
DELETE FROM ledger_emp WHERE ORABCTAB_USER_NUMBER$ = 119
          *
ERROR at line 1:
ORA-05715: operation not allowed on the blockchain table

SQL>

You cannot delete rows in a blockchain table with the DML DELETE command. You must use the DBMS_BLOCKCHAIN_TABLE package.


SQL> SET SERVEROUTPUT ON
SQL> DECLARE
   NUMBER_ROWS NUMBER;
BEGIN
   DBMS_BLOCKCHAIN_TABLE.DELETE_EXPIRED_ROWS('AUDITOR','LEDGER_EMP', null, NUMBER_ROWS);
   DBMS_OUTPUT.PUT_LINE('Number of rows deleted=' || NUMBER_ROWS);
END;
/    2    3    4    5    6    7
Number of rows deleted=0

PL/SQL procedure successfully completed.

SQL>

You can delete rows in a blockchain table only by using the DBMS_BLOCKCHAIN_TABLE package, and only rows that are outside the retention period. This is the reason why the procedure successfully completes without deleting any row.

Truncate the table.


SQL> TRUNCATE TABLE ledger_emp;
TRUNCATE TABLE ledger_emp
               *
ERROR at line 1:
ORA-05715: operation not allowed on the blockchain table

SQL>

Specify now that rows cannot be deleted until 15 days after they were created.
```
SQL> ALTER TABLE ledger_emp NO DELETE UNTIL 15 DAYS AFTER INSERT;
ALTER TABLE ledger_emp NO DELETE UNTIL 15 DAYS AFTER INSERT
*
ERROR at line 1:
ORA-05731: blockchain table LEDGER_EMP cannot be altered

SQL> 
```
Why cannot you change this attribute? You created the table with the NO DELETE LOCKED attribute. The LOCKED clause indicates that you can never subsequently modify the row retention.

Step 4 : Drop the blockchain table

Drop the table.
```
SQL> DROP TABLE ledger_emp;
DROP TABLE ledger_emp
           *
ERROR at line 1:
ORA-05723: drop blockchain table LEDGER_EMP not allowed

SQL> 
```
Observe that the error message is slightly different. The error message from the TRUNCATE TABLE command explained that the operation was not possible on a blockchain table. The current error message explains that the DROP TABLE is not possible but on this LEDGER_EMP table.

The blockchain table was created so that it cannot be dropped before 31 days of inactivity.

Change the behavior of the table to allow a lower retention.


SQL> ALTER TABLE ledger_emp NO DROP UNTIL 1 DAYS IDLE;
ALTER TABLE auditor.ledger_emp NO DROP UNTIL 1 DAYS IDLE
*
ERROR at line 1:
ORA-05732: retention value cannot be lowered

SQL> ALTER TABLE ledger_emp NO DROP UNTIL 40 DAYS IDLE;

Table altered.

SQL>

You can only increase the retention value. This prohibits the possibility to drop and remove any historical information that needs to be kept for security purposes.

Step 5 : Check the validity of rows in the blockchain table

Create another blockchain table AUDITOR.LEDGER_TEST. Rows cannot be deleted until 5 days after they were inserted, allowing rows to be deleted. Moreover the blockchain table can be dropped only after 1 day of inactivity.


SQL> CREATE BLOCKCHAIN TABLE auditor.ledger_test (id NUMBER, label VARCHAR2(2))
       NO DROP UNTIL 1 DAYS IDLE
       NO DELETE UNTIL 5 DAYS AFTER INSERT
       HASHING USING "SHA2_512" VERSION "v1";
 2    3    4  CREATE BLOCKCHAIN TABLE auditor.ledger_test (id NUMBER, label VARCHAR2(2))
*
ERROR at line 1:
ORA-05741: minimum retention time too low, should be at least 16 days

SQL> CREATE BLOCKCHAIN TABLE auditor.ledger_test (id NUMBER, label VARCHAR2(2))
       NO DROP UNTIL 16 DAYS IDLE
       NO DELETE UNTIL 16 DAYS AFTER INSERT
       HASHING USING "SHA2_512" VERSION "v1";

Table created.

SQL>

Connect as HR and insert a row into the blockchain table as if your auditing application would do it. First grant the INSERT privilege on the table to HR
```
SQL> GRANT insert ON auditor.ledger_test TO hr;

Grant succeeded.

SQL> 
```

Connect as HR and insert a new row.


SQL> CONNECT hr@PDB21
Enter password: password
Connected.
SQL> INSERT INTO auditor.ledger_test VALUES (1,'A1');

1 row created.

SQL> COMMIT;

Commit complete.

SQL>

Connect as AUDITOR and display the row inserted.


SQL> CONNECT auditor@PDB21
Enter password: password
Connected.
SQL> SELECT * FROM auditor.ledger_test;

        ID LA
---------- --
         1 A1

SQL>

Verify that the content of the rows are still valid. Use the DBMS_BLOCKCHAIN_TABLE.VERIFY_ROWS.


SQL> CONNECT auditor@PDB21
Enter password: password
Connected.
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
   row_count NUMBER;
   verify_rows NUMBER;
   instance_id NUMBER;
BEGIN
  FOR instance_id IN 1 .. 2 LOOP
    SELECT COUNT(*) INTO row_count FROM auditor.ledger_test WHERE ORABCTAB_INST_ID$=instance_id;
    DBMS_BLOCKCHAIN_TABLE.VERIFY_ROWS('AUDITOR','LEDGER_TEST', NULL, NULL, instance_id, NULL, verify_rows);
    DBMS_OUTPUT.PUT_LINE('Number of rows verified in instance Id '|| instance_id || ' = '|| row_count);
  END LOOP;
END;
/
Number of rows verified in instance Id 1 = 1
Number of rows verified in instance Id 2 = 0

PL/SQL procedure successfully completed.

SQL> EXIT
$