AUDIT TRONG SQL SERVER 2012

AUDIT TRONG SQL SERVER 2012

ữ ướ ề ả ộ ố ả ế ề ẽ ạ ả ấ ớ ồ ấ Ngoài nh ng tính năng v Audit đã có trong các phiên b n tr c, SQL Server 2012 cung c p thêm r t nhi u tính năng m i, trong đó bao g m m t s c i ti n m nh m cho kh năng giám sát (audit) SQL Server.

t này xin khái quát l i t ầ ạ ấ ả ế ữ t c nh ng tính năng v audit có trong nh ng ề Ph n đ u c a bài vi ủ phiên b n SQL Server tr ữ c đây, bao g m các tính năng: ầ ả ướ ồ

• Trigger • • Change Data Capture (CDC) • Change Tracking (CT)

SQL Server Audit

1. Trigger

ng (object) trong database cho phép ch y th t c l u tr (stored procedure) ố ượ ủ ụ ư ữ ạ Trigger là 1 đ i t nào đó m i khi có 1 thay đ i x y ra. Có 2 lo i Trigger: ổ ả ạ ỗ

• DDL Trigger: áp d ng cho nh ng thay đ i liên quan đ n c u trúc c a database, nh : ư ổ ALTER DATABASE, CREATE TABLE, … ụ

• DML Trigger: áp d ng cho nh ng thay đ i liên quan tr c ti p đ n d li u, nh : ư

ụ ữ ủ ế ấ

ữ ệ ữ ự ế ế ổ UPDATE, INSERT, DELETE.

• Đ ngăn không cho thay đ i c u trúc c a 1 Table nào đó (DDL Trigger cho ALTER ủ

Trigger có th đ ể ượ ử ụ c s d ng v i nhi u m c đích khác nhau, ví d nh : ụ ư ụ ề ớ

ổ ấ ể

ạ ạ ị ướ ớ c khi chèn vào 1 Table nào đó (DML Trigger cho Insert v i i giá tr tr ch đ Instead ho c After) ặ i nh ng thay đ i v m c c u trúc hay d li u c a 1 Table (DDL Trigger ữ ổ ề ặ ữ ệ ủ ấ ạ ho c DML Trigger) TABLE) • Đ đ nh d ng l ể ị ế ộ • Đ log l ể ặ

• L u l

Các Trigger có th đ i nh ng thay đ i đó ể ượ c cài đ t đ log l ặ ể ạ ữ ổ ở nhi u n i: ề ơ

•

ổ ữ ư ạ ề ặ ộ i nh ng thay đ i trong 1 ho c nhi u table, thu c 1 ho c nhi u database khác ủ ề ặ ử ụ ư ử ế ặ nhau, c a 1 ho c nhi u server khác nhau (s d ng Linked Server). S d ng Service Broker g i message đ n nhi u n i khác nhau, nh : file trong server, event trong Event Viewer, hay email đ n ng ườ ề ử ụ ơ ề i qu n tr . ị ả ế

t c các s ki n liên quan đ n các câu l nh DDL trong ụ ắ ấ ả ự ệ ế ệ ở Ví d sau đây s t o 1 Trigger b t t ẽ ạ database Test và l u vào trong table DDL_Log. ư

USE Test GO

ạ

ả

-- t o b ng DDL_Log CREATE TABLE DDL_Log

( PostTime datetime, DB_User nvarchar (100), Event nvarchar (100), TSQL nvarchar (2000) ) GO

ạ

-- t o Trigger CREATE TRIGGER myDDLTrigger ON DATABASE FOR DDL_DATABASE_LEVEL_EVENTS AS

DECLARE @data XML SET @data = EVENTDATA() INSERT DDL_Log (PostTime, DB_User, Event, TSQL) VALUES ( GETDATE(), CONVERT(nvarchar(100), CURRENT_USER), @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'), @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)') ) ;

GO

2. SQL Server Audit

phiên b n SQL Server 2008 ả ả ơ ệ ế ả Enterprise. Tính năng này đ n gi n hoá SQL Server Audit có t ừ kh năng giám sát t đ ng (automatic audit) và có th thay th cho vi c cài đ t các Trigger. ể ự ộ SQL Server Audit có th c u hình giám sát ặ 2 m c (level): instance và database. ể ấ ứ ở

