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
    $