How to implement Audit/Alert for Row level access?

[Neil Jones]

Hello,

We have a large database of our customers. Some of the important
customers records are flagged and can be accessed only by applications
designed for upper management. The management would like to know if
anyone else is trying or successfully accessed the flagged customers.

Is there anyway to send an email alert when a normal user/sysadmin tries
to access the flagged customers in the table?

Thank you in advance for any help.

NJ

[Tom van Stiphout]

On Mon, 05 May 2008 21:09:00 -0400, Neil Jones
<ca******************@yahoo.comwrote:

You may have a logic error in your question, because if your assertion
"can be accessed only by applications designed for upper management"
is true, then obviously no one else has successfully accessed the
flagged customers.

-Tom.

>Hello,

We have a large database of our customers. Some of the important
customers records are flagged and can be accessed only by applications
designed for upper management. The management would like to know if
anyone else is trying or successfully accessed the flagged customers.

Is there anyway to send an email alert when a normal user/sysadmin tries
to access the flagged customers in the table?

Thank you in advance for any help.

NJ

[Neil Jones]

Ok. Well, actually anyone user can access the data in the table. We
would like to monitor the access to the flagged customers.

NJ

Tom van Stiphout wrote:

On Mon, 05 May 2008 21:09:00 -0400, Neil Jones
<ca******************@yahoo.comwrote:

You may have a logic error in your question, because if your assertion
"can be accessed only by applications designed for upper management"
is true, then obviously no one else has successfully accessed the
flagged customers.

-Tom.

>Hello,

We have a large database of our customers. Some of the important
customers records are flagged and can be accessed only by applications
designed for upper management. The management would like to know if
anyone else is trying or successfully accessed the flagged customers.

Is there anyway to send an email alert when a normal user/sysadmin tries
to access the flagged customers in the table?

Thank you in advance for any help.

NJ

[Tom van Stiphout]

On Tue, 06 May 2008 03:03:22 -0400, Neil Jones
<ca******************@yahoo.comwrote:

Just checking.

You can apply SQL Server security to revoke access to that table, then
create two views:
select * from SecretTable
and give access to the Mgmt group only
and:
select * from SecretTable
where ImportantCustomerRecFlag = 0
and give access to Everyone.

-Tom.

>Ok. Well, actually anyone user can access the data in the table. We
would like to monitor the access to the flagged customers.

NJ

Tom van Stiphout wrote:

>On Mon, 05 May 2008 21:09:00 -0400, Neil Jones
<ca******************@yahoo.comwrote:

You may have a logic error in your question, because if your assertion
"can be accessed only by applications designed for upper management"
is true, then obviously no one else has successfully accessed the
flagged customers.

-Tom.

>>Hello,

We have a large database of our customers. Some of the important
customers records are flagged and can be accessed only by applications
designed for upper management. The management would like to know if
anyone else is trying or successfully accessed the flagged customers.

Is there anyway to send an email alert when a normal user/sysadmin tries
to access the flagged customers in the table?

Thank you in advance for any help.

NJ

[--CELKO--]

>The management would like to know if anyone else is trying or successfully accessed the flagged customers. <<

This is a HIPAA requirement and it is best done with third party
software that has been certified. Conventional tools based off the
log files will show changes, but not access by user.

[Lennart]

On May 6, 5:54 pm, --CELKO-- <[e-mail]:

The management would like to know if anyone else is trying or successfully accessed the flagged customers. <<

This is a HIPAA requirement and it is best done with third party
software that has been certified. Conventional tools based off the
log files will show changes, but not access by user.

Joe, you don't happen to have a list of such software?
/Lennart

[Erland Sommarskog]

Neil Jones (ca******************@yahoo.com) writes:

We have a large database of our customers. Some of the important
customers records are flagged and can be accessed only by applications
designed for upper management. The management would like to know if
anyone else is trying or successfully accessed the flagged customers.

Is there anyway to send an email alert when a normal user/sysadmin tries
to access the flagged customers in the table?

Thank you in advance for any help.

In SQL 2000 and SQL 2005 there is very little support for this in
the product. You can set up a trace to caputre access to these tables.
You would then need a program that reads these trace files and checks
them for violation.

If you can afford to rework your applications, you can revoke access to
the underlying tables, and expose the customers through views. All
customers in one view, and another view with only non-flagged customers.
Only upper management would have permission to the former view.

In SQL 2008, currently in beta, there is a new auditing feature that I
think could meets your needs to some extent. But you would still have
to monitor the auditing log for violations on your own.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[--CELKO--]

>Joe, you don't happen to have a list of such software? <<

Craig Mullins likes NEON, so I'd start there.