Use parameterized secure views Stay organized with collections Save and categorize content based on your preferences.

Preview — Parameterized secure views

This feature is subject to the "Pre-GA Offerings Terms" in the General Service Terms section of the Service Specific Terms. Pre-GA features are available "as is" and might have limited support. For more information, see the launch stage descriptions.

This document describes how to use parameterized secure views in Cloud SQL for MySQL, which let you limit data access based on application-specific named parameters, like application user credentials. Parameterized secure views improve security and access control by extending the functionality of MySQL views. These views also mitigate the risks of running untrusted queries from applications by enforcing a number of restrictions automatically on any query that is executed.

For more information, see Parameterized secure views in Cloud SQL.

Before you begin

This document assumes that you've already created a Cloud SQL for MySQL instance.

1. Make sure that your Cloud SQL instance is running MySQL 8.0.43 or later. If your MySQL 8.0 instance is running an earlier minor version, then you can perform a minor version upgrade.

2. Enable the cloudsql_parameterized_secure_view database flag for your instance.

   This flag change doesn't require a database restart. For more information, see Configure database flags.

Create a parameterized secure view

To create a parameterized secure view, run the CREATE VIEW DDL command. For example:

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

Use the following syntax:

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

Replace the following:

• VIEW_NAME: the name of the parameterized secure view
• TABLE_NAME or TABLE_NAME_N: the name of the table or tables to use in the parameterized view
• COLUMN_NAME or COLUMN_NAME_N: the name of the table column or columns to use in the parameterized view

• CONDITION or CONDITION_N: the condition statement or statements to evaluate as part of a WHERE, ON, or HAVING clause. You can specify the session variable that is used as the parameter when you later perform a query on the view. A condition resembles the following:

  WHERE customer_id = @local_customer_id

  Session variables, @variable_name, are permitted only within the WHERE, ON, or HAVING clauses of a CREATE VIEW statement.

Query a parameterized secure view

To query a parameterized secure view in Cloud SQL for MySQL, you have two options:

• Assign values to variables by using the SET_VIEW_VARS hint while querying the parameterized secure view. If any of the variables that are referenced by the view aren't set, or if any unrelated variables appear in the SET_VIEW_VARS hint, then the query against the parameterized secure view fails.

• Call the mysql.execute_parameterized_query procedure to query the parameterized secure view. If you use the mysql.execute_parameterized_query procedure, then you provide two arguments to the procedure. The first argument is a query against the parameterized secure view without any hints about the session variable. The second argument provides all the needed session variables and the values that you want to provide.

Use hint values

To query the parameterized secure view by using the SET_VIEW_VARS hint, do the following:

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

Replace the following:

• SESSION_VARIABLE: the named parameter of the parameterized secure view that you can provide different values for. You define the session variable name when you create the view by using the @variable_name notation in the view condition clause.
• VALUE: a value for this named parameter
• VIEW_NAME: name of the parameterized secure view

For example:

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

Use the parameterized query procedure

To query the parameterized secure view by using the mysql.execute_parameterized_query procedure, use the following syntax:

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

Replace the following:

• SESSION_VARIABLE: the named parameter of the parameterized secure view that you can provide different values for. You define the session variable name when you create the view by using the @variable_name notation in the view condition clause.
• VALUE: a value for this named parameter
• DATABASE_NAME: name of the database of the view
• VIEW_NAME: name of the parameterized secure view

For example:

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

Restrictions on queries

The following lists the set of restricted operations for queries that you run using the options described in Query a parameterized secure view:

• The query can only be a SELECT statement.
• The query can't include invocations to user-defined functions or stored procedures.
• The query can't assign variables.
• If you use the CALL mysql.execute_parameterized_query method, then the query can't be multiple statements concatenated into a single statement. For example, the query statement SELECT * FROM a; DROP TABLE a; isn't valid.

View parameterized secure views in logs

When Cloud SQL for MySQL database users try to create a parameterized secure view, an information-level message is logged in the MySQL error log similar to the following:

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

Troubleshoot parameterized secure views

SHOW VARIABLES LIKE 'cloudsql_parameterized_secure_view';

What's next

• Parameterized secure views overview

• Context sets overview