none
DB SQL מרובה סכמות RRS feed

  • שאלה

  • שלום לכולם!

    יש לי DB עם הרבה סכמות.

    לכל יוזר שנוצר, נוצרות לו חלק מהטבלאות על הסכמה שלו, למשל ליוזר -1 נוצר טבלה : customer1.orders.

    (זה הDBA החליט ככה כדי ליעל)

    אני משתמשת בד"כ עם entity framework, אבל כשאני בודקת מה עושים עם ריבוי סכמות בEF, נראה לי שמבחינת הביצועים תהיה פה בעיה...

    השאלה שלי, היא, מהי הדרך הנכונה לעשות במצב כזה? להשתמש בprepare statement ושאילתות?

    נראה לי שהיא גם מהירה יותר מEF, נכון?

    תודה!!!
    שרה.


    יום שלישי 21 פברואר 2017 08:59

תשובות

  • יש לך כאן דיי הרבה שאלות טובות :-)

    אני אתחיל מהשאלה השניה של פרצת האבטחה

    מה שיש לך כאן זה פרצת אבטחה בסיסית של SQL INJECTION

    בואי נבין למה:

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

    את מצפה שהמשתמש יעביר ערך של שם schema ולכן אם למשל המשתמש מעביר את הערך Schema_Name אז את מצפה לקבל שאיתה שניראית בערך כך (כאמור אני מתעלם רק\גע מהחלק של הסינון וה ID):

    SELECT * FROM [Schema_Name].[Company] c

    אבל שימי לב מה קורה אם המשתמש יעביר את הערך המוזר הבא:

    Schema_Name].[Company] c; delete [Schema_Name

    הפרוצדורה שלך מסרשרת את הטקס כדי ליצור שאילתה אחת ולכן השאילתה שאת תקבלי ותריצי תהיה:

     
    SELECT * FROM [Schema_Name].[Company] c; delete [Schema_Name].[Company] c 

    ואני חושב שהתוצאה ברורה לך :-)

    אני מאוד ממליץ לקרוא יותר על נושא SQL INJECTION. זה נושא מאוד מאוד מיושן שהיום שום מפתח לא אמור לסבול ממנו מפני שהפתרונות כל כך קלים וכולם אמורים להיות מודעים לבעיות ולפתרונות כבר בשלבי לימוד מוקדמים.

    אז מה הפתרון?

    כמו שאמרתי אני לא יכול להמליץ על הפתרון המתאים מכיוון שאני לא מכיר את המערכת שלכם אבל אני יכול לתת דוגמה שהזכרתי.

    1. בקריאה לפרוצדורה תמיד צריך לעבוד עם פרמטרים ולא עם שרשור טקסט בצד האפליקציה

    2. בפרוצדורה עצמה יש לבדוק את הערך שמקבלים ולא לשרשרת אותו כפי שהוא

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

    בצורה כזו אם המשתמש מעביר ערך מוזר כפי שכתבתי מעל הרי שלא תמצאי סכמה בשם זה.

    נעבור לשאלה של הפרדת רשויות (סליחה סתם שם טיפשי שאני קורא להפרדת השכבות)

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

    נעבור לשאלה של יצירת פרוצדורה שמקבלת פרמטר ומריצה פרוצדורות אחרות בהתאם.

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

    הרעיון הוא פשוט ליצור פרוצדורה שמקבל פרמטר והיא מריצה פרוצדורה אחרת בהתאם

    למשל נניח שהפרוצדורה הראשית מקבלת את הפרמטר MyParam אז בתוך הפרוצשורה הראשית נעזר ב IF או CASE על מנת לבחור איזה פרוצדורה אנחנו רוצי להפעיל

       IF @MyParam = 'schema1'
          EXECUTE sp_Schema1;
       ELSE IF param = 'schema2'
          EXECUTE sp_Schema2;
       ELSE
          EXECUTE sp_Schema999;

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

    בפרוצדורות עצמן אין צורך עתה בשאילתה שניבנית דינאמית ואפשר להריץ שאילתה פשוטה עם פרמטר. למשל בפרוצדורה בשם sp_Schema1 נריץ את השאילתה הפשוטה

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

    ALTER PROCEDURE [dbo].[sp_Schema1] @ID int AS BEGIN
        SELECT * FROM [sp_Schema1].[Company] WHERE [ID]= @ID
    END
    

    כל ההבדל בין הפרוצדורות השונות יהיה בשאילתה עצמה. בפרוצדורה sp_Schema2 נשמש בשאילתה שפונה לסכמה sp_Schema2 וכן הלאה

    * כמובן שלא בדקתי בדיוק את הקוד וזה רק להדגמה על בסיס הקודששלחת לנו :-)

    אני מקווה שזה מסביר את הנושאים :-)


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    • סומן כתשובה על-ידי ssfrank יום רביעי 01 מרץ 2017 14:19
    יום רביעי 01 מרץ 2017 14:01
    מנחה דיון

