河端善博 ブログ / SQL Server / PASSJ

SQL Server, .NET, PASSJ
  PASSJブログ :: ホーム :: 連絡をする :: RSS  :: ATOM :: Login
  945 投稿数 :: 112 ストーリー :: 1482 コメント :: 596 トラックバック

ニュース

Windows Live Alerts
My Yahoo!に追加
Add to Google dotnetnuke.jp
NPOPLUS.JP
MVP
PASSJ

記事のカテゴリ

過去の記事

カテゴリ

イメージギャラリ

.TEXT

INETA コミュニティ

MOM2005

MSMVP

PASSJ

SQL コミュニティ

セキュリティ TIPS

セキュリティのキホン

テスト

ブログ翻訳

多言語対応

.TEXT

INETA コミュニティ

MOM2005

MSMVP

PASSJ

SQL コミュニティ

セキュリティ TIPS

セキュリティのキホン

テスト

ブログ翻訳

多言語対応

プロファイラで監視する

今回は、SQL Server の動作をプロファイラをつかって監視する方法をまとめます。

SQL Server のセキュリティ運用では、アクセスが正常に行われているか、不審な動作がないか、適切なアクセス権が設定されているかどうか、監視する必要があります。
そこで、SQL Server の監視ツール「プロファイラ」を利用したセキュリティ監視を確認しておきます。

「プロファイラ」は、SQL Server 製品に付属するアプリケーションで、SQL Serverへのアクセスをすべて記録することができます。
記録するイベントを自分で選択することもできます。

セキュリティに関するイベントは次のとおりです。

■セキュリティ監査イベント (21種類)

  1. Audit Add DB User Event
    データベースへのユーザーの追加/削除
  2. Audit Add Login to Server Role Event
    固定サーバーロールへのログインの追加/削除
  3. Audit Add Member to DB Role Event
    データベース ロールへのメンバの追加/削除
  4. Audit Add Role Event
    データベース ロールの追加/削除
  5. Audit Addlogin Event SQL Serve
    ログインの追加/削除
  6. Audit App Role Change Password Event
    アプリケーションロールのパスワードの変更
  7. Audit Backup/Restore Event
    バックアップ、リストア
  8. Audit Change Audit Event
    監査変更
  9. Audit DBCC Event
    DBCC コマンド
  10. Audit Login Event
    ログインの接続
  11. Audit Login Change Password Event SQL Server
    SQL Server 認証ログインのパスワード変更
  12. Audit Login Change Property Event
    ログインのパスワード以外のプロパティの変更
  13. Audit Login Failed Event
    ログイン接続の失敗
  14. Audit Login GDR Event
    Windows 認証ログインの許可/拒否/取消
  15. Audit Logout Event
    ログイン切断
  16. Audit Object Derived Permission Event
    オブジェクトの作成/削除/変更
  17. Audit Object GDR Event
    オブジェクトのアクセス権限の作成/削除/変更
  18. Audit Object Permission Event
    オブジェクトへのアクセス時の権限の確認
  19. Audit Server Starts and Stops Event
    サービスのシャットダウン/起動/一時停止
  20. Audit Statement GDR Event
    ステートメント権限の許可/拒否/削除
  21. Audit Statement Permission Event
    ステートメント実行時の権限の確認

このうち、プロファイラの標準のトレーステンプレートは、Audit Login Event, Audit Logout Event のみ設定されています。これによって、いつ誰が SQL Server に接続/切断したのかを記録することができます。

では、このイベントの具体的な使用例を紹介しましょう。

  • パスワードが間違って接続しようとしている
    「13. Audit Login Failed Event」
    を設定しておきます。
    ログインに失敗した場合、このイベントが記録されます。
    このイベントがあがった場合の原因としては、パスワードが間違っている、
    アクセス権がない、または拒否に設定されているなどがあります。
  • あるテーブルがアクセスされているかどうかを知りたい
    「18. Audit Object Permission Event」
    を設定しておきます。
    トレースプロパティのフィルタの「ObjectName」の Like プロパティに検査したいテール名を指定します。
    これにより、テーブルを利用しているログイン,ユーザーを知ることができます。
    ストアドプロシージャの中で呼び出されている場合は、元のストアドプロシージャ
    名も知ることができます。
  • 権限の変更を記録したい
    「17. Audit Object GDR Event」
    を設定しておきます。
    アクセス権限が不正に緩められてしまったりすることがないように監視することがでます。

