How to implement Audit/Alert for Row level access?

#11

Neil Jones (castellan2004-atnews (AT) yahoo (DOT) com) writes:

Quote:

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, esquel (AT) sommarskog (DOT) 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

#12

Neil Jones (castellan2004-atnews (AT) yahoo (DOT) com) writes:

Quote:

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, esquel (AT) sommarskog (DOT) 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

#13

Neil Jones (castellan2004-atnews (AT) yahoo (DOT) com) writes:

Quote:

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, esquel (AT) sommarskog (DOT) 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