CREATE DATABASE AUDIT SPECIFICATION (Transact-SQL)CREATE DATABASE AUDIT SPECIFICATION (Transact-SQL)

适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions)

使用 SQL ServerSQL Server 审核功能创建数据库审核规范对象。Creates a database audit specification object using the SQL ServerSQL Server audit feature. 有关详细信息,请参阅 SQL Server Audit(数据库引擎)For more information, see SQL Server Audit (Database Engine).

主题链接图标 Transact-SQL 语法约定Topic link icon Transact-SQL Syntax Conventions

语法Syntax

CREATE DATABASE AUDIT SPECIFICATION audit_specification_name  
{  
    FOR SERVER AUDIT audit_name   
        [ { ADD ( { <audit_action_specification> | audit_action_group_name } )   
      } [, ...n] ]  
    [ WITH ( STATE = { ON | OFF } ) ]  
}  
[ ; ]  
<audit_action_specification>::=  
{  
      action [ ,...n ]ON [ class :: ] securable BY principal [ ,...n ]  
}  

备注

若要查看 SQL Server 2014 及更早版本的 Transact-SQL 语法,请参阅早期版本文档To view Transact-SQL syntax for SQL Server 2014 and earlier, see Previous versions documentation.

参数Arguments

audit_specification_name audit_specification_name
是审核规范的名称。Is the name of the audit specification.

audit_name audit_name
是应用此规范的审核的名称。Is the name of the audit to which this specification is applied.

audit_action_specification audit_action_specification
是主体对安全对象执行的应记录到审核中的操作的规范。Is the specification of actions on securables by principals that should be recorded in the audit.

actionaction
是一个或多个数据库级别可审核操作的名称。Is the name of one or more database-level auditable actions. 要获取审核操作列表,请参阅 SQL Server 审核操作组和操作For a list of audit actions, see SQL Server Audit Action Groups and Actions.

audit_action_group_name audit_action_group_name
是一个或多个数据库级别可审核操作组的名称。Is the name of one or more groups of database-level auditable actions. 要获取审核操作组列表,请参阅 SQL Server 审核操作组和操作For a list of audit action groups, see SQL Server Audit Action Groups and Actions.

class class
是安全对象上的类名(如果适用)。Is the class name (if applicable) on the securable.

securable securable
是应用审核操作或审核操作组的数据库中的表、视图或其他安全对象。Is the table, view, or other securable object in the database on which to apply the audit action or audit action group. 有关详细信息,请参阅 SecurablesFor more information, see Securables.

principal principal
应用审核操作或审核操作组的数据库主体的名称。Is the name of database principal on which to apply the audit action or audit action group. 使用数据库主体 public 审核所有数据库主体。To audit all database principals use the database principal public. 有关详细信息,请参阅主体(数据库引擎)For more information, see Principals (Database Engine).

WITH ( STATE = { ON | OFF } )WITH ( STATE = { ON | OFF } )
允许或禁止审核收集此审核规范的记录。Enables or disables the audit from collecting records for this audit specification.

备注Remarks

数据库审核规范是驻留在给定数据库中的非安全对象。Database audit specifications are non-securable objects that reside in a given database. 数据库审核规范在创建之后处于禁用状态。When a database audit specification is created, it is in a disabled state.

权限Permissions

拥有 ALTER ANY DATABASE AUDIT 权限的用户可以创建数据库审核规范并将其绑定到任何审核。Users with the ALTER ANY DATABASE AUDIT permission can create database audit specifications and bind them to any audit.

在创建数据库审核规范之后,拥有 CONTROL SERVERALTER ANY DATABASE AUDIT 权限的主体或 sysadmin 帐户可查看该规范。After a database audit specification is created, it can be viewed by principals with the CONTROL SERVER, ALTER ANY DATABASE AUDIT permissions, or the sysadmin account.

示例Examples

A.A. 审核任何数据库主体的表上的 SELECT 和 INSERTAudit SELECT and INSERT on a table for any database principal

下面的示例创建名为 Payrole_Security_Audit 的服务器审核,然后创建名为 Payrole_Security_Audit 的数据库审核规范,该规范针对 SELECT 数据库中的 INSERT 表审核 dbo 用户发出的 HumanResources.EmployeePayHistoryAdventureWorks2012 语句。The following example creates a server audit called Payrole_Security_Audit and then a database audit specification called Payrole_Security_Audit that audits SELECT and INSERT statements by the dbo user, for the HumanResources.EmployeePayHistory table in the AdventureWorks2012 database.

