Menggunakan tampilan aman berparameter

Dokumen ini menjelaskan cara menggunakan tampilan aman berparameter di Cloud SQL untuk MySQL, yang memungkinkan Anda membatasi akses data berdasarkan parameter bernama spesifik per aplikasi, seperti kredensial pengguna aplikasi. Tampilan aman yang diberi parameter meningkatkan keamanan dan kontrol akses dengan memperluas fungsi tampilan MySQL. 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 Tampilan aman berparameter di Cloud SQL.

Sebelum memulai

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

  1. Pastikan instance Cloud SQL Anda menjalankan MySQL 8.0.43 atau yang lebih baru. Jika instance MySQL 8.0 Anda menjalankan versi minor yang lebih lama, Anda dapat melakukan upgrade versi minor.

  2. Aktifkan flag database cloudsql_parameterized_secure_view untuk instance Anda.

    Perubahan flag ini tidak memerlukan mulai ulang database. Untuk mengetahui informasi selengkapnya, lihat Mengonfigurasi flag database.

Membuat tampilan aman yang diberi parameter

Untuk membuat tampilan aman yang diberi parameter, jalankan perintah DDL CREATE VIEW. Contoh:

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

Gunakan sintaksis berikut:

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

Ganti kode berikut:

  • VIEW_NAME: nama tampilan aman berparameter
  • TABLE_NAME atau TABLE_NAME_N: nama tabel yang akan digunakan dalam tampilan berparameter
  • COLUMN_NAME atau COLUMN_NAME_N: nama kolom tabel yang akan digunakan dalam tampilan berparameter

  • CONDITION atau CONDITION_N: pernyataan atau pernyataan kondisi yang akan dievaluasi sebagai bagian dari klausul WHERE, ON, atau HAVING. Anda dapat menentukan variabel sesi yang digunakan sebagai parameter saat Anda melakukan kueri pada tampilan nanti. Kondisi akan terlihat seperti berikut:

    WHERE customer_id = @local_customer_id

    Variabel sesi, @variable_name, hanya diizinkan dalam klausa WHERE, ON, atau HAVING dari pernyataan CREATE VIEW.

Membuat kueri tampilan aman berparameter

Untuk membuat kueri tampilan aman berparameter di Cloud SQL untuk MySQL, Anda memiliki dua opsi:

  • Tetapkan nilai ke variabel menggunakan petunjuk SET_VIEW_VARS saat membuat kueri tampilan aman berparameter. Jika ada variabel yang direferensikan oleh tampilan tidak ditetapkan, atau jika ada variabel yang tidak terkait muncul dalam petunjuk SET_VIEW_VARS, maka kueri terhadap tampilan aman berparameter akan gagal.

  • Panggil prosedur mysql.execute_parameterized_query untuk membuat kueri tampilan aman yang diberi parameter. Jika Anda menggunakan prosedur mysql.execute_parameterized_query, Anda akan memberikan dua argumen ke prosedur tersebut. Argumen pertama adalah kueri terhadap tampilan aman yang diberi parameter tanpa petunjuk apa pun tentang variabel sesi. Argumen kedua menyediakan semua variabel sesi yang diperlukan dan nilai yang ingin Anda berikan.

Menggunakan nilai petunjuk

Untuk membuat kueri tampilan aman berparameter menggunakan petunjuk SET_VIEW_VARS, lakukan hal berikut:

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

Ganti kode berikut:

  • SESSION_VARIABLE: parameter bernama dari tampilan aman yang diparameterkan yang dapat Anda berikan nilai yang berbeda untuknya. Anda menentukan nama variabel sesi saat membuat tampilan dengan menggunakan notasi @variable_name dalam klausa kondisi tampilan.
  • VALUE: nilai untuk parameter bernama ini
  • VIEW_NAME: nama tampilan aman yang diberi parameter

Contoh:

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

Menggunakan prosedur kueri berparameter

Untuk membuat kueri tampilan aman berparameter menggunakan prosedur mysql.execute_parameterized_query, gunakan sintaksis berikut:

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

Ganti kode berikut:

  • SESSION_VARIABLE: parameter bernama dari tampilan aman yang diparameterkan yang dapat Anda berikan nilai yang berbeda untuknya. Anda menentukan nama variabel sesi saat membuat tampilan dengan menggunakan notasi @variable_name dalam klausa kondisi tampilan.
  • VALUE: nilai untuk parameter bernama ini
  • DATABASE_NAME: nama database tampilan
  • VIEW_NAME: nama tampilan aman yang diberi parameter

Contoh:

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

Batasan pada kueri

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

  • Kueri hanya dapat berupa pernyataan SELECT.
  • Kueri tidak dapat menyertakan pemanggilan ke fungsi yang ditentukan pengguna atau prosedur tersimpan.
  • Kueri tidak dapat menetapkan variabel.
  • Jika Anda menggunakan metode CALL mysql.execute_parameterized_query, maka kueri tidak dapat berupa beberapa pernyataan yang digabungkan menjadi satu pernyataan. Misalnya, pernyataan kueri SELECT * FROM a; DROP TABLE a; tidak valid.

Melihat tampilan aman yang diberi parameter dalam log

Saat pengguna database Cloud SQL untuk MySQL mencoba membuat tampilan aman berparameter, pesan tingkat informasi dicatat dalam log error MySQL yang mirip dengan berikut:

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

Memecahkan masalah tampilan aman yang diberi parameter

Masalah Pemecahan masalah
Flag cloud_parameterized_secure_view tidak disetel untuk instance Jika tanda cloudsql_parameterized_secure_view disetel ke OFF, setiap upaya untuk membuat kueri tampilan aman berparameter yang ada akan menghasilkan error berikut:

Cannot operate on a Parameterized Secure View without `cloudsql_parameterized_secure_view` being set. Please turn the flag on first before querying Parameterized Secure Views

Jika Anda mencoba membuat tampilan aman yang diberi parameter tanpa menetapkan tanda, Anda akan menerima error berikut:

View's SELECT contains a variable or parameter

Anda dapat memverifikasi apakah tampilan aman berparameter diaktifkan untuk instance Cloud SQL dengan memeriksa variabel global:

SHOW VARIABLES LIKE 'cloudsql_parameterized_secure_view';
Kueri gagal dengan error View's SELECT contains a variable or parameter. Variabel tidak diizinkan di luar klausa WHERE, HAVING, atau ON. Menggunakan variabel di luar klausa ini akan menghasilkan error.
Kueri gagal dengan error User variable `%s` used in Parameterized Secure View %s must be set in the query before using SET_VIEW_VARS hint Tetapkan variabel sesi sebelum Anda menjalankan kueri.
Kueri gagal dengan error versi MySQL Jika Anda mencoba membuat kueri tampilan aman berparameter yang ada dari versi MySQL yang lebih lama dari 8.0.43, Anda dapat mengalami error berikut:

Variable %s found in view %s. Please upgrade to 8.0.43 or above to use Parameterized Secure Views

Langkah berikutnya