יצירה וניהול של תצוגות

בדף הזה מוסבר איך ליצור ולנהל תצוגות של Spanner במסדי נתונים עם ניב GoogleSQL ובמסדי נתונים עם ניב PostgreSQL. למידע נוסף על תצוגות ב-Spanner, ראו סקירה כללית על תצוגות.

הרשאות

כדי ליצור, להעניק ולבטל גישה לתצוגה, נדרשת ההרשאה spanner.database.updateDdl.

יצירת תצוגה

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

  • ‫CREATE VIEW מגדיר תצוגה חדשה במסד הנתונים הנוכחי. אם כבר קיימת תצוגה בשם view_name, ההצהרה CREATE VIEW תיכשל.

  • ‫CREATE OR REPLACE VIEW מגדיר תצוגה חדשה במסד הנתונים הנוכחי. אם כבר קיימת תצוגה בשם view_name, ההגדרה שלה תוחלף.

התחביר של ההצהרה CREATE VIEW הוא:

{CREATE | CREATE OR REPLACE } VIEW  view_name
SQL SECURITY { INVOKER | DEFINER }
AS query

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

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

צריך לציין את הערך של SQL SECURITY בתור INVOKER או DEFINER בהצהרה CREATE VIEW או CREATE OR REPLACE VIEW. למידע נוסף על ההבדל בין שני סוגי האבטחה, קראו את הסקירה הכללית על תצוגות.

לדוגמה, נניח שהטבלה Singers מוגדרת באופן הבא:

GoogleSQL 

CREATE TABLE Singers (
  SingerId   INT64 NOT NULL,
  FirstName  STRING(1024),
  LastName   STRING(1024),
  SingerInfo BYTES(MAX)
) PRIMARY KEY (SingerId);

PostgreSQL 

CREATE TABLE Singers (
  SingerId   BIGINT PRIMARY KEY,
  FirstName  VARCHAR(1024),
  LastName   VARCHAR(1024),
  SingerInfo BYTEA
);

כך מגדירים את התצוגה SingerNames עם הרשאות של מפעיל:

CREATE VIEW SingerNames
SQL SECURITY INVOKER
AS SELECT
   Singers.SingerId AS SingerId,
   Singers.FirstName || ' ' || Singers.LastName AS Name
FROM Singers;

לטבלה הווירטואלית שנוצרת כשמשתמשים בתצוגה SingerNames בשאילתה יש שתי עמודות, SingerId ו-Name.

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

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

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

בדוגמה הבאה, התצוגה עם הפרמטרים מעניקה למשתמש באפליקציה גישה לעמודות SingerId,‏ FirstName ו-LastName מהטבלה Singers. התצוגה מגבילה את התוצאות לשורות שבהן UserID תואם לפרמטר הנדרש, app_end_userid.

  CREATE VIEW SingerNames
  SQL SECURITY DEFINER
  AS SELECT
   Singers.SingerId AS SingerId,
   Singers.FirstName || ' ' || Singers.LastName AS Name
  FROM Singers
  WHERE UserID = CAST(SECURE_CONTEXT('app_end_userid'), INT64);

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

שיטות מומלצות ליצירת תצוגות

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

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

CREATE VIEW SingerNames
SQL SECURITY INVOKER
AS SELECT
   Singers.SingerId AS SingerId,
   Singers.FirstName || ' ' || Singers.LastName AS Name
FROM Singers;

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

GoogleSQL 

CREATE OR REPLACE VIEW SingerNames
SQL SECURITY INVOKER
AS SELECT
 CAST(Singers.SingerId AS INT64) AS SingerId,
 CAST(Singers.FirstName AS STRING) || " " || CAST(Singers.LastName AS STRING) AS Name
FROM Singers;

PostgreSQL 

CREATE OR REPLACE VIEW SingerNames
SQL SECURITY INVOKER
AS SELECT
 CAST(Singers.SingerId AS bigint) AS SingerId,
 CAST(Singers.FirstName AS varchar) || ' ' || CAST(Singers.LastName AS varchar) AS Name
FROM Singers;

הענקה וביטול של הרשאות גישה לתצוגה

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

GoogleSQL 

GRANT SELECT ON VIEW SingerNames TO ROLE Analyst;

PostgreSQL 

GRANT SELECT ON TABLE SingerNames TO Analyst;

כדי לבטל את ההרשאה SELECT בתצוגה מתפקיד במסד הנתונים:

GoogleSQL 

REVOKE SELECT ON VIEW SingerNames FROM ROLE Analyst;

PostgreSQL 

REVOKE SELECT ON TABLE SingerNames FROM Analyst;

שליחת שאילתה לתצוגה

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

שליחת שאילתה לתצוגה עם זכויות של מפעיל

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

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

SELECT COUNT(SingerID) as SingerCount
FROM SingerNames;

שליחת שאילתה לתצוגה של זכויות המגדיר

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

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

SELECT COUNT(SingerID) as SingerCount
FROM SingerNames;

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

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

החלפת תצוגה

כדי להחליף תצוגה, אפשר להשתמש בהצהרה CREATE OR REPLACE VIEW כדי לשנות את הגדרת התצוגה או את סוג האבטחה שלה.

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

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

CREATE OR REPLACE VIEW SingerNames
SQL SECURITY DEFINER
AS SELECT
   Singers.SingerId AS SingerId,
   Singers.FirstName || ' ' || Singers.LastName AS Name
FROM Singers;

מחיקת תצוגה

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

DROP VIEW SingerNames;

איך מקבלים מידע על תצוגה

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

  • בטבלה INFORMATION_SCHEMA.TABLES מופיעים השמות של כל התצוגות שהוגדרו.

  • הטבלה INFORMATION_SCHEMA.VIEWS מכילה את השמות, הגדרת התצוגה, סוג האבטחה וטקסט השאילתה של כל התצוגות המוגדרות. משתמשי FGAC שיש להם הרשאת SELECT בתצוגה יכולים לקבל מידע על התצוגה מהטבלה INFORMATION_SCHEMA.VIEWS. משתמשי FGAC אחרים צריכים את התפקיד spanner_info_reader אם אין להם את ההרשאה SELECT לתצוגה.

כדי לבדוק את הגדרת התצוגה וסוג האבטחה של תצוגה בשם ProductSoldLastWeek:

  SELECT *
  FROM INFORMATION_SCHEMA.VIEWS
  WHERE TABLE_NAME = 'ProductSoldLastWeek';