הבלוג של גרי רשף

13/05/2010

האח הגדול עינו פקוחה (5) – מי שינה את הנתונים בטבלה?

Filed under: Uncategorized — תגיות: , , , — גרי רשף @ 14:33

בחברה קמעונאית בה עבדתי בעבר היו כל מיני מבצעים לקידום מכירות,
על פי התוצאות היו מתגמלים את מנהלי האזורים וסוכני המכירות,
והיו כל מיני הגדרות מי שייך למה ומי זכאי למה לטובת הנ"ל.

אינני זוכר את הפרטים בדיוק, אבל באופן כללי הקמתי טבלה עם נתונים הקשורים למערכת התגמולים,
כעבור מספר חודשים באו אלי בטענות שההגדרות אינן מתאימות למה שנקבע מלכתחילה,
כולם – כולל אני – ניערו חוצנם וטענו שלא הם שינו את הנתונים האלו,
ועכשיו לך חפש מי ומה ומתי..

נניח שיש לנו טבלת עובדים צנועה עם שמות העובדים ומשכורותיהם (אפשר כמובן להרחיב את הטבלה בהתאם לדוגמה):

USE tempdb;

GO


If object_id( 'T_Ovdim') Is Not Null Drop Table T_Ovdim;

Go


Create Table T_Ovdim(Shem Varchar(50) Primary Key,

                    Maskoret Int);

Go

הנתונים בטבלה רגישים ולכן נצרף לה שתי טבלאות מעקב: מי שינה (T_Maakav) ומה שינה (T_MaakavPerut):

If object_id( 'T_Maakav') IS NOT NULL Drop Table T_Maakav;

Go


Create Table dbo.T_Maakav(Mone Int Identity,

            Taarih Datetime,

            UserName Varchar(128),

            SystemUser Varchar(128),

            AppName Varchar(128),

            HostID Varchar(10),

            HostName Varchar(128),

            SPID Smallint,

            SUserName Varchar(128),

            SQL Varchar(Max));

GO


If object_id( 'T_MaakavPerut') IS NOT NULL Drop Table T_MaakavPerut;

Go


Create Table dbo.T_MaakavPerut(Mone Int,

                            Shem Varchar(50),

                            OldMaskoret Int,

                            NewMaskoret Int);

GO

כעת ניצור טריגר על טבלת העובדים שיופעל בכל מקרה של שינוי,

לטבלת המעקב הוא יוסיף את הפרטים של מי ששינה (מועד מדוייק, שם משתמש, עמדה, אפליקציה, פקודת SQL),

ולטבלת מעקב פירוט את הרשומות שהשתנו תוך פירוט – מה השתנה, כאשר שדה Mone יקשר בין שתי הטבלאות (למעשה יש כאן Foreign Key):

If object_id( 'Tr_Ovdim') Is Not Null Drop Trigger Tr_Ovdim;

Go


Create Trigger Tr_Ovdim On T_Ovdim

For Insert, Update, Delete As

Begin


Declare @Tmp Table (EventType Varchar(50), Parameters Int, EventInfo Varchar(Max));

Insert Into @Tmp Exec('DBCC InputBuffer(@@SPID)');


Insert Into T_Maakav(Taarih,UserName,SystemUser,AppName,HostID,HostName,SPID,SUserName,SQL)

Select    GetDate() Taarih,

        User_Name() UserName,

        System_User SystemUser,

        App_Name() AppName,

        Host_ID() HostID,

        Host_Name( ) HostName,

        @@SPID SPID,

        SUser_Name() SUserName,

        EventInfo

From    @Tmp;


Insert Into T_MaakavPerut(Mone, Shem, OldMaskoret, NewMaskoret)

Select    @@Identity,

        IsNull(D.Shem,I.Shem),

        D.Maskoret,

        I.Maskoret

From    Inserted I

Full Join Deleted D

        On I.Shem=D.Shem

End

GO

נאכלס כעת את טבלת העובדים:

Insert Into T_Ovdim

Select    'Geri',1000

Union All

Select    'Ruti',2000

Union All

Select    'Sara',1500

Union All

Select    'Rani',2000

Union All

Select    'Ilana',1800;

Go