プロファイラを利用することで、SQL Server の動作を詳細に監視することができます。
なお、多くのイベントを監査するように設定すると、サーバーのパフォーマンスに影響します。
監視要件にあわせて、サーバーに十分なパフォーマンスを確保するか、監視するイベントを絞り込むようにしてください。

運用中のサーバーに対してプロファイラで接続する場合には、サーバーのパフォーマンスに余裕があるか、接続前に確認してください。

投稿日時 : 2004年10月13日 22:02

コメントを追加

# PASSJ【Leaders'Report】連載の セキュリティTIPS をブログに掲載 2004/11/02 6:30 河端善博の .TEXT でウェブログ
PASSJ【Leaders'Report】連載の セキュリティTIPS をブログに掲載

# PASSJ Leaders' Report のセキュリティ TIPS は、13回目 2005/02/22 21:44 河端善博の .TEXT でウェブログ
PASSJ Leaders' Report のセキュリティ TIPS は、13回目

# SQL Server お勧めセキュリティ TIPS (2004/10 ～2005/3 連載分) 2005/04/04 21:05 河端善博の .TEXT でウェブログ
SQL Server お勧めセキュリティ TIPS (2004/10 ～2005/3 連載分)

# re: プロファイラで監視する 2005/04/06 13:51 小野健二郎
『プロファイラをバックグランドで稼動させる方法』をご紹介頂けますでしょうか。


# re: プロファイラで監視する 2005/04/06 14:59 河端善博
小野健二郎さん:
　バックグランドで動作させるには、トレースを利用することになります。
　簡単な利用例を、次にまとめています

　「オブジェクトの使用を監視する」
　http://blogs.sqlpassj.org/yoshihirokawabata/articles/5720.aspx

　一度、ご確認いただければと思います。

# re: プロファイラで監視する 2007/07/02 23:21 Pacioli
[SQL SERVER 2000 プロファイラについての質問です]
リアルタイムで、ファイル出力した、.trcをSQL SERVERへ格納する方法はありますでしょうか？
また、SQLのサービス自動起動時にトレースを開始する場合、トレースだけを一定間隔で自動的に取得する方法があるのでしょうか？
→SQLのサービスを停止しない方法があれば、ご教授願います。

# re: プロファイラで監視する 2007/07/03 11:17 河端善博
Pacioli さん:
　ご質問の件について、回答させていただきます。
Q1. トレース出力した .trc を SQL Server 上のテーブルへ、リアルタイムに挿入する方法.
　ありません。
　ただし、SQL プロファイラには、トレースをテーブルに出力する機能があります。
　設定するには、次の手順となります。
　メニュー「ファイル」-「プロパティ」を選択して、ダイアログ「トレースプロパティ」を開きます。
　タブ「全般」の項目「テーブルに保存」をチェックします。

　また、SQL Server 2005 では、.NET プログラムからトレース機能を利用することができますので、SQL プロファイラと同様なプログラムを作成することができます。
　名前空間 Microsoft.SqlServer.Management.Trace を参照してください。

Q2.一定間隔でトレースを自動取得する方法
　トレースの取得開始、取得終了を処理するストアドプロシージャを作成し、SQL Agent を利用して、定期的に呼び出す方法はいかがでしょうか。
　下記のブログに、ストアドプロシージャの作成方法を紹介しています。

　「オブジェクトの使用を監視する」
http://blogs.sqlpassj.org/yoshihirokawabata/articles/5720.aspx

　また、SQL Server 2005 であれば、上記に紹介しました Trace クラスを利用して、専用のプログラムを構築することにより、一定間隔でトレース取得する処理を実装することができます。

　ご参考になればと思います。


# re: プロファイラで監視する 2007/07/07 0:24 Pacioli
ご丁寧に、回答賜りまして深謝申し上げます。
御礼が遅れて恐縮です。

さて、またプロファイラ関連で質問させて下さい。
イベントクラスを下記３項目とし、
　Audit Login Failed Event
　Audit Object Permission Event
　Audit Object GDR Event
データ列を下記９項目としたトレースを作成しました。
　EventClass
　TextData
　ApplicationName
　NTUserName
　HostName
　LoginName
　ObjectName
　StartTime
　SPID
その上で、.trcファイルをMS社の下記サイトを元に
タスクスケジューラにより、SQL Server上のテーブルへ日々、
自動取込を考えているのですが、
http://support.microsoft.com/kb/270599
当該テーブルへ設定すべき主キー項目にアタマを抱えております。