כל התגובות

  • שלום לכולם!

    יש לי DB עם הרבה סכמות.

    לכל יוזר שנוצר, נוצרות לו חלק מהטבלאות על הסכמה שלו, למשל ליוזר -1 נוצר טבלה : customer1.orders.

    (זה הDBA החליט ככה כדי ליעל)

    אני משתמשת בד"כ עם entity framework, אבל כשאני בודקת מה עושים עם ריבוי סכמות בEF, נראה לי שמבחינת הביצועים תהיה פה בעיה...

    השאלה שלי, היא, מהי הדרך הנכונה לעשות במצב כזה? להשתמש בprepare statement ושאילתות?

    נראה לי שהיא גם מהירה יותר מEF, נכון?

    תודה!!!

    שרה.

    בוקר טוב

    כשלב ראשוני את צריכה למחוק את המושג ORM אם את רוצה לדבר על מיטוב ביצועים :-)

    * EF הוא סוג של ORM ז"א Object-relational mapping שהיא סביבת פיתוח שנועדה לבצע את מיפוי האובייקטים שלנו באפליקציה למסד הנתונים.

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

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

    תיעוד קצת ישן אבל עדיין רלוונטי ומומלץ לקרוא בנושא תוכלי למצוא כאן:
    https://technet.microsoft.com/en-us/library/dd283095(v=sql.100).aspx

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

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

    [Schema = "Ordering"] 
    public class MyContext:DbContext
    {
        public MyContext()
            : base("name=ConnectionString")
        {
        }
    
        public DbSet<Student> Students { get; set; }
    }

    * בקיצור, עבודה עם סכמה ב EF יכולה להעשות בכצמה דרכים (כפי שהראתי מעל או באירוע של יצירת המודל OnModelCreating) או באמצעות HasDefaultSchema שמתאים החל מגרסת EF 6.  

    ** באופן מסורתי אכן כתיבת שאילתות וניהול ידני מתאים יותר למיטוב ביצועים.


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]


    יום רביעי 22 פברואר 2017 08:16
    מנחה דיון
  • תודה רבה!!!!!!

    ממש רציתי שדווקא אתה תענה, כי אני יודעת שאתה מבין בזה... :)
    כ"כ יפה מצדך תמיד!!!

    הבעיה שהתכוונתי בביצועים, זה שבד"כ הEF מגדיר לעצמו את הconnectionstring והכל פעם אחת בעליה של המערכת וא"א לשנות אותו.

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

    מצאתי משהו שכן מאפשר לשנות את הסכמה בזמן ריצה (ובאמת זה מה שעשיתי):

    http://efmodeladapter.codeplex.com/

    אבל זה הרבה יותר מאט...

    עוד שאלה בקשר לשאילתות, יש לי 2 אפשרויות:

    1. ליצור לכל סכמה את מקבץ השאילתות שלה, וכל פעם שאני יוצרת סכמה חדשה, היא תיצור ביחד איתה את כל השאילתות

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

    מה יהיה יותר טוב?

    שוב תודה!!!

    שרה.

    יום רביעי 22 פברואר 2017 09:39
  • אהלן,

    ההפרדה לסכמות היא לא הגורם היישר להאטה. היא יכולה להיות גורם עקיף (למשל תוכניות הרצה שונות)

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

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

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

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

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

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


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    יום חמישי 23 פברואר 2017 10:08
    מנחה דיון
  • תודה על ההסבר המפורט!!

    משהו שלא הבנתי פה, מה הכוונה:"ליצור פרוצדורה אחת שמקבלת פרמטר ולפי הפרמטר מריצה פרוצדורה מתאימה"

    יש לי פרוצדורה פשוטה לדוג':

    ALTER PROCEDURE [dbo].[Company_GetById]
    	@Schema NVARCHAR(10),
    	@ID int
    AS
    BEGIN
    
    
    declare @sql nvarchar(max)
    set @sql = 'SELECT * FROM ['+@Schema+'].[Company] c
    	WHERE [ID]='+CAST(@ID as varchar(10))
      
    EXEC (@sql)
    
    END

    איך אני צריכה לשנות כדי שלא תהיה פרצת אבטחה? ואיך אני יכולה לתת לה להריץ בצורה גמישה , אבל בלי לשרשר?

    אני בונה את השכבה של הגישה לDB בצור נפרדת לגמרי מהאפליקציה, ככה שמודל השכבות נשמר, ואם רוצים לשנות את הצורה יהיה מאד קל.

    תודה!!!

    שרה.

    יום שני 27 פברואר 2017 10:51
  • יש לך כאן דיי הרבה שאלות טובות :-)

    אני אתחיל מהשאלה השניה של פרצת האבטחה

    מה שיש לך כאן זה פרצת אבטחה בסיסית של SQL INJECTION

    בואי נבין למה:

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

    את מצפה שהמשתמש יעביר ערך של שם schema ולכן אם למשל המשתמש מעביר את הערך Schema_Name אז את מצפה לקבל שאיתה שניראית בערך כך (כאמור אני מתעלם רק\גע מהחלק של הסינון וה ID):

    SELECT * FROM [Schema_Name].[Company] c

    אבל שימי לב מה קורה אם המשתמש יעביר את הערך המוזר הבא:

    Schema_Name].[Company] c; delete [Schema_Name

    הפרוצדורה שלך מסרשרת את הטקס כדי ליצור שאילתה אחת ולכן השאילתה שאת תקבלי ותריצי תהיה:

     
    SELECT * FROM [Schema_Name].[Company] c; delete [Schema_Name].[Company] c 

    ואני חושב שהתוצאה ברורה לך :-)

    אני מאוד ממליץ לקרוא יותר על נושא SQL INJECTION. זה נושא מאוד מאוד מיושן שהיום שום מפתח לא אמור לסבול ממנו מפני שהפתרונות כל כך קלים וכולם אמורים להיות מודעים לבעיות ולפתרונות כבר בשלבי לימוד מוקדמים.

    אז מה הפתרון?

    כמו שאמרתי אני לא יכול להמליץ על הפתרון המתאים מכיוון שאני לא מכיר את המערכת שלכם אבל אני יכול לתת דוגמה שהזכרתי.

    1. בקריאה לפרוצדורה תמיד צריך לעבוד עם פרמטרים ולא עם שרשור טקסט בצד האפליקציה

    2. בפרוצדורה עצמה יש לבדוק את הערך שמקבלים ולא לשרשרת אותו כפי שהוא

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

    בצורה כזו אם המשתמש מעביר ערך מוזר כפי שכתבתי מעל הרי שלא תמצאי סכמה בשם זה.

    נעבור לשאלה של הפרדת רשויות (סליחה סתם שם טיפשי שאני קורא להפרדת השכבות)

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

    נעבור לשאלה של יצירת פרוצדורה שמקבלת פרמטר ומריצה פרוצדורות אחרות בהתאם.

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

    הרעיון הוא פשוט ליצור פרוצדורה שמקבל פרמטר והיא מריצה פרוצדורה אחרת בהתאם

    למשל נניח שהפרוצדורה הראשית מקבלת את הפרמטר MyParam אז בתוך הפרוצשורה הראשית נעזר ב IF או CASE על מנת לבחור איזה פרוצדורה אנחנו רוצי להפעיל

       IF @MyParam = 'schema1'
          EXECUTE sp_Schema1;
       ELSE IF param = 'schema2'
          EXECUTE sp_Schema2;
       ELSE
          EXECUTE sp_Schema999;

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

    בפרוצדורות עצמן אין צורך עתה בשאילתה שניבנית דינאמית ואפשר להריץ שאילתה פשוטה עם פרמטר. למשל בפרוצדורה בשם sp_Schema1 נריץ את השאילתה הפשוטה

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

    ALTER PROCEDURE [dbo].[sp_Schema1] @ID int AS BEGIN
        SELECT * FROM [sp_Schema1].[Company] WHERE [ID]= @ID
    END
    

    כל ההבדל בין הפרוצדורות השונות יהיה בשאילתה עצמה. בפרוצדורה sp_Schema2 נשמש בשאילתה שפונה לסכמה sp_Schema2 וכן הלאה

    * כמובן שלא בדקתי בדיוק את הקוד וזה רק להדגמה על בסיס הקודששלחת לנו :-)

    אני מקווה שזה מסביר את הנושאים :-)


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    • סומן כתשובה על-ידי ssfrank יום רביעי 01 מרץ 2017 14:19
    יום רביעי 01 מרץ 2017 14:01
    מנחה דיון
  • עכשיו הבנתי, 

    תודה רבה!!!!!

    יום רביעי 01 מרץ 2017 14:19
  • בבקשה :-)

    אני שמח שיכולתי לעזור


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    יום רביעי 01 מרץ 2017 16:19
    מנחה דיון