使用参数化安全视图

本文档介绍了如何在 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 中查询参数化安全视图，您有以下两种选择：

  • 在查询参数化安全视图时，使用 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; 无效。

在日志中查看参数化安全视图

当 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

后续步骤