The user id on your connection string is not a variable and is different from the user id (can be GUID for example) of your program. How do you audit log deletes if your connection string's user id is static?
连接字符串上的用户标识不是变量,不同于程序的用户标识(例如,可以是GUID)。如果连接字符串的用户标识是静态的,如何审核日志删除?
The best place to log insert/update/delete is through triggers. But with static connection string, it's hard to log who delete something. What's the alternative?
记录插入/更新/删除的最佳位置是通过触发器。但是使用静态连接字符串时,很难记录删除某些内容的人。有什么选择?
2 个解决方案
#1
With SQL Server, you could use CONTEXT_INFO to pass info to the trigger.
使用SQL Server,您可以使用CONTEXT_INFO将信息传递给触发器。
I use this in code (called by web apps) where I have to use triggers (eg multiple write paths on the table). This is where can't put my logic into the stored procedures.
我在代码中使用它(由web应用程序调用),我必须使用触发器(例如,表上有多个写入路径)。这是无法将我的逻辑放入存储过程的地方。
#2
We have a similar situation. Our web application always runs as the same database user, but with different logical users that out application tracks and controls.
我们有类似的情况。我们的Web应用程序始终作为相同的数据库用户运行,但具有不同的逻辑用户,即应用程序跟踪和控制。
We generally pass in the logical user ID as a parameter into each stored procedure. To track the deletes, we generally don't delete the row, just mark the status as deleted, set the LastChgID and LastChgDate fields accordingly. For important tables, where we keep an audit log (a copy of every change state), we use the above method and a trigger copies the row to a audit table, the LastChgID is already set properly and the trigger doesn't need to worry about getting the ID.
我们通常将逻辑用户ID作为参数传递到每个存储过程中。要跟踪删除,我们通常不会删除该行,只需将状态标记为已删除,相应地设置LastChgID和LastChgDate字段。对于重要的表,我们保留审计日志(每个更改状态的副本),我们使用上面的方法,触发器将行复制到审计表,LastChgID已经正确设置,触发器不需要担心关于获取ID。
#1
With SQL Server, you could use CONTEXT_INFO to pass info to the trigger.
使用SQL Server,您可以使用CONTEXT_INFO将信息传递给触发器。
I use this in code (called by web apps) where I have to use triggers (eg multiple write paths on the table). This is where can't put my logic into the stored procedures.
我在代码中使用它(由web应用程序调用),我必须使用触发器(例如,表上有多个写入路径)。这是无法将我的逻辑放入存储过程的地方。
#2
We have a similar situation. Our web application always runs as the same database user, but with different logical users that out application tracks and controls.
我们有类似的情况。我们的Web应用程序始终作为相同的数据库用户运行,但具有不同的逻辑用户,即应用程序跟踪和控制。
We generally pass in the logical user ID as a parameter into each stored procedure. To track the deletes, we generally don't delete the row, just mark the status as deleted, set the LastChgID and LastChgDate fields accordingly. For important tables, where we keep an audit log (a copy of every change state), we use the above method and a trigger copies the row to a audit table, the LastChgID is already set properly and the trigger doesn't need to worry about getting the ID.
我们通常将逻辑用户ID作为参数传递到每个存储过程中。要跟踪删除,我们通常不会删除该行,只需将状态标记为已删除,相应地设置LastChgID和LastChgDate字段。对于重要的表,我们保留审计日志(每个更改状态的副本),我们使用上面的方法,触发器将行复制到审计表,LastChgID已经正确设置,触发器不需要担心关于获取ID。