Các thành ph n c a SQL Server Audit: ầ ủ

• Audit Object: Trong m i Instance chúng ta có th t o ra nhi u audit khác nhau. ng Ứ c giám

•

ỗ i nh ng thông tin đ ỗ ả ẽ ể ạ ể ề ữ ượ ạ ỉ

• Database Audit Specification: quy đ nh cho 1 Audit c th nào đó 1 t p các ị

ụ ể ậ ị hành đ ngộ , ví d : CREATE LOGIN, ALTER DATABASE, … . Chúng ầ v i m i Audit s ph i ch ra 1 audit destination đ ghi l ớ sát. Server Audit Specification: quy đ nh cho 1 Audit c th nào đó 1 t p các c a Instance c n giám sát ụ ủ ta có th t o 1 server audit specification cho m i SQL Server Audit. ể ạ ỗ ụ ể ậ hành , ví d : CREATE TABLE, ALTER VIEW, … . ộ ủ ầ ụ ỗ ể ạ • Target: chính là Audit destination đ c ch ra trong m i Audit. Target có th là 1 file, 1 đ ng c a database object c n giám sát Chúng ta có th t o 1 database audit specification cho m i SQL Server Audit. ể ượ ỗ ỉ Windows Security event log, hay 1 Windows Application event log.

i thông tin m i khi có 1 connection nào đó login b fail ụ ể ạ ỗ ị ư ữ ề Ví d sau đây s t o 1 Audit đ log l ẽ ạ (do nhi u nguyên nhân khác nhau: không đúng username, sai password, …) và l u nh ng thông tin đó trong Windows Application log.

USE master ; GO

ạ

--T o Audit CREATE SERVER AUDIT mySQLServerAudit TO APPLICATION_LOG WITH ( QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE); GO

ạ

--T o Server Audit Specification và gán cho Audit CREATE SERVER AUDIT SPECIFICATION FailedLoginServerAuditSpecification FOR SERVER AUDIT mySQLServerAudit ADD (FAILED_LOGIN_GROUP);

--Enable audit ALTER SERVER AUDIT mySQLServerAudit WITH (STATE = ON); GO

3. Change Data Capture (CDC)

ừ ấ ả ả i d u v t c a nh ng d phiên b n SQL Server 2008 đ đ n gi n hoá và ữ ể ơ ế ủ ệ ư ạ ấ ắ ầ ế ữ ể ặ Microsoft b t đ u cung c p tính năng CDC t có th thay th cho vi c cài đ t các DML Trigger trong vi c l u l ệ li u đã b thay đ i. ị ệ ổ

t c các thay đ i (bao g m Insert, Update, i t ổ ồ Sau khi b t và c u hình tính năng, CDC s ghi l ẽ Delete) v d li u trên 1 table và l u tr nh ng thay đ i đó trong các System table. ữ ữ ạ ấ ả ổ ậ ấ ề ữ ệ ư

• Update mask: ch ra l nh DML tác đ ng lên d li u, trong đó: 1 = Delete, 2 = Insert, 3 =

CDC có th l u l i 1 s thông tin nh sau: ể ư ạ ư ố

ữ ệ ệ ộ ỉ Before Update, 4 = After Update

• Các d li u b thay đ i ổ ị • Th i đi m d li u b thay đ i ổ ị ữ ệ • …

ữ ệ ể ờ

ự ổ i qu n tr có th d dàng ki m tra, theo dõi nh ng thay đ i ể ễ ườ ữ ể ả ị D a vào nh ng thông tin trên, ng này thông qua các Stored Procedure ho c xem tr c ti p các system table do CDC t o ra. ự ế ữ ạ ặ

ậ

ụ ẽ ậ ữ ổ Ví d sau đây s b t tính năng CDC cho table Test và xem nh ng thay đ i đó thông qua vi c ệ truy v n stored procedure hay system table. ấ

--b t tính năng CDC cho database EXEC sys.sp_cdc_enable_db GO