また、同テーブルには、データ列で指定していないフィールドに
自動的に値が格納されている（ServerName）理由が不明です。

恐れ入りますが、お知恵を拝借願います。

# re: プロファイラで監視する 2007/07/07 1:10 河端善博
Pacioli さん:
　主キー項目については、適切な項目はありません。
　int データ型でIdenitity 属性をもった列を別途テーブルに
　作成し、クラスタ化インデックスに設定してはいかがでしょうか。

　::fn_trace_gettable の ServerName 列など、トレース時に
　データ列として指定していない列が存在する点については、
　未確認です。
　ただ、自動的に指定されてしまうのではないかと考えられます。

　したがって、select * into とする代わりに、
　insert into ... select ... from ::fn_trace_gettable
　としたほうがいいと思われます。

　また、大量のデータインサートによるパフォーマンス不足が
　発生した場合には、バルクインサートやパーティション設定など
　検討される必要があると思います。

よろしくお願いいたします

# re: プロファイラで監視する 2007/07/07 1:41 Pcioli
早々のご回答に改めて敬服致します。
>主キー項目については、適切な項目はありません。
>int データ型でIdenitity 属性をもった列を別途テーブルに
>作成し、クラスタ化インデックスに設定してはいかがでしょうか。
こちらについては、別途検討してみます（∵当初の設計とズレるため）

ご提案頂きました、
insert into ... select ... from ::fn_trace_gettable
についてですが、仮に挿入先のテーブルをAとした場合、
別途、CREATE TABLE で、先頭列にSEQを持つ列をAテーブルに作成した際に、
insert into A select * from ::fn_trace_gettable
を実行すると、列の並びが1列分、繰り上がってしまうことになりませんでしょうか？
→SEQを持つ列に、TextDataが挿入されてしまう？

稚拙な質問で恐れ入りますが、宜しくお願いします。


# re: プロファイラで監視する 2007/07/08 23:24 河端善博
Pcioli さん:
　次のようにテーブル定義、insert into してみては
　いかがでしょうか。
insert into では、挿入先の列名、SELECT の列名を
　指定しています。

-- テーブル定義
CREATE TABLE [dbo].[trace_table]
(
[ID] [bigint] identity(1,1) primary key,
[EventClass] [int] NULL,
[TextData] [ntext] NULL,
[ApplicationName] [nvarchar](256) NULL,
[NTUserName] [nvarchar](256) NULL,
[HostName] [nvarchar](256) NULL,
[LoginName] [nvarchar](256) NULL,
[ObjectName] [nvarchar](256) NULL,
[StartTime] [datetime] NULL,
[SPID] [int] NULL
)

-- insert into
insert into
trace_table
(
EventClass,
TextData,
ApplicationName,
NTUserName,
HostName,
LoginName,
ObjectName,
StartTime,
SPID
)
select
EventClass,
TextData,
ApplicationName,
NTUserName,
HostName,
LoginName,
ObjectName,
StartTime,
SPID
from ::fn_trace_gettable('c:\test.trc', default)

# re: プロファイラで監視する 2007/07/09 20:42 Pacioli
ご丁寧にありがとうございます。
非常に助かりました！

# re: プロファイラで監視する 2007/09/07 15:35 悩み人
# re: プロファイラで監視する 2007/07/08 23:24 河端善博 さんのご回答に質問させてください(SQL Server 2000 sp3 の環境です)。

プロファイラで取得する「データ列」に、[NTUserName]を指定したのですが、トレースファイルには、同カラムには「NULL」としかデータ取得できておりません。その他の、[EventClass]、[HostName]、[ApplicationName]等のデータは、取得できているのですが・・・。

なぜ、[NTUserName]に「NULL」が格納されてしまうのでしょうか？
また、[NTUserName]に、Windowsのユーザ名を表示させるためには特別な方法があるのでしょうか？

ご存知であれば、是非教えていただけませんでしょうか。
宜しく御願いします。

コメント

タイトル:
名前:
Url:
コメント: