Re: Audit logging an adp

Merlin wrote:
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
--

.

Relevant Pages

  • Re: Audit logging an adp
    ... Allen Browne's audit code. ... Some people may be tempted to suggest using SQL Server triggers, ... but without an adequate security model your audit trail ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Audit logging an adp
    ... if Access MDB had triggers; I would probably be using it right now lol. ... Allen Browne's audit code. ... Some people may be tempted to suggest using SQL Server triggers, ... but without an adequate security model your audit trail ...
    (microsoft.public.access.adp.sqlserver)
  • Re: One Web Service updates SQL, another cant?
    ... In the profiler, in the Events tab, click on Security Audit. ... Columnist, SQL Server Professional ... And then I start getting the database closed errors on subsequent calls. ... >>> thing to look at is the permissions for that user in EM. ...
    (microsoft.public.sqlserver.security)
  • Re: file backup component
    ... Otherwise you can try to use audit techniques to find out who's trying to access the LDF file. ... file backup app or any other app that need LDF file a while. ... he meant something that belongs to the SQL server itself. ... Delay the start of the SQL server service until manual start. ...
    (microsoft.public.windowsxp.embedded)
  • Re: Multiple Instances - Properties tuning
    ... Why do you want to install mutiple instances of SQL Server since you have ... memory usage for each instance of SQL Server. ... would you yourself choose to audit All or None? ... > If I were to audit, would I benefit from only auditing failure, or ...
    (microsoft.public.sqlserver.server)