使用參數化安全檢視區塊

本文說明如何在 MySQL 適用的 Cloud SQL 中使用參數化安全檢視區塊，根據應用程式專屬的具名參數 (例如應用程式使用者憑證) 限制資料存取權。參數化安全檢視區塊可擴充 MySQL 檢視區塊的功能，進而提升安全性和存取權控管。此外，這些檢視畫面也會對執行的任何查詢自動強制執行多項限制，降低從應用程式執行不受信任查詢的風險。

詳情請參閱「Cloud SQL 中的參數化安全檢視區塊」。

事前準備

本文假設您已建立 MySQL 適用的 Cloud SQL 執行個體。

  1. 確認 Cloud SQL 執行個體執行的是 MySQL 8.0.43 以上版本。 如果 MySQL 8.0 執行個體執行的是較早的子版本，您可以升級子版本。

  2. 為執行個體啟用 cloudsql_parameterized_secure_view 資料庫旗標。

    變更這個標記不需要重新啟動資料庫。詳情請參閱「設定資料庫標記」。

建立參數化安全檢視區塊

如要建立參數化安全檢視區塊，請執行 CREATE VIEW DDL 指令。 例如：

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

請使用下列語法：

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

更改下列內容：

  • VIEW_NAME：參數化安全檢視區塊的名稱
  • TABLE_NAME 或 TABLE_NAME_N：要在參數化檢視畫面中使用的資料表名稱
  • COLUMN_NAME 或 COLUMN_NAME_N：要在參數化檢視畫面中使用的資料表欄名稱

  • CONDITION 或 CONDITION_N： 要評估的條件陳述式，做為 WHERE、ON 或 HAVING 子句的一部分。您可以在稍後對檢視區塊執行查詢時，指定要當做參數的工作階段變數。條件類似於下列項目：

    WHERE customer_id = @local_customer_id

    工作階段變數 @variable_name 只能在 CREATE VIEW 陳述式的 WHERE、ON 或 HAVING 子句中使用。

查詢參數化安全檢視區塊

如要在 MySQL 適用的 Cloud SQL 中查詢參數化安全檢視區塊，有兩種做法：

  • 查詢參數化安全檢視區塊時，使用 SET_VIEW_VARS 提示為變數指派值。如果檢視畫面參照的任何變數未設定，或 SET_VIEW_VARS 提示中出現任何不相關的變數，針對參數化安全檢視畫面的查詢就會失敗。

  • 呼叫 mysql.execute_parameterized_query 程序，查詢參數化安全檢視區塊。如果您使用 mysql.execute_parameterized_query 程序，則須為程序提供兩個引數。第一個引數是針對參數化安全檢視區塊的查詢，其中不含任何有關工作階段變數的提示。第二個引數會提供所有必要的工作階段變數，以及您想提供的值。

使用提示值

如要使用 SET_VIEW_VARS 提示查詢參數化安全檢視區塊，請按照下列步驟操作：

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

更改下列內容：

  • SESSION_VARIABLE：參數化安全檢視區塊的具名參數，您可以為此參數提供不同的值。您可以在建立檢視區塊時，使用檢視區塊條件子句中的 @variable_name 標記，定義工作階段變數名稱。
  • VALUE：這個具名參數的值
  • VIEW_NAME：參數化安全檢視區塊的名稱

例如：

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

使用參數化查詢程序

如要使用 mysql.execute_parameterized_query 程序查詢參數化安全檢視區塊，請使用下列語法：

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

更改下列內容：

  • SESSION_VARIABLE：參數化安全檢視區塊的具名參數，您可以為此參數提供不同的值。您可以在建立檢視區塊時，使用檢視區塊條件子句中的 @variable_name 標記，定義工作階段變數名稱。
  • VALUE：這個具名參數的值
  • DATABASE_NAME：檢視區塊的資料庫名稱
  • VIEW_NAME：參數化安全檢視區塊的名稱

例如：

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

查詢限制

以下列出使用「查詢參數化安全檢視區塊」一節所述選項執行的查詢，其受限作業如下：

  • 查詢只能是 SELECT 陳述式。
  • 查詢不得包含使用者定義函式或已儲存程序的呼叫。
  • 查詢無法指派變數。
  • 如果您使用 CALL mysql.execute_parameterized_query 方法，查詢就不能是串連成單一陳述式的多個陳述式。舉例來說，查詢陳述式 SELECT * FROM a; DROP TABLE a; 無效。

在記錄中查看參數化安全檢視區塊

當 MySQL 適用的 Cloud SQL 資料庫使用者嘗試建立參數化安全檢視區塊時，MySQL 錯誤記錄檔中會記錄資訊層級訊息，類似於下列訊息：

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

排解參數化安全檢視區塊問題

問題 疑難排解
執行個體未設定 cloud_parameterized_secure_view 旗標 如果 cloudsql_parameterized_secure_view 旗標設為 OFF，任何查詢現有參數化安全檢視區塊的嘗試都會導致下列錯誤：

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

如果您嘗試建立參數化安全檢視區塊，但未設定標記，系統會顯示下列錯誤訊息：

View's SELECT contains a variable or parameter

您可以檢查全域變數，確認 Cloud SQL 執行個體是否已啟用參數化安全檢視區塊：

SHOW VARIABLES LIKE 'cloudsql_parameterized_secure_view';
查詢失敗，並顯示 View's SELECT contains a variable or parameter 錯誤。 變數只能用於 WHERE、HAVING 或 ON 子句。在這些子句以外使用變數會導致錯誤。
查詢失敗，並顯示 User variable `%s` used in Parameterized Secure View %s must be set in the query before using SET_VIEW_VARS hint 錯誤 請先設定工作階段變數，再執行查詢。
查詢失敗，並顯示 MySQL 版本錯誤 如果您嘗試從早於 8.0.43 的 MySQL 版本查詢現有的參數化安全檢視區塊，可能會遇到下列錯誤：

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

後續步驟