パラメータ化された安全なビューを使用する

このドキュメントでは、Cloud SQL for MySQL でパラメータ化されたセキュアビューを使用する方法について説明します。これにより、アプリケーション固有の名前付きパラメータ（アプリケーション ユーザー認証情報など）に基づいてデータアクセスを制限できます。パラメータ化されたセキュアビューは、MySQL ビューの機能を拡張することで、セキュリティとアクセス制御を強化します。また、これらのビューは、実行されるクエリにさまざまな制限を自動的に適用することで、アプリケーションから信頼できないクエリを実行するリスクを軽減します。

詳細については、Cloud SQL のパラメータ化されたセキュアビューをご覧ください。

始める前に

このドキュメントでは、Cloud SQL for MySQL インスタンスがすでに作成されていることを前提としています。

  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 句内でのみ使用できます。

パラメータ化されたセキュアビューにクエリを実行する

Cloud SQL for MySQL でパラメータ化されたセキュアビューをクエリするには、次の 2 つの方法があります。

  • パラメータ化されたセキュアビューをクエリするときに、SET_VIEW_VARS ヒントを使用して変数に値を割り当てます。ビューで参照されている変数が設定されていない場合、または関係のない変数が SET_VIEW_VARS ヒントに表示されている場合、パラメータ化されたセキュアビューに対するクエリは失敗します。

  • mysql.execute_parameterized_query プロシージャを呼び出して、パラメータ化されたセキュアビューをクエリします。mysql.execute_parameterized_query プロシージャを使用する場合は、プロシージャに 2 つの引数を指定します。最初の引数は、セッション変数に関するヒントのないパラメータ化されたセキュアビューに対するクエリです。2 番目の引数には、必要なすべてのセッション変数と、指定する値が渡されます。

ヒント値を使用する

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 メソッドを使用する場合、クエリは複数のステートメントを 1 つのステートメントに連結したものであってはなりません。たとえば、クエリ ステートメント SELECT * FROM a; DROP TABLE a; は無効です。

ログでパラメータ化されたセキュアビューを表示する

Cloud SQL for MySQL データベース ユーザーがパラメータ化された安全なビューを作成しようとすると、次のような情報レベルのメッセージが 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

次のステップ