Secure and control access to application data using parameterized secure views

Parameterized secure views provide data security and row access control to applications. They filter query results based on the application user's identity.

This tutorial describes how to set up parameterized secure views in Cloud SQL, configure database roles and privileges to restrict access to base tables, and verify data security. The examples provided in this document are for demonstration purposes only.

What are parameterized secure views?

As a general best practice, your application should run using a service account with the minimal required access to your database. For example, if your application shouldn't write to your database, it should use a role with read-only access. You should configure these access controls at the database level.

When your application needs more granular security than standard database-level access permits, you can use parameterized secure views to make sure that users view only their authorized data. Parameterized secure views provide dynamic data security and row access control suitable for multi-tenant SaaS applications without requiring separate database roles for each application end user.

Using parameterized secure views provides three main benefits:

Dynamic data security and row access control: Filters queries using end-user identifiers so users access only their authorized data regardless of query phrasing.
Easier role management: Uses a single shared database role for all application users instead of managing separate database roles for each individual.

Objectives

Create parameterized secure views with named view parameters.
Create the database role that is used by the application to connect to the database and access parameterized secure views.
Grant the new role permissions to the parameterized secure views and revoke access to the base tables.
Connect using the new role and verify that the restricted tables can't be accessed.
Run queries on the parameterized view using the mysql.execute_parameterized_query stored procedure or using the QueryData API.

Costs

In this document, you use the following billable components of Google Cloud:

Cloud SQL for MySQL

To generate a cost estimate based on your projected usage, use the pricing calculator.

New Google Cloud users might be eligible for a free trial.

To avoid continued billing, delete the resources you created when you finish the tasks in this document. For more information, see Clean up.

Before you begin

Before you create parameterized secure views, complete the following prerequisites.

Enable billing and required APIs

In the Google Cloud console, select a project.

Go to project selector
Make sure that billing is enabled for your Google Cloud project.
Enable the Cloud APIs necessary to create and connect to Cloud SQL.
1. Enable the API
2. In the Confirm project step, click Next to confirm the name of the project you are going to make changes to.
3. In the Enable APIs step, click Enable to enable the following:
  - Cloud SQL for MySQL API
  - Knowledge Catalog API
  - Data Analytics API with Gemini (geminidataanalytics.googleapis.com)

Create and connect to a database

Create an instance.
Connect to your instance and create a database.
Enable the Cloud SQL Data API on your Cloud SQL for MySQL instance so that the QueryData API can execute queries against it:
```
gcloud sql instances patch INSTANCE_ID \
    --project=PROJECT_ID \
    --data-api-access=ALLOW_DATA_API
```
Replace the following:
- INSTANCE_ID: the ID of your Cloud SQL for MySQL instance.
- PROJECT_ID: your Google Cloud project ID.

Grant required roles and create an IAM database user

To authenticate with Identity and Access Management (IAM) and run queryData requests against parameterized secure views, configure the IAM user or service account:

Grant the following IAM roles to IAM_USER_EMAIL on your Google Cloud project:
- roles/geminidataanalytics.queryDataUser
- roles/serviceusage.serviceUsageConsumer
- roles/cloudsql.instanceUser
For more information about granting IAM roles, see Add IAM policy binding for a project.
Enable IAM database authentication on your Cloud SQL instance and add IAM_USER_EMAIL as an IAM database user on the instance. You must create this IAM database user before you grant the psv_user database role to it. For more information about setting up IAM database users, see Manage users with IAM database authentication.

Prepare your environment

To prepare for running queries on a parameterized secure view, set up the database, database roles, and the application schema.

Enable the database flag

Make sure that your Cloud SQL for MySQL instance is running Cloud SQL for MySQL 8.0.43 or later. If your Cloud SQL for MySQL 8.0 instance is running an earlier minor version, then you can perform a minor version upgrade.
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 an instance's database flags.

Set up the database and roles

In the Google Cloud console, go to the Cloud SQL page.

Go to Cloud SQL
Select an instance from the list.
In the navigation menu, click Cloud SQL Studio.
Sign in to Studio using administrative user account.
Click Authenticate. The Explorer pane displays a list of the objects in your database.
Click New SQL editor tab or New tab to open a new tab.
Create a database named store_db for the application data and views:
```
CREATE DATABASE IF NOT EXISTS store_db;
USE store_db;
```
Create a new database role for running queries against parameterized views. The application uses this role to connect to the database and run queries with access restricted strictly to authorized views:
```
CREATE ROLE 'psv_user';
```

Create the table and insert data

In the Google Cloud console, go to the Cloud SQL page.

Go to Cloud SQL
Select a Cloud SQL for MySQL instance from the list.
In the navigation menu, click Cloud SQL Studio.
Sign in to Studio using database authentication.
Click New tab to open a new SQL editor tab.

Create the tables and insert data:

CREATE TABLE store_db.checked_items (
bag_id INT,
timestamp TIMESTAMP,
loc_code CHAR(3),
scan_type CHAR(1),
location TEXT,
customer_id INT
);

INSERT INTO store_db.checked_items (bag_id, timestamp, loc_code, scan_type, location, customer_id) VALUES
(101, '2023-10-26 10:00:00', 'ABC', 'I', 'Warehouse A', 123),
(102, '2023-10-26 10:15:30', 'DEF', 'O', 'Loading Dock B', 456),
(103, '2023-10-26 10:30:45', 'GHI', 'I', 'Conveyor Belt 1', 789),
(104, '2023-10-26 11:00:00', 'JKL', 'O', 'Shipping Area C', 101),
(105, '2023-10-26 11:45:15', 'MNO', 'I', 'Sorting Station D', 202),
(106, '2023-10-26 12:00:00', 'PQR', 'O', 'Truck Bay E', 303);

