Membuat dan mengelola tabel virtual

Halaman ini menjelaskan cara membuat dan mengelola tampilan Spanner untuk database dialek GoogleSQL dan database dialek PostgreSQL. Untuk mengetahui informasi selengkapnya tentang tampilan Spanner, lihat Ringkasan tampilan.

Izin

Untuk membuat, memberikan, dan mencabut akses ke tampilan, Anda harus memiliki izin spanner.database.updateDdl.

Membuat tampilan

Untuk membuat tampilan, gunakan pernyataan DDL CREATE VIEW untuk memberi nama tampilan dan memberikan kueri yang menentukannya. Pernyataan ini memiliki dua bentuk:

  • CREATE VIEW menentukan tampilan baru dalam database saat ini. Jika tampilan bernama view_name sudah ada, pernyataan CREATE VIEW akan gagal.

  • CREATE OR REPLACE VIEW menentukan tampilan baru dalam database saat ini. Jika tampilan bernama view_name sudah ada, definisinya akan diganti.

Sintaksis untuk pernyataan CREATE VIEW adalah:

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

Karena tampilan adalah tabel virtual, query yang Anda tentukan harus memberikan nama untuk semua kolom dalam tabel virtual tersebut.

Selain itu, Spanner memeriksa query yang Anda tentukan menggunakan resolusi nama yang ketat, yang berarti bahwa semua nama objek skema yang digunakan dalam kueri harus memenuhi syarat sehingga mengidentifikasi satu objek skema secara jelas. Misalnya, dalam contoh berikut, kolom SingerId dalam tabel Singers harus dikualifikasi sebagai Singers.SingerId.

Anda harus menentukan SQL SECURITY sebagai INVOKER atau DEFINER dalam pernyataan CREATE VIEW atau CREATE OR REPLACE VIEW. Untuk mengetahui informasi selengkapnya tentang perbedaan antara kedua jenis keamanan, lihat Ringkasan tampilan.

Misalnya, asumsikan tabel Singers ditentukan seperti yang ditunjukkan berikut ini:

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
);

Anda dapat menentukan tampilan SingerNames dengan hak invoker seperti yang ditunjukkan berikut ini:

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

Tabel virtual yang dibuat saat tampilan SingerNames digunakan dalam kueri memiliki dua kolom, SingerId dan Name.

Meskipun definisi tampilan SingerNames ini valid, definisi ini tidak mematuhi praktik terbaik dalam mentransmisikan jenis data untuk memastikan stabilitas di seluruh perubahan skema, seperti yang dijelaskan di bagian berikutnya.

Membuat tampilan berparameter

Untuk membuat tampilan berparameter, sertakan fungsi SECURE_CONTEXT() dalam klausa WHERE dari pernyataan SELECT.

Dalam contoh berikut, tampilan berparameter memberikan akses kepada pengguna aplikasi ke kolom SingerId, FirstName, dan LastName dari tabel Singers. Tabel virtual membatasi hasil ke baris tempat UserID cocok dengan parameter yang diperlukan, 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);

Untuk menggunakan tampilan berparameter, Anda harus memiliki hak istimewa SELECT pada tampilan. Untuk mengetahui informasi selengkapnya, lihat Memberikan dan mencabut akses ke tampilan.

Praktik terbaik saat membuat tampilan

Untuk meminimalkan kebutuhan untuk memperbarui definisi tampilan, transmisikan jenis data semua kolom tabel secara eksplisit dalam kueri yang menentukan tampilan. Saat Anda melakukannya, definisi tampilan dapat tetap valid di seluruh perubahan skema pada jenis kolom.

Misalnya, definisi tampilan SingerNames berikut mungkin menjadi tidak valid akibat perubahan jenis data kolom dalam tabel Singers.

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

Anda dapat menghindari tampilan menjadi tidak valid dengan mentransmisikan kolom secara eksplisit ke jenis data yang diperlukan, seperti yang ditunjukkan dalam contoh berikut:

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;

Memberikan dan mencabut akses ke tampilan

Sebagai pengguna kontrol akses terperinci, Anda harus memiliki hak istimewa SELECT pada tampilan. Untuk memberikan hak istimewa SELECT pada tampilan ke peran database:

GoogleSQL 

GRANT SELECT ON VIEW SingerNames TO ROLE Analyst;

