1. データベース管理者のための主要20c新機能の学習
  2. データベース管理者のための主要20c新機能の学習
  3. ツールおよび言語
  4. 分析SQL関数および統計関数
  5. 新しい分析関数および統計集計関数
  6. 演習: CHECKSUM関数を使用したデータ改ざんの検出

演習: CHECKSUM関数を使用したデータ改ざんの検出

この演習では、CHECKSUM集計関数を使用して表の変更を検出する方法を示します。この関数は、列、定数、バインド変数またはそれらを含む式に適用できます。ADTおよびJSON以外のすべてのデータ型がサポートされます。表内の行の順序は、結果に影響しません。

  1. 新しい演習を開始する前に、演習の環境の推奨事項を参照してください。
  2. /home/oracle/labs/M104784GC10/setup_SH_tables.shシェル・スクリプトを実行して、SH.SALES表およびSH.TIMES表を作成してロードします。
    $ cd /home/oracle/labs/M104784GC10
$ /home/oracle/labs/M104784GC10/setup_SH_tables.sh
SQL*Plus: Release 20.0.0.0.0 - Production on Wed Mar 25 03:18:51 2020
Version 20.2.0.0.0

Copyright (c) 1982, 2020, Oracle.  All rights reserved.

Last Successful login time: Wed Mar 25 2020 03:17:43 +00:00

Connected to:
Oracle Database 20c Enterprise Edition Release 20.0.0.0.0 - Production
Version 20.2.0.0.0

Tablespace dropped.

Tablespace created.

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 Wed Mar 25 03:19:13 2020
Version 20.2.0.0.0

Copyright (c) 1982, 2020, Oracle.  All rights reserved.

Last Successful login time: Wed Mar 25 2020 03:18:51 +00:00

Connected to:
Oracle Database 20c Enterprise Edition Release 20.0.0.0.0 - Production
Version 20.2.0.0.0

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

SQL*Loader: Release 20.0.0.0.0 - Production on Wed Mar 25 03:10:13 2020
Version 20.2.0.0.0

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

SQL*Loader: Release 20.0.0.0.0 - Production on Wed Mar 25 03:10:45 2020
Version 20.2.0.0.0

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 300.
Save data point reached - logical record count 400.
Save data point reached - logical record count 500.
Save data point reached - logical record count 600.
Save data point reached - logical record count 700.
Save data point reached - logical record count 800.
Save data point reached - logical record count 900.
Save data point reached - logical record count 1000.
Save data point reached - logical record count 1100.
Save data point reached - logical record count 1200.
Save data point reached - logical record count 1300.
Save data point reached - logical record count 1400.
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.


PL/SQL procedure successfully completed.

SQL>
  3. 各月および会計期間の最終日には、法規制上の理由から、販売内容を格納する監査表があります。1998会計年度末の販売金額を確認します。
    SQL> CONNECT system@PDB20 
Enter password: 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>
  4. 監査のためにデータを格納する前に、CHECKSUM値に注意してください。これは、誰も古い売上を改ざんしていないことを確認するのに役立ちます。
    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>
  5. 一方、SHセッションと呼ばれる別のターミナル・セッションでは、販売金額を更新するバッチが実行されます。
    $ /home/oracle/labs/M104784GC10/app_SH_tables.sh				  
SQL*Plus: Release 20.0.0.0.0 - Production on Wed Mar 25 03:28:37 2020
Version 20.2.0.0.0

Copyright (c) 1982, 2020, Oracle.  All rights reserved.

Last Successful login time: Wed Mar 25 2020 03:20:17 +00:00

Connected to:
Oracle Database 20c Enterprise Edition Release 20.0.0.0.0 - Production
Version 20.2.0.0.0

525 rows updated.

Commit complete.

Disconnected from Oracle Database 20c Enterprise Edition Release 20.0.0.0.0 - Production
Version 20.2.0.0.0
$
  6. 最初のターミナル・セッションで、誰も古い売上を改ざんしていないことを確認します。
    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>

    チェックサム値はステップ4で取得した値と異なるため、誰かがデータを改ざんしました。

  7. 誰かが古い売上を改ざんしようとした場合、どうなりますか。SHセッションで、古い売上の一部を更新した後、トランザクションをロールバックします。
    $ sqlplus sh@PDB20

