Audit logging an adp

[Merlin]

Hello
I need to add an audit trail to an existing, large adp application, which
was developed in AccessXP with a SQLServer 2000 backend.
I searched through all the Access pages and found numerous reference to
Allen Browne's audit code.
This is very much the sort of log I would like to keep, but his solution
won't work in an adp for a couple of reasons:
* You need client side tempTables, which, as far as I can see, can not exist
in an adp;
* There are issues with event ordering; and
* Autonumbering happens too late in new records to get the new ID.

I was wondering if anyone has adapted Allen Browne's solution for .ADPs, or
if anyone can suggest an alternative method.

Some people may be tempted to suggest using SQL Server triggers, so I'll
jump in early and say they won't help me for these reasons:
* All connections to the SQL Server are done through a single account, so I
can't get the user ID of the person making the change; and
* Most of the tables contain NText and Text fields that will need to be
auditted, and the triggers don't let you see the value of fields of that type

Thank you

 

[David Portas]

Hello
I need to add an audit trail to an existing, large adp application, which
was developed in AccessXP with a SQLServer 2000 backend.
I searched through all the Access pages and found numerous reference to
Allen Browne's audit code.
This is very much the sort of log I would like to keep, but his solution
won't work in an adp for a couple of reasons:
* You need client side tempTables, which, as far as I can see, can not exist
in an adp;
* There are issues with event ordering; and
* Autonumbering happens too late in new records to get the new ID.

I was wondering if anyone has adapted Allen Browne's solution for .ADPs, or
if anyone can suggest an alternative method.

Some people may be tempted to suggest using SQL Server triggers, so I'll
jump in early and say they won't help me for these reasons:
* All connections to the SQL Server are done through a single account, so I
can't get the user ID of the person making the change; and
* Most of the tables contain NText and Text fields that will need to be
auditted, and the triggers don't let you see the value of fields of that type

Thank you

Triggers are the obvious solution. You can easily detect updates to
TEXT columns using the UPDATE function. It is slightly harder to track
actual changes to TEXT columns because they aren't exposed in the
INSERTED and DELETED virtual tables but it can still be done by
preserving the TEXT value for each update (see below).

By definition to accomplish any reliable kind of audit you need some
form of access control. In a two-tier environment access control
usually means some combination of either user roles, application roles
and/or EXECUTE-only permission through procedures. If you aren't
familiar with all these options then do read about users and roles in
Books Online. It is not easy to add security as an afterthought because
security needs to be factored in to your whole application
architecture, but without an adequate security model your audit trail
will be wide open to abuse.

In your case it sounds like you have your own application-level
security model. To audit user activity you'll therefore have to pass
the user name to a stored procedure for each auditable event or update
it as a column value in each table you want to audit.

Hope this helps.

CREATE TRIGGER trg ON tbl for UPDATE, INSERT
AS

IF UPDATE(text_col)
BEGIN;

INSERT INTO dbo.audit (key_col,text_col)
SELECT key_col,text_col FROM dbo.tbl
WHERE EXISTS
(SELECT *
FROM inserted AS i
JOIN dbo.tbl AS t
ON i.key_col = t.key_col);

END;

GO

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

 

[aaron.kempf]

yes triggers rock.

if Access MDB had triggers; I would probably be using it right now lol.

Also; Microsoft and Sybase were rumored to be building a 'Select
Trigger' in one of the first editions of SQL Server (it was a joint
project- dang I wish that MS would buy Sybase and start offering SQL
Server on Unix lol)

On this select trigger; you could say 'when someone runs a select from
this table then do this'

I think that you can also setup DDL triggers-- respond to a Create
Procedure statement or Create User for example-- in 2005.

but it would have rocked to be able to have some sort of 'Select
Trigger' on tables; it just would come in handy every once in a while..
(say moving a table for example)

-Aaron

 

[Merlin]

Thanks David

Looks like there isn't going to be any easy solution.
Thanks for the sample code showing me how to get the value of Text fileds.

 

[Steve Troxell]

Some people may be tempted to suggest using SQL Server triggers, so I'll
jump in early and say they won't help me for these reasons:
* All connections to the SQL Server are done through a single account, so I
can't get the user ID of the person making the change; and

If the application can call SET CONTEXT_INFO after opening a database
connection (for example calling a stored procedure which calls SET
CONTEXT_INFO), then you can pass in any username you like (or any other
piece of data up to 128 bytes total) and it will remain local to the
database connection. The triggers can read this data and store whatever
value you pass in as the user ID.

* Most of the tables contain NText and Text fields that will need to be
auditted, and the triggers don't let you see the value of fields of that type

INSTEAD OF triggers will.

I sell a SQL Server audit product which automatically creates audit
triggers for you, including the SET CONTEXT_INFO stuff. It audits
TEXT/NTEXT/IMAGE fields but only to the degree as described previously
by David (it captures the changed value...you have to look at the
previous audit to get the "before" value). Implementing an INSTEAD OF
trigger option is on the todo list.

Steve Troxell
http://www.krell-software.com/omniaudit

 

[dbahooker]

a) dont use ntext, keep your data properly normalized instead of having
a gigantic 'notes' field (have a table for notes; and make them
varchar(400) or something along those lines-- something reasonable.
one dumb text field isn't reasonable
b) dont use sql authentication, use real windows authentication in a
production environment

 

[dbahooker]

and for the record

NOBODY _EVER_ NEEDS clientside temp tables

tell us more about your requirements.