USE master ;  
GO  
-- Create the server audit.  
CREATE SERVER AUDIT Payrole_Security_Audit  
    TO FILE ( FILEPATH =   
'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA' ) ;  
GO  
-- Enable the server audit.  
ALTER SERVER AUDIT Payrole_Security_Audit   
WITH (STATE = ON) ;  
GO  
-- Move to the target database.  
USE AdventureWorks2012 ;  
GO  
-- Create the database audit specification.  
CREATE DATABASE AUDIT SPECIFICATION Audit_Pay_Tables  
FOR SERVER AUDIT Payrole_Security_Audit  
ADD (SELECT , INSERT  
     ON HumanResources.EmployeePayHistory BY dbo )  
WITH (STATE = ON) ;  
GO  

B.B. 审核特定数据库角色“sales”架构中所有对象上的任何 DML(INSERT、UPDATE 或 DELETE) Audit any DML (INSERT, UPDATE or DELETE) on all objects in the sales schema for a specific database role

下面的示例针对 DataModification_Security_Audit 数据库中 Audit_Data_Modification_On_All_Sales_Tables 架构的所有对象,创建名为 INSERT 的服务器审核,然后创建可由新数据库角色 UPDATE 中的用户审核 DELETESalesUKSales 语句的数据库审核规范,其名称为 AdventureWorks2012The following example creates a server audit called DataModification_Security_Audit and then a database audit specification called Audit_Data_Modification_On_All_Sales_Tables that audits INSERT, UPDATE and DELETE statements by users in a new database role SalesUK, for all objects in the Sales schema in the AdventureWorks2012 database.

USE master ;  
GO  
-- Create the server audit.
-- Change the path to a path that the SQLServer Service has access to. 
CREATE SERVER AUDIT DataModification_Security_Audit  
    TO FILE ( FILEPATH = 
'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA' ) ; 
GO  
-- Enable the server audit.  
ALTER SERVER AUDIT DataModification_Security_Audit   
WITH (STATE = ON) ;  
GO  
-- Move to the target database.  
USE AdventureWorks2012 ;  
GO  
CREATE ROLE SalesUK
GO
-- Create the database audit specification.  
CREATE DATABASE AUDIT SPECIFICATION Audit_Data_Modification_On_All_Sales_Tables  
FOR SERVER AUDIT DataModification_Security_Audit  
ADD ( INSERT, UPDATE, DELETE  
     ON Schema::Sales BY SalesUK )  
WITH (STATE = ON) ;    
GO  

另请参阅See Also

CREATE SERVER AUDIT (Transact-SQL) CREATE SERVER AUDIT (Transact-SQL)
ALTER SERVER AUDIT (Transact-SQL) ALTER SERVER AUDIT (Transact-SQL)
DROP SERVER AUDIT (Transact-SQL) DROP SERVER AUDIT (Transact-SQL)
CREATE SERVER AUDIT SPECIFICATION (Transact-SQL) CREATE SERVER AUDIT SPECIFICATION (Transact-SQL)
ALTER SERVER AUDIT SPECIFICATION (Transact-SQL) ALTER SERVER AUDIT SPECIFICATION (Transact-SQL)
DROP SERVER AUDIT SPECIFICATION (Transact-SQL) DROP SERVER AUDIT SPECIFICATION (Transact-SQL)
CREATE DATABASE AUDIT SPECIFICATION (Transact-SQL) CREATE DATABASE AUDIT SPECIFICATION (Transact-SQL)
ALTER DATABASE AUDIT SPECIFICATION (Transact-SQL) ALTER DATABASE AUDIT SPECIFICATION (Transact-SQL)
DROP DATABASE AUDIT SPECIFICATION (Transact-SQL) DROP DATABASE AUDIT SPECIFICATION (Transact-SQL)
ALTER AUTHORIZATION (Transact-SQL) ALTER AUTHORIZATION (Transact-SQL)
sys.fn_get_audit_file (Transact-SQL) sys.fn_get_audit_file (Transact-SQL)
sys.server_audits (Transact-SQL) sys.server_audits (Transact-SQL)
sys.server_file_audits (Transact-SQL) sys.server_file_audits (Transact-SQL)
sys.server_audit_specifications (Transact-SQL) sys.server_audit_specifications (Transact-SQL)
sys.server_audit_specification_details (Transact-SQL) sys.server_audit_specification_details (Transact-SQL)
sys.database_audit_specifications (Transact-SQL) sys.database_audit_specifications (Transact-SQL)
sys.database_audit_specification_details (Transact-SQL) sys.database_audit_specification_details (Transact-SQL)
sys.dm_server_audit_status (Transact-SQL) sys.dm_server_audit_status (Transact-SQL)
sys.dm_audit_actions (Transact-SQL) sys.dm_audit_actions (Transact-SQL)
创建服务器审核和服务器审核规范Create a Server Audit and Server Audit Specification