SQL*Plus: Release 20.0.0.0.0 - Production on Wed Mar 25 03:45:09 2020
Version 20.2.0.0.0

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:
Oracle Database 20c Enterprise Edition Release 20.0.0.0.0 - Production
Version 20.2.0.0.0

SQL> UPDATE sh.sales SET amount_sold = amount_sold*2 WHERE time_id='30-NOV-98';

525 rows updated.

SQL> ROLLBACK;

Rollback complete.

SQL>
  8. 最初のターミナル・セッションで、誰も古い売上を改ざんしていないことを確認します。
    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>

    列のチェックサム値は、ロールバックされた更新前と同じままです。

  9. また、1998会計年度末の販売数量とチェックサム値を確認します。
    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>

    ご覧のとおり、売上げに対する販売数量は1です。

    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>

    チェックサム値は0で、別の数量値と区別できる値ではありません。

    DISTINCT (またはUNIQUE - UNIQUEはANSI規格ではなくOracle固有のキーワード)を使用するとどうなりますか。 

    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>
  10. SHセッションで、すべての売上に対する数量を2倍にします。
    SQL> UPDATE sh.sales SET quantity_sold = 2;

918843 rows updated.

SQL> COMMIT;

Commit complete.

SQL>
  11. 最初のターミナル・セッションで、誰も古い売上を改ざんしていないことを確認します。
    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>

    列のチェックサム値は、コミットされた更新前と同じままです。

    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>

    列のチェックサム値は、ステップ9で取得した値とは異なります。

  12. NULLはどのように考慮されますか。引き続き最初のターミナル・セッションで、SALES_TRANSACTIONS_EXT表に格納され、売上額が1282.7である1998会計年度末の古い売上を誰も改ざんしていないことを確認します。
    1. まず、売上額が1282.7である1998会計年度末の古い売上のチェックサム値を取得します。
      SQL> SELECT CHECKSUM(DISTINCT amount_sold), CHECKSUM(DISTINCT quantity_sold) FROM sh.SALES_TRANSACTIONS_EXT s
     JOIN sh.times t ON (s.time_id = t.time_id)
     WHERE fiscal_month_number = 12 AND fiscal_year = 1998 AND amount_sold = to_number('1282.7');

CHECKSUM(AMOUNT_SOLD) CHECKSUM(QUANTITY_SOLD)
--------------------- -----------------------
               422955                  863352

SQL>
    2. SHセッションで、ユーザーは、販売金額が1282.7である1998会計年度末の古い販売に対して、販売数量をNULL値で置換するバッチを起動しました。
      SQL> @/home/oracle/labs/M104784GC10/batch.sql
...
SQL> EXIT
$
    3. 最初のターミナル・セッションで、更新後に、販売金額が1282.7である1998会計年度末の古い売上の新しいチェックサム値を取得します。
      SQL> SELECT CHECKSUM(DISTINCT amount_sold), CHECKSUM(DISTINCT quantity_sold) FROM sh.SALES_TRANSACTIONS_EXT s
     JOIN sh.times t ON (s.time_id = t.time_id)
     WHERE fiscal_month_number = 12 AND fiscal_year = 1998 AND amount_sold = to_number('1282.7');

CHECKSUM(AMOUNT_SOLD) CHECKSUM(QUANTITY_SOLD)
--------------------- -----------------------
               422955                  863352

SQL> SELECT amount_sold, quantity_sold FROM sh.SALES_TRANSACTIONS_EXT 
     WHERE  amount_sold = to_number('1282.7') 
     AND quantity_sold IS NULL;

AMOUNT_SOLD QUANTITY_SOLD
----------- -------------
     1282.7

SQL> EXIT
$

      CHECKSUM列のNULL値は無視されることに注意してください。

親トピック: 新しい分析関数および統計集計関数