Filter SQL Server Audit on action_id / class_type predicate

Published Mar 23 2019 12:57 PM 1,600 Views
Microsoft
First published on MSDN on Oct 03, 2012

In SQL Server 2012, Server Audit can be created with a predicate expression (refer to MSDN ). This predicate expression is evaluated before audit events are written to the audit target. If the evaluation returns TRUE the event is written to the audit target else it's not. Hence one can filter audit records going to the audit target based on the predicate expression.


Predicate can refer to any of the audit fields described in sys.fn_get_audit_file (Transact-SQL) except file_name and audit_file_offset.


For example:


Consider a server principal ‘foo’ that already exists in SQL Server. This principal has server_principal_id of 261. Now following server audit will write all the audit events (configured in audit specification) generated by this principal (with id 261) to file target. It will not write audit events generated by other principals in SQL Server to the target.


CREATE SERVER AUDIT AuditDataAccessByPrincipal


TO FILE (FILEPATH ='C:\SQLAudit\' )


WHERE SERVER_PRINCIPAL_ID = 261


GO


Now, in order to use action_id field as a predicate in the predicate expression, one has to provide integer value of action_id. Specifying a character code value for action_id results in following error:


CREATE SERVER AUDIT AuditDataAccessByAction_Id


TO FILE ( FILEPATH ='C:\SQLAudit\' )


WHERE ACTION_ID = 'SL'


GO


Error:


Msg 25713, Level 16, State 23, Line 1


The value specified for event attribute or predicate source, "ACTION_ID", event, "audit_event", is invalid.



This is because internally action_id is stored as an integer value. sys.fn_get_audit_file DMV converts the integer value to a character code value for two main reasons:


1)      Readability: Character code is more readable then integer value


2)      Consistency with our internal metadata layer where we define such mapping between integer value and character code.


The above explanation also applies for class_type field that we have in sys.fn_get_audit_file.


Following functions will help to get around above mentioned problem with action_id and class_type fields.


1)       This function converts action_id string value of varchar(4) to an integer value which can be used in the predicate expression.


create function dbo.GetInt_action_id ( @action_id varchar(4)) returns int


begin


declare @x int


SET @x = convert(int, convert(varbinary(1), upper(substring(@action_id, 1, 1))))


if LEN(@action_id)>=2


SET @x = convert(int, convert(varbinary(1), upper(substring(@action_id, 2, 1)))) * power(2,8) + @x


else


SET @x = convert(int, convert(varbinary(1), ' ')) * power(2,8) + @x


if LEN(@action_id)>=3


SET @x = convert(int, convert(varbinary(1), upper(substring(@action_id, 3, 1)))) * power(2,16) + @x


else


SET @x = convert(int, convert(varbinary(1), ' ')) * power(2,16) + @x


if LEN(@action_id)>=4


SET @x = convert(int, convert(varbinary(1), upper(substring(@action_id, 4, 1)))) * power(2,24) + @x


else


SET @x = convert(int, convert(varbinary(1), ' ')) * power(2,24) + @x


return @x


end



Select dbo.GetInt_action_id ('SL') as Int_Action_Id


Int_Action_Id


------------------


538987603


Following command will now succeed.


CREATE SERVER AUDIT AuditDataAccessByAction_Id


TO FILE ( FILEPATH ='C:\SQLAudit\' )


WHERE ACTION_ID = 538987603


GO



2)      This function converts class_type string value of varchar(2) to an integer value which can be used in the predicate expression.


create function dbo.GetInt_class_type ( @class_type varchar(2)) returns int


begin


declare @x int


SET @x = convert(int, convert(varbinary(1), upper(substring(@class_type, 1, 1))))


if LEN(@class_type)>=2


SET @x = convert(int, convert(varbinary(1), upper(substring(@class_type, 2, 1)))) * power(2,8) + @x


else


SET @x = convert(int, convert(varbinary(1), ' ')) * power(2,8) + @x


return @x


end


go



Select dbo.GetInt_class_type ('A') as Int_class_type


Int_class_type


-------------


8257



Following command will now succeed.


CREATE SERVER AUDIT ClasstypeAuditDataAccess