Create parameterized secure views and set up access privileges

To create parameterized secure views, follow these steps:

In the Google Cloud console, go to the Cloud SQL page.

Go to Cloud SQL
Select an instance from the list.
In the navigation menu, click Cloud SQL Studio.
Sign in to Studio and connect to the store_db database as the administrative user account, such as root or ADMIN_USER, that you used in the previous section.
Click Authenticate. The Explorer pane displays a list of the objects in your database.
Click New SQL editor tab or New tab to open a new tab.
To provide limited access to the view, create a parameterized view:
```
CREATE VIEW store_db.secure_checked_items AS
SELECT bag_id, timestamp, location
FROM store_db.checked_items t
WHERE customer_id = @app_end_userid;
```
Note: Make sure that you use unique parameter names, such as @app_end_userid, when creating parameterized secure views.

Grant SELECT access on the parameterized view to the psv_user role:

GRANT SELECT ON store_db.secure_checked_items TO 'psv_user';

Grant execution permission on the built-in system stored procedure used to execute parameterized queries safely:
```
GRANT EXECUTE ON PROCEDURE mysql.execute_parameterized_query TO 'psv_user';
```
Optional. Explicitly revoke any direct privileges on the base table from psv_user to ensure users cannot bypass the secure view:
```
REVOKE ALL PRIVILEGES ON store_db.checked_items FROM 'psv_user';
```
Note: If a Error 1147: There is no such grant defined for user occurs, you can safely ignore it. This error appears if you try to revoke a privilege that was never granted. A new role has no privileges by default. Only run this command if you are modifying an existing role that already had access to the base table.
Make sure that IAM_USER_EMAIL has been added to the instance as an IAM database user, and then grant the psv_user role to the IAM-authenticated database user or application login account:
```
GRANT 'psv_user' TO 'IAM_USERNAME'@'%';

SET DEFAULT ROLE 'psv_user' TO 'IAM_USERNAME'@'%';
```
Replace IAM_USERNAME with your IAM database username or application login account. For an IAM user account, Cloud SQL for MySQL strips @ and the domain name from the email address, so specify the email prefix before @, such as user for user@example.com.

Verify data security

To verify that you can query the parameterized secure views, follow these steps:

Sign in to Cloud SQL Studio using the restricted IAM email address or application login.
Click New tab to open a new SQL editor tab.
Query the base table and verify that access is denied.
```
SELECT * FROM store_db.checked_items;
```
The database returns the ERROR 1142 (42000): SELECT command denied to user 'IAM_USERNAME'@'%' for table 'checked_items' error.

Query the parameterized secure view using the built-in mysql.execute_parameterized_query stored procedure.

CALL mysql.execute_parameterized_query(
  'SELECT * FROM store_db.secure_checked_items',
  'app_end_userid=303'
);

Sign in to the Google Cloud CLI as IAM_USER_EMAIL by running gcloud auth application-default login so that gcloud auth application-default print-access-token generates a token for the restricted IAM database user, and then query the parameterized secure views using the queryData method with PSV parameters:

 curl -X POST \
   "https://geminidataanalytics.googleapis.com/v1beta/projects/PROJECT_ID/locations/REGION:queryData" \
   -H "Authorization: Bearer $(gcloud auth application-default print-access-token)" \
   -H "x-goog-user-project: PROJECT_ID" \
   -H "Content-Type: application/json; charset=utf-8" \
   -d '{
     "prompt": "Show me the checked items.",
     "context": {
       "datasource_references": {
         "cloud_sql_reference": {
           "database_reference": {
             "engine": "MYSQL",
             "project_id": "PROJECT_ID",
             "region": "REGION",
             "instance_id": "INSTANCE_ID",
             "database_id": "DATABASE_ID",
             "table_ids": [
               "secure_checked_items"
             ]
           }
         }
       },
       "parameterized_secure_view_parameters": {
         "parameters": [
           {
             "key": "app_end_userid",
             "value": "303"
           }
         ]
       }
     },
     "generation_options": {
       "generate_query_result": true,
       "generate_natural_language_answer": true,
       "generate_explanation": true
     }
   }'

Use the table_ids field in database_reference to ensure QueryData only generates SQL against the secure_checked_items parameterized secure view. If you omit table_ids and the database role has SELECT access on the checked_items base table, QueryData might query the base table directly, bypassing the secure view.

Replace the following:

PROJECT_ID: your Google Cloud project ID.
REGION: the region where your Cloud SQL for MySQL instance is located.
INSTANCE_ID: the ID of your Cloud SQL for MySQL instance.
DATABASE_ID: the ID of your Cloud SQL for MySQL database, which is store_db in this tutorial.

Clean up

Delete the instance

When you delete the instance that you created in the Before you begin section, you also delete all of the objects that you created.

In the Google Cloud console, go to the Cloud SQL page.

Go to Cloud SQL
Select an instance from the list.
Click Delete.
Confirm that you want to delete the instance by entering the instance name and clicking Delete.

What's next

Learn about parameterized secure views.

Learn how to use parameterized secure views.