ậ

--b t tính năng CDC cho table Test EXEC sys.sp_cdc_enable_table N'dbo', N'Test',DEFAULT,DEFAULT, 1 GO

ố ệ

ự

ệ

/* th c hi n 1 s l nh Insert, Update, Delete */

ằ

ấ

-- xem thông tin b ng cách truy v n stored procedure SELECT * FROM cdc.fn_cdc_get_all_changes_dbo_TestTable (sys.fn_cdc_get_min_lsn('dbo_Test'), sys.fn_cdc_get_max_lsn(),N'all')

ừ

ự

ế

--xem thông tin tr c ti p t system table SELECT * FROM cdc.dbo_Test_CT

4. Change Tracking (CT)

c nh ng thay đ i ữ ượ t c a CT so v i CDC là CT không l u l ớ ạ ổ ỗ ổ trong các table ệ i các d li u hi n ữ ệ ư ạ ữ ủ i 1 s thông tin c b n nh Primary key c a dòng d ư ơ ả Change Tracking cho phép các ng d ng theo dõi đ ứ ụ c aủ ngứ d ngụ đó. Đi m khác bi ể ệ ủ đ i mà ch l u hành m i khi có thay ỉ ư l li uệ có thay đ i, s l n thay đ i c a dòng d li u đó, … ổ ủ ố ữ ệ ổ ố ầ

ậ

Ph i enable tính năng Change Tracking cho t ng table mà b n mu n theo dõi ừ ả ạ ố

-- b t tính năng Change Tracking cho database ALTER DATABASE AdventureWorks2012 SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON);

ậ

--b t tính năng Change Tracking cho table ALTER TABLE Test ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON);

ệ

ể ử ụ ể ậ ặ ố Sau khi b t Change Tracking, chúng ta có th s d ng 1 s view ho c function đ xem thông tin:

ậ --li t kê các database trong instance có b t tính năng Change Tracking SELECT * FROM sys.change_tracking_databases

ệ

ạ

ậ

ệ --li t kê các table trong database hi n t i có b t tính năng Change Tracking SELECT * FROM sys.change_tracking_tables

ả

ấ

ệ

ệ

ậ tính năng Change

ứ

ề

ơ

ớ

ữ /*li t kê t t c các dòng d li u trong table Test (đã b t Tracking) cùng v i các thông tin v version tu ng ng */ SELECT t.*, ct.* FROM Test t CROSS APPLY CHANGETABLE ( VERSION Test, (id), t.id ) AS ct;

i qu n tr có đ kh năng đ giám sát 1 cách chi ti ớ ủ ả ể ế ữ t nh ng V i nh ng tính năng trên, ng ườ ả ị thay đ i v c u trúc cũng nh v d li u trong các database c a mình . ư ề ữ ệ ữ ổ ề ấ ủ

V y trong SQL Server 2012 s có thêm nh ng c i ti n gì? ẽ ả ế ữ ậ

ậ ữ ệ ả ế ề ẽ ỏ ả ị V y trong SQL Server 2012, s có thêm nh ng c i ti n gì trong vi c Audit nói chung hay SQL Server Audit nói riêng? Câu h i này đ ẽ c làm rõ thông qua các tính năng đ đ ượ i qu n tr database quan tâm và s c r t nhi u ng ườ c đ c p trong Ph n 2 này, bao g m: ầ ượ ấ ượ ề ậ ồ

• Kh năng t o • Kh năng l c Audit ( • H tr t

Audit Filtering) đ ng khôi ph c log ( Audit resilience) ạ User-defined Audit ả ả ọ ỗ ợ ự ộ ụ

c ỉ ượ cung c p trong b n ấ ả Enterprise nh ư ở ả ủ ả Thêm vào đó, thay vì ch đ 2008 thì nay SQL Server Audit đ Server 2012 v i t c đ đ ớ ố ộ ượ ả cượ tích h p trong t ợ ệ c c i thi n 1 cách đáng k cùng nhi u tính năng h n. ể phiên b n SQL Server t c các b n edition c a SQL ơ ấ ả ề

