Shulou(Shulou.com)06/01 Report--

Click (here) to collapse or open

-you need to switch to the master database before creating audit objects

-- USE [master]

-- GO

-- CREATE SERVER AUDIT MyAudit TO FILE (FILEPATH='D:\ SqlAudits')-- Files cannot be specified in the folder specified here, and all generated files will be saved in this folder.

-- GO

In fact, we can specify audit options when we create the audit object. Here is the script

-- the advantage of putting logs on disk is that you can use the new TVF: Sys.[ FN _ get_audit_file] to filter and sort audit data. It is very troublesome to store audit data in the Windows event log and query it.

USE [master]

GO

CREATE SERVER AUDIT MyAudit TO FILE (

FILEPATH='D:\ SqlAudits'

MAXSIZE=2GB

MAX_ROLLOVER_FILES=12)

WITH (

ON_FAILURE=CONTINUE

QUEUE_DELAY=1000)

ALTER SERVER AUDIT MyAudit WITH (STATE = ON)

-- MAXSIZE: indicates that the maximum size of each audit log file is 4GB

-- MAX_ROLLOVER_FILES: indicate the number of scrolling files, similar to SQL ERRORLOG, and delete the previous history files after reaching the number of files. Here are 6 files.

-- ON_FAILURE: indicates the action to take when an error occurs in the audit data. Here, the audit continues. If shutdown is specified, the entire instance will be shutdown.

-- queue_delay: indicates the delay time for audit data writing. Here, it is 1 second, and the minimum value is 1 second. If 0 is specified, it is a real-time write. Of course, there are some effects on performance.

-- STATE: indicates that the audit function is enabled. The option STATE cannot be shared with other options, so it can only be used alone.

-- when modifying audit options, you need to disable auditing before enabling auditing.

-- ALTER SERVER AUDIT MyFileAudit WITH (STATE = OFF)

-- ALTER SERVER AUDIT MyFileAudit WITH (QUEUE_DELAY = 1000)

-- ALTER SERVER AUDIT MyFileAudit WITH (STATE = ON)

USE [T_restore]

GO

CREATE DATABASE AUDIT SPECIFICATION CaptureDBActionToEventLog

FOR SERVER AUDIT MyAudit

ADD (database_object_change_group)

ADD (INSERT,UPDATE,DELETE ON schema::dbo BY PUBLIC)

WITH (STATE = ON)

-- Let's first create a sqldbaudits folder on disk D

The first operation group records the DDL statements, create,alter,drop, and so on of all objects in the database.

The second statement monitors the DML operations done by any public user (that is, all users) on any object in the dbo schema

The following sql show how to read the bitry audt file

Click (here) to collapse or open

SELECT [event_time] AS 'date and time when the audit was triggered'

Sequence_number AS 'order of records in a single audit record'

ID' for action_id AS 'operation

Succeeded AS 'whether the action that triggered the event was successful'

Permission_bitmask AS 'permission mask'

Is_column_permission AS 'whether it is a column level permission'

Session_id AS 'ID' of the session in which the event occurred

Server_principal_id AS 'login context ID' to perform the operation

Database_principal_id AS 'database user context ID' to perform the operation

Target_server_principal_id AS 'server principal performing GRANT/DENY/REVOKE operation'

Target_database_principal_id AS 'database principal performing GRANT/DENY/REVOKE operations'

Object_id AS'ID (server object, DB, database object, schema object) of the entity on which the audit occurred

Class_type AS 'type of auditable entity'

Session_server_principal_name AS 'server principal of the session'

Server_principal_name AS 'current login'

Server_principal_sid AS 'current login name SID'

Database_principal_name AS 'current user'

Target_server_principal_name AS 'target login for the operation'

Target_server_principal_sid AS 'SID' of the target login

Target_database_principal_name AS 'target user for operation'

Server_instance_name AS 'name of the audited server instance'

Database_name AS 'database context in which this operation occurred'

Schema_name AS'Architectural context for this operation'

Object_name AS 'name of the entity being audited'

Statement AS 'TSQL statement (if present)'

Additional_information AS 'unique information for a single event, returned as XML'

File_name AS 'record the path and name of the audit log file from the source'

Audit_file_offset AS 'buffer offset in the file containing audit records'

User_defined_event_id AS 'user-defined event ID' passed as a sp_audit_write parameter

User_defined_information AS 'records any additional information that the user wants to record in the audit log by using the sp_audit_write stored procedure'

FROM sys. [FN _ get_audit_file] ('D:\ SqlAudits\ MyAudit_1FE965A7-77D0-41A6-9D40-543162C722F2unknown 131447450891790000.sqlaudit`

DEFAULT, DEFAULT)

{color:red} * how to change the audit actions {color}

-- each time diable the audit before you chagne anything

{code:sql}

Use datayesdb

Go

Alter DATABASE AUDIT SPECIFICATION CaptureDBActionToEventLog with (STATE = OFF)

Use master

ALTER SERVER AUDIT MyAudit WITH (STATE = OFF)

{code}

-- switch to user db and change your audit actions

{code:Sql}

Use datayesdb

Go