וכעת נניח שהעובד Geri שמשכורתו היא הכי נמוכה מחליט לשנות באין רואים את המצב:

את העובדים שמרוויחים 2000 ש"ח הוא מוחק בסתר מהטבלה,

ולעצמו הוא מעלה את המשכורת בנדיבות ל-2000 ש"ח:

Delete From T_Ovdim Where Maskoret=2000;

Go

Update T_Ovdim Set Maskoret=2000 Where Shem='Geri';

Go

אִם בַּמַּחְתֶּרֶת יִמָּצֵא הַגַּנָּב וְהֻכָּה וָמֵת, אֵין לוֹ דָּמִים (שמות כ"ב 1): נעיין בטבלת מעקב ונוכל לראות את שלוש הפעולות שהתבצעו על הטבלה, את פקודת ה-SQL, ואת פרטי העבריין:

Select * From T_Maakav;

ובטבלת מעקב פירוט נוכל לראות לכל אחת מהפעולות הנ"ל אילו שינויים היא כללה:

Select * From T_MaakavPerut;

כדאי לשים לב לכך שלרשומות שהוכנסו מלכתחילה לטבלה אין בטבלת המעקב OldMaskoret מכיוון שלא היה שינוי אלא הוספה,

לרשומות שנמחקו אין NewMaskoret מאותה סיבה,

ורק לרשומה לה שינו את המשכורת יש שני ערכים – ישן וחדש.

למיקרוסופט יש דף עם הפניות לפתרונות לבעיות שונות הקשורות ל-SQL Server ובין היתר יש שם הצעת פתרון ל-How to Audit or Bypass TRIGGER Execution Using CONTEXT_INFO.

הרעיון שם הוא לציין בכל פרוצדורה את ה-Context Info כך שהטריגר יוכל לזהות אותה כשהוא יופעל ולפי זה לדעת מי הפעיל אותו.

הפתרון כבודו במקומו מונח בהנחה שהמתכנתים מקפידים להשתמש בו, אבל לטעמי יתרונו הגדול של הטריגר שלי הוא בין היתר עבור מקרים בהם ה-DBA אינו יודע מאיפה זה בא לו..

הדוגמה שנתתי כוללת פתרון חלקי לכך- היא "לוכדת" את הפקודה שהפעילה את הטריגר בעזרת DBCC (וכמובן את כל הנתונים מסביב) אך לא את שם הפרוצדורה.

השימוש הזה בטריגר מתאים לטבלאות שלא כוללות שינויים תדירים ומסיביים בנתונים: אין טעם לעקוב אחר כל רשומה בטבלאות בנות מליונים.. היא כן מתאימה לטבלאות עם הגדרות רגישות, וטבלאות המעקב יוצרות למעשה תחליף ל-Slowly changing Dimmension כי יש שם תיעוד של כל השינויים בטבלה, כאשר הטבלה עצמה כוללת רק את הנתונים השוטפים.

אם רוצים- ניתן ליצור View שיאחד את הטבלה וטבלאות המעקב למעין Slowly Changing Dimmension.

מודעות פרסומת

תגובה אחת »

  1. מעולה!!
    ההסבר היה מפורט מאוד 🙂
    עזר לי מאוד.
    תודה רבה,
    מיכל

    תגובה של michal — 10/07/2011 @ 11:36


RSS feed for comments on this post. TrackBack URI

להשאיר תגובה

הזינו את פרטיכם בטופס, או לחצו על אחד מהאייקונים כדי להשתמש בחשבון קיים:

הלוגו של WordPress.com

אתה מגיב באמצעות חשבון WordPress.com שלך. לצאת מהמערכת / לשנות )

תמונת Twitter

אתה מגיב באמצעות חשבון Twitter שלך. לצאת מהמערכת / לשנות )

תמונת Facebook

אתה מגיב באמצעות חשבון Facebook שלך. לצאת מהמערכת / לשנות )

תמונת גוגל פלוס

אתה מגיב באמצעות חשבון Google+ שלך. לצאת מהמערכת / לשנות )

מתחבר ל-%s

יצירה של אתר חינמי או בלוג ב־WordPress.com.

%d בלוגרים אהבו את זה: