Usar visualizações seguras parametrizadas

MySQL   |  PostgreSQL   |  SQL Server

Visualização prévia : visualizações seguras parametrizadas

Este recurso está sujeito aos Termos das Soluções Pré-GA na seção Termos Gerais de Serviço dos Termos Específicos de Serviço. Os recursos pré-GA estão disponíveis "no estado em que se encontram" e podem ter suporte limitado. Para mais informações, consulte as descrições da fase de lançamento.

Este documento descreve como usar visualizações seguras parametrizadas no Cloud SQL para PostgreSQL, que permitem limitar o acesso a dados com base em parâmetros nomeados específicos do aplicativo, como credenciais de usuário. As visualizações seguras parametrizadas melhoram a segurança e o controle de acesso, estendendo a funcionalidade das visualizações do PostgreSQL. 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 seguras parametrizadas no Cloud SQL e proteja e controle o acesso aos dados do aplicativo usando visualizações seguras parametrizadas.

Antes de começar

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

Antes de usar visualizações seguras parametrizadas, faça o seguinte:

1. Ative a flag de banco de dados cloudsql.enable_parameterized_views para sua instância do Cloud SQL. Essa mudança de flag exige a reinicialização do banco de dados. Para mais informações, consulte Configurar flags de banco de dados.

2. Use Cloud SQL Studio ou psql para criar a extensão parameterized_views em qualquer banco de dados em que uma visualização parametrizada seja criada:

   -- Requires cloudsql.enable_parameterized_views set to on
   CREATE EXTENSION parameterized_views;
   

   Quando a extensão é criada, um esquema chamado parameterized_views também é criado pelo sistema para que as APIs sejam contidas no namespace desse esquema e não entrem em conflito com as APIs atuais.

Criar uma visualização segura parametrizada

Para criar uma visualização segura parametrizada, siga estas etapas:

1. Execute o comando DDL CREATE VIEW usando a opção security_barrier:

   CREATE VIEW VIEW_NAME WITH (security_barrier) AS
   SELECT COLUMN_NAME, COLUMN_NAME_N
   FROM TABLE_NAME ALIAS
   WHERE CONDITION;

   Substitua:

   • VIEW_NAME: o nome da visualização segura parametrizada
   • TABLE_NAME: o nome da tabela a ser usada na visualização segura parametrizada
   • ALIAS: o alias do nome da tabela a ser usado na visualização segura parametrizada
   • COLUMNNAME ou COLUMN_NAMEN: o nome da coluna ou colunas da tabela a serem usadas na visualização segura parametrizada

   • CONDITION: a instrução de condição usada para restringir os usuários do aplicativo para que eles só possam visualizar as linhas a que têm permissão de acesso. Adicione os parâmetros necessários usando a sintaxe $@PARAMETER_NAME na cláusula WHERE. Um caso de uso comum é verificar o valor de uma coluna usando WHERE COLUMN = $@PARAMETER_NAME.

     $@PARAMETER_NAME indica um parâmetro de visualização nomeado. O valor é fornecido quando você usa a API execute_parameterized_query. Os parâmetros de visualização nomeados têm os seguintes requisitos:

     • Os parâmetros de visualização nomeados precisam começar com uma letra (a-z) ou um sublinhado (_).
     • Os caracteres subsequentes podem ser letras, sublinhados ou dígitos (0-9).
     • Os parâmetros de visualização nomeados diferenciam maiúsculas de minúsculas. Por exemplo, $@PARAMETER_NAME é interpretado de maneira diferente de $@parameter_name.

     Confira a seguir um exemplo de criação de uma visualização segura parametrizada que usa um parâmetro de visualização nomeado:

     CREATE VIEW user_specific_items WITH (security_barrier) AS
     SELECT item_id, item_name, description, owner_id
     FROM items t
     WHERE owner_id = $@app_user_id;
     

2. Conceda SELECT na visualização a qualquer usuário do banco de dados que tenha permissão para consultar a visualização.

3. Conceda USAGE no esquema que contém as tabelas definidas na visualização a qualquer usuário do banco de dados que tenha permissão para consultar a visualização.

Para mais informações, consulte Proteja e controle o acesso aos dados do aplicativo usando visualizações seguras parametrizadas.

Configurar a segurança do aplicativo

Para configurar a segurança dos aplicativos usando visualizações seguras parametrizadas, siga estas etapas:

1. Crie a visualização segura parametrizada como um usuário administrativo. Esse é um usuário do banco de dados do Cloud SQL que realiza operações administrativas para o aplicativo.

2. Crie um novo papel de banco de dados para executar consultas em visualizações seguras parametrizadas. Esse é um papel de banco de dados do Cloud SQL que o aplicativo usa para se conectar e fazer login no banco de dados.

   1. Conceda permissões ao novo papel para as visualizações seguras, que normalmente incluem privilégios SELECT para as visualizações e USAGE nos esquemas.
   2. Limite os objetos que esse papel pode acessar ao conjunto mínimo necessário de funções e objetos públicos de que o aplicativo precisa. Evite fornecer acesso a esquemas e tabelas que não são públicos.

   Ao consultar as visualizações, o aplicativo fornece os valores dos parâmetros de visualização necessários, que estão vinculados à identidade do usuário do aplicativo.

   Para mais informações, consulte Criar e gerenciar usuários.

Consultar uma visualização segura parametrizada

Para consultar uma visualização segura parametrizada, use uma das seguintes opções:

• Baseada em JSON: use essa API para executar a consulta em uma única vez e retornar linhas JSON.
• Baseada em CURSOR: use essa API quando tiver consultas de execução mais longa ou consultas grandes e quiser buscar o resultado em lotes.
• Instrução PREPARE .. AS RESTRICTED: use PREPARE .. AS RESTRICTED para definir o plano de consulta e, em seguida, execute EXECUTE ... WITH VIEW PARAMETERS (...) para executá-lo com parâmetros específicos para a visualização.