TO FILE ( FILEPATH ='C:\SQLAudit\' )


WHERE CLASS_TYPE = 8257


GO


Following audit record will be generated for Server Audit (‘A’) class type.


ALTER SERVER AUDIT ClasstypeAuditDataAccess


WITH (STATE = ON)



...

%3CLINGO-SUB%20id%3D%22lingo-sub-384140%22%20slang%3D%22en-US%22%3EFilter%20SQL%20Server%20Audit%20on%20action_id%20%2F%20class_type%20predicate%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-384140%22%20slang%3D%22en-US%22%3E%0A%20%26lt%3Bmeta%20http-equiv%3D%22Content-Type%22%20content%3D%22text%2Fhtml%3B%20charset%3DUTF-8%22%20%2F%26gt%3B%3CSTRONG%3E%20First%20published%20on%20MSDN%20on%20Oct%2003%2C%202012%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%3CP%3EIn%20SQL%20Server%202012%2C%20Server%20Audit%20can%20be%20created%20with%20a%20predicate%20expression%20(refer%20to%20%3CA%20href%3D%22http%3A%2F%2Fmsdn.microsoft.com%2Fen-us%2Flibrary%2Fcc280448.aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%20MSDN%20%3C%2FA%3E%20).%20This%20predicate%20expression%20is%20evaluated%20before%20audit%20events%20are%20written%20to%20the%20audit%20target.%20If%20the%20evaluation%20returns%20TRUE%20the%20event%20is%20written%20to%20the%20audit%20target%20else%20it's%20not.%20Hence%20one%20can%20filter%20audit%20records%20going%20to%20the%20audit%20target%20based%20on%20the%20predicate%20expression.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EPredicate%20can%20refer%20to%20any%20of%20the%20audit%20fields%20described%20in%20%3CA%20href%3D%22http%3A%2F%2Fmsdn.microsoft.com%2Fen-us%2Flibrary%2Fcc280765.aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%20sys.fn_get_audit_file%20(Transact-SQL)%20%3C%2FA%3E%20except%20file_name%20and%20audit_file_offset.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EFor%20example%3A%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EConsider%20a%20server%20principal%20%E2%80%98foo%E2%80%99%20that%20already%20exists%20in%20SQL%20Server.%20This%20principal%20has%20server_principal_id%20of%20261.%20Now%20following%20server%20audit%20will%20write%20all%20the%20audit%20events%20(configured%20in%20audit%20specification)%20generated%20by%20this%20principal%20(with%20id%20261)%20to%20file%20target.%20It%20will%20not%20write%20audit%20events%20generated%20by%20other%20principals%20in%20SQL%20Server%20to%20the%20target.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ECREATE%20SERVER%20AUDIT%20AuditDataAccessByPrincipal%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ETO%20FILE%20(FILEPATH%20%3D'C%3A%5CSQLAudit%5C'%20)%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EWHERE%20SERVER_PRINCIPAL_ID%20%3D%20261%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EGO%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ENow%2C%20in%20order%20to%20use%20action_id%20field%20as%20a%20predicate%20in%20the%20predicate%20expression%2C%20one%20has%20to%20provide%20integer%20value%20of%20action_id.%20Specifying%20a%20character%20code%20value%20for%20action_id%20results%20in%20following%20error%3A%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ECREATE%20SERVER%20AUDIT%20AuditDataAccessByAction_Id%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ETO%20FILE%20(%20FILEPATH%20%3D'C%3A%5CSQLAudit%5C'%20)%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EWHERE%20ACTION_ID%20%3D%20'SL'%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EGO%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EError%3A%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EMsg%2025713%2C%20Level%2016%2C%20State%2023%2C%20Line%201%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EThe%20value%20specified%20for%20event%20attribute%20or%20predicate%20source%2C%20%22ACTION_ID%22%2C%20event%2C%20%22audit_event%22%2C%20is%20invalid.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EThis%20is%20because%20internally%20action_id%20is%20stored%20as%20an%20integer%20value.%20sys.fn_get_audit_file%20DMV%20converts%20the%20integer%20value%20to%20a%20character%20code%20value%20for%20two%20main%20reasons%3A%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E1)%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Readability%3A%20Character%20code%20is%20more%20readable%20then%20integer%20value%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E2)%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Consistency%20with%20our%20internal%20metadata%20layer%20where%20we%20define%20such%20mapping%20between%20integer%20value%20and%20character%20code.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EThe%20above%20explanation%20also%20applies%20for%20class_type%20field%20that%20we%20have%20in%20sys.fn_get_audit_file.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EFollowing%20functions%20will%20help%20to%20get%20around%20above%20mentioned%20problem%20with%20action_id%20and%20class_type%20fields.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E1)%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%26nbsp%3BThis%20function%20converts%20action_id%20string%20value%20of%20varchar(4)%20to%20an%20integer%20value%20which%20can%20be%20used%20in%20the%20predicate%20expression.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Ecreate%20function%20dbo.GetInt_action_id%20(%20%40action_id%20varchar(4))%20returns%20int%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Ebegin%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Edeclare%20%40x%20int%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ESET%20%40x%20%3D%20convert(int%2C%20convert(varbinary(1)%2C%20upper(substring(%40action_id%2C%201%2C%201))))%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Eif%20LEN(%40action_id)%26gt%3B%3D2%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ESET%20%40x%20%3D%20convert(int%2C%20convert(varbinary(1)%2C%20upper(substring(%40action_id%2C%202%2C%201))))%20*%20power(2%2C8)%20%2B%20%40x%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Eelse%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ESET%20%40x%20%3D%20convert(int%2C%20convert(varbinary(1)%2C%20'%20'))%20*%20power(2%2C8)%20%2B%20%40x%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Eif%20LEN(%40action_id)%26gt%3B%3D3%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ESET%20%40x%20%3D%20convert(int%2C%20convert(varbinary(1)%2C%20upper(substring(%40action_id%2C%203%2C%201))))%20*%20power(2%2C16)%20%2B%20%40x%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Eelse%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ESET%20%40x%20%3D%20convert(int%2C%20convert(varbinary(1)%2C%20'%20'))%20*%20power(2%2C16)%20%2B%20%40x%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Eif%20LEN(%40action_id)%26gt%3B%3D4%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ESET%20%40x%20%3D%20convert(int%2C%20convert(varbinary(1)%2C%20upper(substring(%40action_id%2C%204%2C%201))))%20*%20power(2%2C24)%20%2B%20%40x%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Eelse%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ESET%20%40x%20%3D%20convert(int%2C%20convert(varbinary(1)%2C%20'%20'))%20*%20power(2%2C24)%20%2B%20%40x%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Ereturn%20%40x%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Eend%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ESelect%20dbo.GetInt_action_id%20('SL')%20as%20Int_Action_Id%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EInt_Action_Id%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E------------------%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E538987603%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EFollowing%20command%20will%20now%20succeed.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ECREATE%20SERVER%20AUDIT%20AuditDataAccessByAction_Id%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ETO%20FILE%20(%20FILEPATH%20%3D'C%3A%5CSQLAudit%5C'%20)%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EWHERE%20ACTION_ID%20%3D%20538987603%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EGO%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E2)%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20This%20function%20converts%20class_type%20string%20value%20of%20varchar(2)%20to%20an%20integer%20value%20which%20can%20be%20used%20in%20the%20predicate%20expression.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Ecreate%20function%20dbo.GetInt_class_type%20(%20%40class_type%20varchar(2))%20returns%20int%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Ebegin%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Edeclare%20%40x%20int%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ESET%20%40x%20%3D%20convert(int%2C%20convert(varbinary(1)%2C%20upper(substring(%40class_type%2C%201%2C%201))))%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Eif%20LEN(%40class_type)%26gt%3B%3D2%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ESET%20%40x%20%3D%20convert(int%2C%20convert(varbinary(1)%2C%20upper(substring(%40class_type%2C%202%2C%201))))%20*%20power(2%2C8)%20%2B%20%40x%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Eelse%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ESET%20%40x%20%3D%20convert(int%2C%20convert(varbinary(1)%2C%20'%20'))%20*%20power(2%2C8)%20%2B%20%40x%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Ereturn%20%40x%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Eend%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Ego%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ESelect%20dbo.GetInt_class_type%20('A')%20as%20Int_class_type%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EInt_class_type%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E-------------%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E8257%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EFollowing%20command%20will%20now%20succeed.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ECREATE%20SERVER%20AUDIT%20ClasstypeAuditDataAccess%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ETO%20FILE%20(%20FILEPATH%20%3D'C%3A%5CSQLAudit%5C'%20)%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EWHERE%20CLASS_TYPE%20%3D%208257%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EGO%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EFollowing%20audit%20record%20will%20be%20generated%20for%20Server%20Audit%20(%E2%80%98A%E2%80%99)%20class%20type.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EALTER%20SERVER%20AUDIT%20ClasstypeAuditDataAccess%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EWITH%20(STATE%20%3D%20ON)%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F97613i418F86636409465F%22%20%2F%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E...%3C%2FP%3E%0A%20%0A%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-384140%22%20slang%3D%22en-US%22%3EFirst%20published%20on%20MSDN%20on%20Oct%2003%2C%202012%20In%20SQL%20Server%202012%2C%20Server%20Audit%20can%20be%20created%20with%20a%20predicate%20expression%20(refer%20to%20MSDN).%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-384140%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ESQLServerSecurity%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Version history
Last update:
‎Mar 23 2019 12:57 PM
Updated by: