SQL Server security: Auditing

Auditing requirements have had an impact on SQL Server security. Like with authentication and permissions, auditing requirements have become more stringent and encompassing, especially those in HIPAA, Sarbanes-Oxley and Basel II. Many companies now have a legal obligation to track not only who modified a particular piece of data but also who viewed it.

More on SQL Server The changing database consolidation landscape SQL Server database security

Since SQL Server 6 it has been possible to run Profiler to capture most queries and Data Manipulation Language (DML). SQL Server 2005 allows for richer auditing of events; for example, with Data Definition Language (DDL) triggers, you can audit schema changes and use Profiler to audit querying activity to see who views a particular table. However, using Profiler for auditing consumes significant resources.

SQL Server 2008 introduces:

• Login triggers: These triggers will execute when a user logs on, to enable more granular control over what occurs during a login.
• Expanded auditing: SQL Server 2008 allows for more granular auditing. For example, you can audit an event such as someone increasing or decreasing inventory above or below a certain event. In addition, SQL Server 2008 allows auditing of any predefined server and database actions. These audits can be written to a file, Windows Application Log or the Windows Server log. SQL Server 2008 also introduces All Actions Audited, a setting that causes all database events to be audited, to comply with the most stringent auditing requirements.
• Policy-based management: SQL Server 2008 introduces Declared Management Framework (DMF), allowing policies to be created to monitor and report on objects' compliance with specific server, database or table settings. For example, you can increase SQL Server's security posture by using DMF to set a policy that all user tables belong to user schemas and do not belong to the dbo schema. If an object falls out of compliance, a script can be run that makes it complaint or sends out an alert.

About the author

Hilary Cotter has been involved in IT for more than 20 years as a Web and database consultant. Microsoft first awarded Cotter the Microsoft SQL Server MVP award in 2001. Cotter received his bachelor of applied science degree in mechanical engineering from the University of Toronto and subsequently studied economics at the University of Calgary and computer science at UC Berkeley. He is the author of a book on SQL Server transactional replication and is currently working on books on merge replication and Microsoft search technologies.

Go to page: 1 - 2 - 3