Practice: Managing Blockchain Tables and Rows

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

  1. Before starting any new practice, refer to the practices environment recommendations.
  2. Create the blockchain table named AUDITOR.LEDGER_EMP to maintain a tamper-resistant ledger of current and historical transactions about HR.EMPLOYEES in PDB20. 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.
    1. Before starting creating the table, execute the /home/oracle/labs/M104781GC10/setup_user.sh shell script.
      $ cd /home/oracle/labs/M104781GC10
      $ /home/oracle/labs/M104781GC10/setup_user.sh
      SQL*Plus: Release 20.0.0.0.0 - Production on Mon Mar 9 05:34:10 2020
      Version 20.2.0.0.0
      
      Copyright (c) 1982, 2020, Oracle.  All rights reserved.
      
      
      Connected to:
      Oracle Database 20c Enterprise Edition Release 20.0.0.0.0 - Production
      Version 20.2.0.0.0
      
      
      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> Disconnected from Oracle Database 20c Enterprise Edition Release 20.0.0.0.0 - Production
      Version 20.2.0.0.0
      
      SQL*Plus: Release 20.0.0.0.0 - Production on Mon Mar 9 05:34:16 2020
      Version 20.2.0.0.0
      
      Copyright (c) 1982, 2020, Oracle.  All rights reserved.
      
      
      Connected to:
      Oracle Database 20c Enterprise Edition Release 20.0.0.0.0 - Production
      Version 20.2.0.0.0
      SQL> DROP USER auditor CASCADE;
      DROP USER auditor CASCADE
                *
      ERROR at line 1:
      ORA-01918: user 'AUDITOR' does not exist
      
      
      SQL> ALTER SYSTEM SET db_create_file_dest='/home/oracle/labs';
      
      System altered.
      
      SQL>
      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
      Disconnected from Oracle Database 20c Enterprise Edition Release 20.0.0.0.0 - Production
      Version 20.2.0.0.0
      $
    2. Create the blockchain table named AUDITOR.LEDGER_EMP.
      $ sqlplus auditor@PDB20
      SQL*Plus: Release 20.0.0.0.0 - Production on Mon Mar 9 05:37:25 2020
      Version 20.2.0.0.0
      
      Copyright (c) 1982, 2020, Oracle.  All rights reserved.
      
      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

      Note:

      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>
    3. 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>
    4. Show the description of the table.
      SQL> DESC ledger_emp
       Name                                      Null?    Type
       ----------------------------------------- -------- ----------------------------
       EMPLOYEE_ID                                        NUMBER
       SALARY                                             NUMBER
      
      SQL>

      Note:

      Observe that the description displays only the visible columns.
    5. 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>
  3. Insert rows into the blockchain table as if your auditing application would do it.
    1. Insert a first row into the blockchain table.
      SQL> INSERT INTO ledger_emp VALUES (106,12000);
      
      1 row created.
      
      SQL> COMMIT;
      
      Commit complete.
      
      SQL>
    2. 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>
    3. 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>
    4. Connect as HR and insert a new row.
      SQL> CONNECT hr@PDB20
      Enter password: password
      Connected.
      SQL> INSERT INTO  auditor.ledger_emp VALUES (106,24000);
      
      1 row created.
      
      SQL> COMMIT;
      
      Commit complete.
      
      SQL>
    5. Connect as AUDITOR and display the internal and external values of the blockchain table rows.
      SQL> CONNECT auditor@PDB20
      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.

  4. Delete the row inserted by HR.
    SQL> DELETE FROM ledger_emp WHERE ORABCTAB_USER_NUMBER$ = 106;
    DELETE FROM ledger_emp WHERE ORABCTAB_USER_NUMBER$ = 106
              *
    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.

    If the Oracle Database release installed is 20.0.0, then the procedure to use is DBMS_BLOCKCHAIN_TABLE.DELETE_ROWS and not DBMS_BLOCKCHAIN_TABLE.DELETE_EXPIRED_ROWS.

  5. 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>
  6. 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.

  7. 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>

    Note:

    Observe that the error message is slightly different. The error message from the two previous commands 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.

  8. 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.

  9. 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, but to .
    1. Create the blockchain table.
      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>
    2. 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>
    3. Connect as HR and insert a new row.
      SQL> CONNECT hr@PDB20
      Enter password: password
      Connected.
      SQL> INSERT INTO auditor.ledger_test VALUES (1,'A1');
      
      1 row created.
      
      SQL> COMMIT;
      
      Commit complete.
      
      SQL>
    4. Connect as AUDITOR and display the row inserted.
      SQL> CONNECT auditor@PDB20
      Enter password: password
      Connected.
      SQL> SELECT * FROM auditor.ledger_test;
      
              ID LA
      ---------- --
               1 A1
      
      SQL>
  10. Regularly verify that the content of the rows are still valid.
    1. Use the DBMS_BLOCKCHAIN_TABLE.VERIFY_ROWS to validate the rows.
      SQL> CONNECT auditor@PDB20
      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
      $