CODEWITHSUNDEEP
sql-serversecurityms-accessvbaaudit
Daniel St-Jean
Daniel St-Jean

Reputation: 117

Security Audit (CRUD) - Microsoft Access

My client has been using Microsoft Access 2010 for quite a while and they received some Security Audit Requirements. They are using a Linked Tables approach connecting to Microsoft SQL Server 2012 Express.

The requirements states that all actions against the data must be logged. (INSERT, UPDATE, DELETE AND SELECT statements)

For the INSERT, UPDATE, DELETE statements I could create a trigger which would log the changes.

The issue is around the audit of SELECT statements. If the data was read-only, I could have used a Stored Procedure which would have logged the query. But executing a Stored Proc makes a Recordset not updatable.

Does anyone have an idea how to approach this challenge?

I'm open to a lot of strategies... (Connecting Access to SQL through a web service, anything...)

It's important to note that my client does not have $30k to spend on an Enterprise edition of SQL Sever as they are a small-business with less than 10 employees.

Upvotes: 1

Views: 955

Answers (3)

Albert D. Kallal
Albert D. Kallal

Reputation: 49169

You could turn on JET showplan. This would log all queries used by Access.

http://www.techrepublic.com/article/use-microsoft-jets-showplan-to-write-more-efficient-queries/?siu-container

As I pointed out in comments you really fooling the audit requirements UNLESS each form is opened using a where clause that limits the viewing of data in that form to the ONE record. If you don’t do this, then a form opened to a linked table could have 1000’s of records, and user(s) hitting ctrl-f to find and jump to one record means the SELECT statement tells you ZERO about what the user actually looked at. So while you can turn on show plan, the audit concept would not tell you anything about what the user actually looked at unless application design changes are made to restrict forms to one record. And to be fair, 99% of my applications in fact do open and restrict the main editing form to the one record via a where clause.

So while you can technology wise log all SELECT commands as per above, it not really in the sprit of such a log since such a log would not be of any use to determine what actual records the user looked at.

Upvotes: 0

Daniel Calbimonte
Daniel Calbimonte

Reputation: 35

You could upgrade to a SQL Server edition that supports SQL Server profiler. The other option is to get other tools to audit like sql audit for example.

Upvotes: 0

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95642

SELECT statements are part of the database-level audit action groups in SQL Server. (Search that page for "database-level audit actions".) But that level of auditing requires SQL Server Enterprise edition.

Theoretically, you can limit all access to use only stored procedures regardless of whether the data is read-only. Write the stored procedure to write auditing information to the log first, then do whatever else needs to be done--SELECT, INSERT, etc.

Practically, you might not be able to do that. It depends on the applications that hit your database. Limiting all access to use only stored procedures can break applications that expect other things. (How would a Ruby on Rails application respond if you switched to just stored procedures?)

A bulletproof audit system that makes your database unusable isn't very good; it's simpler and cheaper to just shut down the database server altogether.

Upvotes: 1

Related Questions