SQL Audit

کنترل تغییرات در SQL Server – بخش سوم SQL Audit

سلام دوستان، امیدوارم که مثل همیشه عالی باشید. در ادامه سری مقالات کنترل تغییرات دیتا در SQL Server امروز بخش سوم و پایانی اون رو به بررسی SQL Audit خواهیم پرداخت، SQL Audit میشه گفت کاملترین و بهینه‌ترین(به لحاظ Performance) مکانیزم Auditing در SQL Server به شمار میره که این تکنیک دقیقاً به این سوال‌ها که چه کسی؟ چه چیزی رو؟ در چه زمانی؟ تغییر داده پاسخ میده

SQL Audit

 

 

همونطور که اشاره کردم SQL Audit بهترین Performance ممکنه رو در بین سه تکنیکی که در این سری مقالات باهم بررسی کردیم ارائه میده، که دلیلش هم استفاده از Extended Eventsها است، SQL Audit ا این حد انعطاف در اختیارمون قرار میده که حتی میتونیم مشخص کنیم که لاگ تغییرات کجا ذخیره بشه

  • میتونه درون یک فایل باینری باشه
  • درون Windows Security Log
  • یا Windows Application Log

با SQL Audit این امکان رو داریم که مشخص کنیم که عملیات تولید لاگ روی تراکنش‌های یک کاربر خاص باشه، برای پیاده‌سازی مکانیزم تولید لاگ میتونیم از طریق رابط گرافیکی درون SSMS استفاده کنیم یا از طریق T-SQL، در این مقاله ما به بررسی تولید لاگ‌ درون فایل باینری و Event log از طریق دستورات T-SQL می‌پردازیم.

فعال سازی SQL Audit

برای اینکه بتونیم به درک کاملی از نحوه کارکرد این  تکنیک برسیم در این مقاله دو نوع Auditing رو مورد بررسی قرار خواهیم داد. اول به بررسی Auditing در سطح یک SQL Object میپردازم و سپس در مورد Auditing در بحث‌های security صحبت خواهیم کرد.در نهایت، نحوه ذخیره خروجی این تکنیک رو درون یک فایل و همچنین Event Log سرور بررسی خواهیم کرد.

مثل همیشه یک دیتابیس تست میسازیم تا کاربرد این تکنیک رو باهاش بررسی کنیم

USE master 

IF DB_ID('TestSQLAudit') >0 
   BEGIN	
      ALTER DATABASE TestSQLAudit SET SINGLE_USER WITH ROLLBACK IMMEDIATE
      DROP DATABASE TestSQLAudit
   END	

CREATE DATABASE TestSQLAudit;
GO

بعد از ساختن دیتابیس، برای تست SQL Audit یک جدول ایجاد می‌کنیم و تعدادی رکورد درون آن درج می‌کنیم

IF OBJECT_ID('dbo.Products') >0
   DROP TABLE dbo.Products;

CREATE TABLE dbo.Products
(
   ProductID INT NOT NULL CONSTRAINT PK_Products PRIMARY KEY,
   ProductName VARCHAR(30) NOT NULL,
   ProductCost MONEY NOT NULL
)
GO	

INSERT INTO dbo.Products
        ( ProductID ,ProductName ,ProductCost)
VALUES  (1,'DVD Player',1000.00) ,(2,'Samsung TV',1500.00),(3,'Cannon Camera',150)
GO

درج تعدادی رکورد در جدول در SQL Audit

 

 

 

بعد از این نوبت به تعریف اجزای مرتبط با SQL Audit میرسه که در زیر یک به یک اونها رو توضیح میدم

  • Server Audit: یکی از objectهای پایه‌ای SQL Audit که برای استفاده از این مکانیزم حتماً باید فعال بشه و به طور کلی جهت collectکردن Action‌های در سطح سرور و دیتابیس مورد استفاده قرار می‌گیره. هنگامی که Server Audit رو فعال می‌کنید باید مکان ذخیره‌سازی داده‌های مربوط به Audit رو هم مشخص کنید. output location یا همون مکان خروجی با نام Target شناخته میشه که میتونه یک فایل و یا Event log (که شامل Application یا Security log میشه) باشه. برای این مثال ما قصد داریم خروجی رو درون یک text file قرار بدیم