1. User-Defined Audit

ứ ự ạ ự ệ ủ ủ ụ ạ ơ ế ụ ư ượ User-Defined Audit cho phép các ng d ng t tuỳ bi n, t o ra các s ki n c a riêng mình và ghi thông tin audit log 1 cách linh ho t h n, ví d nh : ghi vào audit log tên c a các user đăng nh p vào application thay cho tên login chung đ ể ứ c khai báo trong connection string đ ng ậ d ng k t n i đ n database. ụ ế ố ế

ể ự ệ ằ ư ậ ự ả c, tr ệ ử ụ đ ượ ẽ d ng đ ụ ki n s ệ s ử ư ượ ướ ể Chúng ta có th add thêm các s ki n (audit event) nh v y b ng vi c s d ng th t c (stored ủ ụ c l u trong group procedure) sp_audit_write. Khi đó t USER_DEFINED_AUDIT_GROUP. L u ý r ng đ c đó USER_DEFINED_AUDIT_GROUP ph i đ các s ằ c enable. t c ấ ư ả ượ

Ví d sau s ghi vào audit log 1 s ki n v i id = 141 cùng 1 s thông tin tuỳ thích. ự ệ ụ ẽ ớ ố

EXEC sp_audit_write @user_defined_event_id = 141, @succeeded = 0, @user_defined_information = N'My information' ;

c thêm vào sys.server_audits, sys.server_file_audits, ộ ố ộ ượ M t s c t m i (column) đ ớ và sys.fn_get_audit_file đ theo dõi các user-defined audit event.. ể

2. Audit Filtering

ọ ự ệ ữ c đây, th t không d dàng đ ch ễ i qu n tr đang quan tâm. Đi u này đã đ ả ể ỉ l c (theo 1 đi u ki n c th nào đó) nh ng s ki n mà ệ ụ ể c kh c ph c trong phiên b n SQL Server 2012 ụ ề ề ắ ượ ả Tr ậ ướ ng ị ườ v i tính năng Audit Filtering. ớ

ự ệ ầ ỗ ợ ướ ữ ả ọ đây, SQL Server Audit h tr kh năng l c nh ng s ki n c n audit tr c ghi vào audit log thông qua m nh đ c khi chúng ề WHERE trong câu l nhệ CREATE SERVER Gi ờ đ ệ ượ AUDIT và ALTER SERVER AUDIT.

2.1. CREATE SERVER AUDIT (Transact-SQL)

http://msdn.microsoft.com/en-us/library/cc280448%28v=sql.110%29.aspx

Syntax

CREATE SERVER AUDIT audit_name { TO { [ FILE ( [ , ...n ] ) ] | APPLICATION_LOG | SECURITY_LOG } [ WITH ( [ , ...n ] ) ]

[ WHERE ] } [ ; ]

::= { FILEPATH = 'os_file_path' [ , MAXSIZE = { max_size { MB | GB | TB } | UNLIMITED } ] [ , { MAX_ROLLOVER_FILES = { integer | UNLIMITED } } | { MAX_FILES = integer } ] [ , RESERVE_DISK_SPACE = { ON | OFF } ] }

::= { [ QUEUE_DELAY = integer ] [ , ON_FAILURE = { CONTINUE | SHUTDOWN | FAIL_OPERATION } ] [ , AUDIT_GUID = uniqueidentifier ] }

::= { [NOT ] [ { AND | OR } [NOT ] { } ] [,...n ] }

::= event_field_name { = | < > | ! = | > | > = | < | < = } { number | ' string ' }

Arguments TO { FILE | APPLICATION_LOG | SECURITY_LOG }

Determines the location of the audit target. The options are a binary file, The Windows Application log, or the Windows Security log. SQL Server cannot write to the Windows Security log without configuring additional settings in Windows. For more information, see Write SQL Server Audit Events to the Security Log.

FILEPATH ='os_file_path'

The path of the audit log. The file name is generated based on the audit name and audit GUID.

MAXSIZE = { max_size }

