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 tabel virtual Spanner, lihat Ringkasan tabel virtual.

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 ketat, yang berarti 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 memenuhi syarat 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 tersebut, lihat Ringkasan tampilan.

Misalnya, asumsikan tabel Singers ditentukan seperti yang ditunjukkan berikut:

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 pemanggil seperti yang ditunjukkan di bawah 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 valid, definisi tampilan SingerNames ini tidak mematuhi praktik terbaik untuk melakukan transmisi jenis data guna memastikan stabilitas di seluruh perubahan skema, seperti yang dijelaskan di bagian berikutnya.

Praktik terbaik saat membuat tampilan

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

Misalnya, definisi tampilan SingerNames berikut mungkin menjadi tidak valid sebagai akibat dari 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 secara eksplisit mentransmisikan kolom 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 hak pemanggil atau hak penentu tampilan sama. Namun, bergantung pada jenis keamanan tampilan, Spanner mungkin perlu atau tidak perlu memeriksa objek skema yang dirujuk dalam tampilan terhadap peran database pokok yang memanggil kueri.

Membuat kueri tampilan hak pemanggil

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

Misalnya, jika peran database memiliki akses ke semua objek yang dirujuk oleh tampilan SingerNames, mereka dapat membuat kueri tampilan SingerNames:

SELECT COUNT(SingerID) as SingerCount
FROM SingerNames;

Membuat kueri tampilan hak penentu

Jika tampilan memiliki hak penentu, pengguna dapat membuat kueri tampilan tanpa memerlukan hak istimewa pada objek pokok selama Anda memberikan hak istimewa SELECT pada tampilan kepada peran yang diperlukan.

Dalam contoh berikut, pengguna dengan peran database Analis ingin membuat kueri tampilan SingerNames. Namun, pengguna ditolak aksesnya karena SingerNames adalah tampilan hak pemanggil dan peran Analis tidak memiliki akses ke semua objek pokok. Dalam hal ini, jika Anda memutuskan untuk memberi Analis akses ke tampilan, tetapi tidak ingin memberi mereka akses ke tabel Singers, Anda dapat mengganti jenis keamanan tampilan ke hak penentu. Setelah Anda mengganti jenis keamanan tampilan, berikan akses peran Analis ke tampilan. Pengguna kini dapat membuat kueri tabel virtual SingerNames meskipun tidak memiliki akses ke tabel Singers.

SELECT COUNT(SingerID) as SingerCount
FROM SingerNames;

Mengganti tampilan

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

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 penentu:

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 tampilan tersebut tidak lagi memiliki akses. Untuk menghapus tampilan, gunakan pernyataan DROP VIEW.

DROP VIEW SingerNames;

Mendapatkan informasi tentang tampilan

Anda bisa mendapatkan informasi tentang tampilan 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 memberikan nama, definisi tampilan, jenis keamanan, dan teks kueri dari semua tampilan yang ditentukan. Pengguna FGAC yang memiliki hak istimewa SELECT pada tampilan dapat memperoleh informasi tentang tampilan dari tabel INFORMATION_SCHEMA.VIEWS. Pengguna FGAC lain memerlukan peran spanner_info_reader jika mereka tidak memiliki hak istimewa SELECT untuk tampilan.

Untuk memeriksa definisi tampilan dan jenis keamanan tampilan yang disebut ProductSoldLastWeek:

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