USE master 
GO	
CREATE SERVER AUDIT [Audit-TestAudit]
TO FILE
(
   FILEPATH=N'C:\AuditTest\',
   MAXSIZE=5 MB,
   MAX_ROLLOVER_FILES = 20 ,
   RESERVE_DISK_SPACE=OFF
)
WITH 
(
   QUEUE_DELAY=1000,
   ON_FAILURE=CONTINUE
)
GO

قبل از اینکه کوئری بالا رو اجرا کنید، بیاید با هم Syntax دستورش و مفهوم پارامترهاش رو بررسی کنیم. همونطور که می‌بینید توی این دستور قصد داریم یک Server Audit ایجاد کنیم که خروجی خودش رو درون یک فایل داره قرار میده که با پارامتر FILE PATH اون رو مشخص کردیم براش، نکته ای که باید بهش توجه بشه اینه که مسیری که مشخص کردیم باید از قبل وجود داشته باشه.نام فایل‌ها هم براساس نام Audit و همچنین audit GUID ساخته خواهد شد.بیاید پارامترها رو دونه به دونه بررسی کنیم:

  1. MAXSIZE : همونطور که از نامش مشخصه حداکثر اندازه‌ای که فایل اجازه رشد کردن رو داره مشخص می‌کنه، که می‌تونیم بر اساس MB،GB و TB اون رو تعیین کنیم که به طور پیش فرض برابر UNLIMITED مقدار گرفته
  2. MAX_ROLLOVER_FILES : این پارامتر حداکثر تعداد فایل‌هایی که اجازه داره درون سیستم نگه داره رو مشخص می‌کنه، به طور پیش فرض برابرUNLIMITED قرار گرفته
  3. RESERVE_DISK_SPACE : این پارامتر دو مقدار ON و OFF رو میتونه بگیره، که وقتی ON باشه فایل خروجی Audit از همون ابتدا کل حجم MAXSIZE تخصیص داده میشه، این مورد زمانی کاربرد داره که MAXSIZE برابر UNLIMITED نباشه
  4. QUEUE_DELAY : این پارامتر حداکثر زمانی Engine میتونه برای پردازش یک Audit هزینه کنه رو مشخص میکنه، این زمان برحسب میلی ثانیه است که اگر برابر ۰ قرار بگیره به معنی نامحدود طلقی میشه
  5. ON_FAILURE : این پارامتر مشخصه میکنه که اگر هنگام ذخیره خروجی بروی دیسک با خطا مواجه شد چه کاری انجام بده؟ دو تا انتخاب داریم CONTINUE (ادامه بده) یا SHUTDOWN (قطع انجام عملیات)
  6. AUDIT_GUID : این پارامتر تنها زمانی مورد استفاده قرار می‌گیره که نیاز به پشتیبانی از Database Mirroring داشته باشیم

خب حالا با اجرای دستور تنها با خروجی زیر مواجه میشیم

 

SQL Audit After Creating Server Audit

توسط اسکریپت بالا یکی از پایه‌ای ترین objectهای SQL Audit رو ایجاد کردیم، که اگر بخواهیم Server Audit ایجاد شده رو ببینیم کافیه که از طریق SQL Server Management Studio یا همون SSMS  گزینه Security و سپس Audits رو Expand کنیم

SQL Audit in SSMS

