将用户名传递给MS SQL触发器 [英] Pass username to MS SQL trigger

问题描述

我正在处理一个较旧的项目。它最初是作为MS Access前端和Jet后端创建的。用户名和密码（此处的安全性并不重要）以纯文本形式存储在后端用户表中。非常简单的系统。



后来，后端被转换为MS SQL。他们保持相同的用户表系统。前端有一个SQL登录。因此，当事情启动时，它立即登录到SQL（SQL凭证只是硬编码到代码中），然后弹出登录屏幕，访问我上面提到的用户表。再一次，非常简单。



但现在我们遇到了一些问题。他们希望我为该计划的一些领域添加一些审计功能。我添加了所需的触发器和审计表。没什么大不了的，但是我需要触发器来知道谁登录了所以它将正确的用户发布到审计表。我不想在此时创建单独的SQL用户。它现在对系统的影响太大了。



我认为全局临时表（1列，1行）可以在我存储的地方工作用户，但这对所有人来说都是全局的，而不仅仅是连接，因为我猜是有一个SQL登录。它可以工作，但它会发布最近登录的用户，因此它无法正确跟踪事物。因此，后续用户每次都会覆盖该表。我读了一些关于上下文信息的内容，但似乎也不是解决方案。



我在最近几次搜索中做了很多搜索。关于如何处理这个问题的几天，我没有提出解决这个问题的任何事情。那里有任何明智的建议（除了创建和管理一大堆SQL用户以及他们管理它们的前端工具之外）？



TIA，



AR。

I'm working with an older project. It was originally created as an MS Access front end and a Jet backend. The user names and passwords (security is not much of a concern here) were stored in plain text in a backend user table. Very simple system.

Later on, the backend was converted to MS SQL. They kept the same user table system. The front end has a single SQL login. So when the thing starts up, it logs into SQL right off the bat (the SQL credentials are just hard coded into the code) and then pops a login screen that accesses the user table I mentioned above. Again, very simple.

But now we have a bit of a problem. They want me to add some auditing capabilities for a few areas of the program. I've added the triggers needed and an audit table. No big deal, but I need the triggers to know who's logged in so it posts the correct user to the audit table. I don't want to create separate SQL users at this time. It impacts too much of the system for that to happen right now.

I thought a global temp table (1 column, 1 row) would work where I'd store the user but that's global to everyone, not just the connection because there's a single SQL login I'm guessing. It works, but it posts the most recently logged in user so it doesn't track things correctly. SO subsequent users overwrite that table each time. I read something about "context" info but it didn't seem like that was the solution either.

I've done a lot of searching in the last couple of days on how to handle this and I'm not coming up with anything that solves this problem. Any bright suggestions out there (other than creating and managing a load of SQL users and the front end tools for them to manage them)?

TIA,

AR.

推荐答案

我猜你有登录并在应用程序中工作的人的用户名。 br $>


在主表中再添加一列用于用户名，并使用正在修改它的用户名对其进行更新。这将保存谁修改了当前记录的信息。



现在将相同的列存储在触发器表中，这样就可以保留修改历史记录。 />


希望这会有所帮助。

I guess you have the username of the person who is logged in and working in application.

Add one more column to your main tables for username and update it with the username who is modifying it. That will hold the information that who has modified current record.

now store the same column in your trigger table and this way you can maintain the history of modification.

Hope this helps.

这篇关于将用户名传递给MS SQL触发器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！