Specifies the maximum size to which the audit file can grow. The max_size value must be an integer followed by MB, GB, TB, or UNLIMITED. The minimum size that you can specify for max_size is 2 MB and the maximum is 2,147,483,647 TB. When UNLIMITED is specified, the file grows until the disk is full. Specifying a value lower than 2 MB will raise the error MSG_MAXSIZE_TOO_SMALL. The default value is UNLIMITED.

MAX_ROLLOVER_FILES ={ integer | UNLIMITED }

Specifies the maximum number of files to retain in the file system in addition to the current file. The MAX_ROLLOVER_FILES value must be an integer or UNLIMITED. The default value is UNLIMITED. This parameter is evaluated whenever the audit restarts (which can happen when the instance of the Database Engine restarts or when the audit is turned off and then on again) or when a new file is needed because the MAXSIZE has been reached. When MAX_ROLLOVER_FILES is evaluated, if the number of files exceeds the MAX_ROLLOVER_FILES setting, the oldest file is deleted. As a result, when the setting of MAX_ROLLOVER_FILES is 0 a new file is created each time the MAX_ROLLOVER_FILES setting is evaluated. Only one file is automatically deleted when MAX_ROLLOVER_FILES setting is evaluated, so when the value of MAX_ROLLOVER_FILES is decreased, the number of files will not shrink unless old files are manually deleted. The maximum number of files that can be specified is 2,147,483,647.

MAX_FILES =integer

Specifies the maximum number of audit files that can be created. Does not rollover to the first file when the limit is reached. When the MAX_FILES limit is reached, any action that causes additional audit events to be generated will fail with an error.

RESERVE_DISK_SPACE = { ON | OFF }

This option pre-allocates the file on the disk to the MAXSIZE value. It applies only if MAXSIZE is not equal to UNLIMITED. The default value is OFF.

QUEUE_DELAY =integer

Determines the time, in milliseconds, that can elapse before audit actions are forced to be processed. A value of 0 indicates synchronous delivery. The minimum settable query delay value is 1000 (1 second), which is the default. The maximum is 2,147,483,647 (2,147,483.647 seconds or 24 days, 20 hours, 31 minutes, 23.647 seconds). Specifying an invalid number will raise the error MSG_INVALID_QUEUE_DELAY.

ON_FAILURE = { CONTINUE | SHUTDOWN | FAIL_OPERATION }

Indicates whether the instance writing to the target should fail, continue, or stop SQL Server if the target cannot write to the audit log. The default value is CONTINUE.

CONTINUE

SQL Server operations continue. Audit records are not retained. The audit continues to attempt to log events and will resume if the failure condition is resolved. Selecting the continue option can allow unaudited activity which could violate your security policies. Use this option, when continuing operation of the Database Engine is more important than maintaining a complete audit.

SHUTDOWN

Forces a server shut down when the server instance writing to the target cannot write data to the audit target. The login issuing this must have the SHUTDOWN permission. If the logon does not

have this permission, this function will fail and an error message will be raised. No audited events occur. Use the option when an audit failure could compromise the security or integrity of the system.

FAIL_OPERATION

Database actions fail if they cause audited events. Actions which do not cause audited events can continue, but no audited events can occur. The audit continues to attempt to log events and will resume if the failure condition is resolved. Use this option when maintaining a complete audit is more important than full access to the Database Engine.

AUDIT_GUID =uniqueidentifier

To support scenarios such as database mirroring, an audit needs a specific GUID that matches the GUID found in the mirrored database. The GUID cannot be modified after the audit has been created.

predicate_expression

Specifies the predicate expression used to determine if an event should be processed or not. Predicate expressions are limited to 3000 characters, which limits string arguments.

event_field_name

Is the name of the event field that identifies the predicate source. Audit fields are described in sys.fn_get_audit_file (Transact-SQL). All fields can be audited except file_name and audit_file_offset.

number

Is any numeric type including decimal. Limitations are the lack of available physical memory or a number that is too large to be represented as a 64-bit integer.

' string '

Either an ANSI or Unicode string as required by the predicate compare. No implicit string type conversion is performed for the predicate compare functions. Passing the wrong type results in an error.

Examples

A. Creating a server audit with a file target

