שימוש בתצוגות מאובטחות עם פרמטרים

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

מידע נוסף זמין במאמר בנושא תצוגות מאובטחות עם פרמטרים ב-Cloud SQL.

לפני שמתחילים

במסמך הזה אנחנו מניחים שכבר יצרתם מכונת Cloud SQL ל-MySQL.

1. מוודאים שבמכונה של Cloud SQL פועלת גרסה MySQL 8.0.43 ואילך. אם המכונה שלכם עם MySQL 8.0 פועלת בגרסה משנית קודמת, תוכלו לבצע שדרוג של גרסה משנית.

2. מפעילים את הדגל של מסד הנתונים cloudsql_parameterized_secure_view במופע.

   השינוי הזה בדגל לא מחייב הפעלה מחדש של מסד הנתונים. מידע נוסף זמין במאמר הגדרת דגלים של מסד נתונים.

יצירת תצוגה מאובטחת עם פרמטרים

כדי ליצור תצוגה מאובטחת עם פרמטרים, מריצים את פקודת ה-DDL‏ CREATE VIEW. לדוגמה:

CREATE VIEW v_orders
AS SELECT * FROM orders
WHERE customer_id = @local_customer_id;

משתמשים בתחביר הבא:

-- Create a view with a parameter using the WHERE clause
CREATE VIEW VIEW_NAME AS
SELECT * FROM TABLE_NAME
WHERE CONDITION;

-- Create a view with a parameter using the HAVING clause
CREATE VIEW VIEW_NAME AS
SELECT * FROM TABLE_NAME
WHERE CONDITION_1      -- row level security
GROUP BY COLUMN_NAME
HAVING CONDITION_2;    -- grouping

-- Create a view with a parameter using the ON clause
CREATE VIEW VIEW_NAME AS
SELECT * FROM TABLE_NAME_1
JOIN TABLE_NAME_2
ON CONDITION_1       --  join criteria
WHERE CONDITION_2;   --  row level security

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

• VIEW_NAME: השם של התצוגה המאובטחת עם הפרמטרים
• ‫TABLE_NAME או TABLE_NAME_N: השם של הטבלה או הטבלאות שרוצים להשתמש בהן בתצוגה המפורטת עם הפרמטרים
• ‫COLUMN_NAME או COLUMN_NAME_N: שם העמודה או העמודות בטבלה שבהן רוצים להשתמש בתצוגה עם פרמטרים

• ‫CONDITION או CONDITION_N: המשפט או המשפטים של התנאי שצריך להעריך כחלק מסעיף WHERE, ON או HAVING. אפשר לציין את משתנה הסשן שמשמש כפרמטר כשמריצים בהמשך שאילתה בתצוגה. תנאי נראה כך:

  WHERE customer_id = @local_customer_id

  משתני סשן, @variable_name, מותרים רק בסעיפים WHERE, ON או HAVING של הצהרת CREATE VIEW.

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

כדי לשלוח שאילתה לתצוגה מאובטחת עם פרמטרים ב-Cloud SQL ל-MySQL, יש שתי אפשרויות:

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

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

שימוש בערכי רמז

כדי לשלוח שאילתה לתצוגה מאובטחת עם פרמטרים באמצעות הרמז SET_VIEW_VARS:

-- Set the parameter and query
SELECT /*+ SET_VIEW_VARS(SESSION_VARIABLE = VALUE) */ *
FROM VIEW_NAME;

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

• ‫SESSION_VARIABLE: הפרמטר בעל השם של התצוגה המאובטחת עם הפרמטרים שאפשר לספק לו ערכים שונים. אתם מגדירים את שם המשתנה של הסשן כשאתם יוצרים את התצוגה המפורטת באמצעות הסימון @variable_name בסעיף התנאים של התצוגה המפורטת.
• ‫VALUE: ערך לפרמטר בעל שם
• ‫VIEW_NAME: שם התצוגה המאובטחת עם הפרמטרים

לדוגמה:

SELECT /*+ SET_VIEW_VARS(local_customer_id = 12345) */ *
FROM v_orders;

שימוש בהליך של שאילתה עם פרמטרים

כדי לשלוח שאילתה לתצוגה מאובטחת עם פרמטרים באמצעות הפרוצדורה mysql.execute_parameterized_query, משתמשים בתחביר הבא:

CALL mysql.execute_parameterized_query(
  'SELECT * FROM DATABASE_NAME.VIEW_NAME',
  'SESSION_VARIABLE=VALUE');

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

• ‫SESSION_VARIABLE: הפרמטר בעל השם של התצוגה המאובטחת עם הפרמטרים שאפשר לספק לו ערכים שונים. אתם מגדירים את שם המשתנה של הסשן כשאתם יוצרים את התצוגה המפורטת באמצעות הסימון @variable_name בסעיף התנאים של התצוגה המפורטת.
• ‫VALUE: ערך לפרמטר בעל שם
• ‫DATABASE_NAME: שם מסד הנתונים של התצוגה
• ‫VIEW_NAME: שם התצוגה המאובטחת עם הפרמטרים

לדוגמה:

   CALL mysql.execute_parameterized_query(
    'SELECT * FROM db.v_orders',
    'local_customer_id = 5, earliest_year = 2021');

הגבלות על שאילתות

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

• השאילתה יכולה להיות רק הצהרת SELECT.
• השאילתה לא יכולה לכלול קריאות לפונקציות שהוגדרו על ידי המשתמש או לפרוצדורות מאוחסנות.
• השאילתה לא יכולה להקצות משתנים.
• אם משתמשים בשיטה CALL mysql.execute_parameterized_query, השאילתה לא יכולה להיות מספר הצהרות ששורשרו להצהרה אחת. לדוגמה, משפט השאילתה SELECT * FROM a; DROP TABLE a; לא תקין.

צפייה בתצוגות מאובטחות עם פרמטרים ביומנים

כשמשתמשים במסד נתונים של Cloud SQL ל-MySQL מנסים ליצור תצוגה מאובטחת עם פרמטרים, הודעה ברמת המידע נרשמת ביומן השגיאות של MySQL, בדומה להודעה הבאה:

 User variables permitted in %s.%s through Parameterized
 Secure View feature

פתרון בעיות בתצוגות מאובטחות עם פרמטרים

SHOW VARIABLES LIKE 'cloudsql_parameterized_secure_view';

המאמרים הבאים

• סקירה כללית על תצוגות מאובטחות עם פרמטרים

• סקירה כללית בנושא קבוצות הקשר