דוח הרשאות חריגות של משתמשים

31/07/2014

אין תגובות

פרטים על ההרשאות ניתן לקבל על ידי קליקים ימניים ב-SSMS, והם גם נשמרים בטבלאות המערכת, רק שבמקרה זה צריך לפעמים קצת להתאמץ כדי לקבל את התוצאה המבוקשת.
הבעייה שניצבנו בפניה מוכרת לי ממקומות עבודה קודמים: בודקים ומגלים שלמשתמש (Login) פלוני יש הרשאות מסויימות ברמת השרת ו\או ברמת הדטבייס (בכל דטבייס הרשאות אחרות). לכאורה זה שנצפה למצוא ב-Security; אלא שלא כך עובדים: הרשאות יש לתת לקבוצות ולא למשתמשים ספציפיים.
למשל- יוסי מהשיווק זקוק להרשאות כאלה ואחרות כדי לתחקר את הנתונים המעניינים אותו. במקום לתת לו אישית את ההרשאות המתאימות ראוי שניצור קבוצה של אנליסטים או אנשי שיווק, נשייך אותו אליה, וניתן לקבוצה את ההרשאות. היתרון הוא שאם מצטרף אליו מישהו לעבודה – ולו באופן זמני, או שאולי מחליף אותו; לא נצטרך להתחיל לתת לאיש השיווק החדש את כל ההרשאות (ולגרוע אותן כשאחד מהם יסיים את תפקידו). יהיה הרבה יותר נכון לשייך ולנתק בקלות משתמשים מהקבוצה, וכל עוד הם משוייכים אליה – הם יורשים ממנה את כל ההרשאות הרלוונטיות.
בשל כך אנחנו זקוקים לדוח כדי למצוא משתמשים שיש להם הרשאות באופן אישי ולבחון את ההצדקה לכך. במקרים מסויימים יכולה להיות לזה הצדקה, למשל – Login שנוצר עבור Linked Server שפונה אלינו משרת אחר, אך ברוב המקרים – לא.

הסקריפט שלהלן הוא סקריפט דינאמי שפונה לכל הדטבייסים ויוצר שורה לכל Login בכל דטבייס. הוא די מורכב גם מהבחינות האלו (SQL דינאמי ופניה לכל הדטבייסים) וגם מפני שיש צורך להתשמש בפטנט מתאים עם XML כדי לשרשר ל-Login את כל ההרשאות שלו ברמת השרת ואת כל ההרשאות שלו בכל דטבייס בנפרד (כדאי לעיין בפלט של ה-SQL הדינאמי בלשונית ה-Messages כדי להבין מה הסקריפט מבצע):

Declare    @SQL Varchar(Max);

Select    @SQL=IsNull(@SQL+' Union All'+Char(13)+Char(9)+Char(9),'')+Concat('Select    ''',name,''' DB,

                P1.sid,

                IsNull(Stuff((Select    Concat('','',(Select P2.name Collate database_default DB_Role From [',name,'].sys.database_principals P2 Where M1.role_principal_id=P2.principal_id)) As [text()]

                From    [',name,'].sys.database_role_members M1

                Where    M1.member_principal_id=M.member_principal_id

                Order By SUser_Name(M1.role_principal_id)

                For XML Path('''')),1,1,''''),''public'') DB_Role

        From    [',name,'].sys.database_role_members M

        Right Join [',name,'].sys.database_principals P1

                On P1.principal_id=M.member_principal_id

        Group By P1.sid,M.member_principal_id')

From    sys.databases;

Select    @SQL=Concat('Select    S.name,

        S.type_desc,

        D.DB,

        M.Server_Role,

        D.DB_Role

From sys.server_principals S

Left Join (',@SQL,') D

        On S.sid=D.sid

Left Join (Select    member_principal_id,

                Stuff((Select    '',''+SUser_Name(M1.role_principal_id) As [text()]

                From    sys.server_role_members M1

                Where    M1.member_principal_id=M.member_principal_id

                Order By SUser_Name(M1.role_principal_id)

                For XML Path('''')),1,1,'''') Server_Role

        From    sys.server_role_members M

        Group By member_principal_id) M

        On S.principal_id=M.member_principal_id

Where    S.type_desc In (''SQL_LOGIN'',''WINDOWS_LOGIN'')

        And S.name Not Like ''##%%##''

        And S.name Not Like ''NT %\%''

        And S.name<>''sa''

Order By Lower(S.name),

        Lower(D.DB);')

Print    @SQL;

Exec(@SQL);

Go

image

(השמטתי את שמות ה-Logins מסיבות ברורות)

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

הוסף תגובה
facebook linkedin twitter email

כתיבת תגובה

האימייל לא יוצג באתר. שדות החובה מסומנים *