همونطور که توی تصویر ملاحضه می‌کنید کنار آیکون Audit تازه ایجاد شده یک فلش قرمز رنگ وجود داره که نشون دهنده غیر فعال بودنه اونه و برای استفاده ابتدا باید فعال بشه

  • Database Audit Specification : علاوه بر Server Audit که کامل توضیح داده شد، Database Audit Specification برای تعریف Audit های مختلف در سطح دیتابیس مورد استفاده قرار می‌گیره،همونطور که در ابتدای مقاله اشاره کردم SQL Audit توسط Extended Events تمامی عملیات‌های خودش رو انجام میده و به دلیل سبک بودن Extended Eventها بار Performanceی خاصی بابت SQL Audit به سرور بانک اطلاعاتی اعمال نمیشه. برای ادامه سناریو الان قصد داریم از طریق SQL Audit تمام دستورات DML که بروی dbo.Products اجرا میشن رو Collect کنیم:
USE TestSQLAudit
GO	
ALTER DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification-test] WITH (STATE=OFF)

DROP DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification-test]

CREATE DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification-test]
FOR SERVER AUDIT [Audit-TestAudit]
ADD (DELETE ON OBJECT::[dbo].[Products] BY [public]),
ADD (INSERT ON OBJECT::[dbo].[Products] BY [public]),
ADD (SELECT ON OBJECT::[dbo].[Products] BY [public]),
ADD (UPDATE ON OBJECT::[dbo].[Products] BY [public])
WITH (STATE = ON)
GO

قبل از اجرای دستورات بالا حتماً Server Auditی که توی مرحله قبل ایجاد کردید رو فعال کنید، در غیر این صورت با خطا مواجه می‌شید، برای این کار برروی Server Audit کلیک راست کنید و Enable Audit رو انتخاب کنید.

با اجرای دستورات بالا به SQL Server داریم میگیم که تمام دستورات DELETE ,INSERT, SELECT و UPDATE ی که برروی جدول Products اجرا میشه توی مسیر خروجی‌ای که برات مشخص کردیم لاگ کن.اجازه بدید این موضوع رو با اجرای یک دستور SELECT امتحان کنیم:

SELECT 
   ProductID,ProductName,ProductCost 
FROM dbo.Products

به محض اجرای دستور بالا در مسیر مشخص کرده برای ذخیره خروجی SQL Audit سه فایل اضافه خواهد شد

خروجی SQL Audit پس از اجرای اولین دستور

اگر بخواهیم این فایل ها رو با یک Text Editor مثل Notepad باز کنیم، با یک سری کاراکتر های درهم روبرو خواهیم شد، که نمیشه زیاد چیزی ازش متوجه بشیم

خروجی SQL Audit در Notepad

برای خواندن محتویات SQL Audit نیاز به استفاده از فانکشن sys.fn_get_audit_file داریم که این فانکشن سه پارامتر ورودی می‌گیره:

  1. file_pattern : از این پارامتر برای مشخص کردن مکان ذخیره‌سازی داده های SQL Audit استفاده میشه
  2. initial_file_name : توسط این پارامتر می‌تونیم مشخص کنیم که نمایش لاگ رو از چه فایلی شروع کنه، همچنین اگر مقداری ندیم برای این پارامتر به طور پیشفرض از قدیمی‌ترین فایل موجود شروع به نمایش لاگ‌ها خواهد کرد
  3. audit_file_offset : این پارامتر برای مشخص کردن offset روی فایل Audit به کار میره که مثل پارامتر قبلی میتونه DEFAULT باشه

اگر بازم اطلاعات بیشتری در مورد این فانکشن میخواستید میتونید اینجا رو مطالعه کنید. حالا برای اینکه بتونیم به طور خاص روی موارد پایه ای این فانکشن تمرکز کنیم تعدادی از فیلدهای خروجی این فانکشن رو با هم بررسی می‌کنیم:

SELECT 
      action_id,
      succeeded,
      session_id,
      session_server_principal_name,
      object_name,
      statement,
      file_name,
      audit_file_offset 
