The article directories

  • Audit/meter
    • Creating audit objects
    • Create a server audit specification
    • Create database audit specifications
    • Enable the audit
    • Viewing audit Logs
    • Delete the audit
  • summary

Audit/meter

In simple terms, it tells you who did what and when, and further ensures database security by accounting for it in a log/file. General process for creating and using audits. 1. Create audits and define goals. 2. Create a server audit specification or database audit specification that maps to the audit. Enable audit specifications. 3. Enable auditing. 4. Use The Windows Event Viewer, Log File Viewer, or fn_get_audit_file function to read audit events. A server audit object can be configured with one server audit specification, one or more database audit specifications

Creating audit objects

Create a server audit object ① SQL statement implementation

create server audit myAudit
to file
 (
  filepath = 'D:\'.-- Audit log file path
  maxsize  = 500MB,         The maximum size to which audit files can be increased
  max_rollover_files = 10.-- The maximum number of files to remain in the file system, plus the current file
  reserve_disk_space = off  Presize files on disk by MAXSIZE value
 )
with 
 (
  queue_delay = 1000.Determine the number of milliseconds that can be delayed before an audit action is forced to process
  on_failure = continue   -- Whether the SQL instance is closed when the target cannot be written
 )
Copy the code

② Interface realization

Right-click new audit -> Set path parameters and so on.



Create a server audit specification

Create a server review specification, implement (a) review BACKUP and RESTORE, (b) database change review ① SQL statement implementation

-- Note: view the server audit specification audit activities
Select name from sys.dm_audit_actions where class_desc='SERVER' and configuration_level='Group'
Copy the code
create server audit specification mySevSpf
for server audit myAudit
add(BACKUP_RESTORE_GROUP),
add(DATABASE_CHANGE_GROUP)
Copy the code

② Interface realization

Right-click New Server Audit specification -> Add the appropriate audit operation type



Create database audit specifications

To create a database review specification, need to review the (a) table STU query, (b) update operation ① SQL statement implementation

-- Note: view, database review specification can review activities
Select name from sys.dm_audit_actions where class_desc='DATABASE' and configuration_level='Group'
Copy the code
create database audit specification myDtbSpf
for server audit myAudit
add (select on dbo.stu by public),
add (update on dbo.stu by public)
Copy the code

② Interface realization

Right-click new Database audit specification -> Add the appropriate audit operation type



Enable the audit

① IMPLEMENTATION of SQL statement

alter server audit specification mySevSpf with (state=on)
alter server audit myAudit with (state=on)
use xscj
go
alter database audit specification myDtbSpf with (state=on)
-- Disable audit can also be set to off
Copy the code

② Interface realization

Right-click to enable the database audit specification and do the same for the rest.

Viewing audit Logs

① IMPLEMENTATION of SQL statement

select event_time, action_id, succeeded , session_id ,session_server_principal_name ,
object_name , statement , file_name, audit_file_offset
from sys.fn_get_audit_file('D:\'.default.default)
Copy the code

② Interface realization

Delete the audit

drop server audit myAudit
drop server audit specification mySevSpf
use xscj
go
drop database audit specification myDtbSpf
Copy the code

summary

  1. Audit can be used in departments with high security requirements and can be flexibly turned on or off.
  2. Auditing takes time and space.

Original is not easy, please do not reprint (this is not rich visits add insult to injury) blogger home page: blog.csdn.net/qq_45034708 If the article is helpful to you, remember to focus on the likes collection ❤