API JSON

Execute a função execute_parameterized_query(), que tem a seguinte sintaxe:

SELECT * FROM
parameterized_views.execute_parameterized_query(
    query => SQL_QUERY,
    param_names => ARRAY [PARAMETER_NAMES],
    param_values => ARRAY [PARAMETER_VALUES]
);

A função retorna uma tabela de objetos JSON. Cada linha na tabela é equivalente ao valor row_to_json() da linha de resultado da consulta original.

O uso dessa API limita o tamanho do conjunto de resultados por tamanho (em KB) e pelo número de linhas. É possível configurar esses limites usando parameterized_views.json_results_max_size e parameterized_views.json_results_max_rows.

API CURSOR

Execute a função execute_parameterized_query() com um nome de cursor, que cria e retorna um CURSOR com escopo de transação:

-- Must be in a transaction block
BEGIN;

SELECT * FROM
parameterized_views.execute_parameterized_query(
    query => SQL_QUERY,
    cursor_name => CURSOR_NAME,
    param_names => ARRAY [PARAMETER_NAMES],
    param_values => ARRAY [PARAMETER_VALUES]
);

FETCH ALL FROM CURSOR_NAME;

END;

Instruções preparadas

O método de instruções preparadas permite preparar um plano de consulta uma vez e executá-lo várias vezes com valores diferentes para os parâmetros posicionais da consulta e os parâmetros nomeados da visualização.

Para usar uma instrução preparada, faça o seguinte:

1. Crie a instrução preparada.

   -- Prepare the statement
   PREPARE PREPARED_STATEMENT_NAME (QUERY_PARAM_TYPE_1, QUERY_PARAM_TYPE_N)
   AS RESTRICTED SQL_QUERY;

   Substitua:

   • PREPARED_STATEMENT_NAME: o nome da instrução preparada
   • QUERY_PARAM_TYPE_N: o tipo de dados do parâmetro de consulta, como TEXT
   • SQL_QUERY: a consulta SQL a ser executada como parte da instrução preparada com o valor ou valores fornecidos

2. Execute a instrução preparada.

   -- Execute the statement with query parameters and view parameters
   EXECUTE PREPARED_STATEMENT_NAME (QUERY_VALUE_1, QUERY_VALUE_N)
   WITH VIEW PARAMETERS (PARAMETER_NAME_1 := 'PARAMETER_VALUE_1', PARAMETER_NAME_N := 'PARAMETER_VALUE_N');

   Substitua:

   • PREPARED_STATEMENT_NAME: o nome da instrução preparada.
   • QUERY_VALUE_N: o valor ou valores a serem fornecidos como um parâmetro ou parâmetros para a consulta SQL
   • PARAMETER_NAME_N: o nome do parâmetro ou parâmetros de visualização nomeados que você definiu ao criar a visualização segura parametrizada. Você cria o parâmetro de visualização nomeado na coluna da tabela.
   • PARAMETER_VALUE_N: o valor ou valores para o parâmetro de visualização nomeado que restringe a visualização segura parametrizada às linhas associadas ao valor.

3. Limpe a instrução preparada.

   -- Cleanup
   DEALLOCATE PREPARED_STATEMENT_NAME>;

O exemplo a seguir usa uma visualização segura parametrizada chamada user_specific_items, que exige o parâmetro de visualização nomeado $@app_user_id.

-- Prepare a query with a positional parameter $1 for the item_name pattern
PREPARE get_items_by_name (TEXT) AS RESTRICTED
SELECT item_id, item_name FROM user_specific_items
WHERE item_name LIKE $1;

Depois de criar a instrução preparada, você pode executá-la várias vezes e atribuir valores diferentes para a consulta e o parâmetro de visualização nomeado.

Por exemplo, a primeira execução de consulta:

-- Execute for user 123, looking for items like '%Laptop%'
EXECUTE get_items_by_name ('%Laptop%')
WITH VIEW PARAMETERS (app_user_id := '123');

A segunda execução de consulta:

-- Execute for user 456, looking for items like '%Book%'
EXECUTE get_items_by_name ('%Book%')
WITH VIEW PARAMETERS (app_user_id := '456');

A cláusula WITH VIEW PARAMETERS é onde os parâmetros de visualização nomeados (123, 456) para a visualização segura parametrizada são fornecidos, separados dos parâmetros posicionais para a consulta preparada (%Laptop%, %Book%).

Por fim, limpe a instrução preparada.

-- Clean up the get_item_by_name prepared statement
DEALLOCATE get_items_by_name;

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 segura parametrizada:

• Somente leitura: apenas instruções SELECT somente leitura são permitidas. DML (INSERT, UPDATE, DELETE) e DDL (CREATE, ALTER) são proibidos.
• Sem aninhamento: chamadas recursivas para execute_parameterized_query são proibidas.
• Limites de extensão: algumas extensões que iniciam novas sessões em segundo plano (por exemplo, dblink, pg_cron) não são permitidas.
• Instruções EXPLAIN não são permitidas para evitar possíveis vazamentos de informações usando planos de consulta.

Listar todas as visualizações parametrizadas

Use a visualização parameterized_views.all_parameterized_views para listar todas as visualizações parametrizadas (aquelas que contêm pelo menos um parâmetro nomeado $@...).

SELECT * FROM parameterized_views.all_parameterized_views;

A seguir

• Visão geral das visualizações seguras parametrizadas

• Proteja e controle o acesso aos dados do aplicativo usando visualizações seguras parametrizadas (tutorial)

• Visão geral dos conjuntos de contexto