FROM sys.fn_get_audit_file('C:\AuditTest\Audit-TestAudit_*.sqlaudit',DEFAULT,DEFAULT)
GO

با توجه به کوئری بالا ما سه پارامتر فانکشن sys.fn_get_audit_file رو با مقادیر پارامتر اول که دقیقاً نام فایل یا بهتر بگم بخش مشترک بین چند فایل موجود در فولدر انتخاب شده است و اون ستاره هم نشون میده که وجه تمایز فایل‌ها در این بخش قرار گرفته که اگر به نا فایل‌ها توی مسیر مورد نظر دقت کنید توی این قسمت یک مقدار GUID قرار گرفته و همچنین دو پارامتر دیگه هم با DEFAULT مقدار دهی کردیم تا مفهوم کلی این دستور این بشه که ” تمام لاگ های موجود در مسیر مشخص شده رو نمایش بده”، با اجرای دستور بالا با خروجی زیر مواجه می‌شیم

 

Get SQL Audit File

 

 

 

 

 

همونطور که توی تصویر بالا مشاهده می‌کنید دستور Selectی که آخرین بار بروی جدول Products اجرا کردیم در این مکان لاگ شده، اجازه بدید همون دستور Select رو مجدد روی جدول Products اجرا کنیم

SELECT 
   ProductID,ProductName,ProductCost 
FROM dbo.Products

که اجرای این دستور منجربه اضافه شدن یک سطر دیگر به فایل های خروجی می‌شود

خروجی با اجرای دستور دوم

 

 

 

ممکنه مدیریت و شاید حتی بررسی این حجم دیتا از لاگ دستورات اجرا شده روی یک جدول با Worload بالا زیاد روش بهینه ای نباشه برای بررسی لاگ‌ها توی دستور بعدی میخواهیم کاری کنیم که تنها آخرین تغییرات رو مشاهده کنیم، برای این کار اگر یادتون باشه توی توضیحات فانکشن دو پارامتر آخرمون نام فایلی که بازگردانی رو باید از اون شروع کنه و همچنین Offset درون اون فایل بود، خب این اطلاعات رو الان ما از دستورات قبلی بدست آوردیم و میتونیم توی این مرحله از اونها استفاده کنیم(به دو ستون آخر خروجی دستور قبل توجه کنید)

SELECT 
      action_id,
      succeeded,
      session_id,
      session_server_principal_name,
      object_name,
      statement,
      file_name,
      audit_file_offset 
FROM sys.fn_get_audit_file('C:\AuditTest\Audit-TestAudit_*.sqlaudit','C:\AuditTest\Audit-TestAudit_8A25E744-0C2F-4C3E-819B-D0083C0151F3_0_130961638430490000.sqlaudit',6144)
GO

دقت کنید که مقدار پارامتر Offset، ممکن که چه عرض کنم به احتمال خیلی زیاد متفاوت با چیزی که اینجا می‌بینید خواهد بود، اما قبل از اجرای کوئری بالا اجازه بدید یک دستور UPDATE روی جدول مورد نظرمون اجرا کنیم و سپس نتیجه رو بررسی کنیم

UPDATE dbo.Products SET	ProductCost=2750 WHERE ProductID=1

آموزش SQL

 

 

 

با اجرای دستور UPDATE و مشاهده خروجی SQL Audit دو سطر جدید به خروجی اضافه شده که هر دوی آنها مرتبط با دستور UPDATE اجرا شده هستند و تنها وجه تمایز آنها بروی فیلد action_id آنهاست که یکی برابر SL یا Select  و دیگری UP یا همان Update است که نشان دهنده آن است که برای Update کردن یک رکورد ابتدا آن رکورد را Select کرده و سپس اقدام به انجام عملیات Update نموده است.

