-- システムの操作内容が記録される通常のテーブルを作成（操作マスター） 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
oracle4engineer
nikinusu
gumamon
yoshidashingo
asei
iotengineer22
abemii
emiki
smt7174
kniino
salaboy
bbrbbq
shumei_ito
rasmusluckow
jeffersonlam
eileencodes
reverentgeek
paigeccino
tammyeverts
eitanlees
addyosmani
cromwellryan
colly
marktimemedia
sonatard
