ניהול משתמשים יתומים ב- SQL Server: מדריך מקיף

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

רקע

כדי לחבר למסד נתונים של SQL Server, על משתמש להיות בעל כניסה תקפה בבסיס הנתונים הראשי. כניסה זו מאמתת את המשתמש וממפה אותו למשתמש בבסיס הנתונים לצורך_authorization. המיפוי מתוחזק באמצעות מזהה אבטחה (SID), המבטיח כי משתמשי בסיס הנתונים וכניסות השרת מחוברות כראוי.

חריגות מהכלל של מיפוי כניסה-משתמש כוללות:

1. משתמשי בסיס נתונים כלואים: מאומתים ברמת בסיס הנתונים, מסלקים תלות על כניסות השרת. משתמשים אלו משפרים את ניידות בסיס הנתונים אך דורשים שיחזור נפרד לכל בסיס נתונים.
2. חשבונות אורח: מופעלים כברירת מחדל, מאפשרים גישה למשתמשים ללא מיפוי מפורש אך בדרך כלל לא מומלצים מסיבות אבטחה.
3. חברויות בקבוצות Microsoft Windows: חברי קבוצות Windows יכולים לגשת לבסיסי נתונים אם הקבוצה מתווספת כמשתמשים.

משתמש בבסיס הנתונים הופך ליתום כאשר:

• הכניסה המתאימה נמחקת.
• המסד נתונים מועבר או משוחזר ללא הכניסות הקשורות.
• המיפוי של ה-SID בין המשתמש לכניסה שגוי.

מהם משתמשים יתומים?

משתמש יתום קורה כאשר:

• משתמש בבסיס הנתונים קיים, אך הכניסה המתאימה ב-SQL Server בבסיס הנתונים הראשי אינה קיימת.
• SID של משתמש בסיס הנתונים ו-SID של הכניסה אינם תואמים.

תסריטים אלו מפריעים לאימות ולהרשאה, מה שמוביל לשגיאות או גישה מוגבלת.

זיהוי משתמשים יתומים

עבור SQL Server

השתמש בשאילתא הבאה כדי לזהות משתמשים יתומים:

SELECT dp.type_desc, dp.sid, dp.name AS user_name  

FROM sys.database_principals AS dp  

LEFT JOIN sys.server_principals AS sp  

    ON dp.sid = sp.sid  

WHERE sp.sid IS NULL  

    AND dp.authentication_type_desc = 'INSTANCE';

עבור Azure SQL Database או Synapse Analytics

1. שלוף את ה-SIDs של הכניסות בבסיס הנתונים הראשי:

SELECT sid 

FROM sys.sql_logins 

WHERE type = 'S'; 

2. שלוף את ה-SIDs של המשתמשים בבסיס הנתונים היעד:

SELECT name, sid, principal_id

FROM sys.database_principals 

WHERE type = 'S' 

  AND name NOT IN ('guest', 'INFORMATION_SCHEMA', 'sys')

  AND authentication_type_desc = 'INSTANCE';

3. השווה את הרשימות כדי לזהות חוסר תיאום.

פתרון משתמשים יתומים

1. שיחזור כניסה חסרה עם SID 

אם הכניסה נמחקה:

CREATE LOGIN <login_name>   

WITH PASSWORD = '<use_a_strong_password_here>',  

SID = <SID>;

2. מפתחים משתמש יתום לכניסה קיימת 

אם הכניסה קיימת אך לא ממופה:

ALTER USER <user_name> WITH LOGIN = <login_name>;

3. שנה את סיסמת המשתמש 

לאחר שחזור הכניסה, עדכן את הסיסמה שלה:

ALTER LOGIN <login_name> WITH PASSWORD = '<enterStrongPasswordHere>';

באמצעות sp_FindOrphanedUser

הפרוצדורה השמורה sp_FindOrphanedUser היא כלי חזק לזיהוי ופתרון משתמשים יתומים. היא מספקת:

1. רשימה של משתמשים יתומים.
2. סקריפטים שנוצרו אוטומטית לתיקונים.
3. פרטי סכמות בבעלות משתמשים יתומים.

קבל את קוד החבילה מ־sp_FindOrphanedUser.

הרצת ההליך

ביצוע ברירת מחדל:

EXEC sp_FindOrphanedUser;

למסד נתונים ספציפי:

EXEC sp_FindOrphanedUser @DatabaseName = '<DatabaseName>';

דוגמאות לתסריטים של משתמשים יתומים

דוגמה 1: משתמש ללא כניסה

תסריט

כניסה נמחקת, ומשאירה את משתמש מסד הנתונים יתום.

• גלה את המשתמש היתום:
  MS SQL

   

  EXEC sp_FindOrphanedUser;

• יצירת כניסה:
  MS SQL

   

  CREATE LOGIN [User1] WITH PASSWORD = 'SecurePassword123';

  USE [ExampleDB];

  ALTER USER [User1] WITH LOGIN = [User1];

דוגמה 2: אי התאמה של SID

תסריט

ה-SID של ההתחברות אינו תואם את ה-SID של המשתמש.

• גלה את חוסר ההתאמה:
  MS SQL

   

  EXEC sp_FindOrphanedUser @DatabaseName = 'ExampleDB';

• תקן את מיפוי ה-SID:
  MS SQL

   

  שימוש [ExampleDB];

  EXEC sp_change_users_login 'UPDATE_ONE', 'User2', 'User2';

דוגמה 3: משתמש יתום בעל סכימה

תסריט

משתמש יתום מונע מחיקת סכימה.

• גלה בעלות על סכמות:
  MS SQL

   

  EXEC sp_FindOrphanedUser @DatabaseName = 'ExampleDB';

• הקצה מחדש בעלות על הסכמה:
  MS SQL

   

  שימוש [ExampleDB];

  שנה בעלות על הסכמה::[User3Schema] ל-[dbo];

  מחק משתמש [User3];

שיטות עבודה מומלצות

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

סיכום

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