خب تا به اینجا به طور کامل نحوه ذخیره خروجی بروی فایل رو بررسی کردیم، حالا قصد داریم نحوه ذخیره خروجی لاگ درون Windows Application Log رو بررسی کنیم. برای این کار ابتدا تمام کار‌هایی که تا به اینجا انجام دادیم رو غیر فعال می‌کنیم:

  1. در وهله اول Audit Specification رو در سطح دیتابیس غیر فعال می‌کنیم
  2. بعد از اون اقدام به غیرفعال کردن و حذف Server Audit می‌کنیم
USE TestSQLAudit
GO	
ALTER DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification-test] 
WITH (STATE=OFF)
GO

DROP DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification-test]
GO	
USE master 
GO	

IF EXISTS(SELECT * FROM sys.server_audits WHERE name=N'Audit-TestAudit')
BEGIN
   ALTER SERVER AUDIT [Audit-TestAudit] WITH (STATE=OFF)
   DROP SERVER AUDIT [Audit-TestAudit]
END	
GO

با اجرای دستورات فوق تنظیمات Auditing به طور کامل از روی دیتابیس TestSQLAudit حذف خواهد شد.

Server Audit Specification

در مرحله قبل Auditing رو در سطح دیتابیس مورد بررسی قرار دادیم، حالا قصد داریم Auditing رو در سطح سرور بررسی کنیم. به طور کلی Server Audit Specification به SQL Server میگه که چه چیزی رو Audit کنه. Server Audit Specification یک object هست که تعداد زیادی از  action groupهای Extended Events رو که در سطح سرور(یا Instance) هستند  رو جمع‌آوری می‌کنه و اونها رو سمت targetی که شما تعیین کردید (که میتونه فایل یا ذخیره در Event Log باشه) هدایت میکنه. نکته دیگه‌ای که باید بهش توجه کنید اینه که Server Audit Specification در سطح Instance شما عمل می‌کنه، از این رو تنها یک Server Audit Specification می‌تونیم به ازای هر Server Audit تعریف کنیم. شما می‌تونید چند Action Group درون یک Server Audit Specification داشته باشید.برای اطلاعات بیشتر در مورد Action Group ها اینجا رو ببینید.

خب اجازه بدید شروع کنیم به بررسی نحوه عملکرد اون، اگر یادتون باشه توی مرحله قبل که داشتیم تنظیمات مربوط به Database Level Specification رو غیرفعال می‌کردیم Server Audit رو هم حذف کردیم و حالا با ساخت یک Server Audit جدید شروع می‌کنیم و در ادامه Server Audit Specification رو خواهیم ساخت

ساخت Server Audit

دفعه قبلی که یک Server Audit  ایجاد کردیم Target اون رو برابر یک فایل قرار دادیم، اما این‌بار قصد داریم Application Log رو به عنوان Target تعریف کنیم

CREATE SERVER AUDIT [WriteToApplicationLog] 
TO APPLICATION_LOG --Target
WITH	
(
   QUEUE_DELAY=1500, --Delay Set in ms
   ON_FAILURE=CONTINUE --Action to be Taken when Server Audit Fails
)

همونطور که دفعه قبل هم دیدیم وقتی یک Server Audit ایجاد می‌کنیم به طور پیش‌فرض در وضعیت disable قرار داره که قبل از استفاده نیاز به فعال سازی داره، برای این کار دستور زیر رو اجرا می‌کنیم

ALTER SERVER AUDIT [WriteToApplicationLog] 
WITH(STATE=ON)
GO

خب Server Audit رو ایجاد کردیم و آماده است الان باید یک Server audit Specification ایجاد کنیم و برای این مثال قصد دارم توسط این مکانیزم تمام اعمال ساخت و حذف SQL Login ها رو لاگ کنیم

CREATE SERVER AUDIT SPECIFICATION [AuditDropCreateLogin]
FOR SERVER AUDIT [WriteToApplicationLog]
ADD (SERVER_PRINCIPAL_CHANGE_GROUP)
WITH (STATE=ON);