The following example creates a server audit called HIPPA_Audit with a binary file as the target and no options.

CREATE SERVER AUDIT HIPAA_Audit TO FILE ( FILEPATH ='\\SQLPROD_1\Audit\' );

B. Creating a server audit with a Windows Application log target with options

The following example creates a server audit called HIPPA_Audit with the target set for the Windows Application log. The queue is written every second and shuts down the SQL Server engine on failure.

CREATE SERVER AUDIT HIPAA_Audit TO APPLICATION_LOG WITH ( QUEUE_DELAY = 1000, ON_FAILURE = SHUTDOWN);

C. Creating a server audit containing a WHERE clause

The following example creates a database, schema, and two tables for the example. The table named DataSchema.SensitiveData will contain confidential data and access to the table must be recorded in the audit. The table named DataSchema.GeneralData does not contain confidential data. The database audit specification audits access to all objects in the DataSchema schema. The server audit is created with a WHERE clause that limits the server audit to only the SensitiveData table. The server audit presumes a audit folder exists at C:\SQLAudit.

Transact-SQL

CREATE DATABASE TestDB; GO USE TestDB; GO CREATE SCHEMA DataSchema; GO CREATE TABLE DataSchema.GeneralData (ID int PRIMARY KEY, DataField varchar(50) NOT NULL); GO CREATE TABLE DataSchema.SensitiveData (ID int PRIMARY KEY, DataField varchar(50) NOT NULL); GO -- Create the server audit in the master database USE master; GO CREATE SERVER AUDIT AuditDataAccess TO FILE ( FILEPATH ='C:\SQLAudit\' ) WHERE object_name = 'SensitiveData' ; GO ALTER SERVER AUDIT AuditDataAccess WITH (STATE = ON); GO -- Create the database audit specification in the TestDB database USE TestDB; GO CREATE DATABASE AUDIT SPECIFICATION [FilterForSensitiveData] FOR SERVER AUDIT [AuditDataAccess] ADD (SELECT ON SCHEMA::[DataSchema] BY [public]) WITH (STATE = ON); GO -- Trigger the audit event by selecting from tables SELECT ID, DataField FROM DataSchema.GeneralData; SELECT ID, DataField FROM DataSchema.SensitiveData; GO -- Check the audit for the filtered content SELECT * FROM fn_get_audit_file('C:\SQLAudit\AuditDataAccess_*.sqlaudit',default,default); GO

2.2. ALTER SERVER AUDIT (Transact-SQL) http://msdn.microsoft.com/en-us/library/cc280563%28v=sql.110%29.aspx

Syntax

ALTER SERVER AUDIT audit_name { [ TO { { FILE ( [, ...n] ) } | APPLICATION_LOG | SECURITY_LOG } ] [ WITH ( [ , ...n] ) ] [ WHERE ] } | REMOVE WHERE | MODIFY NAME = new_audit_name [ ; ]

::= { FILEPATH = 'os_file_path' | MAXSIZE = { max_size { MB | GB | TB } | UNLIMITED } | MAX_ROLLOVER_FILES = { integer | UNLIMITED } | MAX_FILES = integer | RESERVE_DISK_SPACE = { ON | OFF } }

::= { QUEUE_DELAY = integer | ON_FAILURE = { CONTINUE | SHUTDOWN | FAIL_OPERATION } | STATE = = { ON | OFF } }

::= { [NOT ] [ { AND | OR } [NOT ] { } ] [,...n ] }

::= event_field_name { = | < > | ! = | > | > = | < | < = } { number | ' string ' } Arguments TO { FILE | APPLICATION_LOG | SECURITY }

Determines the location of the audit target. The options are a binary file, the Windows application log, or the Windows security log.

FILEPATH = 'os_file_path'

The path of the audit trail. The file name is generated based on the audit name and audit GUID.

MAXSIZE =max_size

Specifies the maximum size to which the audit file can grow. The max_size value must be an integer followed by MB, GB, TB, or UNLIMITED. The minimum size that you can specify for max_size is 2 MB and the maximum is 2,147,483,647 TB. When UNLIMITED is specified the file grows until the disk is full. Specifying a value lower than 2 MB will raise the error MSG_MAXSIZE_TOO_SMALL. The default value is UNLIMITED.

MAX_ROLLOVER_FILES =integer | UNLIMITED

Specifies the maximum number of files to retain in the file system. When the setting of MAX_ROLLOVER_FILES=0 there is no limit imposed on the number of rollover files that will be created. The default value is 0. The maximum number of files that can be specified is 2,147,483,647.

MAX_FILES =integer

Specifies the maximum number of audit files that can be created. Does not rollover to the first file when the limit is reached. When the MAX_FILES limit is reached, any action that causes additional audit events to be generated will fail with an error.

RESERVE_DISK_SPACE = { ON | OFF }

This option pre-allocates the file on the disk to the MAXSIZE value. Only applies if MAXSIZE is not equal to UNLIMITED. The default value is OFF.

QUEUE_DELAY =integer

Determines the time in milliseconds that can elapse before audit actions are forced to be processed. A value of 0 indicates synchronous delivery. The minimum settable query delay value is 1000 (1 second), which is the default. The maximum is 2,147,483,647 (2,147,483.647 seconds or 24 days, 20 hours, 31 minutes, 23.647 seconds). Specifying an invalid number will raise the error MSG_INVALID_QUEUE_DELAY.

ON_FAILURE = { CONTINUE | SHUTDOWN | FAIL_OPERATION}

Indicates whether the instance writing to the target should fail, continue, or stop if SQL Server cannot write to the audit log.

CONTINUE

SQL Server operations continue. Audit records are not retained. The audit continues to attempt to log events and will resume if the failure condition is resolved. Selecting the continue option can allow unaudited activity which could violate your security policies. Use this option, when continuing operation of the Database Engine is more important than maintaining a complete audit.

SHUTDOWN

Forces a server shut down when the server instance writing to the target cannot write data to the audit target. The login issuing this must have the SHUTDOWN permission. If the logon does not have this permission, this function will fail and an error message will be raised. No audited events occur. Use the option when an audit failure could compromise the security or integrity of the system.

FAIL_OPERATION

Database actions fail if they cause audited events. Actions which do not cause audited events can continue, but no audited events can occur. The audit continues to attempt to log events and will resume if the failure

condition is resolved. Use this option when maintaining a complete audit is more important than full access to the Database Engine.

STATE = { ON | OFF }

Enables or disables the audit from collecting records. Changing the state of a running audit (from ON to OFF) creates an audit entry that the audit was stopped, the principal that stopped the audit, and the time the audit was stopped.

MODIFY NAME = new_audit_name

Changes the name of the audit. Cannot be used with any other option.

predicate_expression

Specifies the predicate expression used to determine if an event should be processed or not. Predicate expressions are limited to 3000 characters, which limits string arguments.

event_field_name

Is the name of the event field that identifies the predicate source. Audit fields are described in sys.fn_get_audit_file (Transact-SQL). All fields can be audited except file_name and audit_file_offset.

number

Is any numeric type including decimal. Limitations are the lack of available physical memory or a number that is too large to be represented as a 64-bit integer.

' string '

Either an ANSI or Unicode string as required by the predicate compare. No implicit string type conversion is performed for the predicate compare functions. Passing the wrong type results in an error.

Examples

A. Changing a server audit name

The following example changes the name of the server audit HIPPA_Audit to HIPAA_Audit_Old.

USE master GO ALTER SERVER AUDIT HIPAA_Audit WITH (STATE = OFF); GO ALTER SERVER AUDIT HIPAA_Audit MODIFY NAME = HIPAA_Audit_Old; GO ALTER SERVER AUDIT HIPAA_Audit_Old WITH (STATE = ON); GO

B. Changing a server audit target

The following example changes the server audit called HIPPA_Audit to a file target.

USE master GO ALTER SERVER AUDIT HIPAA_Audit WITH (STATE = OFF); GO ALTER SERVER AUDIT HIPAA_Audit TO FILE (FILEPATH ='\\SQLPROD_1\Audit\', MAXSIZE = 1000 MB, RESERVE_DISK_SPACE=OFF) WITH (QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE); GO ALTER SERVER AUDIT HIPAA_Audit WITH (STATE = ON); GO

C. Changing a server audit WHERE clause

The following example modifies the where clause created in example C of CREATE SERVER AUDIT (Transact-SQL). The new WHERE clause filters for the user defined event if of 27.

Transact-SQL

ALTER SERVER AUDIT [FilterForSensitiveData] WITH (STATE = OFF) GO ALTER SERVER AUDIT [FilterForSensitiveData] WHERE user_defined_event_id = 27; GO ALTER SERVER AUDIT [FilterForSensitiveData] WITH (STATE = ON); GO

D. Removing a WHERE clause

The following example removes a WHERE clause predicate expression.

Transact-SQL

ALTER SERVER AUDIT [FilterForSensitiveData] WITH (STATE = OFF) GO ALTER SERVER AUDIT [FilterForSensitiveData] REMOVE WHERE; GO ALTER SERVER AUDIT [FilterForSensitiveData] WITH (STATE = ON); GO

E. Renaming a server audit

The following example changes the server audit name from FilterForSensitiveData to AuditDataAccess.

Transact-SQL

ALTER SERVER AUDIT [FilterForSensitiveData] WITH (STATE = OFF) GO ALTER SERVER AUDIT [FilterForSensitiveData] MODIFY NAME = AuditDataAccess; GO

ALTER SERVER AUDIT [AuditDataAccess] WITH (STATE = ON); GO

Ví d sau s t o 1 Server Audit ch l u vào audit log nh ng s ki n có id = 141 ự ệ ẽ ạ ỉ ư ụ ữ

CREATE SERVER AUDIT [MyFilteredAuditEvent] WHERE user_defined_event_id = 141; GO

3. Audit Resilience

ế ự ả ồ ủ ề ấ ữ ệ ả ệ ị ỗ ể ư ỗ i i m ng, … Audit Resilience cung c p kh năng tuỳ bi n s ph n h i c a SQL Server cũng nh gi m ư ả ấ thi u m t d li u audit khi vi c ghi audit log b l i do nhi u nguyên nhân khác nhau nh l ghi d li u, l ữ ệ ạ ỗ

Có 2 option m i đ h tr tính năng này và s đ c c u hình m i khi t o 1 Audit: ớ ể ỗ ợ ẽ ượ ấ ạ ỗ

• On Audit Log Failure: s có 3 l a ch n khác nhau quy đ nh ph n ng c a SQL Server khi không th ghi Audit log: Continue, Shutdown Server, ho c Fail operation. Tính năng ể m i h tr c ch có 1 l a ch n duy nh t là phiên b n tr này r t quan tr ng vì ấ shutdown server hay không mà thôi.

• Maximum Rollover Files: Tr

ả ứ ự ủ ẽ ọ ị ặ ớ ỗ ợ ướ ự ấ ả ọ ở ọ ỉ

ự ể ọ ướ không gi ng log file ho c gi ng log file ch m ng ng t ỉ ố ượ ố ượ ng h p có gi ợ ố ớ ạ ể ấ ẽ ự ộ i 1 s l ượ c ị ố i h n s lu ng log ố ợ ớ ạ i đa, file log ưỡ ườ đ ng xoá đi đ có th ghi thêm file log m i h n (g i là roll-over). SQL ọ ng file log c đ nh ố ượ ặ ạ ớ ơ l ữ ạ ố ị ự dùng đ l u audit log: ể ư file. Trong tr cũ nh t s t Server 2012 cung c p thêm 1 l a ch n n a cho phép gi ấ ng roll-over. mà không b m t thông tin audit vì hi n t c đây ch có 2 l a ch n đ quy đ nh s file log đ i h n s l ớ ạ i h n, khi s l ể ọ ữ ệ ượ ị ấ

ữ ệ ấ ả Trên đây là 1 s c i ti n trong SQL Server 2012 nh m cung c p kh năng Audit d li u 1 ố ả ế cách linh ho t và h u ích cho nh ng ng ữ ằ i qu n tr . ị ả ườ ữ ạ