SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

SQL Server で DDL トリガーを展開するいくつかの方法

leave a comment

SQL Server では、DML トリガー (INSERT / UPDATE / DELETE をトラップしてのトリガー) と DDL トリガー (CREATE / ALTER / DROP / GRANT / DENY / REVOKE / UPDATE STATISTICS をトラップしてのトリガー) を使用することができます。
# ログオントリガーというものもありますが今回は割愛で。

今回の投稿では DDL トリガーを設定する際の 2 つの方法についてまとめてみたいと思います。

DDL トリガーでは 2 種類のスコープを設定することができます。

  • ALL SERVER
  • DATABASE

これについては作成したトリガーをサーバーレベルで適用するのか (ALL SERVER) 、特定のデータベースでのみ適用するのか (DATABASE) によって設定の方法が異なってきます。

DDL トリガーの作成が必要となるのは、

  • 特定のオブジェクトの作成を禁止または、ロギング
  • 特定の権限の許可 / 拒否を禁止または、ロギング

というようなケースが考えられます。

トリガーの展開方法ですが、シンプルなものですと、以下のような方法が考えられるかと。

  1. イベントをトラップする必要があるデータベースに個別にトリガーを作成
  2. model データベーストリガーを作成し、新規にデータベースを作成した際には自動で展開
  3. サーバースコープのトリガーを作成し、インスタンス全体に設定

 

今回は、データベースの db_owner ロールに追加された場合にデバッグ用のメッセージを表示するトリガーを実装してみたいと思います。

以下のようなテスト用のトリガーを作成してみます。

CREATE TRIGGER [add_owner_trigger] ON DATABASE WITH EXECUTE AS 'dbo', ENCRYPTION FOR
 ADD_ROLE_MEMBER, GRANT_DATABASE AS BEGIN
   SET ANSI_PADDING ON;
   SET NOCOUNT ON;
   DECLARE @data xml;
   DECLARE @user sysname;
   DECLARE @role sysname;
   DECLARE @type sysname;
   
   SELECT @data = EVENTDATA();
   SELECT @type = @data.value('(/EVENT_INSTANCE/EventType)[1]', 'sysname');
    
   IF UPPER(@type) = 'ADD_ROLE_MEMBER' 
   BEGIN
      SELECT @user = @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname'),
       @role = @data.value('(/EVENT_INSTANCE/RoleName)[1]', 'sysname')
      IF @role IN ('db_owner') BEGIN
		SELECT DB_NAME(), 'add_owner', EVENTDATA()
      END
   END 
END
GO

ENABLE TRIGGER [add_owner_trigger] ON DATABASE
GO

 

このトリガーが設定されているデータベースに対して、db_owner にユーザーを追加します。

USE [TRIGGER]
GO
ALTER ROLE [db_owner] ADD MEMBER [sqluser]
GO

 

そうすると以下のようなデバッグ用のメッセージが出力されます。

image

このトリガーを model データベースに作成しておけば、新規にデータベースを作成した場合には、トリガーが含まれた状態で作成されます。

しかし、model データベースに作成して、新規に作成したデータベースでもトリガーを設定しようとした場合、

  • db_owner の権限を持っている場合、トリガーが削除できてしまう
  • トリガーの内容を変更する必要が出た場合、各データベースのトリガーを修正する必要がある

というような懸念が考えられます。

そのため、すべてのデータベースで有効にしたいトリガーについては、サーバースコープのトリガーとして実装できるかを検討したほうがよいかと思います。

先ほどのトリガーを以下のように変更します。

CREATE TRIGGER [add_owner_trigger] ON ALL SERVER WITH EXECUTE AS 'sa', ENCRYPTION FOR
 ADD_ROLE_MEMBER, GRANT_DATABASE AS BEGIN
   SET ANSI_PADDING ON;
   SET NOCOUNT ON;
   DECLARE @data xml;
   DECLARE @user sysname;
   DECLARE @role sysname;
   DECLARE @type sysname;
   
   SELECT @data = EVENTDATA();
   SELECT @type = @data.value('(/EVENT_INSTANCE/EventType)[1]', 'sysname');
    
   IF UPPER(@type) = 'ADD_ROLE_MEMBER' 
   BEGIN
      SELECT @user = @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname'),
       @role = @data.value('(/EVENT_INSTANCE/RoleName)[1]', 'sysname')
      IF @role IN ('db_owner') BEGIN
		SELECT DB_NAME(), 'add_owner', EVENTDATA()
      END
   END 
END
GO

ENABLE TRIGGER [add_owner_trigger] ON ALL SERVER
GO

 

これにより ALL SERVER のトリガーとなるため、サーバースコープで動作するトリガーとなります。

このトリガーが有効化されている場合には、すべてのデータベースに対して処理が有効となりますので、トリガーの変更が必要となった場合にも、サーバースコープのトリガーのみを変更すれば対応することができるようになります。

また、オブジェクトを削除されないようにする場合、サーバーオブジェクトのトリガーの権限につては db_owner とは独立して設定できますので、ユーザーデータベースの所有者権限を保持していもトリガーを削除することはできないというセキュリティの設定も可能となります。

注意をしておきたい点としては、トリガーの動作が [master] データベースで行われるということでしょうか。

image

image

上が、データベース / 下がサーバースコープのトリガーですが、データベース名が異なっているのが確認できます。

サーバースコープのトリガーで、トリガーをキックするための操作が行われたデータベースに対して処理を実行したい場合には、USE でデータベースを変更して処理を行うようにします。

CREATE TRIGGER [add_owner_trigger] ON ALL SERVER WITH EXECUTE AS 'sa', ENCRYPTION FOR
 ADD_ROLE_MEMBER, GRANT_DATABASE AS BEGIN
   SET ANSI_PADDING ON;
   SET NOCOUNT ON;
   DECLARE @data xml;
   DECLARE @user sysname;
   DECLARE @role sysname;
   DECLARE @type sysname;
   DECLARE @sql nvarchar(max);
   DECLARE @database sysname;
   
   SELECT @data = EVENTDATA();
   SELECT @type = @data.value('(/EVENT_INSTANCE/EventType)[1]', 'sysname');
    
   IF UPPER(@type) = 'ADD_ROLE_MEMBER' 
   BEGIN
      SELECT @user = @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname'),
       @role = @data.value('(/EVENT_INSTANCE/RoleName)[1]', 'sysname'),
	   @database = @data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'sysname')
      IF @role IN ('db_owner') BEGIN
		SET @sql = 'USE ' + QUOTENAME(@database) + ';SELECT DB_NAME(), ''add_owner'''
		EXECUTE (@sql)
      END
   END 
END
GO

ENABLE TRIGGER [add_owner_trigger] ON ALL SERVER
GO

これで、処理実行のデータベースをトリガーをキックする操作を行ったデータベースに変更してから処理をすることができますので、サーバースコープのトリガーでも、以下のように master 以外のデータベースに対して処理を行うことができます。

image

Written by masayuki.ozawa

3月 1st, 2015 at 5:41 am

Posted in SQL Server

Tagged with

Leave a Reply

*