Data Security has rightly been among the top 5 priorities for CIOs s for many straight years.
Moreover, the rise in building of applications having a large user base across the globe has resulted in dynamic data usage patterns. Access routes have been increased to accommodate these patterns and will continue to do so. In this scenario, making sure whether your business data is secure, becomes pivotal for your growth.
With the rise in cloud-based and third party storage solutions, adhering to data security standards and compliance requirements around the globe, makes this scenario even more challenging.
One of the very important aspects of data security management is to ensure that appropriate security controls are applied at the core database level. The level of security required can be governed by the level of sensitive data handled by the application. For example, an application dealing with a Customer’s personal data and or financial data would require more stringent security controls as compared to an application that deals with non-financial data.
The applications need to be strong enough to hold fast to various data security related constraints by having all the layers of application architecture secure enough. To bring robustness in your architecture for data security, it is very important to have regular audit features at the database level for various events like access patterns, potential security breaches, malicious or unauthorized attempt to access data, potential weaknesses in applications etc. which may lead to breach in data security.
Audit features in SQL Server helps in addressing these concerns and in making the data security policy robust.
SQL Server Audit offers various advantages and features in terms of security as mentioned below:
- With SQL Server one can conduct audit in two ways, one is, Instance level and the other one is, database level. For some cases we can define audit process at both the levels.
- SQL Server audit is fast and lightweight as compared to SQL Server profiler trace file. This is because SQL Server engine automatically manages the Audit event features following which we can schedule a job for audit.
- The setup of SQL Server audit can be very easy and manageable. By using T-SQL or Management Studio we can easily configure the SQL Server audit feature.
- Capture daily activities: In our daily routine we perform many activities on database level i.e. DML and DDL operations. We can capture all these activities in an audit log. If the Management wants to monitor the DBAs activities, it can do so by performing the audit job on both the levels and capture and record individual users and object level audit logs.
- Viewing audit logs: We can view audit logs on the SQL Server itself or if required, store the physical hard copy of the logs on a disk and import them from the disk to the SQL server for analyzing the audit logs as and when required.
Limitations of SQL Server Audit:
Every coin has two sides, with benefits come limitations. Here are the aspects which we can consider as SQL server audit limitations:
- As mentioned earlier, when SQL Server runs an audit job, most of the resources are used behind the scene, hence audit jobs run only when required from a security point of view and not on a regular basis.
- It is s difficult to manage multiple instance audits from one centralized location. We will have to write scripts for it, if required.
- Audit logs track each event at user and object levels. If we want to filter some events, this task has to be performed manually.
- We can view audit logs only in an audit viewer.. There is no inbuilt, up to date report available. If required, we will have to use the SQL Server Reporting Services to serve this purpose.
In a nutshell, Audit is a tool that allows Database administrators to capture all the events which were performed on a Database Server. My recommendation is, if you do not want to purchase any third party utility for the Security of your database, you can easily accomplish the same with the SQL Server Audit Feature.
