8.4 Database Audit Specification

8.4 Database Audit Specification

8.4.1 Database Audit Specification

Create audit specifications at the database level to enable auditing at the database level. Users with the ALTER any database AUDIT permission can create a database audit specification and bind it to any auditing. After you create a database audit specification, you can view the specification by a principal or sysadmin account that has CONTROL SERVER or ALTER any DATABASE AUDIT permissions.

The type of operation for database-level auditing can be either an action group or a single operation (SELECT, UPDATE, INSERT, DELETE, EXECUTE, RECEIVE, REFERENCES, and so on).

You can view the audit log in SSMS, or use a system function.

Database-level Audit operations do not apply to columns.

When the query processor is parameterized on a query, the audit event log appears with parameters instead of column values for the query.

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M02/79/6E/wKiom1aQrwCge9OXAAAd8X4IODY353.png "title=" View the log. png "alt=" Wkiom1aqrwcge9oxaaad8x4iody353.png "/>

8.4.2 New Database Audit specification

Database audit specifications are located in the database where they were created (except for thetempdb system database).

For a single database, each database audit specification needs to be bound to an "audit." If a database needs to create multiple audit specifications, it must be bound to multiple "audits."

In SSMS, expand security for the database, right-click Database Audit Specification, and select New Database audit specification.

650) this.width=650; "src=" Http://s2.51cto.com/wyfs02/M00/79/6B/wKioL1aQpc-hf5e_AACfB1Sx4MM570.png "style=" float: none; "title=" New 0.png "alt=" Wkiol1aqpc-hf5e_aacfb1sx4mm570.png "/>

650) this.width=650; "src=" Http://s2.51cto.com/wyfs02/M01/79/6C/wKiom1aQpaXiCX29AABs9kQg2S8265.png "style=" float: none; "title=" New 1.png "alt=" Wkiom1aqpaxicx29aabs9kqg2s8265.png "/>

The object class can be DATABASE, SCHEME, or object. If you select OBJECT, you can audit specific operations such as tables, views, stored procedures, functions, extended stored procedures, queues, synonyms, and so on. Click the Browse button to the right of the object name drop-down list and the Select Object dialog box appears.

650) this.width=650; "src=" Http://s2.51cto.com/wyfs02/M01/79/6C/wKiom1aQpbigpgFEAAAjeTZcLhs028.png "style=" float: none; "title=" Select Object 1.png "alt=" Wkiom1aqpbigpgfeaaajetzclhs028.png "/>

If you enter an object name that encounters multiple matching results, the Find Multiple Objects dialog box appears, selecting one or more matching objects.

650) this.width=650; "src=" Http://s2.51cto.com/wyfs02/M02/79/6B/wKioL1aQpeSCYX2gAAArdSpGLSI401.png "style=" float: none; "title=" Select Object 2.png "alt=" Wkiol1aqpescyx2gaaardspglsi401.png "/>

Tips:

When you create or modify a database audit specification in the user database, do not include audit actions for server-scoped objects, such as system views. If you include server-scoped objects, an audit is created. However, server-scoped objects will not be included, and no errors will be returned. To audit server-scoped objects, use the database audit specification in the master database.

Selecting the principal name action is similar to selecting the object name action method, except that the selected object can only be a user, database role, or application role.

650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M00/79/6C/wKiom1aQpcKQjDkvAAAhTkB7nmw492.png "title=" Select the principal name. png "alt=" Wkiom1aqpckqjdkvaaahtkb7nmw492.png "/>

When you are finished, click the OK button.

Examples of T-SQL syntax are:

Use [SQLDB01] GO CREATE DATABASE AUDIT specification [Databaseauditspecification-table-customer] For SERVER AUDIT [Audit-to-file] ADD (SELECT on object::[dbo].[ Customer] by [public]), ADD (INSERT on object::[dbo].[ Customer] by [public]), ADD (DELETE on object::[dbo].[ Customer] by [public]), ADD (UPDATE on object::[dbo].[ Customer] by [public]) GO

8.4.3 Enabling the Database audit specification

The newly created database Audit specification is a disabled state. If you need to enable it, you need to enable it manually.

650) this.width=650; "src=" Http://s2.51cto.com/wyfs02/M00/79/6D/wKiom1aQpmjzibE7AACTz8dzrwM708.png "title=" Enable. png "alt=" Wkiom1aqpmjzibe7aactz8dzrwm708.png "/>

Examples of T-SQL syntax are:

ALTER DATABASE AUDIT specification [Databaseauditspecification-table-customer] With (state = on);

This article from "SQLServer2014 series" blog, declined reprint!

8.4 Database Audit Specification