لاگ کردن ردپای کاربران به همراه هویت آنها در دیتابیس

در بسیاری از سناریوها نیاز داریم عملیات کاربران را در جداول جداگانه ثبت نماییم. روشهای متنوعی در این خصوص وجود دارد که هر کدام مزایا و معایت خودشان را دارا هستند. بعضی به ازای هر کاربر یک Login در SQL Server ایجاد میکنند تا به کمک تابع suser_name هویت کاربر را در هر کجای کد بدست آورند.

برخی دیگر مانند وب اپلیکیشنها نمیتوانند هزاران لاگین در SQL Server تعریف کنند و تقریبا تمام Connectionها با یک لاگین مشترک به دیتابیس برقرار میگردد.

در این حالت نام های کاربری و سطوح دسترسی در جداول خودمان تعریف میشوند. این روش که به اصطلاح Hand Rolled Security نام دارد مناسب است اما باید هویت کاربر را برای لاگ کردن به تمام پروسیجرها پاس داد. با این وجود نمیتوان پارامتر به داخل تریگر ارسال کرد و هنگام ویرایش اطلاعات از طریق تریگر، تشخیص هویت کاربر مشکل است.

تابع Context_Info میتواند مانند یک متغیر Global در کل Session شما نقش داشته باشد و شاید ترجیح دهید نگهداری هویت کاربر را به آن بسپارید. کافیست هنگام برقراری Connection یکبار مقدار دهی انجام دهید سپس در هر کجای کد حتی داخل تریگر به آن دسترسی خواهید داشت. توجه نمایید که مقدار آن به صورت باینری set میشود. برای آزمایش، جدول و تریگر زیر را بسازید:

CREATE TABLE T1(c1 int)
GO

CREATE TRIGGER Trg1 on T1
AFTER INSERT,UPDATE,DELETE
AS
BEGIN
  SELECT CAST(CONTEXT_INFO() as varchar)
END

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

DECLARE @x varbinary(128)
SET @x=CAST( 'MyUser' AS varbinary(128))
SET CONTEXT_INFO @x
GO

INSERT T1 VALUES (1)

با تغییر مقدار متغییر x و تخصیص آن به Context_Info تریگر مقدار جدید را منعکس میسازد. از آنجاییکه مقدار تابع در کل Session قابل دسترس است شما نیازی به پاس کردن هویت کاربر به پروسیجرها ندارید. یادآوری میکنم فقط یک بار در Connection لازم است مقدار Context_Info را مثلا معادل نام کاربری تنظیم نمایید تا در پروسیجرها یا تریگرها هنگام ثبت لاگ، آنرا بخوانید.