Início / dba / Perguntas / 168102
Accepted
i-one
Asked: 2017-03-25 07:28:49 +0800 CST

Acionador DDL para ALTER_AUTHORIZATION

  • 772

Eu preciso realizar alguma auditoria, quando mudar de propriedade segura, como

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

acontece no banco de dados.

O gatilho DDL do escopo do banco de dados parece ser um mecanismo apropriado para essa finalidade. Na documentação (seção DDL Statements That Have Server or Database Scope ) vejo que deve haver ALTER_AUTHORIZATIONevent.

No entanto, quando estou tentando criar um DDL-trigger apropriado

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

Estou recebendo erro Msg 1084

Msg 1084, Level 15, State 1, Procedure OnAlterAuthorization, Line 2 [Batch Start Line 0] 'ALTER_AUTHORIZATION' é um tipo de evento inválido.

Dentrosys.event_notification_event_types

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

não há nenhum ALTER_AUTHORIZATIONevento, apenas

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

deles ALTER_AUTHORIZATION_SERVERnão atende obviamente, e ALTER_AUTHORIZATION_DATABASEde acordo com a documentação

Aplica-se à instrução ALTER AUTHORIZATION quando ON DATABASE é especificado

Então, a questão é. Onde está ALTER_AUTHORIZATIONprometido na documentação? Como posso pegar a mudança de uma propriedade segura no banco de dados?

sql-server trigger

2 respostas

  1. Me deparei com o mesmo problema que você. Eu continuaria descobrindo que as Notificações de Eventos podem ser usadas para lidar com o AUDIT_CHANGE_DATABASE_OWNERevento. (Observe que esse evento não pode ser usado com um gatilho DDL.) Eu escrevi um artigo de blog Event Notifications que por acaso usa o AUDIT_CHANGE_DATABASE_OWNERevento como exemplo: SQL Server Event Handling: Event Notifications

    Abaixo está um script que pode ajudá-lo a começar.

    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

    Descobri que apesar desse evento ALTER_AUTHORIZATION_DATABASEde acordo com a documentação ser declarado como

    Aplica-se à instrução ALTER AUTHORIZATION quando ON DATABASE é especificado

    ele é acionado não apenas quando o proprietário do banco de dados é alterado, mas também quando o proprietário de uma alteração protegível no banco de dados.

    Em outras palavras, o gatilho DDL

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

    funciona não só para

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

    Mas por exemplo para

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

    ou

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

    também.

    • 0

relate perguntas