主页 / dba / 问题 / 168102
Accepted
i-one
Asked: 2017-03-25 07:28:49 +0800 CST

ALTER_AUTHORIZATION 的 DDL 触发器

  • 772

当安全所有权发生变化时，我需要执行一些审计，例如

ALTER AUTHORIZATION ON SCHEMA::[SchemaName] TO [PrincipalName];

发生在数据库中。

数据库范围 DDL 触发器似乎是一种适合此目的的机制。在文档中（DDL Statements That Have Server or Database Scope部分）我看到应该有ALTER_AUTHORIZATION事件。

但是，当我尝试创建适当的 DDL 触发器时

CREATE TRIGGER [OnAlterAuthorization] ON DATABASE
FOR ALTER_AUTHORIZATION
AS
BEGIN
    PRINT 'Perform audit';
END

我收到错误消息 1084

消息 1084，级别 15，状态 1，过程 OnAlterAuthorization，第 2 行 [批处理起始行 0]“ALTER_AUTHORIZATION”是无效的事件类型。

在sys.event_notification_event_types

SELECT type_name
FROM sys.event_notification_event_types
WHERE type_name LIKE 'ALTER_AUTHOR%';

没有ALTER_AUTHORIZATION事件，只是

type_name
-----------------------------
ALTER_AUTHORIZATION_SERVER
ALTER_AUTHORIZATION_DATABASE

他们ALTER_AUTHORIZATION_SERVER显然不适合，并且ALTER_AUTHORIZATION_DATABASE根据文档

指定 ON DATABASE 时适用于 ALTER AUTHORIZATION 语句

所以，问题是。文档中在哪里ALTER_AUTHORIZATION承诺？如何捕捉数据库中安全所有权的变化？

sql-server trigger

2 个回答

  1. 我遇到了和你一样的问题。我会继续发现可以使用事件通知来处理AUDIT_CHANGE_DATABASE_OWNER事件。（请注意，此事件不能与 DDL 触发器一起使用。）我写了一篇事件通知博客文章，恰好以该AUDIT_CHANGE_DATABASE_OWNER事件为例：SQL Server 事件处理：事件通知

    下面是一个可以帮助您入门的脚本。

    USE SomeDatabase
GO

--Create a queue just for change db owner events.
CREATE QUEUE queChangeDBOwnerNotification

--Create a service just for change db owner events.
CREATE SERVICE svcChangeDBOwnerNotification
ON QUEUE queChangeDBOwnerNotification ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification])

-- Create the event notification for change db owner events on the service.
CREATE EVENT NOTIFICATION enChangeDBOwner
ON SERVER
WITH FAN_IN
FOR AUDIT_CHANGE_DATABASE_OWNER
TO SERVICE 'svcChangeDBOwnerNotification', 'current database';
GO

CREATE PROCEDURE dbo.ReceiveChangeDBOwner
AS
BEGIN
    SET NOCOUNT ON
    DECLARE @MsgBody XML

    WHILE (1 = 1)
    BEGIN
        BEGIN TRANSACTION

        -- Receive the next available message FROM the queue
        WAITFOR (
            RECEIVE TOP(1) -- just handle one message at a time
                @MsgBody = CAST(message_body AS XML)
                FROM queChangeDBOwnerNotification
        ), TIMEOUT 1000  -- if the queue is empty for one second, give UPDATE and go away
        -- If we didn't get anything, bail out
        IF (@@ROWCOUNT = 0)
        BEGIN
            ROLLBACK TRANSACTION
            BREAK
        END 
        ELSE
        BEGIN
            --Interrogate the event data for relevant properties/values.
            DECLARE @Cmd VARCHAR(1024)
            DECLARE @MailBody NVARCHAR(MAX)
            DECLARE @Subject NVARCHAR(255)

            SET @Cmd = @MsgBody.value('(/EVENT_INSTANCE/TextData)[1]', 'VARCHAR(1024)')
            SET @Subject = @@SERVERNAME + ' -- ' + @MsgBody.value('(/EVENT_INSTANCE/EventType)[1]', 'VARCHAR(128)' )    

            --Build an html table for use with an html-formatted email message.
            SET @MailBody = 
                '<table border="1">' +
                '<tr><td>Server Name</td><td>' + @MsgBody.value('(/EVENT_INSTANCE/ServerName)[1]', 'VARCHAR(128)' ) + '</td></tr>' + 
                '<tr><td>Start Time</td><td>' + @MsgBody.value('(/EVENT_INSTANCE/StartTime)[1]', 'VARCHAR(128)' ) + '</td></tr>' +  
                '<tr><td>Session Login Name</td><td>' + @MsgBody.value('(/EVENT_INSTANCE/SessionLoginName)[1]', 'VARCHAR(128)' ) + '</td></tr>' + 
                '<tr><td>Login Name</td><td>' + @MsgBody.value('(/EVENT_INSTANCE/LoginName)[1]', 'VARCHAR(128)') + '</td></tr>' + 
                '<tr><td>Windows Domain\User Name</td><td>' + @MsgBody.value('(/EVENT_INSTANCE/NTDomainName)[1]', 'VARCHAR(256)') + '\' +
                    @MsgBody.value('(/EVENT_INSTANCE/NTUserName)[1]', 'VARCHAR(256)') + '</td></tr>' +  
                '<tr><td>DB User Name</td><td>' + @MsgBody.value('(/EVENT_INSTANCE/DBUserName)[1]', 'VARCHAR(128)' ) + '</td></tr>' + 
                '<tr><td>Host Name</td><td>' + @MsgBody.value('(/EVENT_INSTANCE/HostName)[1]', 'VARCHAR(128)' ) + '</td></tr>' +  
                '<tr><td>Application Name</td><td>' + @MsgBody.value('(/EVENT_INSTANCE/ApplicationName)[1]', 'VARCHAR(128)' ) + '</td></tr>' + 
                '<tr><td>Command Succeeded</td><td>' + @MsgBody.value('(/EVENT_INSTANCE/Success)[1]', 'VARCHAR(8)' ) + '</td></tr>' + 
                '</table><br/>' +
                '<p><b>Text Data:</b><br/>' + REPLACE(@Cmd, CHAR(13) + CHAR(10), '<br/>') +'</p><br/>'
            --PRINT @Subject
            --PRINT @MailBody

            --Note: you may need to set [msdb] to trustworthy for this to work.
            --Another option is to sign a stored proc with a certificate.
            --See: https://learn.microsoft.com/en-us/sql/relational-databases/tutorial-signing-stored-procedures-with-a-certificate
            EXEC msdb.dbo.sp_send_dbmail 
                @recipients = '[email protected]', 
                @subject = @Subject,
                @body = @MailBody,
                @body_format = 'HTML',
                @exclude_query_output = 1
            /*
                Commit the transaction.  At any point before this, we 
                could roll back -- the received message would be back 
                on the queue AND the response wouldn't be sent.
            */
            COMMIT TRANSACTION
        END
    END
END
GO

ALTER QUEUE dbo.queChangeDBOwnerNotification 
WITH 
    STATUS = ON, 
    ACTIVATION ( 
        PROCEDURE_NAME = dbo.ReceiveChangeDBOwner, 
        STATUS = ON, 
        MAX_QUEUE_READERS = 1, 
        EXECUTE AS OWNER) 
GO
    • 1
  2. Best Answer

    我发现尽管ALTER_AUTHORIZATION_DATABASE根据文档发生了该事件，但它被声明为

    指定 ON DATABASE 时适用于 ALTER AUTHORIZATION 语句

    它不仅在数据库所有者更改时触发，而且在数据库中安全更改的所有者时触发。

    换句话说，DDL 触发器

    CREATE TRIGGER [OnAlterAuthorization] ON DATABASE
FOR ALTER_AUTHORIZATION_DATABASE
AS
BEGIN
    PRINT 'Check ownership';
END

    不仅适用于

    ALTER AUTHORIZATION ON DATABASE::[DbName] TO [PrincipalName];

    但是例如对于

    ALTER AUTHORIZATION ON SCHEMA::[SchemaName] TO [PrincipalName];

    或者

    ALTER AUTHORIZATION ON ROLE::[RoleName] TO [PrincipalName];

    以及。

    • 0

相关问题