Transmission de références pour les tables, les vues, les fonctions et les requêtes aux procédures stockées¶

Un exemple simple¶

Supposons qu’une procédure stockée avec droits du propriétaire soit conçue pour insérer des lignes dans une table spécifiée par un argument d’entrée. Vous trouverez ci-dessous un exemple écrit avec Exécution de scripts Snowflake :

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

Voici un exemple similaire écrit en JavaScript :

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);
$$;

Supposons que vous deviez appeler cette procédure pour une table appartenant à un autre rôle :

USE ROLE table_owner;

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

Si vous appelez la procédure stockée en transmettant le nom de la table, la procédure stockée échouera, car le propriétaire de la procédure stockée ne dispose pas des privilèges suffisants pour accéder à la table :

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.

Pour permettre à la procédure stockée d’effectuer des actions SQL sur la table en tant qu’appelant, créez une référence à la table et transmettez cette référence plutôt que le nom de la table.

Pour créer la référence, appelez la fonction SYSTEM$REFERENCE. Par exemple :

USE ROLE table_owner;

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

L’exemple ci-dessus transmet les arguments suivants à la fonction SYSTEM$REFERENCE :

• 'TABLE' pour le type de l’objet.

• 'table_with_different_owner' pour le nom de la table.

• 'SESSION' pour indiquer que la référence doit être limitée à la session.

• 'INSERT' comme le privilège nécessaire pour effectuer l’action sur l’objet.

Précision du champ d’application de la référence¶

La référence est valable soit pour la durée de l’appel dans lequel la référence est transmise, soit pour la durée de la session. Le contexte dans lequel la référence est créée détermine son champ d’application :

• Si vous créez et transmettez une référence à une procédure stockée dans une seule instruction, la référence a la même visibilité qu’une variable déclarée dans le bloc le plus externe de la procédure stockée :

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

  Copy

• Si vous créez une référence et que vous l’affectez à une variable de session, la référence reste valable pendant toute la durée de la session, même si vous désactivez la variable de session :

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

  Copy

Pour spécifier que le champ d’application de la référence doit être la durée de la session, quel que soit le contexte dans lequel la référence est créée, transmettez 'SESSION' comme troisième argument (session_scope) de la fonction SYSTEM$REFERENCE :

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

Attribution de privilèges supplémentaires dans une référence¶

Par défaut, une référence confère un sous-ensemble de privilèges, en fonction du type de l’objet référencé. Par exemple, une référence à une table confère le privilège SELECT sur cette table pour le rôle actif ou le rôle secondaire de l’utilisateur qui a créé la référence. Les privilèges par défaut dépendent du type d’objet. Pour la liste des objets pris en charge, des privilèges et des privilèges par défaut, consultez Types d’objets pris en charge et privilèges pour les références.

Pour conférer des privilèges supplémentaires, spécifiez ces privilèges en tant qu’arguments supplémentaires à la fonction SYSTEM$REFERENCE. Par exemple, pour conférer les privilèges INSERT, UPDATE et TRUNCATE à une table :

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

Notez que vous ne pouvez pas spécifier OWNERSHIP ou ALL comme privilèges.

Après la création d’une référence, les modifications apportées aux privilèges du créateur de la référence se répercutent sur les privilèges associés à la référence. Par exemple, si le privilège INSERT est révoqué pour le créateur d’une référence, le privilège INSERT n’est plus associé à la référence.

Utilisation de références à des tables et vues avec des politiques de masquage¶

Lorsque vous utilisez une référence à une table ou à une vue qui a une politique de masquage, le rôle de la référence est le rôle de l’invocateur (le rôle renvoyé par INVOKER_ROLE), que la référence soit utilisée dans une requête, une procédure stockée ou une fonction définie par l’utilisateur.

L’utilisation d’une référence ne modifie pas le rôle actuel (le rôle renvoyé par CURRENT_ROLE).

Création de références dans les procédures stockées¶

Si vous écrivez une procédure stockée avec droits du propriétaire, ne créez pas de référence dans le corps de la procédure stockée.

Une référence créée dans une procédure stockée avec droits du propriétaire utilise le rôle du propriétaire de la procédure stockée. Les références doivent utiliser le rôle de l’utilisateur qui appelle la procédure stockée. Pour une procédure stockée avec droits du propriétaire, l’utilisateur qui appelle la procédure stockée doit créer la référence et la transmettre à la procédure stockée.

Si vous écrivez une procédure stockée avec droits de l’appelant, vous pouvez créer une référence dans le corps de la procédure stockée.

Utilisation des références de requête¶

Si vous devez transmettre une requête utilisée dans la clause FROM d’une instruction SELECT dans une procédure stockée, créez et transmettez une référence de requête.

Par exemple, supposons qu’une procédure stockée transmette une instruction SELECT destinée à être utilisée dans la clause FROM d’une autre instruction SELECT. Dans l’exemple ci-dessous, l’argument de la requête est censé être une instruction SELECT. Cet exemple se trouve dans Exécution de scripts Snowflake :

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;

Voici un exemple similaire écrit en JavaScript :

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);
$$;

La procédure stockée utilise les droits du propriétaire. Si le propriétaire de la procédure stockée ne dispose pas des privilèges nécessaires pour interroger la table dans l’instruction SELECT, l’appel à la procédure stockée échoue.

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.

Pour permettre à la procédure stockée d’exécuter la requête en tant qu’appelant, créez une référence de requête pour l’instruction SELECT et transmettez cette référence plutôt que l’instruction SELECT.

Pour créer la référence, appelez la fonction SYSTEM$QUERY_REFERENCE en lui transmettant les arguments suivants :

• 'SELECT x FROM table_with_different_owner' comme requête.

  Notez que si l’instruction SELECT contient des guillemets simples ou d’autres caractères spéciaux (par exemple, des sauts de lignes), vous devez échapper ces caractères avec des barres obliques inverses.

• true pour indiquer que la référence de la requête doit être limitée à la session.

Par exemple :

USE ROLE table_owner;

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

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

Dans la procédure stockée, vous pouvez ajouter une référence de requête à la clause FROM d’une requête. Par exemple :

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

Pour plus de détails sur cette fonction, reportez-vous à SYSTEM$QUERY_REFERENCE.

Pour connaître les limitations liées à la création et à l’utilisation des références de requête, reportez-vous à Limites actuelles.

Limites actuelles¶

Actuellement, les références présentent les limites suivantes :

• GET_DDL et SYSTEM$GET_TAG ne prennent pas en charge les références en tant qu’arguments d’entrée.

• Vous ne pouvez créer que des références à des tables, des vues et des fonctions.

• Dans les requêtes qui contiennent des références, le cache du plan et le cache du résultat ne sont pas utilisés.

• Pour les références des requêtes :

  • Vous ne pouvez créer des références de requête que pour les instructions SELECT qui servent de vues en ligne.

  • Lorsque vous créez une référence de requête, vous ne pouvez pas spécifier de variable de liaison ou de session.

  • Dans votre procédure stockée, vous ne pouvez utiliser une référence de requête que dans la clause FROM d’une instruction SELECT.