Alter DATABASE AUDIT SPECIFICATION CaptureDBActionToEventLog

FOR SERVER AUDIT MyAudit

Drop (INSERT,UPDATE,DELETE ON schema::dbo BY PUBLIC)

Add (UPDATE,DELETE ON schema::dbo BY PUBLIC)

Alter DATABASE AUDIT SPECIFICATION CaptureDBActionToEventLog WITH (STATE = ON)

{code}

-- select the detail into from database_audit_specification_details for verification

{code:sql} select * from sys.database_audit_specification_details

{code}}

Database_specification_id audit_action_id audit_action_name class class_desc major_id minor_id audited_principal_id audited_result is_group

65536 MNDO DATABASE_OBJECT_CHANGE_GROUP 0 DATABASE 0 0 0 SUCCESS AND FAILURE 1

65536 DL DELETE 3 SCHEMA 1 0 0 SUCCESS AND FAILURE 0

65536 UP UPDATE 3 SCHEMA 1 0 0 SUCCESS AND FAILURE 0

(3 lines affected)

-- the following script will help you get the audit records

{code:sql}

Use datayesdb

Gp

SELECT CONVERT (datetime,SWITCHOFFSET (CONVERT (datetimeoffset, event_time), DATENAME (TzOffset, SYSDATETIMEOFFSET () AS event_time

'date and time when the audit was triggered'

Sequence_number AS 'order of records in a single audit record'

ID' for action_id AS 'operation

Succeeded AS 'whether the action that triggered the event was successful'

Permission_bitmask AS 'permission mask'

Is_column_permission AS 'whether it is a column level permission'

A.session_id AS 'ID' of the session in which the event occurred

Server_principal_id AS 'login context ID' to perform the operation

Database_principal_id AS 'database user context ID' to perform the operation

Target_server_principal_id AS 'server principal performing GRANT/DENY/REVOKE operation'

Target_database_principal_id AS 'database principal performing GRANT/DENY/REVOKE operations'

Object_id AS'ID (server object, DB, database object, schema object) of the entity on which the audit occurred

Class_type AS 'type of auditable entity'

Session_server_principal_name AS 'server principal of the session'

Server_principal_name AS 'current login'

Server_principal_sid AS 'current login name SID'

Database_principal_name AS 'current user'

Target_server_principal_name AS 'target login for the operation'

Target_server_principal_sid AS 'SID' of the target login

Target_database_principal_name AS 'target user for operation'

Server_instance_name AS 'name of the audited server instance'

Database_name AS 'database context in which this operation occurred'

Schema_name AS'Architectural context for this operation'

Object_name AS 'name of the entity being audited'

Statement AS 'TSQL statement (if present)'

Additional_information AS 'unique information for a single event, returned as XML'

File_name AS 'record the path and name of the audit log file from the source'

Audit_file_offset AS 'buffer offset in the file containing audit records'

User_defined_event_id AS 'user-defined event ID' passed as a sp_audit_write parameter

User_defined_information AS 'records any additional information that the user wants to record in the audit log by using the sp_audit_write stored procedure'

B.CLIENT_NET_ADDRESS AS 'ClientIPAddress'

FROM sys. [FN _ get_audit_file] ('D:\ SqlAudits\ * .sqlaudit'

DEFAULT, DEFAULT) a left join SYS.DM_EXEC_CONNECTIONS b

On a.session_id=b.session_id

Where event_time between

Dateadd (mi,-10final eventual time) and event_time

{code}

-- you can get the audit file path and detail info with the following scipt

Select * from sys.server_file_audits

-- create procedure

-- =

-- Template generated from Template Explorer using:

Create Procedure (New Menu). SQL

--

-- Use the Specify Values for Template Parameters

-command (Ctrl-Shift-M) to fill in the parameter

-- values below.

--

-- This block of comments will not be included in

-- the definition of the procedure.

-- =

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =

-- Author:

-- Create date:

-- Description:

-- =

CREATE PROCEDURE audit_record

@ I int = 10

AS

BEGIN

/ * Script for SelectTopNRows command from SSMS * /

Insert into [MyAudit] .[dbo] .[Audit _ DYDB_UPDL]

([event_time]

, [sequence_number]

, [action_id]

, [succeeded]

, [permission_bitmask]

, [is_column_permission]

, [session_id]

, [server_principal_id]

, [database_principal_id]

, [target_server_principal_id]

, [target_database_principal_id]

, [object_id]

, [class_type]

, [session_server_principal_name]

, [server_principal_name]

, [server_principal_sid]

, [database_principal_name]

, [target_server_principal_name]

, [target_server_principal_sid]

, [target_database_principal_name]

, [server_instance_name]

, [database_name]

, [schema_name]

, [object_name]

, [statement]

, [additional_information]

, [file_name]

, [audit_file_offset]

, [user_defined_event_id]

, [user_defined_information]

, [CLIENT_NET_ADDRESS])

SELECT CONVERT (datetime,SWITCHOFFSET (CONVERT (datetimeoffset, event_time), DATENAME (TzOffset, SYSDATETIMEOFFSET () AS 'date and time when the audit was triggered'

-- b.connect_time

Sequence_number AS 'order of records in a single audit record'

ID' for action_id AS 'operation

Succeeded AS 'whether the action that triggered the event was successful'

Permission_bitmask AS 'permission mask'

Is_column_permission AS 'whether it is a column level permission'

A.session_id AS 'ID' of the session in which the event occurred

Server_principal_id AS 'login context ID' to perform the operation

Database_principal_id AS 'database user context ID' to perform the operation

Target_server_principal_id AS 'server principal performing GRANT/DENY/REVOKE operation'

Target_database_principal_id AS 'database principal performing GRANT/DENY/REVOKE operations'

Object_id AS'ID (server object, DB, database object, schema object) of the entity on which the audit occurred

Class_type AS 'type of auditable entity'

Session_server_principal_name AS 'server principal of the session'

Server_principal_name AS 'current login'

Server_principal_sid AS 'current login name SID'

Database_principal_name AS 'current user'

Target_server_principal_name AS 'target login for the operation'

Target_server_principal_sid AS 'SID' of the target login

Target_database_principal_name AS 'target user for operation'

Server_instance_name AS 'name of the audited server instance'

Database_name AS 'database context in which this operation occurred'

Schema_name AS'Architectural context for this operation'

Object_name AS 'name of the entity being audited'

Statement AS 'TSQL statement (if present)'

Additional_information AS 'unique information for a single event, returned as XML'

File_name AS 'record the path and name of the audit log file from the source'

Audit_file_offset AS 'buffer offset in the file containing audit records'

User_defined_event_id AS 'user-defined event ID' passed as a sp_audit_write parameter

User_defined_information AS 'records any additional information that the user wants to record in the audit log by using the sp_audit_write stored procedure'

B.CLIENT_NET_ADDRESS AS 'ClientIPAddress'-- into MyAudit..Audit_DYDB_UPDL

FROM sys. [FN _ get_audit_file] ('D:\ SqlAudits\ * .sqlaudit'

DEFAULT, DEFAULT) a left join SYS.DM_EXEC_CONNECTIONS b

On a.session_id=b.session_id

Where

CONVERT (datetime,SWITCHOFFSET (CONVERT (datetimeoffset, event_time), DATENAME (TzOffset, SYSDATETIMEOFFSET () between dateadd (mi,-@ iForce getdate ()) and getdate ()

END

GO

-

-- create job

USE [msdb]

GO

DECLARE @ jobId BINARY (16)

EXEC msdb.dbo.sp_add_job @ job_name=N'DBA_Audit_10min'

@ enabled=1

@ notify_level_eventlog=0

@ notify_level_email=2

@ notify_level_netsend=2

@ notify_level_page=2

@ delete_level=0

@ description=N'record the audit each 10 min'

@ category_name=N' [Uncategorized (Local)]'

@ owner_login_name=N'dba_monitor', @ job_id = @ jobId OUTPUT

Select @ jobId

GO

EXEC msdb.dbo.sp_add_jobserver @ job_name=N'DBA_Audit_10min', @ server_name = Nissan SH DMmuri DB04'

GO

USE [msdb]

GO

EXEC msdb.dbo.sp_add_jobstep @ job_name=N'DBA_Audit_10min', @ step_name=N'DBA_audit_record_10'

@ step_id=1

@ cmdexec_success_code=0

@ on_success_action=1

@ on_fail_action=2

@ retry_attempts=0

@ retry_interval=0

@ os_run_priority=0, @ subsystem=N'TSQL'

@ command=N'MyAudit..audit_record 10'

@ database_name=N'datayesdb'

@ flags=0

GO

USE [msdb]

GO

EXEC msdb.dbo.sp_update_job @ job_name=N'DBA_Audit_10min'

@ enabled=1

@ start_step_id=1

@ notify_level_eventlog=0

@ notify_level_email=2

@ notify_level_netsend=2

@ notify_level_page=2

@ delete_level=0

@ description=N'record the audit each 10 min'

@ category_name=N' [Uncategorized (Local)]'

@ owner_login_name=N'dba_monitor'

@ notify_email_operator_name=N''

@ notify_netsend_operator_name=N''

@ notify_page_operator_name=N''

GO

USE [msdb]

GO

DECLARE @ schedule_id int

EXEC msdb.dbo.sp_add_jobschedule @ job_name=N'DBA_Audit_10min', @ name=N'DBA_audit_10'

@ enabled=1

@ freq_type=4

@ freq_interval=1

@ freq_subday_type=4

@ freq_subday_interval=10

@ freq_relative_interval=0

@ freq_recurrence_factor=1

@ active_start_date=20170719

@ active_end_date=99991231

@ active_start_time=0

@ active_end_time=235959, @ schedule_id = @ schedule_id OUTPUT

Select @ schedule_id

GO

Click (here) to collapse or open

USE [master]

GO

CREATE SERVER AUDIT SPECIFICATION [MyAudit_login]

FOR SERVER AUDIT [MyAudit]

ADD (SUCCESSFUL_LOGIN_GROUP)

GO

ALTER SERVER AUDIT SPECIFICATION [MyAudit_login] WITH (STATE = ON)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.