Practice: Detecting Data Tampering with the CHECKSUM Function

Overview

This practice shows how to use the CHECKSUM aggregate function to detect changes in a table. The function can be applied on a column, a constant, a bind variable, or an expression involving them. All datatypes except ADT and JSON are supported. The order of the rows in the table does not affect the result.

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

Step 1 : Set up the environment

  • Execute the /home/oracle/labs/M104784GC10/setup_SH_tables.sh shell script to create and load the SH.SALES and SH.TIMES tables.

    
    $ cd /home/oracle/labs/M104784GC10
    $ /home/oracle/labs/M104784GC10/setup_SH_tables.sh
    ...
    Tablespace dropped.
    
    Tablespace created.
    
    Copyright (c) 1982, 2020, Oracle.  All rights reserved.
    
    Last Successful login time: Wed Mar 25 2020 03:18:51 +00:00
    
    Connected to:
    
    specify password for SH as parameter 1:
    
    specify default tablespace for SH as parameter 2:
    
    specify temporary tablespace for SH as parameter 3:
    
    specify password for SYS as parameter 4:
    
    specify directory path for the data files as parameter 5:
    
    writeable directory path for the log files as parameter 6:
    
    specify version as parameter 7:
    
    specify connect string as parameter 8:
    
    Session altered.
    
    User dropped.
    ...
    loading TIMES using:
    /home/oracle/labs/M104784GC10/sales_history/time_v3.ctl
    /home/oracle/labs/M104784GC10/sales_history/time_v3.dat
    /home/oracle/labs/M104784GC10/time_v3.log
    
    Copyright (c) 1982, 2020, Oracle and/or its affiliates.  All rights reserved.
    
    Path used:      Direct
    Save data point reached - logical record count 1000.
    
    Load completed - logical record count 1826.
    
    Table TIMES:
      1826 Rows successfully loaded.
    ...
    loading additonal SALES using:
    /home/oracle/labs/M104784GC10/sales_history/dmsal_v3.ctl
    /home/oracle/labs/M104784GC10/sales_history/dmsal_v3.dat
    /home/oracle/labs/M104784GC10/dmsal_v3.log
    
    Copyright (c) 1982, 2020, Oracle and/or its affiliates.  All rights reserved.
    
    Path used:      Direct
    Save data point reached - logical record count 100.
    Save data point reached - logical record count 200.
    ...
    Save data point reached - logical record count 1500.
    SQL*Loader-2026: the load was aborted because SQL Loader cannot continue.
    
    Load completed - logical record count 1600.
    
    Table SALES:
      1500 Rows successfully loaded.
    ...
    gathering statistics ...
    
    PL/SQL procedure successfully completed.
    
    SQL> 

Step 2 : Examine data before tampering

  • At the end of each month and fiscal period, for legislative reasons, there is an audit table that stores what was sold. Verify the amount sold at the end of fiscal year 1998.

    
    $ sqlplus system@PDB21
    Enter password:
    SQL> SET PAGES 100
    SQL> SELECT amount_sold FROM sh.sales s
         JOIN sh.times t ON (s.time_id = t.time_id)
         WHERE fiscal_month_number = 12 AND fiscal_year = 1998;
    
    AMOUNT_SOLD
    -----------
          22.99
          44.99
           7.99
         149.99
    ...
          11.99
          44.99
          49.99
          11.99
          44.99
          27.99
         149.99
          44.99
    
    12400 rows selected.
    
    SQL> 
  • Before storing the data for auditing, note the CHECKSUM value. This will help you ensure that no one is tampering with old sales.

    
    SQL> SELECT CHECKSUM(amount_sold) FROM sh.sales s
         JOIN sh.times t ON (s.time_id = t.time_id)
         WHERE fiscal_month_number = 12 AND fiscal_year = 1998;
    
    CHECKSUM(AMOUNT_SOLD)
    ---------------------
                   793409
    
    SQL> 
  • Meanwhile in another terminal session, called SH session, someone executes a batch that updates the amount sold.

    
    $ /home/oracle/labs/M104784GC10/app_SH_tables.sh				  
    
    Copyright (c) 1982, 2020, Oracle.  All rights reserved.
    
    Last Successful login time: Wed Mar 25 2020 03:20:17 +00:00
    
    Connected to:
    
    525 rows updated.
    
    Commit complete.
    
    $

