Passive security refers to the practice of logging user activity to avoid the threat of non-repudiation. This is important because if an attack is launched by a privileged user, it allows appropriate disciplinary or even legal action to be taken. SQL Server provides SQL Server Audit to assist with implementing passive security.
Understanding SQL Server Audit
SQL Server Audit offers DBAs the ability to capture granular information about activity at both the instance level and the database level. Audit logs can be saved to a file, the Windows Security log, or the Windows Application log. The location that the audit logs are saved to is known as the target. There is exactly one target associated with each audit.
The SQL Server Audit resides at the instance level and defines the properties of the audit and the target. It is possible to create multiple server audits in each instance. This is useful if you have to audit many events in a busy environment, as it allows you to distribute the IO by using file targets and placing each target file on a separate volume.
Choosing the correct target is an important security consideration. If you choose the Windows Application log as a target, then any Windows user who is authenticated to the server is able to access it. The Security log is a lot more secure than the Application log but also more complex to configure as a target for SQL Server Audit logs.
When using the Security log as a target, the service account that is running the SQL Server service requires the Generate Security Audits user rights assignment. This can be assigned from the server’s local security policy, but ideally, it is configured at a group policy level to avoid the risk of a GPO change overriding the setting. Application-generated auditing also needs to be enabled.
Size is another consideration for the target. If you decide to use the Application log or Security log, then it is important that you consider and potentially increase the size of these logs before you begin using them for your audit. Also, work with your Windows administration team to decide on how the log is cycled when full and if you are archiving the log by backing it up to tape.
The SQL Server Audit can be associated with one or more server audit specifications and database audit specifications. Specifications define the activity that is captured by the audit at the instance level and the database level, respectively. It is helpful to have multiple server and/or database audit specifications if you are planning to log many actions, because you can categorize them to make administration easier, while still associating them with the same server audit. Each database within the instance must have its own database audit specification if you plan to audit activity across multiple databases.
SQL Server Audit Actions and Action Groups
SQL Server Audit events are based on the SQL Server event classes. Related actions are grouped into audit action groups. These audit action groups map to SQL Server event class categories. When creating a server audit specification or database audit specification, you configure the audit specification to capture audit action groups, which contain the events that you wish to capture.
Audit groups are available at three distinct layers: server (meaning instance), database, and audit. Providing the ability to audit the changes to audits avoids the threat of non-repudiation caused by a privileged user launching an attack and attempting to cover his tracks by changing the audit information that is logged.
Table 3-1 describes the action groups that are available at the server level. Notice that some groups are nested.
Table 3-1. Server-Level Action Groups
Action Group | Description | Actions Contained |
|---|---|---|
APPLICATION_ROLE_CHANGE_PASSWORD_GROUP | The event is raised when an application role’s password is changed. | APPLICATION_ROLE_CHANGE_PASSWORD_GROUP |
AUDIT_CHANGE_GROUP | The event is raised when Audit is created, dropped, or altered. | CREATE ALTER DROP AUDIT SHUTDOWN ON FAILURE CREATE ALTER DROP AUDIT_CHANGE_GROUP |
BACKUP_RESTORE_GROUP | The event is raised when a BACKUP command or RESTORE command is issued. | RESTORE BACKUP_RESTORE_GROUP BACKUP BACKUP LOG |
BROKER_LOGIN_GROUP | The event is raised when Service Broker security events occur. | BROKER LOGIN BROKER_LOGIN_GROUP |
DATABASE_CHANGE_GROUP | The event is raised when a database is created, dropped, or altered. | DATABASE_CHANGE_GROUP CREATE ALTER DROP |
DATABASE_LOGOUT_GROUP | The event is raised when a user without a login logs out of a database | DATABASE LOGOUT DATABASE_LOGOUT_GROUP |
DATABASE_MIRRORING_LOGIN_GROUP | The event is raised when Database Mirroring related security events occur | DATABASE MIRRORING LOGIN DATABASE_MIRRORING_LOGIN_GROUP |
DATABASE_OBJECT_ACCESS_GROUP | The event is raised when non-schema bound database objects are accessed. | DATABASE_OBJECT_ACCESS_GROUP |
DATABASE_OBJECT_CHANGE_GROUP | The event is raised when non-schema bound database objects are created, dropped, or altered. | DATABASE_OBJECT_CHANGE_GROUP |
DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP | The event is raised when the owner of a database object is changed. | DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP |
DATABASE_OBJECT_PERMISSION_CHANGE_GROUP | The event is raised when permissions are assigned or revoked from a database object. | DATABASE_OBJECT_PERMISSION_CHANGE_GROUP |
DATABASE_OPERATION_GROUP | The event is raised when SQL Server background operational tasks, such as a CHECKPOINT, occurs. | VIEW DATABASE STATE CONNECT DATABASE_OPERATION_GROUP CHECKPOINT SUBSCRIBE QUERY NOTIFICATION AUTHENTICATE SHOW PLAN |
DATABASE_OWNERSHIP_CHANGE_GROUP | The event is raised when the owner of a database is changed. | TAKE OWNERSHIP DATABASE_OWNERSHIP_CHANGE_GROUP |
DATABASE_PERMISSION_CHANGE_GROUP | The event is raised when permissions are assigned or revoked to a principal within a database. | DATABASE_PERMISSION_CHANGE_GROUP REVOKE DENY GRANT GRANT WITH GRANT REVOKE WITH GRANT REVOKE WITH CASCADE DENY WITH CASCADE |
DATABASE_PRINCIPAL_CHANGE_GROUP | The event is fired when a principal is created, dropped, or altered, within a database. | DATABASE_PRINCIPAL_CHANGE_GROUP |
DATABASE_PRINCIPAL_IMPERSONATION_GROUP | The event is fired when the impersonation of a database-level principal occurs. | DATABASE_PRINCIPAL_IMPERSONATION_GROUP |
DATABASE_ROLE_MEMBER_CHANGE_GROUP | The event is raised when the membership of a database role is changed. | DATABASE_ROLE_MEMBER_CHANGE_GROUP |
DBCC_GROUP | The event is raised when a DBCC statement is run. | DBCC DBCC_GROUP |
FAILED_DATABASE_AUTHENTICATION_GROUP | The event is raised when a user attempts to authenticate to a contained database, but the authentication fails. | FAILED_DATABASE_AUTHENTICATION_GROUP DATABASE AUTHENTICATION FAILED |
FAILED_LOGIN_GROUP | The event is raised when an attempt to authenticate to the instance fails. | LOGIN FAILED |
FULLTEXT_GROUP | The event is raised when full-text events occur. | FULLTEXT FULLTEXT_GROUP |
LOGIN_CHANGE_PASSWORD_GROUP | The event is raised when a login’s password is changed | RESET PASSWORD RESET OWN PASSWORD CHANGE OWN PASSWORD CHANGE PASSWORD UNLOCK ACCOUNT MUST CHANGE PASSWORD |
LOGOUT_GROUP | The event is raised when a principal logs out of the instance. | LOGOUT |
SCHEMA_OBJECT_ACCESS_GROUP | The event is raised when an object permission is used for a schema. | SELECT INSERT UPDATE DELETE REFERENCES EXECUTE RECEIVE VIEW CHANGETRACKING SCHEMA_OBJECT_ACCESS_GROUP |
SCHEMA_OBJECT_CHANGE_GROUP | The event is raised when a schema is created, dropped, or altered. | SCHEMA_OBJECT_CHANGE_GROUP |
SCHEMA_OBJECT_OWNERSHIP_CHANGE_GROUP | The event is raised when the owner of a schema-bound object is changed. | SCHEMA_OBJECT_OWNERSHIP_CHANGE_GROUP |
SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP | The event is raised when permissions are assigned or revoked to a schema-bound object. | SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP |
SERVER_OBJECT_CHANGE_GROUP | The event is raised when an instance-level object is created, dropped, or altered. | ALTER BACKUP CREATE CREDENTIAL MAP TO LOGIN DROP NO CREDENTIAL MAP TO LOGIN RESTORE |
SERVER_OBJECT_OWNERSHIP_CHANGE_GROUP | The event is raised when the owner of an instance-level object is changed. | TAKE OWNERSHIP |
SERVER_OBJECT_PERMISSION_CHANGE_GROUP | The event is raised when permissions are assigned or revoked to an instance-level object. | DENY DENY WITH CASCADE GRANT GRANT WITH GRANT REVOKE REVOKE WITH CASCADE REVOKE WITH GRANT |
SERVER_OPERATION_GROUP | The event is raised when instance configuration changes are made. | ALTER ALTER RESOURCES CREATE DROP |
SERVER_PERMISSION_CHANGE_GROUP | The event is raised when permissions are assigned or revoked to instance-level permissions. | DENY DENY WITH CASCADE GRANT GRANT WITH GRANT REVOKE REVOKE WITH CASCADE REVOKE WITH GRANT SERVER_PERMISSION_CHANGE_GROUP |
SERVER_PRINCIPAL_CHANGE_GROUP | The event is fired when instance-level principals are created, dropped, or altered. | ALTER CHANGE DEFAULT DATABASE CHANGE DEFAULT LANGUAGE CHANGE LOGIN CREDENTIAL CREATE DISABLE DROP ENABLE NAME CHANGE PASSWORD EXPIRATION PASSWORD POLICY |
SERVER_PRINCIPAL_IMPERSONATION_GROUP | The event is raised when impersonation of an instance-level principal occurs. | IMPERSONATE |
SERVER_ROLE_MEMBER_CHANGE_GROUP | The event is raised when the membership of a server role is changed. | ADD MEMBER DROP MEMBER |
SERVER_STATE_CHANGE_GROUP | The event is raised when the state of the instance is modified. | SERVER CONTINUE SERVER PAUSED SERVER SHUTDOWN SERVER STARTED SERVER_STATE_CHANGE_GROUP |
SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP | The event is raised when a principal successfully authenticates to a contained database. | DATABASE AUTHENTICATION SUCCEEDED SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP |
SUCCESSFUL_LOGIN_GROUP | The event is raised when a principal successfully authenticates to the instance. | LOGIN SUCCEEDED |
TRACE_CHANGE_GROUP | The event is raised if a trace is modified. | ALTER TRACE TRACE AUDIT C2OFF TRACE AUDIT C2ON TRACE AUDIT START TRACE AUDIT STOP TRACE_CHANGE_GROUP |
TRANSACTION_GROUP | The event is raised when a transaction begins, commits, or rolls back. | STATEMENT_ROLLBACK_GROUP TRANSACTION_BEGIN_GROUP TRANSACTION_COMMIT_GROUP TRANSACTION_GROUP TRANSACTION_ROLLBACK_GROUP |
USER_CHANGE_PASSWORD_GROUP | The event is raised when a user with password’s password is changed. | USER_CHANGE_PASSWORD_GROUP |
USER_DEFINED_AUDIT_GROUP | The event is triggered when the sp_audit_write procedure if executed. | USER DEFINED AUDIT USER_DEFINED_AUDIT_GROUP |
Table 3-2 describes the action groups that are available at the database level. Notice that many of the groups are the same as the server-level groups. The difference is that groups at the database-level apply only to the database with which they are associated. The server-level groups apply to all databases on the instance.
Table 3-2. Database-Level Audit Action Groups
Action Group | Description | Actions Contained |
|---|---|---|
APPLICATION_ROLE_CHANGE_PASSWORD_GROUP | The event is triggered when an application role’s password is changed. | APPLICATION_ROLE_CHANGE_PASSWORD_GROUP |
AUDIT_CHANGE_GROUP | The event is raised when an audit is created, dropped, or altered. | CREATE ALTER DROP AUDIT SHUTDOWN ON FAILURE CREATE ALTER DROP AUDIT_CHANGE_GROUP |
BACKUP_RESTORE_GROUP | The event is triggered when a database is backed up or restored. | RESTORE BACKUP_RESTORE_GROUP BACKUP BACKUP LOG |
DATABASE_CHANGE_GROUP | The event is raised when a database is created, dropped, or altered. | DATABASE_CHANGE_GROUP CREATE ALTER DROP |
DATABASE_LOGOUT_GROUP | The event is raised when a user without a login logs out of a database. | DATABASE LOGOUT DATABASE_LOGOUT_GROUP |
DATABASE_OBJECT_ACCESS_GROUP | The event is raised when non-schema bound database objects are accessed. | DATABASE_OBJECT_ACCESS_GROUP |
DATABASE_OBJECT_CHANGE_GROUP | The event is raised when non-schema bound database objects are created, dropped, or altered. | DATABASE_OBJECT_CHANGE_GROUP |
DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP | The event is raised when the owner of a database is changed. | DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP |
DATABASE_OBJECT_PERMISSION_CHANGE_GROUP | The event is raised when permissions are assigned or revoked to a principal within a database. | DATABASE_OBJECT_PERMISSION_CHANGE_GROUP |
DATABASE_OPERATION_GROUP | The event is raised when SQL Server background operational tasks, such as a CHECKPOINT, occurs. | VIEW DATABASE STATE CONNECT DATABASE_OPERATION_GROUP CHECKPOINT SUBSCRIBE QUERY NOTIFICATION AUTHENTICATE SHOW PLAN |
DATABASE_OWNERSHIP_CHANGE_GROUP | The event is raised when the owner of a database is changed. | TAKE OWNERSHIP DATABASE_OWNERSHIP_CHANGE_GROUP |
DATABASE_PERMISSION_CHANGE_GROUP | The event is raised when permissions are assigned or revoked to a principal within a database. | DATABASE_PERMISSION_CHANGE_GROUP REVOKE DENY GRANT GRANT WITH GRANT REVOKE WITH GRANT REVOKE WITH CASCADE DENY WITH CASCADE |
DATABASE_PRINCIPAL_CHANGE_GROUP | The event is fired when a principal is created, dropped, or altered within a database | DATABASE_PRINCIPAL_CHANGE_GROUP |
DATABASE_PRINCIPAL_IMPERSONATION_GROUP | The event is fired when the impersonation of a database-level principal occurs. | DATABASE_PRINCIPAL_IMPERSONATION_GROUP |
DATABASE_ROLE_MEMBER_CHANGE_GROUP | The event is raised when the membership of a database role is changed. | DATABASE_ROLE_MEMBER_CHANGE_GROUP |
DBCC_GROUP | The event is raised when a DBCC statement is run. | DBCC DBCC_GROUP |
FAILED_DATABASE_AUTHENTICATION_GROUP | The event is raised when a user attempts to authenticate to a contained database, but the attempt fails. | FAILED_DATABASE_AUTHENTICATION_GROUP DATABASE AUTHENTICATION FAILED |
SCHEMA_OBJECT_ACCESS_GROUP | The event is raised when an object permission is used for a schema. | SELECT INSERT UPDATE DELETE REFERENCES EXECUTE RECEIVE VIEW CHANGETRACKING SCHEMA_OBJECT_ACCESS_GROUP |
SCHEMA_OBJECT_CHANGE_GROUP | The event is raised when a schema is created, dropped, or altered. | SCHEMA_OBJECT_CHANGE_GROUP |
SCHEMA_OBJECT_OWNERSHIP_CHANGE_GROUP | The event is raised when the owner of a schema-bound object is changed. | SCHEMA_OBJECT_OWNERSHIP_CHANGE_GROUP |
SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP | The event is raised when permissions are assigned or revoked to a schema-bound object. | SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP |
SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP | The event is raised when a principal successfully authenticates to a contained database. | DATABASE AUTHENTICATION SUCCEEDED SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP |
USER_CHANGE_PASSWORD_GROUP | The event is raised when a user with password’s password is changed. | USER_CHANGE_PASSWORD_GROUP |
USER_DEFINED_AUDIT_GROUP | The event is triggered when the sp_audit_write procedure if executed. | USER DEFINED AUDIT USER_DEFINED_AUDIT_GROUP |
Table 3-3 describes the audit action group available at the audit level.
Table 3-3. Audit-Level Audit Action Groups
Action Group | Description | Actions Contained |
|---|---|---|
AUDIT_ CHANGE_GROUP | The event is fired when a SQL Server Audit artifact is created, dropped, or altered. | CREATE SERVER AUDIT ALTER SERVER AUDIT DROP SERVER AUDIT CREATE SERVER AUDIT SPECIFICATION ALTER SERVER AUDIT SPECIFICATION DROP SERVER AUDIT SPECIFICATION CREATE DATABASE AUDIT SPECIFICATION ALTER DATABASE AUDIT SPECIFICATION DROP DATABASE AUDIT SPECIFICATION |
Implementing SQL Server Audit
The following sections discuss how to create a server audit, a server audit specification, and a database audit specification.
Creating a Server Audit
A server audit can be created using the CREATE SERVER AUDIT DDL (Data Definition Language) statement. Table 3-4 describes the options that are available when creating a server audit.
Table 3-4. Server Audit Options
Option | Description |
|---|---|
FILEPATH | Specifies the filepath where the audit logs is generated. Only applies if you choose a file target. |
MAXSIZE | Specifies the largest size that the audit file can grow to. The minimum size you can specify for this is 2MB. Only applies if you choose a file target. |
MAX_ROLLOVER_FILES | When the audit file becomes full, you can either cycle that file or generate a new file. The MAX_ROLLOVER_FILES setting controls how many new files can be generated before they begin to cycle. The default value is UNLIMITED, but specifying a number caps the number of files to this limit. If you set it to 0, then there will only ever be one file, which will cycle every time it becomes full. Any value above 0 indicates the number of rollover files that is permitted. So for example, if you specify 5, then there is a maximum of six files in total. Only applies if you choose a file target. |
MAX_FILES | As an alternative to MAX_ROLLOVER_FILES, the MAX_FILES setting specifies a limit for the number of audit files that can be generated, but when this number is reached, the logs will not cycle. Instead, the audit fails and events that cause an audit action to occur are handled based on the setting for ON_FAILURE. Only applies if you choose a file target. |
RESERVE_DISK_SPACE | Pre-allocate space on the volume equal to the value set in MAXSIZE, as opposed to allowing the audit log to grow as required. Only applies if you choose a file target. |
QUEUE_DELAY | Specifies if audit events are written synchronously or asynchronously. If set to 0, events are written to the log synchronously. Otherwise, specify the duration in milliseconds that can elapse before events are forced to write. The default value is 1000 (1 second), which is also the minimum value. |
ON_FAILURE | Specifies what should happen if events that cause an audit action fail to be audited to the log. Acceptable values are CONTINUE, SHUTDOWN, or FAIL_OPERATION. When CONTINUE is specified, the operation is allowed to continue. This can lead to unaudited activity occurring. FAIL_OPERATION causes auditable events to fail, but allows other actions to continue. SHUTDOWN forces the instance to stop if auditable events cannot be written to the log. |
AUDIT_GUID | Because server and database audit specifications link to the server audit through a GUID, there are occasions when an audit specification can become orphaned. These include when you attach a database to an instance, or when you implement technologies such as AlwaysOn Availability Groups. This option allows you to specify a specific GUID for the server audit, as opposed to having SQL Server generate a new one. |
It is also possible to create a filter on the server audit. This is useful when your Audit Specification captures activity against an entire class of object, but you are only interested in a subset of this information. For example, you may configure a server audit specification to log members added to or removed from server roles; but really, you are only interested in members being added to or removed from the sysadmin server role. In this scenario, you can filter on the sysadmin role and reduce the amount of “noise” recorded in the audit log.
Note
Please refer to Chapter 2 for further information on server roles.
The script in Listing 3-1 demonstrates how to create a server audit. The audit uses a file target and the target may consist of an unlimited number of files, although each file is limited in size to 256MB. The audit is configured so that if the audit fails to log an operation, that operation will fail. There is also a filter placed on the audit so that only activity, where the object_name property is equal to sysadmin, is logged. This allows you to create a server audit specification, which checks for members being added to or removed from a server role, as discussed earlier.
Tip
If you are following along with the demonstrations, then you should change the filepath to match your own configuration.
Listing 3-1. Create a Server Audit
USE MasterGOCREATE SERVER AUDIT [Audit-CarterSecureSafe]TO FILE(FILEPATH = 'c:audit_filesaudit',MAXSIZE = 256 MB,MAX_ROLLOVER_FILES = 2147483647,RESERVE_DISK_SPACE = OFF)WITH(QUEUE_DELAY = 1000,ON_FAILURE = CONTINUE)WHERE object_name = 'sysadmin' ;
An audit can be enabled by altering the audit. This is demonstrated in Listing 3-2.
Listing 3-2. Enabling an Audit
ALTER SERVER AUDIT [Audit-CarterSecureSafe]WITH (STATE = ON) ;
Create a Server Audit Specification
A server audit specification can be created using the CREATE SERVER AUDIT SPECIFICATION DDL statement. Table 3-5 describes the options that can be set when creating a server audit specification.
Table 3-5. Server Audit Specification Options
Argument | Description |
|---|---|
audit_specification_name | The name to be assigned to the server audit specification. |
audit_name | The name of the server audit to which the specification is associated. |
audit_action_group_name | The name of a group of related auditable actions at the instance level. |
STATE | Specifies if the server audit specification should be started on creation. |
Listing 3-3 demonstrates how to create a server audit specification, which captures changes to the membership of server roles.
Listing 3-3. Create a Server Audit Specification
CREATE SERVER AUDIT SPECIFICATION [ServerAuditSpecification-CarterSecureSafe]FOR SERVER AUDIT [Audit-CarterSecureSafe]ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP) ;
Create a Database Audit Specification
Creating a database audit specification is similar to creating a server audit specification but provides more flexibility because you can specify filters, such as the securable or principal to be audited.
You can create a database audit specification by using the CREATE DATABASE AUDIT SPECIFICATION DDL statement. Table 3-6 describes the options that are available when creating a database audit specification .
Table 3-6. Database Audit Specification Options
Argument | Description |
|---|---|
audit_specification_name | The name to be assigned to the database audit specification. |
audit_name | The name of the server audit to which the specification is associated. |
action | The granular action to be audited. |
audit_action_group_name | The name of a group of related auditable actions at the database level. |
class | The class name of the securable. |
securable | The name of the securable to be audited. |
principal | The name of the principal to be audited. |
STATE | Specifies if the server audit specification should be started on creation. |
Listing 3-4 demonstrates how to create a database audit specification that is associated with the Audit-CarterSecureSafe audit, and captures DELETE statements made against the Person.Person table in the AdventureWorks2016 database by any user.
Listing 3-4. Create a Database Audit Specification
CREATE DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification-AdventureWorks2016]FOR SERVER AUDIT [Audit-CarterSecureSafe]ADD (DELETE ON OBJECT::Person.Person BY public) ;
Server audit specifications and database audit specifications can be enabled on creation or by altering the specification. This is demonstrated in Listing 3-5.
Listing 3-5. Enabling an Audit Specification
ALTER SERVER AUDIT SPECIFICATION [DatabaseAuditSpecification-AdventureWorks2016]WITH (STATE = ON) ;
Creating Custom Audit Events
There may be times when you want to use SQL Server Audit to capture very specific events that are not possible to do with the out-of-the-box functionality of SQL Server Audit. If this is the case, then you can create a server audit specification or a database audit specification that is configured to capture the USER_DEFINED_AUDIT_GROUPaudit action group, and then manually fire the event in your application code. The following sections demonstrate how to create the server audit and database audit specification required to log sales orders in which more than five different items are ordered.
Creating the Server Audit and Database Audit Specification
The script in Listing 3-6 uses the techniques that you learned in this chapter to create a server audit and a database audit specification linked to the server audit and captures USER_DEFINED_AUDIT_GROUP. The database audit specification is created in the AdventureWorks2016 database in which the Person.Person table is hosted.
Listing 3-6. Create the Server Audit and Database Audit Specification
USE MasterGOCREATE SERVER AUDIT [Audit-CarterSecureSafeCustom]TO FILE(FILEPATH = 'c:audit_filesaudit-custom',MAXSIZE = 256 MB,MAX_ROLLOVER_FILES = 2147483647,RESERVE_DISK_SPACE = OFF)WITH(QUEUE_DELAY = 1000,ON_FAILURE = CONTINUE) ;GOCREATE SERVER AUDIT SPECIFICATION [ServerAuditSpecification-CarterSecureSafeCustom]FOR SERVER AUDIT [Audit-CarterSecureSafeCustom]ADD (USER_DEFINED_AUDIT_GROUP) ;GOALTER SERVER AUDIT [Audit-CarterSecureSafeCustom]WITH (STATE = ON) ;ALTER DATABASE AUDIT SPECIFICATION [ServerAuditSpecification-CarterSecureSafeCustom]WITH (STATE = ON) ;
Raising the Event
A custom event can be raised by using the sp_audit_write system stored procedures. Table 3-7 describes the parameters accepted by the sp_audit_write procedure. The values for all parameters are user-defined and are recorded in the audit log when the event is fired.
Table 3-7. sp_audit_write Parameters
Parameter | Description |
|---|---|
@user_defined_event_id | Specifies the id of the user-defined event. |
@succeeded | Specifies if the event was successful. • 0 indicates that the event failed • 1 indicates that the event succeeded |
@user_defined_information | Specifies the description of the event. |
The sp_audit_write procedure can be called from a code module, such as a stored procedure or trigger. In our scenario, the table is updated from ad hoc SQL within the Sales application, so you call the sp_audit_write procedure from inside a DML (data manipulation language) trigger. Listing 3-7 demonstrates how to create the trigger.
Caution
DML triggers can cause a negative performance impact if they are created against a table that has many writes. They should be used with caution and performance characteristics should be assessed before being implemented in a production environment.
Listing 3-7. Create a Trigger to Fire the Event
CREATE TRIGGER FireCustomEventON Person.PersonAFTER INSERTASBEGINIF (SELECT COUNT(*) FROM Inserted) > 5BEGINEXEC sys.sp_audit_write 1, 1, 'More than 5 items order' ;ENDEND ;
Summary
SQL Server Audit provides DBAs with a flexible and lightweight auditing mechanism. This is important for avoiding issues of non-repudiation when privileged users perform unauthorized actions.
An audit object is used to configure the target. It is also used to specify the behaviors of the audit, such as what should happen if SQL Server Audit fails to write an event to the audit log. Multiple audits can exist on an instance.
Server audit specifications and database audit specification are used to define which events should be audited. Multiple server audit specifications and database audit specifications can be associated with a single audit.
SQL Server Audit is made extensible by the USER_DEFINED_AUDIT_GROUPaudit action group. This action group enables custom events to be fired. Custom events are triggered by calling the sp_audit_write system stored procedure. This procedure can be called from a code module, such as a stored procedure or trigger; it also allows DBAs to capture events that are specific to their environments. They cannot capture through out-of-the-box functionality.