Usar visualizações seguras parametrizadas

Este documento descreve como usar visualizações parametrizadas seguras no Cloud SQL para MySQL, que permitem limitar o acesso aos dados com base em parâmetros nomeados específicos do aplicativo, como credenciais de usuário do aplicativo. As visualizações parametrizadas seguras melhoram a segurança e o controle de acesso, estendendo a funcionalidade das visualizações do MySQL. Essas visualizações também atenuam os riscos de execução de consultas não confiáveis de aplicativos, aplicando automaticamente várias restrições a qualquer consulta executada.

Para mais informações, consulte Visualizações parametrizadas seguras no Cloud SQL.

Antes de começar

Este documento pressupõe que você já criou uma instância do Cloud SQL para MySQL.

  1. Verifique se a instância do Cloud SQL está executando o MySQL 8.0.43 ou mais recente. Se a instância do MySQL 8.0 estiver executando uma versão secundária anterior, então você pode fazer um upgrade da versão secundária.

  2. Ative a flag de banco de dados cloudsql_parameterized_secure_view para sua instância.

    Essa alteração de flag não exige a reinicialização do banco de dados. Para mais informações, consulte Configurar flags de banco de dados.

Criar uma visualização parametrizada segura

Para criar uma visualização parametrizada segura, execute o comando DDL CREATE VIEW. Exemplo:

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

Use a seguinte sintaxe:

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

Substitua:

  • VIEW_NAME: o nome da visualização parametrizada segura
  • TABLE_NAME ou TABLE_NAME_N: o nome da tabela ou tabelas a serem usadas na visualização parametrizada
  • COLUMN_NAME ou COLUMN_NAME_N: o nome da coluna ou colunas da tabela a serem usadas na visualização parametrizada

  • CONDITION ou CONDITION_N: a instrução ou instruções de condição a serem avaliadas como parte de uma cláusula WHERE, ON, ou HAVING. É possível especificar a variável de sessão usada como parâmetro ao executar uma consulta na visualização. Uma condição é semelhante a esta:

    WHERE customer_id = @local_customer_id

    As variáveis de sessão, @variable_name, são permitidas apenas nas cláusulas WHERE, ON ou HAVING de uma instrução CREATE VIEW.

Consultar uma visualização parametrizada segura

Para consultar uma visualização parametrizada segura no Cloud SQL para MySQL, você tem duas opções:

  • Atribua valores a variáveis usando a dica SET_VIEW_VARS ao consultar a visualização parametrizada segura. Se alguma das variáveis referenciadas pela visualização não estiver definida ou se alguma variável não relacionada aparecer na dica SET_VIEW_VARS, a consulta na visualização parametrizada segura falhará.

  • Chame o procedimento mysql.execute_parameterized_query para consultar a visualização parametrizada segura. Se você usar o procedimento mysql.execute_parameterized_query, forneça dois argumentos. O primeiro argumento é uma consulta na visualização parametrizada segura sem dicas sobre a variável de sessão. O segundo argumento fornece todas as variáveis de sessão necessárias e os valores que você quer fornecer.

Usar valores de dica

Para consultar a visualização parametrizada segura usando a dica SET_VIEW_VARS, faça o seguinte:

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

Substitua:

  • SESSION_VARIABLE: o parâmetro nomeado da visualização parametrizada segura para a qual você pode fornecer valores diferentes. Você define o nome da variável de sessão ao criar a visualização usando a notação @variable_name na cláusula de condição da visualização.
  • VALUE: um valor para esse parâmetro nomeado
  • VIEW_NAME: nome da visualização parametrizada segura

Exemplo:

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

Usar o procedimento de consulta parametrizada

Para consultar a visualização parametrizada segura usando o procedimento mysql.execute_parameterized_query, use a seguinte sintaxe:

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

Substitua:

  • SESSION_VARIABLE: o parâmetro nomeado da visualização parametrizada segura para a qual você pode fornecer valores diferentes. Você define o nome da variável de sessão ao criar a visualização usando a notação @variable_name na cláusula de condição da visualização.
  • VALUE: um valor para esse parâmetro nomeado
  • DATABASE_NAME: nome do banco de dados da visualização
  • VIEW_NAME: nome da visualização parametrizada segura

Exemplo:

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

Restrições em consultas

A lista a seguir mostra o conjunto de operações restritas para consultas executadas usando as opções descritas em Consultar uma visualização parametrizada segura:

  • A consulta só pode ser uma instrução SELECT.
  • A consulta não pode incluir invocações de funções definidas pelo usuário ou procedimentos armazenados.
  • A consulta não pode atribuir variáveis.
  • Se você usar o método CALL mysql.execute_parameterized_query, a consulta não poderá ser várias instruções concatenadas em uma única instrução. Por exemplo, a instrução de consulta SELECT * FROM a; DROP TABLE a; não é válida.

Visualizar visualizações parametrizadas seguras em registros

Quando os usuários do banco de dados do Cloud SQL para MySQL tentam criar uma visualização parametrizada segura, uma mensagem de nível de informação é registrada no registro de erros do MySQL, semelhante a esta:

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

Resolver problemas de visualizações parametrizadas seguras

Problema Solução de problemas
A flag cloud_parameterized_secure_view não está definida para a instância Se a flag cloudsql_parameterized_secure_view estiver definida como OFF, qualquer tentativa de consultar uma visualização parametrizada segura vai resultar no seguinte erro:

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

Se você tentar criar uma visualização parametrizada segura sem a flag definida, vai receber o seguinte erro:

View's SELECT contains a variable or parameter

Para verificar se as visualizações parametrizadas seguras estão ativadas para uma instância do Cloud SQL, confira a variável global:

SHOW VARIABLES LIKE 'cloudsql_parameterized_secure_view';
A consulta falha com o erro View's SELECT contains a variable or parameter. As variáveis não são permitidas fora das cláusulas WHERE, HAVING, ou ON. O uso de uma variável fora dessas cláusulas resulta em um erro.
A consulta falha com o erro User variable `%s` used in Parameterized Secure View %s must be set in the query before using SET_VIEW_VARS hint Defina a variável de sessão antes de executar a consulta.
A consulta falha com um erro de versão do MySQL Se você tentar consultar uma visualização parametrizada segura de uma versão do MySQL anterior à 8.0.43, poderá encontrar o seguinte erro:

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

A seguir