Audit log in 3-tiered applicaiton

In the past, a user would run a Windows application that connected
directly to the database. When that user made changes, we used a
trigger to log those changes to a separate table. We used Current_User
to record who made the changes, Current_TimeStamp to record when and
values out of the inserted or deleted table(s) to record what was
changed. Easy enough.
How can we create a log of changes in a 3-tiered windows application?
The user never touches the database. The account that executes the
middle tier is the same for every user. The GUI (vb.net) can easily
determine the Windows ID of the application user. The GUI already
passes that ID to the middle tier for security reasons. But then what?
Every idea I come up with involves calling stored procs from either the
middle tier or from the application's stored procs. That seems like a
lot of work and triggers were so easy for this task.
Any ideas are appreciated.
Tom the lazy programmer.
E-mail correspondence to and from this address may be subject to the
North Carolina Public Records Law and may be disclosed to third parties.Does the database not record what logged-in user (logged into the
application, not the database) is making changes? If not, why not? It
seems like it would make sense to record that anyway -- and this would
enable your trigger solution to work once again... Or am I missing
something?
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Tom Williams" <Tom.Williams@.NOSPAMncmail.net> wrote in message
news:e2PbWjkLFHA.1308@.tk2msftngp13.phx.gbl...
> In the past, a user would run a Windows application that connected
> directly to the database. When that user made changes, we used a
> trigger to log those changes to a separate table. We used Current_User
> to record who made the changes, Current_TimeStamp to record when and
> values out of the inserted or deleted table(s) to record what was
> changed. Easy enough.
> How can we create a log of changes in a 3-tiered windows application?
> The user never touches the database. The account that executes the
> middle tier is the same for every user. The GUI (vb.net) can easily
> determine the Windows ID of the application user. The GUI already
> passes that ID to the middle tier for security reasons. But then what?
> Every idea I come up with involves calling stored procs from either the
> middle tier or from the application's stored procs. That seems like a
> lot of work and triggers were so easy for this task.
> Any ideas are appreciated.
> Tom the lazy programmer.
>
> --
> E-mail correspondence to and from this address may be subject to the
> North Carolina Public Records Law and may be disclosed to third parties.
>|||Please pardon my ignorance, but I'm .
Our users log into a Windows domain and would then execute the Windows
application(GUI). I don't know what you mean by "logged into the
application". The application does not have it's own user IDs and
passwords. Our users have too many of those already. If they've
already logged into Windows, that's good enough for me. The GUI will
make function calls to the middle tier which will then access the
database.
Are you saying that there is a function in the database that can return
which Windows user called the middle tier function?
thanks
Tom
Adam Machanic wrote:

>Does the database not record what logged-in user (logged into the
>application, not the database) is making changes? If not, why not? It
>seems like it would make sense to record that anyway -- and this would
>enable your trigger solution to work once again... Or am I missing
>something?
>
>
"Tom Williams" <Tom.Williams@.NOSPAMncmail.net> wrote in message
news:e2PbWjkLFHA.1308@.tk2msftngp13.phx.gbl...

> In the past, a user would run a Windows application that connected
> directly to the database. When that user made changes, we used a
> trigger to log those changes to a separate table. We used Current_User
> to record who made the changes, Current_TimeStamp to record when and
> values out of the inserted or deleted table(s) to record what was
> changed. Easy enough.
> How can we create a log of changes in a 3-tiered windows application?
> The user never touches the database. The account that executes the
> middle tier is the same for every user. The GUI (vb.net) can easily
> determine the Windows ID of the application user. The GUI already
> passes that ID to the middle tier for security reasons. But then what?
> Every idea I come up with involves calling stored procs from either the
> middle tier or from the application's stored procs. That seems like a
> lot of work and triggers were so easy for this task.
> Any ideas are appreciated.
> Tom the lazy programmer.
>
>
E-mail correspondence to and from this address may be subject to the
North Carolina Public Records Law and may be disclosed to third parties.|||No; I made the assumption that your application required credentails or a
login of some sort -- which apparently it does (domain authentication). So
the question is, can the front-end pass the logged-in users' name to the
middle tier, which will then pass it into the database? And can a UserName
column be added to each table that the database manipulates, such that the
trigger once again becomes a viable option?
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Tom Williams" <Tom.Williams@.NOSPAMncmail.net> wrote in message
news:%23gmx8dlLFHA.436@.TK2MSFTNGP09.phx.gbl...
> Please pardon my ignorance, but I'm .
> Our users log into a Windows domain and would then execute the Windows
> application(GUI). I don't know what you mean by "logged into the
> application". The application does not have it's own user IDs and
> passwords. Our users have too many of those already. If they've
> already logged into Windows, that's good enough for me. The GUI will
> make function calls to the middle tier which will then access the
> database.
> Are you saying that there is a function in the database that can return
> which Windows user called the middle tier function?
> thanks
> Tom
>|||Oh, I see!
I'll have to think about whether we want to add that column or not.
Thanks for the suggestion.
Tom
Adam Machanic wrote:

>No; I made the assumption that your application required credentails or a
>login of some sort -- which apparently it does (domain authentication). So
>the question is, can the front-end pass the logged-in users' name to the
>middle tier, which will then pass it into the database? And can a UserName
>column be added to each table that the database manipulates, such that the
>trigger once again becomes a viable option?
>
>
E-mail correspondence to and from this address may be subject to the
North Carolina Public Records Law and may be disclosed to third parties.|||Tom Williams wrote:
> In the past, a user would run a Windows application that connected
> directly to the database. When that user made changes, we used a
> trigger to log those changes to a separate table. We used Current_User
> to record who made the changes, Current_TimeStamp to record when and
> values out of the inserted or deleted table(s) to record what was
> changed. Easy enough.
> How can we create a log of changes in a 3-tiered windows application?
> The user never touches the database. The account that executes the
> middle tier is the same for every user. The GUI (vb.net) can easily
> determine the Windows ID of the application user. The GUI already
> passes that ID to the middle tier for security reasons. But then what?
Store the ID using SET CONTEXT_INFO, then have the triggers read that
back and write it in your audit trail. See BOL for SET CONTEXT_INFO.
Steve Troxell
http://www.omniaudit.com