Passing references for objects and queries to stored procedures¶

Background: The problem with passing objects and queries to stored procedures¶

Suponha que um procedimento armazenado de direitos do proprietário seja projetado para inserir linhas em uma tabela especificada por um argumento de entrada. Os seguintes são exemplos escritos em Script Snowflake e JavaScript:

USE ROLE stored_proc_owner;

CREATE OR REPLACE PROCEDURE insert_row(table_identifier VARCHAR)
RETURNS TABLE()
LANGUAGE SQL
AS
$$
BEGIN
  LET stmt VARCHAR := 'INSERT INTO ' || table_identifier || ' VALUES (10)';
  LET res RESULTSET := (EXECUTE IMMEDIATE stmt);
  RETURN TABLE(res);
END;
$$;

USE ROLE stored_proc_owner;

CREATE OR REPLACE PROCEDURE insert_row(table_identifier VARCHAR)
RETURNS FLOAT
LANGUAGE JAVASCRIPT
AS
$$
  let res = snowflake.execute({
    sqlText: "INSERT INTO IDENTIFIER(?) VALUES (10);",
    binds : [TABLE_IDENTIFIER]
  });
  res.next()
  return res.getColumnValue(1);
$$;

Suponha que você precise chamar este procedimento para uma tabela que pertence a uma função diferente:

USE ROLE table_owner;

CREATE OR REPLACE TABLE table_with_different_owner (x NUMBER) AS SELECT 42;

Se você chamar o procedimento armazenado e passar o nome da tabela, o procedimento armazenado falhará porque o proprietário do procedimento armazenado não possui privilégios suficientes para acessar a tabela:

USE ROLE table_owner;

CALL insert_row('table_with_different_owner');

002003 (42S02): Uncaught exception of type 'STATEMENT_ERROR' on line 4 at position 25 : SQL compilation error:
Table 'TABLE_WITH_DIFFERENT_OWNER' does not exist or not authorized.

Para permitir que o procedimento armazenado execute ações SQL na tabela como o chamador, crie uma referência à tabela e passe essa referência, em vez do nome da tabela.

Como criar uma referência¶

Para criar a referência, chame a função SYSTEM$REFERENCE. Por exemplo:

USE ROLE table_owner;

CALL insert_row(SYSTEM$REFERENCE('TABLE', 'table_with_different_owner', 'SESSION', 'INSERT'));

O exemplo acima passa os seguintes argumentos para a função SYSTEM$REFERENCE:

• 'TABLE' para o tipo do objeto.

• 'table_with_different_owner' para o nome da tabela.

• 'SESSION' para indicar que a referência deve ter como escopo a sessão.

• 'INSERT' como o privilégio necessário para executar a ação no objeto.

Especificação do escopo da referência¶

A referência é válida tanto pela duração da chamada na qual a referência é passada quanto pela duração da sessão. O contexto em que a referência é criada determina o escopo:

• Se você criar e passar uma referência a um procedimento armazenado em uma única instrução, a referência terá a mesma visibilidade de uma variável declarada no bloco mais externo do procedimento armazenado:

  CALL select_from_table(SYSTEM$REFERENCE('TABLE', 'my_table');
  

  Copy

• Se você criar uma referência e atribuir a referência a uma variável de sessão, a referência será válida durante a sessão, mesmo que você desmarque a variável de sessão:

  SET tableRef = (SELECT SYSTEM$REFERENCE('TABLE', 'my_table'));
  
  SELECT * FROM IDENTIFIER($tableRef);
  

  Copy

Para especificar que o escopo da referência deve ser a duração da sessão, independentemente do contexto em que a referência é criada, passe 'SESSION' para o terceiro argumento (session_scope) da função SYSTEM$REFERENCE:

CALL insert_row(SYSTEM$REFERENCE('TABLE', 'table_with_different_owner', 'SESSION', 'INSERT'));

Atribuição de privilégios adicionais em uma referência¶

Por padrão, uma referência atribui um subconjunto de privilégios, com base no tipo de objeto referenciado. Por exemplo, uma referência a uma tabela atribui o privilégio SELECT nessa tabela para a função ativa ou função secundária do usuário que criou a referência. Os privilégios padrão dependem do tipo de objeto. Para obter a lista de objetos, privilégios e privilégios padrão suportados, consulte Tipos de objetos e privilégios suportados para referências.

Para atribuir privilégios adicionais, especifique esses privilégios como argumentos adicionais para a função SYSTEM$REFERENCE. Por exemplo, para atribuir os privilégios INSERT, UPDATE e TRUNCATE em uma tabela:

SELECT SYSTEM$REFERENCE('TABLE', 'table_with_different_owner', 'SESSION', 'INSERT', 'UPDATE', 'TRUNCATE');

Note que você não pode especificar OWNERSHIP ou ALL como privilégios.

Depois que uma referência é criada, as alterações nos privilégios do criador da referência são refletidas nos privilégios associados à referência. Por exemplo, se o privilégio INSERT for revogado para o criador de uma referência, o privilégio INSERT não estará mais associado à referência.

Uso de referências a tabelas e exibições com políticas de mascaramento¶

Quando você usa uma referência a uma tabela ou exibição que possui uma política de mascaramento, a função de referência é a função invocadora (a função retornada por INVOKER_ROLE), independentemente de a referência ser usada em uma consulta, procedimento armazenado ou função definida pelo usuário.

O uso de uma referência não altera a função atual (a função retornada por CURRENT_ROLE).

Criação de referências em procedimentos armazenados¶

Se você estiver escrevendo um procedimento armazenado de direitos do proprietário, não crie uma referência dentro do corpo do procedimento armazenado.

Uma referência criada em um procedimento armazenado de direitos do proprietário usa a função do proprietário do procedimento armazenado. As referências devem usar a função do usuário que chama o procedimento armazenado. Para um procedimento armazenado de direitos do proprietário, o usuário que chama o procedimento armazenado deve criar a referência e passá-la para o procedimento armazenado.

Se você estiver escrevendo um procedimento armazenado de direitos do chamador, poderá criar uma referência no corpo do procedimento armazenado.

Uso de referências de consulta¶

Se você precisar passar uma consulta que é usada na cláusula FROM de uma instrução SELECT em um procedimento armazenado, crie e transmita uma referência de consulta.

Por exemplo, suponha que um procedimento armazenado passe uma declaração SELECT que se destina a ser usada na cláusula FROM de outra declaração SELECT. No exemplo abaixo, o argumento de consulta destina-se a ser uma declaração SELECT. Esses exemplos estão em Script Snowflake e JavaScript:

USE ROLE stored_proc_owner;

CREATE OR REPLACE PROCEDURE get_num_results(query VARCHAR)
  RETURNS INTEGER
  LANGUAGE SQL
  AS
  DECLARE
    row_count INTEGER DEFAULT 0;
    stmt VARCHAR DEFAULT 'SELECT COUNT(*) FROM (' || query || ')';
    res RESULTSET DEFAULT (EXECUTE IMMEDIATE :stmt);
    cur CURSOR FOR res;
  BEGIN
    OPEN cur;
    FETCH cur INTO row_count;
    RETURN row_count;
  END;

USE ROLE stored_proc_owner;

CREATE OR REPLACE PROCEDURE get_num_results(query VARCHAR)
RETURNS FLOAT
LANGUAGE JAVASCRIPT
AS
$$
  let res = snowflake.execute({
    sqlText: "SELECT COUNT(*) FROM (" + QUERY + ");",
  });
  res.next()
  return res.getColumnValue(1);
$$;

O procedimento armazenado usa os direitos do proprietário. Se o proprietário do procedimento armazenado não tiver privilégios para consultar a tabela na instrução SELECT, a chamada para o procedimento armazenado falhará.

USE ROLE table_owner;
CREATE OR REPLACE TABLE table_with_different_owner (x NUMBER) AS SELECT 42;

CALL get_num_results('SELECT x FROM table_with_different_owner');

002003 (42S02): Uncaught exception of type 'STATEMENT_ERROR' on line 4 at position 29 : SQL compilation error:
Object 'TABLE_WITH_DIFFERENT_OWNER' does not exist or not authorized.

Para permitir que o procedimento armazenado execute a consulta como o chamador, crie uma referência de consulta para a declaração SELECT, e passe essa referência, em vez da declaração SELECT.

Para criar a referência de consulta, você pode chamar a função SYSTEM$QUERY_REFERENCE.

Se você chamar a função SYSTEM$QUERY_REFERENCE, passe em:

• 'SELECT x FROM table_with_different_owner' como a consulta.

  Note que se a instrução SELECT tiver aspas simples ou outros caracteres especiais (por exemplo, novas linhas), você deverá fazer o escape desses caracteres com barras invertidas.

• true para indicar que a referência de consulta deve ter como escopo a sessão.

Por exemplo:

USE ROLE table_owner;

CALL get_num_results(
  SYSTEM$QUERY_REFERENCE('SELECT x FROM table_with_different_owner', true)
);

+-----------------+
| GET_NUM_RESULTS |
|-----------------|
|               1 |
+-----------------+

Dentro do procedimento armazenado, você pode adicionar uma referência de consulta à cláusula FROM de uma consulta. Por exemplo:

snowflake.execute({
  sqlText: "SELECT COUNT(*) FROM (" + QUERY + ");"
});

Para obter detalhes sobre esta função, consulte SYSTEM$QUERY_REFERENCE.

Para as limitações de criação e uso de referências de consulta, consulte Limitações atuais.

Uso da palavra-chave TABLE para criar uma referência a uma tabela, exibição ou consulta¶

Se você precisar criar uma referência a uma tabela, exibição ou exibição segura que não esteja modificando e que o procedimento armazenado deva consultar, e quiser que a referência seja válida para o escopo da chamada (em vez de para toda a sessão), use a palavra-chave TABLE com a seguinte sintaxe:

TABLE( [[<database_name>.]<schema_name>.]<object_name> )

TABLE("<object_name_that_requires_double_quotes>")

TABLE(IDENTIFIER('string_literal_for_object_name'))

A palavra-chave TABLE fornece uma sintaxe mais simples para chamar a função SYSTEM$REFERENCE para uma tabela ou exibição sem precisar especificar o argumento para o tipo de objeto. Quando você usa a palavra-chave TABLE, a referência apenas confere o privilégio SELECT, e o escopo da referência é a chamada (não a sessão).

Os exemplos a seguir chamam o procedimento armazenado my_procedure e passam referências para tabelas e exibições:

CALL my_procedure(TABLE(my_table));

CALL my_procedure(TABLE(my_database.my_schema.my_view));

CALL my_procedure(TABLE("My Table Name"));

CALL my_procedure(TABLE(IDENTIFIER('my_view')));

Se você quiser criar uma referência a uma consulta, você pode usar a palavra-chave TABLE como alternativa para chamar a função SYSTEM$QUERY_REFERENCE, se a referência só precisar ser válida para o escopo da chamada (em vez de para toda a sessão). Para usar a palavra-chave TABLE, use a seguinte sintaxe:

TABLE(<select_statement>)

Por exemplo:

CALL my_procedure(TABLE(SELECT * FROM my_view));

CALL my_procedure(TABLE(WITH c(s) as (SELECT $1 FROM VALUES (1), (2)) SELECT a, count(*) FROM T, C WHERE s = a GROUP BY a));

Observe o seguinte:

• Você não pode usar variáveis de vinculação no nome do objeto ou na consulta.

• A referência criada pela palavra-chave TABLE é válida durante a chamada. Você não pode especificar um escopo diferente para a referência.

• A referência tem os privilégios padrão conferidos para o tipo de objeto.

Limitações atuais¶

Atualmente, as referências têm as seguintes limitações:

• GET_DDL e SYSTEM$GET_TAG não oferecem suporte às referências como argumentos de entrada.

• You can only create references to tables, views, functions, and procedures.

• Nas consultas que contêm referências, o cache plano e o cache de resultado não são usados.

• Para referências de consulta:

  • Você só pode criar referências de consulta para instruções SELECT que servem como exibições inline.

  • Ao criar uma referência de consulta, você não pode especificar uma variável de vinculação ou variável de sessão.

  • Em seu procedimento armazenado, você só pode usar uma referência de consulta na cláusula FROM de uma instrução SELECT.