Audit (SQL Server 2012)
Auditing of the SQL Server Instance or an
individual database involves tracking and logging events that occur on the
database engine. SQL Server audit allows creating as server audit, which can
contain server audit specifications for server level events, and database audit
specification at database level events. It can be written to event logs or to
audit files.
Basic auditing is available in all the versions of
Microsoft SQL Server 2012 instances but “fine grained auditing” is available
only in Enterprise edition.
SQL Server Audit Components
An audit is the combination of several elements
into a single package for a specific group of server actions or database
actions. The components of SQL Server audit combine to produce an output that
is called an audit.
SQL Server Audit
The SQL Server Audit object collects a single
instance of server or database-level actions and group of actions to monitor.
The audit is at the SQL Server instance level, an auditor can have multiple
audits per SQL Server instance.
When you define an audit, you specify the location
for the output of the result. This is the audit destination. The audit is
created in disabled state, and does not automatically audit any actions. When
audit is enabled, the audit destination receives data from the audit.
Server Audit Specification
The server audit specification object belongs to an
audit. You can create one server audit specification per audit, because both
are created at the SQL Server instance scope.
To know detailed server-level audit groups, please
go through the link http://technet.microsoft.com/en-us/library/cc280663.aspx
Database Audit Specification
The database audit specification object also
belongs to a SQL Server audit. You can create one database audit specification
per SQL Server database per audit.
The aforementioned link in “Server Audit
Specification” will you know groups and audit actions of database-level audit.
Target
The results of an audit are set to target, which
can be a file, the windows event log. Or the windows application log.
Any authenticated user can read and write to the
windows application log. The application event log requires lower permissions
than the windows security even log and is less secure than the windows security
event log.
Writing to Windows Security log requires the SQL
Server service account to be added to the “Generate security audits policy. By
default the Local System, Local Service, and Network Service are the part of
this policy.
When DBAs are saving audit information to a file,
to help prevent tampering, a DBA can restrict access to the file location in
the following ways:
- The SQL Server Service Account must have both Read and Write permission.
- Audit Administrators typically require Read and Write permission. Thus assumes that the Audit Administrators are Windows accounts for administration of audit files, such as: copying them to different location, backing them up, and so on.
- Audit Readers are authorized to read audit files and they must have only Read permission.
Even when the Database Engine is writing to a file,
other Windows users can read the audit file if they have permission. The
Database Engine does not take an exclusive lock which prevents read operations.
The Database Engine can access the file; SQL Server
logins that have “CONTROL SERVER” permission can use the Database Engine to
access the audit files. To record any user that is reading the audit file,
define an audit on “master.sys.fn_get_audit_file”.
The below statements will allow you to create,
alter, and drop server audit and database audits:
USE master;
--
Create the server audit.
CREATE SERVER AUDIT Pay_Security_Audit
TO FILE ( FILEPATH = '<Audit_File_Location>' ) ;
--
Enable the server audit.
ALTER SERVER AUDIT Payrole_Security_Audit WITH
(STATE = ON) ;
--
Move to the target database.
USE <Target_Database_Name> ;
--
Create the database audit specification.
CREATE DATABASE AUDIT SPECIFICATION Audit_Pay_Tables
FOR SERVER AUDIT Pay_Security_Audit
ADD
(SELECT , INSERT
ON <Target_Database_Name>.<Target_Table_Name> BY dbo )
WITH
(STATE = ON) ;
--
Create server audit to log the autis into the Event Log
CREATE SERVER AUDIT [HIPAA]
TO APPLICATION_LOG
WITH
( QUEUE_DELAY = 1000, ON_FAILURE = SHUTDOWN);
A sample scenario for Audit example.
--Create a sample database where audit will be configured
CREATE DATABASE SampleAudit;
--
Select the created database
USE SampleAudit;
--
Create a schema which will be owner of the audits
CREATE SCHEMA AuditSchema;
--
Create sample tables in sample database
CREATE TABLE AuditSchema.NormalData
(
DataID
INT PRIMARY KEY,
DataColumn NVARCHAR(50) NOT NULL
);
CREATE TABLE AuditSchema.SecureData
(
DataID
INT PRIMARY KEY,
DataColumn NVARCHAR(50) NOT NULL
);
--
Create the server audit in the master database
USE master;
CREATE SERVER AUDIT DataAccess TO FILE ( FILEPATH ='E:\audit\' )
WHERE object_name = 'SecureData' ;ALTER SERVER AUDIT DataAccess WITH (STATE = ON);
--
Create the database audit specification in the sample databaseUSE SampleAudit;
USE SampleAudit
CREATE DATABASE AUDIT SPECIFICATION [FilterSecureData] FOR SERVER AUDIT [DataAccess] ADD (SELECT ON SCHEMA::[AuditSchema] BY [public]) WITH (STATE = ON);
--
Trigger the audit event by selecting from tables
SELECT DataID,
DataColumn
FROM AuditSchema.NormalData;
SELECT DataID,
DataColumn
FROM AuditSchema.SecureData;
--
Check the audit for the filtered content
SELECT *
FROM Fn_get_audit_file('E:\audit\AuditDataAccess_*.sqlaudit', DEFAULT, DEFAULT);
The following dynamic views and functions can be
used for SQL Server Auditing by Auditor/DBA:
- Sys.dm_audit_action: Returns a for every audit action that can be reported in the
audit log and every audit action group that can be configured as part of
SQL Server Audit.
- Sys.dm_server_audit_status: Provides information about current status of audit.
- Sys.dm_audit_class_type_map: Returns a table that maps the class_type field in the audit
log to the class_desc field in sys.dm_audit_actions.
- Fn_get_audit_file: Returns information from an audit file created by a server
audit.
The following catalog views can be used for SQL
Server Auditing by Auditor/DBA:
- sys.database_audit_specifications: Contains information about database audit specification in a
SQL Server audit on a server instance.
- Sys.database_audit_specification_details: Contains information about the database audit specification
in a SQL Server audit on a serer instance for all databases.
- Sys.server_audits: Contains one row for each SQL Server audit in a server
instance.
- Sys.server_audit_specification: Contains information about the server audit specification in
a SQL Server audit on a server instance.
- Sys.server_audit_specification_details: Contains information about server audit specification details
(actions) in a SQL Server audit on a server instance.
- Sys.server_file_audits: Contains stores extended information about the file audit in a SQL Server audit on a server instance.
Auditing Administrators
Members of the sysadmin fixed
server role are identified as the dbo user in
each database. To audit actions of the administrators, audit the actions of the dbo user.
Permissions
Each feature and command for SQL Server Audit has
individual permission requirements.
To create, alter, or drop a Server Audit or Server
Audit Specification, server principals require the ALTER ANY SERVER AUDIT or
the CONTROL SERVER permission. To create, alter, or drop a Database Audit
Specification, database principals require the ALTER ANY DATABASE AUDIT or the
ALTER or CONTROL permission on the database. Principal must have permission to
connect to the database, or ALTER SERVER AUDIT or CONTROL SERVER permissions.
Unless otherwise specified, viewing catalog views
requires a principal to have one of the following:
- Membership in the sysadmin fixed server role
- The CONTROL SERVER permission
- The VIEW SERVER STATE permission
- The ALTER ANY AUDIT permission
- The VIEW AUDIT STATE permission (gives only the principal access to
the sys.server_catalog view)
No comments:
Post a Comment