审计触发器中的当前事务 ID [英] Current transaction ID in an audit trigger

问题描述

我正在考虑从审计触发器中存储某种形式的事务 ID.解决方案似乎是在这篇文章中使用 sys.dm_tran_current_transaction SQL Server触发器 - 按交易分组.

但是，我不能使用这个，因为运行sql语句的用户帐户没有VIEW SERVER STATE"权限并导致错误:

<前>Msg 297, Level 16, State 1, Line 3用户无权执行此操作.

有谁知道此视图的替代方案，它将提供类似的事务 ID 或在触发器上使用WITH EXECUTE AS"以允许从此视图中进行选择的方法.

从我对WITH EXECUTE AS"的尝试看来，服务器级别的权限没有被继承，这确实是预期的，因为它是在冒充数据库用户.

解决方案

您可以使用代码签名解决几乎所有安全问题.最精细和微调的访问控制，只是有点难以理解.

在触发器上使用 EXECUTE AS OWNER，创建证书，签署触发器，删除私钥(以便其他人无法再次使用它来签署任何东西)，导出证书(公共仅密钥)，在 master 中导入证书，创建从证书派生的登录名，向此登录名授予身份验证(为了 将数据库扩展为模拟执行)，然后将视图服务器状态授予此登录名.这是防弹的，完全受控的权限控制.如果需要更改触发器，则必须再次执行签名过程(包括证书派生登录和授权).从安全的角度来看，这是需要的(您正在签署触发器的特定变体)，从操作的角度来看，这是一个不错的选择，但可以管理.

create table t (i int);创建表审计(transaction_id int)；走创建触发器 t_audit_trigger在 t以所有者身份执行插入、更新、删除后作为开始不计较；插入审计(transaction_id)从 sys.dm_tran_current_transaction 中选择 transaction_id；如果 (@@ROWCOUNT != 1)raiserror(N'Failed to audit transaction', 16, 1);结尾走创建证书 t_audit_view_server密码加密 = '密码#123'主题 = N't_audit_view_server', 开始日期 = '08/10/2009';走将签名添加到 t_audit_trigger通过证书 t_audit_view_serverwith password = '密码#123';走更改证书 t_audit_view_server删除私钥；备份证书 t_audit_view_serverto file = 'c:\temp\t_audit_view_server.cer';走使用大师；走创建证书 t_audit_view_server来自文件 = 'c:\temp\t_audit_view_server.cer';走创建登录 t_audit_view_server_login来自证书 t_audit_view_server；走将身份验证服务器授予 t_audit_view_server_login；将视图服务器状态授予 t_audit_view_server_login；走

I was looking at storing some form of transaction id from an audit trigger. The solution appeared to be to use sys.dm_tran_current_transaction as in this post SQL Server Triggers - grouping by transactions.

However, I cannot use this because the user account running sql statements will not have the "VIEW SERVER STATE" permission and results in the error:

Msg 297, Level 16, State 1, Line 3
The user does not have permission to perform this action.

Does anyone know of an alternative to this view that will provide a similar transaction id or a way to use "WITH EXECUTE AS" on the trigger to allow selecting from this view.

From my attempts at "WITH EXECUTE AS" it appears that server level permissions are not carried over, which is expected really since it is impersonating a database user.

解决方案

You can resolve almost any security problem using code signing. Most granular and finely tuned access control, is just a bit on the hard side to understand.

Use EXECUTE AS OWNER on the trigger, create a certificate, sign the trigger, drop the private key (so that noone else can use it to ever sign anything again), export the certificate (public key only), import the certificate in master, create a login derived from the certificate, grant authenticate to this login (in order to extend the database execute as impersonation), then grant view server state to this login. This is bullet proof, perfectly controled priviledge control. If the trigger need to be changed, the signing process (including the cert derived login and grants) have to be done again. From a security point of view, this is desired (you are signing a specific variant of the trigger), from operational point of view is rather a pita, but is manageable.

create table t (i int);
create table audit (transaction_id int);
go

create trigger t_audit_trigger
on t
with execute as owner
after insert, update, delete
as
begin
    set nocount on;
    insert into audit (transaction_id) 
    select transaction_id from sys.dm_tran_current_transaction;
    if (@@ROWCOUNT != 1)
        raiserror(N'Failed to audit transaction', 16, 1);
end
go

create certificate t_audit_view_server
    encryption by password = 'Password#123'
    with subject = N't_audit_view_server'
    , start_date = '08/10/2009';
go

add signature to t_audit_trigger
    by certificate t_audit_view_server
    with password = 'Password#123';
go

alter certificate t_audit_view_server
    remove private key;

backup certificate t_audit_view_server
    to file = 'c:\temp\t_audit_view_server.cer';
go

use master;
go

create certificate t_audit_view_server
    from file = 'c:\temp\t_audit_view_server.cer';
go  

create login t_audit_view_server_login
    from certificate t_audit_view_server;
go

grant authenticate server to t_audit_view_server_login;
grant view server state to t_audit_view_server_login;
go

这篇关于审计触发器中的当前事务 ID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！