Κλείσιμο
Κλείσιμο
Αρχική » Databases » Microsoft SQL Server » Πώς ενεργοποιούμε το auditing σε SQL Server

Πώς ενεργοποιούμε το auditing σε SQL Server

Πώς ενεργοποιούμε το auditing σε SQL Server
, , , , Αφήστε ένα σχόλιο
Πώς ενεργοποιούμε το auditing σε SQL Server
Τελευταίες δημοσιεύσεις από Stratos Matzouranis (Προβολή όλων)

Σε αυτό το άρθρο θα δούμε τα βήματα και τις επιλογές που έχουμε για να ενεργοποιήσουμε το database auditing specification στον SQL Server. Μέσα από το database auditing specification μπορούμε να καταγράφουμε ποιος και με τι τρόπο κάνει προσπέλαση ή αλλαγές στα δεδομένα της.

Το auditing μπορούμε να το ενεργοποιήσουμε πάνω σε συγκεκριμένους χρήστες ή ρόλους, σε συγκεκριμένη βάση δεδομένων, σε συγκεκριμένο schema ή και σε συγκεκριμένο object. Επίσης μπορούμε να διαλέξουμε ποια actions θα καταγράφονται π.χ. deletes.

Πώς ενεργοποιείται

Για να το ενεργοποιήσουμε θα πρέπει πρώτα να φτιάξουμε στο instance του SQL Server ένα server audit στο οποίο θα αποθηκεύεται η πληροφορία. Με το παρακάτω script θα δημιουργηθεί ένα server audit με ένα φυσικό αρχείο στον δίσκο που έχουμε επιλέξει να γίνεται rollover με μέγιστο μέγεθος τα 2GB:

USE master ;  
GO  
-- Create the server audit.  
CREATE SERVER AUDIT instance_audit  
TO FILE 
(	FILEPATH = N'C:\Audit'  -- make sure this path exists
	,MAXSIZE = 2 GB
	,MAX_ROLLOVER_FILES = 1
	,RESERVE_DISK_SPACE = ON
)
WITH
(	QUEUE_DELAY = 1000
	,ON_FAILURE = CONTINUE
)

GO
-- Enable the server audit.  
ALTER SERVER AUDIT instance_audit  WITH (STATE = ON) ;  
GO

Έπειτα δημιουργούμε στη βάση δεδομένων που θέλουμε να ενεργοποιήσουμε το auditing ένα database audit specification, που εκεί ορίζουμε τα κριτήρια του auditing. Στο συγκεκριμένο παράδειγμα ορίζουμε να καταγράφει όλα τα SELECT, INSERT, UPDATE, DELETE που εκτελούνται στο default schema dbo από όλους τους χρήστες (του public ρόλου δηλαδή):

*Επέλεξα μόνο το dbo schema καθώς αν επέλεγα όλη την βάση δεδομένων θα κατέγραφε και τις προσπελάσεις σε συστημικά objects που ανήκουν στο schema sys. 

-- Move to the target database.  
USE AdventureWorks2019 ;  
GO  
-- Create the database audit specification.  
CREATE DATABASE AUDIT SPECIFICATION [db_audit]
FOR SERVER AUDIT [instance_audit]
ADD (SELECT , INSERT , UPDATE, DELETE ON SCHEMA::[dbo] BY public )  -- all object for entire schema
-- ,ADD (SELECT , INSERT , UPDATE, DELETE ON DATABASE::[AdventureWorks2019] BY public )  -- all objects for entire database
-- ,ADD (SELECT , INSERT , UPDATE, DELETE ON dbo.AWBuildVersion BY public)  -- all users for specific object
-- ,ADD (SELECT , INSERT , UPDATE, DELETE ON dbo.AWBuildVersion BY sql_login) -- specific user/role for specific object
WITH (STATE = ON)
GO

Στην περίπτωση που θέλουμε να ενεργοποιήσουμε το auditing σε συγκεκριμένο μόνο χρήστη ή ρόλο, ή σε συγκεκριμένο object μπορούμε πολύ εύκολα να το κάνουμε αυτό αλλάζοντας τις γραμμές που είναι σε σχόλιο στα από πάνω παράδειγμα.

Πώς διαβάζουμε τα audit logs

Ο πιο εύκολος και ευανάγνωστος τρόπος να διαβάσουμε την πληροφορία είναι με το παρακάτω query που έχω φτιάξει να διαβάζει την πληροφορία μέσω της function sys.fn_get_audit_file.

Με αυτό το query μπορούμε να δούμε:

  • Πότε εκτελέστηκε
  • Από ποιο session id
  • Από ποιο login
  • Σε ποια βάση
  • Σε ποιο schema
  • Σε ποιο object
  • Ποιο query εκτελέστηκε
  • Απο ποιο πρόγραμμα
  • Από ποιο computer name
  • Και από ποια IP

Επίσης μπορούμε να βάλουμε κριτήριο την ώρα, τον πίνακα ή και κάποιο κομμάτι από το query.

**Το πεδίο event_time είναι σε UTC timezone οπότε για να βάζουμε την πραγματική ώρα που έχει ο server προσθέτουμε την διαφορά ώρας που έχει ο server με το UTC timezone που γίνεται με τις functions (DATEADD(HOUR,DATEDIFF(hour, SYSUTCDATETIME(),SYSDATETIME() ))

Το query είναι το παρακάτω:

declare @file_path nvarchar(260);
set @file_path = 
(select log_file_path+'*.sqlaudit'
from sys.server_file_audits
where name = 'instance_audit');


SELECT distinct
DATEADD(HOUR,DATEDIFF(hour,  SYSUTCDATETIME(),SYSDATETIME() ),event_time) AS [action_time],
session_id, session_server_principal_name,database_name,schema_name,object_name,statement,application_name,host_name,client_ip
FROM sys.fn_get_audit_file(@file_path, default, default)
WHERE 1=1
--and statement like '% from %'
--and DATEADD(HOUR,DATEDIFF(hour,  SYSUTCDATETIME(),SYSDATETIME() ),event_time) between '2022-11-03 13:00:00.000' and '2022-11-03 14:00:00.000'
--and object_name = 'test'
ORDER BY DATEADD(HOUR,DATEDIFF(hour,  SYSUTCDATETIME(),SYSDATETIME() ),event_time) desc;
Πώς ενεργοποιούμε το auditing σε SQL Server
01

Πώς αφαιρούμε το auditing

Για να το διαγράψουμε τρέχουμε το παρακάτω query με τα ανάλογα ονόματα:

USE AdventureWorks2019 ;  
GO  
ALTER DATABASE AUDIT SPECIFICATION [db_audit]
WITH (STATE = OFF)
GO
DROP DATABASE AUDIT SPECIFICATION [db_audit];
GO
USE master
GO
ALTER SERVER AUDIT [instance_audit]
WITH (STATE = OFF)
GO
DROP SERVER AUDIT [instance_audit]
GO

Πηγές:

Μοιράσου το

Αφήστε μία απάντηση