内容
新型コロナの影響で、在宅ワークが増え、社外からVPN接続により業務される方が多い状況かと思います。
VPNの接続を切れば、社内ネットワークに設定されたWebフィルタリングの影響を受けなくなり、普段許可していないサイトの閲覧が発生しがちです。すると、その利用端末からさらには他の業務端末までセキュリティ上の危険に晒される可能性があります。
そこで、普段から業務用コンピュータの監査を強化が求められます。今回は、監査の材料として、下記環境でのWeb閲覧履歴からWebフィルタリングのホワイトリストにないURLがないか確認し、あればそれをCSVファイルで出力する方法を掲載いたします。
環境
- 業務機： Mac
- macOS: 10.15.x Catalina
- ブラウザ： Safari
- 使用言語: AppleScript, SQLite3
必要な作業
・ホワイトリストのテーブルをCSVで用意
あとでデータベース形式のWeb閲覧履歴と比較するためのテーブルをCSVファイルで用意します。CSVなら、SQLite3 でテーブルとしてインポートすることができます。
・History.dbをFinderから別の場所へコピー
Safari のWeb閲覧履歴は、macOS Catalina だとデータベース形式のファイルに保存されています。
ファイルパス：
/Users/(user)/Library/Safari/History.db
このファイルは直接開ません。アクセス権を変更しても不可です。このファイルを別の場所にコピーすれば開けるようになります。シェルの cp コマンドではコピー不可のため、Finderでコピーする必要があります。スクリプトとしてそれを実現するには Apple Scriptを利用する必要がります。
・SQLite3 で History.db とホワイトリストを比較して結果をCSVファイルで出力
SQLite3 は一般的な SQL のコマンドがだいたい使えます。ホワイトリストのURLを含まない閲覧履歴のみに絞って、結果をCSVファイルとして書き出します。
具体的手順
ホワイトリストのテーブルをCSVファイルで用意
テーブルは2カラム、それぞれ name と allowedURL にします。
| name | allowedURL |
|---|---|
| https://www.google.co.jp/ | |
| Qiita | https://qiita.com/ |
| Stack Overflow | https://stackoverflow.com/ |
ファイル名を urlWhiteList.csv とし、保存場所は /var/tmp/ （不可視フォルダ）にしました。
AppleScript でメインのスクリプト作成
※SQLファイルの保存場所は、ホワイトリストのファイル同様、 /var/tmp/ （不可視フォルダ）にしました。
# このスクリプトを実行するユーザのユーザ名を取得
set userName to do shell script "whoami"
# ホームフォルダのPOSIX Pathを取得
set homeFolderPOSIXPath to "/Users/" & userName & "/"
# AppleScript用に「:」区切りのパスに変換
set homeFolderPath to POSIX file homeFolderPOSIXPath
# Safari の Web 閲覧履歴のファイル（History.db）パスを取得
set historyDBPOSIXPath to homeFolderPOSIXPath & "Library/Safari/History.db"
# AppleScript用に「:」区切りのパスに変換
set historyDBPath to POSIX file historyDBPOSIXPath
# 前回コピーしたHistory.dbとそれに関連するファイルを削除
do shell script "rm -f " & homeFolderPOSIXPath & "History*"
# Finder で History.db ファイルをホームフォルダ直下にコピー
tell application "Finder" to duplicate historyDBPath to homeFolderPath
--当初 One Liner コマンドでやろうとしたが難しかった
--do shell script "sqlite3 -header -csv ~/History.db 'select datetime(v.visit_time + 978307200, \"unixepoch\", \"localtime\") as date, i.domain_expansion, v.title, i.url from history_items i left join history_visits v on i.id = v.history_item order by date desc;' > ~/browsingHistory.csv"
# スクリプトファイルの保存先フォルダパス
set scriptFolderPath to "/var/tmp/"
# SQLite3をスクリプト化したsqlファイルを読み込む方法でWeb閲覧履歴とホワイトリストとの差分をcsvでホームフォルダ直下に出力
do shell script "sqlite3 ~/History.db '.read " & scriptFolderPath & "sqlite_browssing_h.sql' > ~/blackurl.csv"
SQLite3 で実行するスクリプトをSQLファイルで作成
.mode csv
.import /var/tmp/urlWhiteList.csv whitelist
.headers on
.separator ,
SELECT datetime(v.visit_time + 978307200, 'unixepoch', 'localtime') as date, i.domain_expansion, v.title, i.url, w.url
FROM history_items i
LEFT JOIN history_visits v
ON i.id = v.history_item
LEFT JOIN whitelist w
ON i.url
LIKE (w.url || '%')
WHERE w.url IS NULL
ORDER BY date desc;
.quit
スクリプトの内容について、より詳細に編集していく予定です。
参考URL
SQLite Tutorial
DBOnline
Sqlite: select, join using like for the join fields
SQLite3 Command Line Shell dot-commands マニュアル(完全版)