PostgreSQL 

GRANT SELECT ON TABLE SingerNames TO Analyst;

Untuk mencabut hak istimewa SELECT pada tampilan dari peran database:

GoogleSQL 

REVOKE SELECT ON VIEW SingerNames FROM ROLE Analyst;

PostgreSQL 

REVOKE SELECT ON TABLE SingerNames FROM Analyst;

Membuat kueri tampilan

Cara membuat kueri tampilan hak invoker atau hak definer adalah sama. Namun, bergantung pada jenis keamanan tampilan, Spanner mungkin perlu atau tidak perlu memeriksa objek skema yang direferensikan dalam tampilan terhadap peran database akun utama yang memanggil kueri.

Membuat kueri tampilan hak invoker

Jika tampilan memiliki hak invoker, pengguna harus memiliki hak istimewa pada semua objek skema pokok tampilan untuk membuat kueri.

Misalnya, jika peran database memiliki akses ke semua objek yang direferensikan oleh tampilan SingerNames, peran tersebut dapat mengkueri tampilan SingerNames:

SELECT COUNT(SingerID) as SingerCount
FROM SingerNames;

Membuat kueri tampilan hak pendefinisi

Jika tampilan memiliki hak pendefinisi, pengguna dapat mengkueri tampilan tanpa memerlukan hak istimewa pada objek yang mendasarinya, asalkan Anda memberikan hak istimewa SELECT pada tampilan kepada peran yang diperlukan.

Dalam contoh berikut, pengguna dengan peran database Analyst ingin mengkueri tampilan SingerNames. Namun, pengguna ditolak aksesnya karena SingerNames adalah tampilan hak invoker dan peran Analyst tidak memiliki akses ke semua objek yang mendasarinya. Dalam hal ini, jika Anda memutuskan untuk memberikan akses ke tampilan kepada Analis, tetapi tidak ingin memberikan akses ke tabel Singers, Anda dapat mengganti jenis keamanan tampilan ke hak pendefinisi. Setelah Anda mengganti jenis keamanan tampilan, berikan akses peran Analyst ke tampilan tersebut. Pengguna kini dapat membuat kueri tabel virtual SingerNames meskipun tidak memiliki akses ke tabel Singers.

SELECT COUNT(SingerID) as SingerCount
FROM SingerNames;

Membuat kueri tampilan berparameter

Anda mengkueri tampilan berparameter dengan cara yang sama seperti tampilan lainnya. Tampilan ini memfilter data untuk meneruskan konteks tingkat aplikasi dengan aman berdasarkan parameter amannya (misalnya, ID pengguna).

Mengganti tampilan

Anda dapat mengganti tabel virtual menggunakan pernyataan CREATE OR REPLACE VIEW untuk mengubah definisi tabel virtual atau jenis keamanan tabel virtual.

Mengganti tampilan mirip dengan menghapus dan membuat ulang tampilan. Setiap pemberian akses yang diberikan ke tampilan awal harus diberikan lagi setelah mengganti tampilan.

Untuk mengganti tampilan hak invoker dengan tampilan hak definer:

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

Menghapus tampilan

Setelah tampilan dihapus, peran database dengan hak istimewa di dalamnya tidak lagi memiliki akses. Untuk menghapus tampilan, gunakan pernyataan DROP VIEW.

DROP VIEW SingerNames;

Mendapatkan informasi tentang tabel virtual

Anda bisa mendapatkan informasi tentang tabel virtual dalam database dengan membuat kueri tabel dalam skema INFORMATION_SCHEMA-nya.

  • Tabel INFORMATION_SCHEMA.TABLES menyediakan nama semua tampilan yang ditentukan.

  • Tabel INFORMATION_SCHEMA.VIEWS menyediakan nama, definisi tampilan, jenis keamanan, dan teks kueri dari semua tampilan yang ditentukan. Pengguna FGAC yang memiliki hak istimewa SELECT pada tabel virtual dapat memperoleh informasi tentang tabel virtual dari tabel INFORMATION_SCHEMA.VIEWS. Pengguna FGAC lainnya memerlukan peran spanner_info_reader jika mereka tidak memiliki hak istimewa SELECT untuk tampilan.

Untuk memeriksa definisi tampilan dan jenis keamanan tampilan bernama ProductSoldLastWeek:

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