Menggunakan tampilan aman berparameter

Dokumen ini menjelaskan cara menggunakan tampilan aman yang diberi parameter di Cloud SQL untuk MySQL, 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 MySQL. Tampilan ini juga mengurangi risiko menjalankan kueri yang tidak tepercaya dari aplikasi dengan menerapkan sejumlah batasan secara otomatis pada kueri yang dijalankan.

Untuk mengetahui informasi selengkapnya, lihat Tampilan aman yang diberi parameter 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 restart 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 yang diberi parameter
  • TABLE_NAME atau TABLE_NAME_N: nama tabel atau tabel yang akan digunakan dalam tampilan yang diberi parameter
  • COLUMN_NAME atau COLUMN_NAME_N: nama kolom tabel atau kolom yang akan digunakan dalam tampilan yang diberi parameter

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

    WHERE customer_id = @local_customer_id

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

Mengirim kueri ke tampilan aman yang diberi parameter

Untuk mengirim kueri ke tampilan aman yang diberi parameter di Cloud SQL untuk MySQL, Anda memiliki dua opsi:

  • Tetapkan nilai ke variabel menggunakan petunjuk SET_VIEW_VARS saat mengirim kueri ke tampilan aman yang diberi parameter. Jika salah satu variabel yang direferensikan oleh tampilan tidak ditetapkan, atau jika variabel yang tidak terkait muncul dalam petunjuk SET_VIEW_VARS, kueri terhadap tampilan aman yang diberi parameter akan gagal.

  • Panggil prosedur mysql.execute_parameterized_query untuk mengirim kueri ke tampilan aman yang diberi parameter. Jika Anda menggunakan prosedur mysql.execute_parameterized_query, Anda akan memberikan dua argumen ke prosedur. 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 mengirim kueri ke tampilan aman yang diberi parameter 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 diberi parameter yang dapat Anda berikan nilai yang berbeda. Anda menentukan nama variabel sesi saat membuat tampilan 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 yang diberi parameter

Untuk mengirim kueri ke tampilan aman yang diberi parameter 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 diberi parameter yang dapat Anda berikan nilai yang berbeda. Anda menentukan nama variabel sesi saat membuat tampilan 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 kumpulan operasi yang dibatasi untuk kueri yang Anda jalankan menggunakan opsi yang dijelaskan dalam Mengirim kueri ke tampilan aman yang diberi parameter:

  • 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, 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 yang diberi parameter, pesan tingkat informasi akan dicatat dalam log error MySQL yang mirip dengan berikut ini:

 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 ditetapkan untuk instance Jika flag cloudsql_parameterized_secure_view ditetapkan ke OFF, setiap upaya untuk mengirim kueri ke tampilan aman yang diberi parameter 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 flag, Anda akan menerima error berikut:

View's SELECT contains a variable or parameter

Anda dapat memverifikasi apakah tampilan aman yang diberi parameter 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 WHERE, HAVING, atau ON klausa. Menggunakan variabel di luar klausa ini menghasilkan error.
Kueri gagal dengan User variable `%s` used in Parameterized Secure View %s must be set in the query before using SET_VIEW_VARS hint error Tetapkan variabel sesi sebelum Anda menjalankan kueri.
Kueri gagal dengan error versi MySQL Jika Anda mencoba mengirim kueri ke tampilan aman yang diberi parameter 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