האח הגדול עינו פקוחה (3) – מי הציץ בטבלה שלי?
לא יפה להסתכל בטבלאות של אחרים,
ובעזרת מנגנון ה-Audit נוכל ללכוד את מי שהציץ ונפגע.
ניצור מחיצה מתאימה בדיסק של השרת לשם יופנו נתוני המעקב,
ונגדיר Audit (אובייקט ברמת השרת) למחיצה זו.
את המחיצות ניתן ליצור ידנית אם ה-xp_CmdShell אינו מאופשר:
Use Master
Go
xp_cmdshell 'md C:\MyAudit\'
Go
CREATE SERVER AUDIT My_Server_Audit
TO FILE (FILEPATH='C:\MyAudit\', MAXSIZE = 5MB,
MAX_ROLLOVER_FILES = 100, RESERVE_DISK_SPACE = ON);
Go
ALTER SERVER AUDIT My_Server_Audit with (STATE = ON);
Go
כעת נגדיר Database Audit Specification (אובייקט ברמת הדטבייס) שבעזרת הנ"ל יעקוב אחר מה שנחליט,
כאשר לצורך העניין נעקוב אחר פעולות DML שכוללות Select, Update, Delete, Insert;
ובמחשבה שניה – כדי להדגים שינוי בדיעבד בהגדרה – נעקוב גם אחרי פעולות DDL (שינויי סכימה),
כל זה בדטבייס AdventureWorks:
Use AdventureWorks
Go
CREATE DATABASE AUDIT SPECIFICATION My_DB_Audit_Spec
FOR SERVER AUDIT My_Server_Audit
ADD (SELECT,DELETE,INSERT,UPDATE ON DATABASE::[AdventureWorks] BY [public])
WITH (STATE = Off)
GO
ALTER DATABASE AUDIT SPECIFICATION My_DB_Audit_Spec
FOR SERVER AUDIT My_Server_Audit
ADD (SCHEMA_OBJECT_CHANGE_GROUP)
Go
ALTER DATABASE AUDIT SPECIFICATION My_DB_Audit_Spec
FOR SERVER AUDIT My_Server_Audit WITH (STATE = ON)
Go
כעקרון קבצי ה-Audit ריקים כרגע חוץ מרשומת פתיחה סימלית:
SELECT *
FROM sys.fn_get_audit_file ('C:\MyAudit\My_Server_Audit*.sqlaudit',default,default)
Order By event_time;
כעת נבצע שורה של פעולות כדי לוודא שהיד פתוח וה-Audit רושמת:
ניצור ב-AdventureWorks טבלה, נכניס נתונים, נשלוף, נעדכן, נפתח טרנזקציה + נמחק נתונים + נתחרט, נמחוק על ידי Truncate, ונבטל את הטבלה;
ניצור View, נשנה אותו, נשלוף ממנו, ונבטל אותו;
ובין הפקודות נפזר פקודות המתנה WaitFor Delay כדי שהן לא יתבצעו באותה אלפית שניה ושנוכל לשלוף לפי סדר הביצוע:
Use AdventureWorks
Go
Create Table dbo.Try001(I Int);WaitFor Delay '00:00:01';
Insert Into Try001 Select 1;WaitFor Delay '00:00:01';
Select * From Try001;WaitFor Delay '00:00:01';
Update Try001 Set I=2;WaitFor Delay '00:00:01';
Begin Tran
Delete From Try001;WaitFor Delay '00:00:01';
RollBack;
Truncate Table Try001;WaitFor Delay '00:00:01';
Drop Table Try001;WaitFor Delay '00:00:01';
Go
Create View dbo.Try002 As Select 2 D;
Go
WaitFor Delay '00:00:01';
Go
Alter View dbo.Try002 As Select 3 D;
Go
WaitFor Delay '00:00:01';
Go
Select * From Try002;WaitFor Delay '00:00:01';
Drop View dbo.Try002;
Go
וכעת ניתן לשלוף שוב את הנתונים,
וניתן לראות שהכל נרשם- כולל הטרנזקציה שבוטלה על ידי RollBack ופקודת ה-Truncate:
SELECT *
FROM sys.fn_get_audit_file ('C:\MyAudit\My_Server_Audit*.sqlaudit',default,default)
Order By event_time;
את ה-Audit ניתן לעצור כך:
Use Master
ALTER SERVER AUDIT My_Server_Audit with (STATE = ON)
Go
ואם רוצים להעלים ראיות, מבטלים את כל מה שנוצר:
Use Master
If (Select Count(*) From sys.server_audits Where name='My_Server_Audit')>0
Begin
ALTER SERVER AUDIT My_Server_Audit with (STATE = OFF);
Drop SERVER AUDIT My_Server_Audit;
End
Go
Use AdventureWorks
Go
If (Select Count(*) From sys.Database_audit_specifications Where name='My_DB_Audit_Spec')>0
Begin
ALTER DATABASE AUDIT SPECIFICATION My_DB_Audit_Spec with (STATE = OFF);
Drop DATABASE AUDIT SPECIFICATION My_DB_Audit_Spec;
End
Go
xp_cmdshell 'del C:\MyAudit\/q'
Go
xp_cmdshell 'rd C:\MyAudit\'
Go