با اجرای این دستورات تمامی دستورات حذف ایجاد و تغییر در لاگین‌های SQL Server توسط این مکانیزم در Application Log ذخیره خواهد شد.اجازه بدید این مورد رو با یک مثال که در اون یک لاگین ایجاد سپس اون رو تغییر بدیم و در نهایت حذفش کنیم بررسی کنیم

CREATE LOGIN TestLogin
WITH PASSWORD='StrongPassword',
CHECK_POLICY=OFF
GO	
ALTER LOGIN TestLogin WITH DEFAULT_DATABASE=tempdb
GO	
DROP LOGIN TestLogin

در بخش اول این اسکریپت یک لاگین ایجاد کردیم و در ادامه در دستور دوم همان لاگین را ویرایش کرده و DEFAULT_DATABASE آن را تغییر دادیم و در نهایت در سومین دستور آن لاگین را حذف کردیم، اجازه بدید نتیجه این دستورات رو در Event log بررسی کنیم. برای مشاهده Event Log دو راه داریم:

  • از طریق نرم‌افزار Event Viewer سیستم عامل
  • از طریق SSMS که در این حالت نام کاربری که با آن لاگین کرده اید باید دسترسی های لازمه را داشته باشد، خودم معمولاً ترجیح میدم این کار رو مستقیم از طریق سیستم‌عامل انجام بدم. میتونیم به راحتی بروی EventID شماره ۳۳۲۰۵ فیلتر کنیم تا بتونیم تغییرات مربوط به این سناریو رو مشاهده کنیم

SQL Audit Target in EventViewer

 

 

 

 

 

 

 

 

 

 

 

 

 

همونطور که توی تصویر مشخص کردم با اعمال فیلتر، به دستوراتی که بروی دیتابیس مورد نظرمون اجرا شده دسترسی داریم

درانتها برای پاکسازی تمام موارد و حذف دیتا بیس دستورات زیر رو اجرا می‌کنیم:

ALTER SERVER AUDIT SPECIFICATION [AuditDropCreateLogin]
WITH (STATE=OFF);
DROP SERVER AUDIT SPECIFICATION [AuditDropCreateLogin];

--you have to disable Audit Specification before you'll be able to drop it!
ALTER SERVER AUDIT [WriteToApplicationLog]
WITH(STATE=OFF);
DROP SERVER AUDIT [WriteToApplicationLog];
GO

 

مزایا و معایب SQL Audit

  • یکی از مزایای SQL Audit بهینه بودن این تکنیک به لحاظ Performance نسبت به تکنیک های دیگست و این هم تنها به واسطه استفاده این تکنیک از Extended Event بدست اومده
  • مزیت دیگه‌ای که میشه بهش اشاره کرد درصد بالای Granularity یا تفکیک پذیری این تکنیک هست که شما میتونید به طور خاص وارد detail بشید و المان های مورد نظرتون رو Audit کنید

و اما معایبی که میشه بهشون اشاره کرد

  • بااینکه توی مزایا اشاره کردم به تفکیک پذیری بالای این تکنیک اما یکی از معایب اون اینه که در بیشتر موارد دیتاهای تغییر کرده رو به ما نشون نمیده
  • یکی دیگه از معایب این تکنیک تا حدی سخت بودن نگهداری یا Maintain کردن اطلاعات اونه که باید ابتدا دیتاها رو از فایل و Event Log برداشت کنیم و در نهایت  این دیتاها برای آنالیز درون یک جدول قرار بگیره

خب دوستان این بود سومین و آخرین قسمت از سری مقالات کنترل تغییرات در SQL Server، امیدوارم که مورد استفاده قرار گرفته باشه

سپاس از همراهیتون

شاد باشید 🙂

دانلود اسکریپت

برنامه نویس و توسعه دهنده و علاقه مند به هر چیزی که با دیتا در ارتباط باشه
SQL Server , Big Data, Hadoop, R,Statistical Programming

Categories: SQL Server

پاسخ دهید

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *