Copyright © 2023, Oracle and/or its affiliates
-- exhibition スキーマ
-- システムの操作内容が記録される通常のテーブルを作成(操作マスター)
CREATE TABLE operations (
operation_id number(10) PRIMARY KEY,
operation_name varchar2(50) NOT NULL UNIQUE
);
-- ユーザーの情報が記録される通常のテーブルを作成(ユーザーマスター)
CREATE TABLE users (
user_id number(10) PRIMARY KEY,
user_name varchar2(30) NOT NULL UNIQUE,
last_name varchar2(30) NOT NULL,
first_name varchar2(30) NOT NULL,
mail varchar2(60) NOT NULL
);
-- システムのログを記録するBlockchain Tableを作成
CREATE BLOCKCHAIN TABLE logs (
log_id number(30) PRIMARY KEY,
user_id number(10) NOT NULL,
operation_id number(10) NOT NULL,
error number(10),
ip_address varchar2(20),
log_stamp timestamp DEFAULT systimestamp NOT NULL,
CONSTRAINT user_id_fk FOREIGN KEY(user_id) REFERENCES users(user_id), -- ユーザーIDに対して外部キーを指定
CONSTRAINT operation_id_fk FOREIGN KEY(operation_id) REFERENCES operations(operation_id) -- 操作IDに対して外部キーを指定
)
NO DROP
NO DELETE UNTIL 365 DAYS AFTER INSERT
HASHING USING "SHA2_512" VERSION "v1"
;
-- 隠しカラムの表示をONにしてからLOGSテーブル定義を表示→Blockchain Table特有の隠しカラムが自動的に作成されている
SET COLINVISIBLE ON;
DESC logs;
-- OPERATIONSテーブルに操作情報をINSERT
INSERT INTO operations VALUES (1, 'login');
INSERT INTO operations VALUES (2, 'logout');
INSERT INTO operations VALUES (3, 'add_user');
INSERT INTO operations VALUES (4, 'delete_user');
INSERT INTO operations VALUES (5, 'insert_data');
-- USERSテーブルにユーザー情報をINSERT
INSERT INTO users VALUES (1, 'tanaka01', 'tanaka', 'tarou', '
[email protected]');
INSERT INTO users VALUES (2, 'hanako02', 'yamada', 'hanako', '
[email protected]');
INSERT INTO users VALUES (3, 'hiroshi03', 'suzuki', 'hiroshi', '
[email protected]');
-- 通常テーブルへのINSERTを確定
COMMIT;
-- 通常テーブルのINSERT確認
SELECT * FROM operations;
SELECT * FROM users;
-- LOGSテーブルにログ情報をINSERT
INSERT INTO logs VALUES (1, 1, 1, 0, '192.168.1.1', systimestamp);
INSERT INTO logs VALUES (2, 2, 1, 0, '192.168.1.2', systimestamp);
INSERT INTO logs VALUES (3, 2, 5, 0, '192.168.1.2', systimestamp);
-- (隠しカラム含め)LOGSテーブルをSELECTしてみる→ハッシュ値などの隠しカラムはまだ入っていない
SELECT log_id, user_id, operation_id, log_stamp, ORABCTAB_INST_ID$ "inst", ORABCTAB_CHAIN_ID$ "chain",
ORABCTAB_SEQ_NUM$ "seq", ORABCTAB_CREATION_TIME$ "time", ORABCTAB_USER_NUMBER$ "user", ORABCTAB_HASH$ "hash"
FROM logs;
-- Blockchain Tableへのinsertを確定
COMMIT;
-- (隠しカラム含め)LOGSテーブルをSELECTしてみる→ハッシュ値などの隠しカラムが自動的に埋められている
SELECT log_id, user_id, operation_id, log_stamp, ORABCTAB_INST_ID$ "inst", ORABCTAB_CHAIN_ID$ "chain",
ORABCTAB_SEQ_NUM$ "seq", ORABCTAB_CREATION_TIME$ "time", ORABCTAB_USER_NUMBER$ "user", ORABCTAB_HASH$ "hash"
FROM logs;
-- LOGSテーブルに対してUPDATEを試す
UPDATE logs SET user_id = 2 WHERE log_id = 1;
-- LOGSテーブルに対してDELETEを試す
DELETE FROM logs WHERE user_id = 1;
-- LOGSテーブルに対してTRUNCATE(テーブル上のデータ全削除)を試す
TRUNCATE TABLE logs;
-- LOGSテーブルに対してDROP(テーブルごと削除)を試す
DROP TABLE logs CASCADE CONSTRAINTS;
-- OUTPUTをON
SET SERVEROUTPUT ON;
-- LOGSテーブルの行の整合性を検証
DECLARE
verify_rows NUMBER;
BEGIN
DBMS_BLOCKCHAIN_TABLE.VERIFY_ROWS('exhibition','logs', NULL, NULL, NULL, NULL, verify_rows);
DBMS_OUTPUT.PUT_LINE('Number of rows verified = '|| verify_rows);
END;
/
-- USERSテーブルとLOGSテーブルに対して行を追加INSERT
INSERT INTO users VALUES (4, 'kenta04', 'maeda', 'kenta', '
[email protected]');
INSERT INTO users VALUES (5, 'haruka05', 'yamamoto', 'haruka', '
[email protected]');
INSERT INTO logs VALUES (4, 1, 3, 0, '192.168.1.1', systimestamp);
INSERT INTO logs VALUES (5, 3, 3, 0, '192.168.1.10', systimestamp);
INSERT INTO logs VALUES (6, 3, 5, 0, '192.168.1.10', systimestamp);
-- 通常テーブルのINSERT確認
SELECT * FROM users;
-- (隠しカラム含め)LOGSテーブルをSELECTしてみる→ハッシュ値などの隠しカラムはまだ入っていない
SELECT log_id, user_id, operation_id, log_stamp, ORABCTAB_INST_ID$ "inst", ORABCTAB_CHAIN_ID$ "chain",
ORABCTAB_SEQ_NUM$ "seq", ORABCTAB_CREATION_TIME$ "time", ORABCTAB_USER_NUMBER$ "user", ORABCTAB_HASH$ "hash"
FROM logs;
-- 通常テーブルとBlockchain TableへのINSERTを取り消し
ROLLBACK;
-- 通常テーブルのROLLBACK確認
SELECT * FROM users;
-- Blockchain TableのROLLBACK確認
SELECT log_id, user_id, operation_id, log_stamp, ORABCTAB_INST_ID$ "inst", ORABCTAB_CHAIN_ID$ "chain",
ORABCTAB_SEQ_NUM$ "seq", ORABCTAB_CREATION_TIME$ "time", ORABCTAB_USER_NUMBER$ "user", ORABCTAB_HASH$ "hash"
FROM logs;
-- 再度USERSテーブルとLOGSテーブルに対して行を追加INSERT
INSERT INTO users VALUES (4, 'kenta04', 'maeda', 'kenta', '
[email protected]');
INSERT INTO users VALUES (5, 'haruka05', 'yamamoto', 'haruka', '
[email protected]');
INSERT INTO logs VALUES (4, 1, 3, 0, '192.168.1.1', systimestamp);
INSERT INTO logs VALUES (5, 3, 3, 0, '192.168.1.10', systimestamp);
INSERT INTO logs VALUES (6, 3, 5, 0, '192.168.1.10', systimestamp);
-- 通常テーブルのINSERT確認
SELECT * FROM users;
-- (隠しカラム含め)LOGSテーブルをSELECTしてみる→ハッシュ値などの隠しカラムはまだ入っていない
SELECT log_id, user_id, operation_id, log_stamp, ORABCTAB_INST_ID$ "inst", ORABCTAB_CHAIN_ID$ "chain",
ORABCTAB_SEQ_NUM$ "seq", ORABCTAB_CREATION_TIME$ "time", ORABCTAB_USER_NUMBER$ "user", ORABCTAB_HASH$ "hash"
FROM logs;
-- 通常テーブルとBlockchain TableへのINSERTを確定
COMMIT;
-- (隠しカラム含め)LOGSテーブルをSELECTしてみる→ハッシュ値などの隠しカラムが自動的に埋められている
SELECT log_id, user_id, operation_id, log_stamp, ORABCTAB_INST_ID$ "inst", ORABCTAB_CHAIN_ID$ "chain",
ORABCTAB_SEQ_NUM$ "seq", ORABCTAB_CREATION_TIME$ "time", ORABCTAB_USER_NUMBER$ "user", ORABCTAB_HASH$ "hash"
FROM logs;
-- ログのユーザー情報と操作内容を表示する監査ログのVIEWを作成…通常のテーブルとBlockchain TableをJOINしたVIEWの利用が可能
CREATE VIEW audit_logs AS
SELECT l.log_id "ID", o.operation_name "operation", l.ip_address "ip_address",
l.log_stamp "time", u.first_name || '.' || u.last_name "name", l.ORABCTAB_HASH$ as hash
FROM logs l, users u, operations o
WHERE l.user_id = u.user_id and l.operation_id = o.operation_id
ORDER BY log_stamp
WITH READ ONLY
;
-- 作成したVIEWで監査ログを確認
SELECT * FROM audit_logs;
SQLスクリプト ~コピペしてご利用ください~
112