Sebagian atau seluruh informasi di halaman ini mungkin tidak berlaku untuk Cloud de Confiance dari S3NS. Lihat Perbedaan dengan Google Cloud untuk mengetahui detail selengkapnya.

Google uses AI technology to translate content into your preferred language. AI translations can contain errors.

Menggunakan tampilan aman berparameter

MySQL | PostgreSQL | SQL Server

Dokumen ini menjelaskan cara menggunakan tampilan aman berparameter di Cloud SQL untuk PostgreSQL, yang memungkinkan Anda membatasi akses data berdasarkan parameter bernama khusus aplikasi, seperti kredensial pengguna aplikasi. Tampilan aman yang diberi parameter meningkatkan keamanan dan kontrol akses dengan memperluas fungsi tampilan PostgreSQL. Tampilan ini juga meminimalkan risiko menjalankan kueri yang tidak tepercaya dari aplikasi dengan menerapkan sejumlah batasan secara otomatis pada kueri apa pun yang dijalankan.

Untuk mengetahui informasi selengkapnya, lihat ringkasan tampilan aman yang diberi parameter dan tutorial tampilan aman yang diberi parameter.

Sebelum memulai

Dokumen ini mengasumsikan bahwa Anda telah membuat instance Cloud SQL untuk PostgreSQL.

Sebelum dapat menggunakan tampilan aman yang diberi parameter, Anda harus melakukan hal berikut:

Aktifkan flag database cloudsql.enable_parameterized_views untuk instance Cloud SQL Anda. Perubahan flag ini memerlukan mulai ulang database. Untuk mengetahui informasi selengkapnya, lihat Mengonfigurasi flag database.
Gunakan Cloud SQL Studio atau psql untuk membuat ekstensi parameterized_views di database mana pun tempat tampilan berparameter dibuat:
```
-- Requires cloudsql.enable_parameterized_views set to on
CREATE EXTENSION parameterized_views;
```
Saat ekstensi dibuat, skema bernama parameterized_views juga dibuat oleh sistem sehingga API dimuat dalam namespace skema tersebut, dan API tidak berkonflik dengan API yang ada.

Membuat tampilan aman yang diberi parameter

Untuk membuat tampilan aman berparameter, ikuti langkah-langkah berikut:

Jalankan perintah DDL CREATE VIEW menggunakan opsi security_barrier:
```
CREATE VIEW VIEW_NAME WITH (security_barrier) AS
SELECT COLUMN_NAME, COLUMN_NAME_N
FROM TABLE_NAME ALIAS
WHERE CONDITION;
```
Ganti kode berikut:
- VIEW_NAME: nama tampilan aman berparameter
- TABLE_NAME: nama tabel yang akan digunakan dalam tampilan aman berparameter
- ALIAS: alias untuk nama tabel yang akan digunakan dalam tampilan aman berparameter
- COLUMNNAME atau COLUMN_NAMEN: nama kolom tabel yang akan digunakan dalam tampilan aman yang diberi parameter
- CONDITION: pernyataan kondisi yang digunakan untuk membatasi pengguna aplikasi agar hanya dapat melihat baris yang diizinkan untuk diakses. Tambahkan parameter yang diperlukan menggunakan sintaksis $@PARAMETER_NAME dalam klausa WHERE. Kasus penggunaan umum adalah memeriksa nilai kolom menggunakan WHERE COLUMN = $@PARAMETER_NAME.
  
  $@PARAMETER_NAME menunjukkan parameter tampilan bernama. Nilainya diberikan saat Anda menggunakan execute_parameterized_query API. Parameter tampilan bernama memiliki persyaratan berikut:
  - Parameter tampilan bernama harus diawali dengan huruf (a-z) atau garis bawah (_).
  - Karakter berikutnya dapat berupa huruf, garis bawah, atau digit (0-9).
  - Parameter tampilan bernama peka huruf besar/kecil. Misalnya, $@PARAMETER_NAME ditafsirkan secara berbeda dengan $@parameter_name.
  Berikut adalah contoh pembuatan tampilan aman yang diberi parameter yang menggunakan parameter tampilan bernama:
```
CREATE VIEW user_specific_items WITH (security_barrier) AS
SELECT item_id, item_name, description, owner_id
FROM items t
WHERE owner_id = $@app_user_id;
```
Berikan SELECT pada tampilan kepada pengguna database mana pun yang diizinkan untuk mengkueri tampilan.
Berikan USAGE pada skema yang berisi tabel yang ditentukan dalam tampilan kepada pengguna database mana pun yang diizinkan untuk membuat kueri tampilan.

Untuk mengetahui informasi selengkapnya, lihat Mengamankan dan mengontrol akses ke data aplikasi menggunakan tampilan aman berparameter (Tutorial).

Mengonfigurasi keamanan untuk aplikasi Anda

Untuk mengonfigurasi keamanan aplikasi menggunakan tampilan aman berparameter, ikuti langkah-langkah berikut:

Buat tampilan aman yang diberi parameter sebagai pengguna administratif. Pengguna database Cloud SQL ini melakukan operasi administratif untuk aplikasi.
Buat peran database baru untuk menjalankan kueri terhadap tampilan aman yang diparameterkan. Ini adalah peran database Cloud SQL yang digunakan aplikasi untuk terhubung dan login ke database.
1. Beri izin peran baru ke tampilan aman, yang biasanya mencakup hak istimewa SELECT ke tampilan dan USAGE pada skema.
2. Batasi objek yang dapat diakses oleh peran ini ke set minimum fungsi dan objek publik yang diperlukan oleh aplikasi. Hindari memberikan akses ke skema dan tabel yang tidak bersifat publik.
Saat Anda membuat kueri tampilan, aplikasi akan memberikan nilai parameter tampilan yang diperlukan, yang terkait dengan identitas pengguna aplikasi.

Untuk mengetahui informasi selengkapnya, lihat artikel Membuat dan mengelola pengguna.

Membuat kueri tampilan aman berparameter

Untuk membuat kueri tampilan aman berparameter, gunakan salah satu opsi berikut:

Berbasis JSON: Gunakan API ini untuk menjalankan kueri sekali jalan dan menampilkan baris JSON.
BERBASIS KURSOR: Gunakan API ini saat Anda memiliki kueri yang berjalan lebih lama atau saat Anda memiliki kueri besar dan ingin mengambil hasilnya dalam batch.
Pernyataan PREPARE .. AS RESTRICTED: Gunakan PREPARE .. AS RESTRICTED untuk menentukan rencana kueri, lalu jalankan EXECUTE ... WITH VIEW PARAMETERS (...) untuk mengeksekusinya dengan parameter tertentu untuk tampilan.

JSON API

Jalankan fungsi execute_parameterized_query(), yang memiliki sintaksis berikut:

SELECT * FROM
parameterized_views.execute_parameterized_query(
    query => SQL_QUERY,
    param_names => ARRAY [PARAMETER_NAMES],
    param_values => ARRAY [PARAMETER_VALUES]
);

Fungsi ini menampilkan tabel objek JSON. Setiap baris dalam tabel setara dengan nilai row_to_json() dari baris hasil kueri asli.

Penggunaan API ini membatasi ukuran set hasil berdasarkan ukuran (dalam KB) dan jumlah baris. Anda dapat mengonfigurasi batas ini menggunakan parameterized_views.json_results_max_size dan parameterized_views.json_results_max_rows.

CURSOR API

Jalankan fungsi execute_parameterized_query() dengan nama kursor, yang membuat dan menampilkan CURSOR yang tercakup dalam transaksi:

-- Must be in a transaction block
BEGIN;

SELECT * FROM
parameterized_views.execute_parameterized_query(
    query => SQL_QUERY,
    cursor_name => CURSOR_NAME,
    param_names => ARRAY [PARAMETER_NAMES],
    param_values => ARRAY [PARAMETER_VALUES]
);

FETCH ALL FROM CURSOR_NAME;

END;

Pernyataan yang disiapkan

Metode pernyataan yang disiapkan memungkinkan Anda menyiapkan rencana kueri satu kali, lalu mengeksekusinya beberapa kali dengan nilai yang berbeda untuk parameter posisi kueri dan parameter bernama tampilan.

Untuk menggunakan pernyataan yang disiapkan, lakukan hal berikut:

Buat pernyataan yang disiapkan.
```
-- Prepare the statement
PREPARE PREPARED_STATEMENT_NAME (QUERY_PARAM_TYPE_1, QUERY_PARAM_TYPE_N)
AS RESTRICTED SQL_QUERY;
```
Ganti kode berikut:
- PREPARED_STATEMENT_NAME: nama pernyataan yang disiapkan
- QUERY_PARAM_TYPE_N: jenis data parameter kueri, seperti TEXT
- SQL_QUERY: kueri SQL yang akan dijalankan sebagai bagian dari pernyataan yang disiapkan dengan nilai yang diberikan
Jalankan pernyataan yang disiapkan.
```
-- Execute the statement with query parameters and view parameters
EXECUTE PREPARED_STATEMENT_NAME (QUERY_VALUE_1, QUERY_VALUE_N)
WITH VIEW PARAMETERS (PARAMETER_NAME_1 := 'PARAMETER_VALUE_1', PARAMETER_NAME_N := 'PARAMETER_VALUE_N');
```
Ganti kode berikut:
- PREPARED_STATEMENT_NAME: nama pernyataan yang disiapkan.
- QUERY_VALUE_N: nilai atau nilai yang akan diberikan sebagai parameter atau parameter ke kueri SQL
- PARAMETER_NAME_N: nama parameter tampilan bernama yang Anda tentukan saat membuat tampilan aman berparameter. Anda membuat parameter tampilan bernama dari kolom tabel.
- PARAMETER_VALUE_N: nilai atau nilai untuk parameter tampilan bernama yang membatasi tampilan aman yang diberi parameter ke baris yang terkait dengan nilai.

Bersihkan pernyataan yang disiapkan.

-- Cleanup
DEALLOCATE PREPARED_STATEMENT_NAME>;

Contoh berikut menggunakan tampilan aman berparameter bernama user_specific_items yang memerlukan parameter tampilan bernama $@app_user_id.

-- Prepare a query with a positional parameter $1 for the item_name pattern
PREPARE get_items_by_name (TEXT) AS RESTRICTED
SELECT item_id, item_name FROM user_specific_items
WHERE item_name LIKE $1;

Setelah membuat pernyataan yang disiapkan, Anda dapat menjalankan pernyataan yang disiapkan beberapa kali dengan dan menetapkan nilai yang berbeda untuk kueri dan parameter tampilan bernama.

Misalnya, eksekusi kueri pertama:

-- Execute for user 123, looking for items like '%Laptop%'
EXECUTE get_items_by_name ('%Laptop%')
WITH VIEW PARAMETERS (app_user_id := '123');

Eksekusi kueri kedua:

-- Execute for user 456, looking for items like '%Book%'
EXECUTE get_items_by_name ('%Book%')
WITH VIEW PARAMETERS (app_user_id := '456');

Klausul WITH VIEW PARAMETERS adalah tempat parameter tampilan bernama (123, 456) untuk tampilan aman berparameter disediakan, terpisah dari parameter posisional untuk kueri yang disiapkan (%Laptop%, %Book%).

Terakhir, bersihkan pernyataan yang disiapkan.

-- Clean up the get_item_by_name prepared statement
DEALLOCATE get_items_by_name;

Pembatasan yang diterapkan pada kueri

Berikut adalah daftar kumpulan operasi terbatas untuk kueri yang Anda jalankan menggunakan opsi yang dijelaskan dalam Mengirim kueri ke tampilan aman berparameter:

Hanya baca: Hanya pernyataan SELECT hanya baca yang diizinkan. DML (INSERT, UPDATE, DELETE) dan DDL (CREATE, ALTER) dilarang.
Tidak Ada Penumpukan: Panggilan rekursif ke execute_parameterized_query dilarang.
Batas Ekstensi: Ekstensi tertentu yang memulai sesi latar belakang baru (misalnya, dblink, pg_cron) tidak diizinkan.
Pernyataan EXPLAIN tidak diizinkan untuk mencegah potensi kebocoran informasi menggunakan rencana kueri.

Mencantumkan semua tampilan yang diberi parameter

Gunakan tampilan parameterized_views.all_parameterized_views untuk mencantumkan semua tampilan berparameter (yang berisi setidaknya satu parameter bernama $@...).

SELECT * FROM parameterized_views.all_parameterized_views;