Step 3 : Examine data after tampering

  • In the initial terminal session, check that no one tampered with old sales.

    
    SQL> SELECT CHECKSUM(amount_sold) FROM sh.sales s
         JOIN sh.times t ON (s.time_id = t.time_id)
         WHERE fiscal_month_number = 12 AND fiscal_year = 1998;
    
    CHECKSUM(AMOUNT_SOLD)
    ---------------------
                   835564
    
    SQL> 

    Since the checksum value is different from the value retrieved in the earlier step, someone tampered the data.

  • What happens if someone attempted to tamper with old sales? In the SH session, update some old sales and then roll back the transaction.

    
    $ sqlplus sh@PDB21
    
    Copyright (c) 1982, 2020, Oracle.  All rights reserved.
    
    Enter password: password
    Last Successful login time: Wed Mar 25 2020 03:28:37 +00:00
    
    Connected to:
    
    SQL> UPDATE sh.sales SET amount_sold = amount_sold*2 WHERE time_id='30-NOV-98';
    
    525 rows updated.
    
    SQL> ROLLBACK;
    
    Rollback complete.
    
    SQL> 
  • In the initial terminal session, check that no one tampered with old sales.

    
    SQL> SELECT CHECKSUM(amount_sold) FROM sh.sales s
         JOIN sh.times t ON (s.time_id = t.time_id)
         WHERE fiscal_month_number = 12 AND fiscal_year = 1998;
    
    CHECKSUM(AMOUNT_SOLD)
    ---------------------
                   835564
    
    SQL> 

    The checksum value for the column is still the same as it was before the update was rolled back.

  • Verify the quantity sold at the end of fiscal year 1998 and the checksum value.

    
    SQL> SELECT DISTINCT quantity_sold FROM sh.sales s
         JOIN sh.times t ON (s.time_id = t.time_id)
         WHERE fiscal_month_number = 12 AND fiscal_year = 1998;
    
    QUANTITY_SOLD
    -------------
                1
    
    SQL> 

    As you can see, the quantity sold for any sales is one.

    
    SQL> SELECT CHECKSUM(quantity_sold) FROM sh.sales s
         JOIN sh.times t ON (s.time_id = t.time_id)
         WHERE fiscal_month_number = 12 AND fiscal_year = 1998;
    
    CHECKSUM(QUANTITY_SOLD)
    -----------------------
                          0
    
    SQL> 

    The checksum value is 0 which is not a distinguishable value from another quantity value.

    What if you use the DISTINCT (or UNIQUE. Note that UNIQUE is an Oracle-specific keyword and not ANSI standard.)?

    
    SQL> SELECT CHECKSUM(DISTINCT quantity_sold) FROM sh.sales s
         JOIN sh.times t ON (s.time_id = t.time_id)
         WHERE fiscal_month_number = 12 AND fiscal_year = 1998;
    
    CHECKSUM(DISTINCTQUANTITY_SOLD)
    -------------------------------
                             863352
    
    SQL> 
  • In the SH session, double the quantity for all sales.

    
    SQL> UPDATE sh.sales SET quantity_sold = 2;
    
    918843 rows updated.
    
    SQL> COMMIT;
    
    Commit complete.
    
    SQL> 
  • In the initial terminal session, check that no one tampered with old sales.

    
    SQL> SELECT CHECKSUM(quantity_sold) FROM sh.sales s
         JOIN sh.times t ON (s.time_id = t.time_id)
         WHERE fiscal_month_number = 12 AND fiscal_year = 1998;
    
    CHECKSUM(AMOUNT_SOLD)
    ---------------------
                        0
    
    SQL> 

    The checksum value for the column is still the same as it was before the update was committed.

    
    SQL> SELECT CHECKSUM(DISTINCT quantity_sold) FROM sh.sales s
         JOIN sh.times t ON (s.time_id = t.time_id)
         WHERE fiscal_month_number = 12 AND fiscal_year = 1998;
    
    CHECKSUM(DISTINCTQUANTITY_SOLD)
    -------------------------------
                              65515
    
    SQL> 

    The checksum value for the column is different from the one retrieved previously.

  • How is NULL considered? Still in the initial terminal session, check that no one tampered with customer email addresses.
    • First, get the checksum value of the customer email addresses whose CUST_INCOME_LEVEL is equal to 300000 or higher.

      
      SQL> SELECT cust_email FROM sh.customers WHERE cust_income_level ='L: 300,000 and above';
      
      CUST_EMAIL
      --------------------------------------------------
      Gowen@company.example.com
      Gowen@company.example.com
      ...
      Krishnan@company.example.com
      Prabu@company.example.com
      
      1684 rows selected.
      
      SQL> SELECT CHECKSUM(cust_email) FROM sh.customers;
      
      CHECKSUM(CUST_EMAIL)
      --------------------
                    107013
      
      SQL> SELECT CHECKSUM(DISTINCT cust_email) FROM sh.customers;
      
      CHECKSUM(DISTINCTCUST_EMAIL)
      ----------------------------
                            227092
      
      SQL> 
    • In the SH session, set the customer email addresses to NULL for customers whose CUST_INCOME_LEVEL is equal to 300000 or higher.

      
      SQL> UPDATE sh.customers SET cust_email = NULL
           WHERE cust_income_level ='L: 300,000 and above';
      
      1684 rows updated.
      
      SQL> COMMIT;
      
      Commit complete.
      
      SQL>
    • In the initial terminal session, get the new checksum value of customer email addresses of customers whose CUST_INCOME_LEVEL is equal to 300000 or higher after the update.

      
      SQL> SELECT DISTINCT cust_email FROM sh.customers WHERE cust_income_level ='L: 300,000 and above';
      
      CUST_EMAIL
      --------------------------------------------------
      
      
      SQL> SELECT CHECKSUM(cust_email) FROM sh.customers;
      
      CHECKSUM(CUST_EMAIL)
      --------------------
                    577487
      
      SQL> SELECT CHECKSUM(DISTINCT cust_email) FROM sh.customers;
      
      CHECKSUM(DISTINCTCUST_EMAIL)
      ----------------------------
                            141231
      
      
      SQL> EXIT
      $

      Be aware that NULL values in the CHECKSUM column are ignored in external tables.