时间戳

首页 > 解决方案 > 是否可以向需要用户没有权限的触发器授予权限？

triggers - 是否可以向需要用户没有权限的触发器授予权限？

问题描述

过去，我使用数据库触发器来记录所有架构更改。这是光荣的。但是，它要求任何需要修改架构的人都必须具有某些权限。我相信需要的是查看服务器状态。无法确定，但这篇文章需要哪个（s）并不重要。

问题是，如果有用户可以更改架构但不应该拥有该权限怎么办？这完全破坏了我的工具，因为触发器只会导致他们收到此错误。我可以将它包装在一个 try catch 中，但是如果它不记录更改，那显然会破坏整个目的。

有没有办法让这个触发器为每个人运行，即使我不能给每个人所有必需的权限？我猜答案是否定的，因为我不知道如何在某种模拟下触发触发器，而且我想不出任何其他解决方案。但我希望你们知道一些我不知道的事情！

这是触发器：

CREATE TRIGGER [LogSchemaEvent]
    ON DATABASE
    FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE,
        CREATE_VIEW, ALTER_VIEW, DROP_VIEW,
        CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE, 
        CREATE_FUNCTION, ALTER_FUNCTION, DROP_FUNCTION,
        CREATE_TRIGGER, ALTER_TRIGGER, DROP_TRIGGER,
        CREATE_SCHEMA, ALTER_SCHEMA, DROP_SCHEMA,
        CREATE_INDEX, ALTER_INDEX, DROP_INDEX,
        CREATE_TYPE, DROP_TYPE
AS
BEGIN
    SET NOCOUNT ON
    DECLARE @EventData XML = EVENTDATA()

    DECLARE @ip VARCHAR(32) = (
        SELECT client_net_address
        FROM sys.dm_exec_connections
        WHERE session_id = @@SPID)
 
    INSERT SchemaEvents (
        EventType,
        EventDDL,
        EventXML,
        DatabaseName,
        SchemaName,
        ObjectName,
        HostName,
        IPAddress,
        ProgramName,
        LoginName)
    SELECT
        @EventData.value('(/EVENT_INSTANCE/EventType)[1]',   'NVARCHAR(100)'), 
        @EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'NVARCHAR(MAX)'),
        @EventData,
        DB_NAME(),
        @EventData.value('(/EVENT_INSTANCE/SchemaName)[1]',  'NVARCHAR(255)'), 
        @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]',  'NVARCHAR(255)'),
        HOST_NAME(),
        @ip,
        PROGRAM_NAME(),
        SUSER_SNAME();
END

标签： triggerspermissionssql-server-2017

解决方案

通过使用与具有所需权限的用户关联的证书对其进行签名，可以将提升的权限授予模块（过程、函数、触发器）。这适用于模式范围的对象，但不适用于 DDL 触发器，因为它们不能直接签名。

但是，您可以创建具有所需功能的模式范围 proc，使用证书对其进行签名，然后从 DDL 触发器中调用它。这种方法的唯一缺点是数据库必须是可信的。我通常建议避免使用 TRUSTWORTHY，但在开发数据库的情况下可能是可以接受的。

下面是一个示例脚本。

USE YourDatabase;
GO

CREATE OR ALTER PROCEDURE dbo.LogDDLEvent
    @EventData xml
AS 
INSERT SchemaEvents (
    EventType,
    EventDDL,
    EventXML,
    DatabaseName,
    SchemaName,
    ObjectName,
    HostName,
    IPAddress,
    ProgramName,
    LoginName)
SELECT
    @EventData.value('(/EVENT_INSTANCE/EventType)[1]',   'NVARCHAR(100)'), 
    @EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'NVARCHAR(MAX)'),
    @EventData,
    DB_NAME(),
    @EventData.value('(/EVENT_INSTANCE/SchemaName)[1]',  'NVARCHAR(255)'), 
    @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]',  'NVARCHAR(255)'),
    HOST_NAME(),
    (SELECT TOP 1 client_net_address FROM sys.dm_exec_connections WHERE session_id = @@SPID),
    PROGRAM_NAME(),
    SUSER_SNAME();
GO
GRANT EXECUTE ON dbo.LogDDLEvent TO public;
GO

CREATE OR ALTER TRIGGER [LogSchemaEvent]
    ON DATABASE

    FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE,
        CREATE_VIEW, ALTER_VIEW, DROP_VIEW,
        CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE, 
        CREATE_FUNCTION, ALTER_FUNCTION, DROP_FUNCTION,
        CREATE_TRIGGER, ALTER_TRIGGER, DROP_TRIGGER,
        CREATE_SCHEMA, ALTER_SCHEMA, DROP_SCHEMA,
        CREATE_INDEX, ALTER_INDEX, DROP_INDEX,
        CREATE_TYPE, DROP_TYPE
    
AS
SET NOCOUNT ON;
DECLARE @EventData XML = EVENTDATA();
EXECUTE dbo.LogDDLEvent @EventData = @EventData;
GO

USE master
GO

-- Create certificate in master.
CREATE CERTIFICATE LogSchemaEventCertificate
   ENCRYPTION BY PASSWORD = 'ephemoral password'
   WITH SUBJECT = 'For view server state';
CREATE LOGIN LogSchemaEventCertificateUser FROM CERTIFICATE LogSchemaEventCertificate;
GRANT VIEW SERVER STATE TO LogSchemaEventCertificateUser;

-- Copy cert to user database
DECLARE @cert_id int = cert_id('LogSchemaEventCertificate')
DECLARE @public_key  varbinary(MAX) = certencoded(@cert_id),
        @private_key varbinary(MAX) =
           certprivatekey(@cert_id,
              'ephemoral password',
              'ephemoral password');

SELECT @cert_id, @public_key, @private_key; --these values should be non-null

DECLARE @sql nvarchar(MAX) =
      'CREATE CERTIFICATE LogSchemaEventCertificate
       FROM  BINARY = ' + convert(varchar(MAX), @public_key, 1) + '
       WITH PRIVATE KEY (BINARY = ' +
          convert(varchar(MAX), @private_key, 1) + ',
          DECRYPTION BY PASSWORD = ''ephemoral password'',
          ENCRYPTION BY PASSWORD = ''ephemoral password'')';

EXEC YourDatabase.sys.sp_executesql @sql;
ALTER CERTIFICATE LogSchemaEventCertificate REMOVE PRIVATE KEY;
ALTER DATABASE YourDatabase SET TRUSTWORTHY ON;
GO

USE YourDatabase;
ADD SIGNATURE TO dbo.LogDDLEvent BY CERTIFICATE LogSchemaEventCertificate WITH PASSWORD = 'ephemoral password';
ALTER CERTIFICATE LogSchemaEventCertificate REMOVE PRIVATE KEY;
GO

推荐阅读