SQL Server：修改“应用程序名称” 用于审计目的的财产
由于我们没有将应用程序的用户实现为 SQL Server 中的用户，因此当应用程序服务器连接到数据库时，每个应用程序始终使用相同的凭据来附加到每个数据库。
这就提出了一个审计问题。 使用触发器，我们希望存储每个更新、插入和删除，并将每个更新归因于特定用户。 一种可能的解决方案是向每个表添加一个“由用户更新”列，并每次都更新它。 这意味着每个表上都有一个新列，每个存储过程上都有一个新参数。 这也意味着您只能进行软删除。
相反，我建议使用连接字符串的应用程序名称属性，并使用触发器内的 App_Name() 属性读取它。 我用一个简单的应用程序对此进行了测试，它似乎可以工作（格式可以是这样的：App = MyApp | User = 100）。
你们的问题是，这是一个坏主意吗？你们有更好的主意吗？
As we do not implement the users of our applications as users in SQL server, when the application server connects to a database each application always uses the same credentials to attach to each database.
This presents an auditing problem. Using triggers, we want to store every update, insert and delete and attribute each to a particular user. One possible solution is to add an "updated by user" column to every table and update this every time. This means a new column on every table and a new parameter on every stored procedure. It also means you can only do soft deletes.
Instead of this I propose using the Application Name property of the connection string and reading this with the App_Name() property inside the trigger. I tested this with a simple app and it seems to work (the format could be as so: App=MyApp|User=100).
The question for you guys is, is this a bad idea and do you have a better one?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
我为此使用 SET CONTEXT_INFO 。 这正是您所需要的。
I use SET CONTEXT_INFO for this. It's just what you need.
这当然看起来是一个可行的解决方案，尽管您需要在每次加载应用程序时将用户名注入连接字符串中。 请注意，此解决方案可能不适用于 Web 应用程序，因为您的连接字符串每次都会不同，这可能会导致巨大的连接池问题。
另一种选择是检索主机名/IP 地址 (SELECT host_name() ) 并存储它。
您不一定需要在每个存储过程上使用新参数，因为您可以修改每个存储过程（或触发器）以自动插入 App_Name/Hostname。
一个潜在的缺点是，通过 Management Studio 执行的任何修改都不会具有自定义 App_Name，并且您将保留“Microsoft Management Studio”作为用户。
It certainly seems like a feasible solution, although you'll need to inject the username into the connection string each time your application loads. Note that this solution probably wouldn't work with a web application, as your connection string will be different each time, which could lead to huge connection pooling issues.
Another option is to retrieve the hostname/IP address (SELECT host_name() ) and store that instead.
You wouldn't necessarily need a new parameter on each stored procedure, as you can modify each stored procedure (or the trigger) to automatically insert the App_Name/Hostname.
A potential drawback is that any modifications performed via Management Studio won't have the custom App_Name, and you'll be left with "Microsoft Management Studio" as the user.
我们使用“应用程序名称”属性来控制审核触发器，并且没有发现任何使用它的问题，也没有注意到任何速度问题（尽管在我们的例子中，我们专门不审核某些应用程序，因此很难衡量有多少不做某事需要时间:))
We use the Application Name property to control auditing triggers and have not seen any problems using it, and haven't noticed any speed issues (though in our case, we're specifically not auditing for certain applications, so its hard to measure how much time not doing something takes :))