Présentation des procédures stockées des droits de l’appelant et des droits du propriétaire¶

Introduction¶

Une procédure stockée des droits de l’appelant s’exécute avec les privilèges de l’appelant. Le principal avantage de la procédure stockée des droits d’un appelant est qu’elle peut accéder aux informations sur cet appelant ou sur la session en cours de l’appelant. Par exemple, une procédure stockée des droits d’un appelant peut lire les variables de session de l’appelant et les utiliser dans une requête.

La procédure stockée des droits d’un propriétaire s’exécute principalement avec les privilèges du propriétaire de la procédure stockée. Le principal avantage de la procédure stockée des droits d’un propriétaire est que le propriétaire peut déléguer des tâches administratives spécifiques, telles que le nettoyage d’anciennes données, à un autre rôle sans lui accorder de privilèges plus généraux, tels que des privilèges pour supprimer toutes les données d’une table spécifique.

Au moment de la création de la procédure stockée, le créateur spécifie si la procédure s’exécute avec les droits du propriétaire ou les droits de l’appelant. La valeur par défaut est avec droits du propriétaire.

Le propriétaire peut modifier la procédure de la procédure stockée des droits d’un propriétaire en procédure stockée des droits de l’appelant (ou vice-versa) en exécutant une commande ALTER PROCEDURE.

Privilèges sur les objets de base de données¶

La procédure stockée des droits d’un appelant s’exécute avec les privilèges de base de données du rôle qui a appelé la procédure stockée. Toute instruction que l’appelant n’a pas pu exécuter en dehors de la procédure stockée ne peut pas non plus être exécutée à l’intérieur de la procédure stockée. Par exemple, si le rôle nommé « Nurse » n’a pas les privilèges pour supprimer des lignes de la table medical_records , et si un utilisateur avec le rôle « Nurse » appelle une procédure stockée de droits d’appelant qui essaie de supprimer des lignes de cette table, alors la procédure stockée échouera.

Une procédure de droits du propriétaire s’exécute avec les droits du propriétaire de la procédure. Cela signifie que si le propriétaire dispose des privilèges nécessaires pour effectuer une tâche, la procédure stockée peut effectuer cette tâche même lorsqu’elle est appelée par un rôle qui ne dispose pas des privilèges nécessaires pour exécuter cette tâche directement. Par exemple, si le rôle nommé « Doctor » dispose des privilèges de base de données pour supprimer des lignes de la table medical_records et que le rôle « Doctor » crée une procédure stockée qui supprime les lignes de plus de 7 ans de cette table, et si le rôle « Doctor » accorde au rôle « Nurse » les privilèges appropriés sur la procédure stockée, alors le rôle « Nurse » peut exécuter la procédure stockée (et supprimer les anciennes lignes de la table via cette procédure stockée), même si le rôle « Nurse » ne dispose pas des privilèges de suppression sur la table.

Accéder à l’état de la session et le définir¶

Comme pour les autres instructions SQL, une instruction CALL s’exécute dans une session et hérite du contexte de cette session, tel que les variables de niveau session, la base de données actuelle, etc. Le contexte exact dont la procédure hérite varie selon que la procédure stockée est une procédure de droits d’un appelant ou une procédure de droits du propriétaire.

Si la procédure stockée des droits d’un appelant apporte des modifications à la session, ces modifications peuvent persister après la fin de CALL. Les procédures stockées avec les droits du propriétaire ne sont pas autorisées à modifier l’état de la session.

Procédures stockées avec droits de l’appelant¶

Les procédures stockées des droits de l’appelant respectent les règles suivantes au sein d’une session :

• Exécuter avec les privilèges de l’appelant, pas les privilèges du propriétaire.

• Hériter l’entrepôt virtuel actuel de l’appelant.

• Utiliser la base de données et le schéma que l’appelant utilise actuellement.

• Peut afficher, définir et désélectionner les variables de session de l’appelant.

• Peut afficher, définir et désélectionner les paramètres de session de l’appelant.

La section ci-dessous fournit des informations plus détaillées sur la manière dont les procédures stockées avec les droits de l’appelant peuvent lire et écrire les variables de niveau session de l’appelant.

CREATE PROCEDURE MyProcedure()
...
$$
   READ SESSION_VAR1;
   SET SESSION_VAR2;
$$
;

SET SESSION_VAR1 = 'some interesting value';
CALL MyProcedure();
SELECT *
  FROM table1
  WHERE column1 = $SESSION_VAR2;

SET SESSION_VAR1 = 'some interesting value';
READ SESSION_VAR1;
SET SESSION_VAR2;
SELECT *
  FROM table1
  WHERE column1 = $SESSION_VAR2;

SET Variable_1 = 49;

CREATE PROCEDURE sv_proc2(PARAMETER_1 FLOAT)
  RETURNS VARCHAR
  LANGUAGE JAVASCRIPT
  AS
  $$
    var rs = snowflake.execute( {sqlText: "SELECT 2 * " + PARAMETER_1} );
    rs.next();
    var MyString = rs.getColumnValue(1);
    return MyString;
  $$
  ;

CALL sv_proc2($Variable_1);

CREATE PROCEDURE sv_proc1()
  RETURNS VARCHAR
  LANGUAGE JAVASCRIPT
  EXECUTE AS CALLER
  AS
  $$
    var rs = snowflake.execute( {sqlText: "SET SESSION_VAR_ZYXW = 51"} );

    var rs = snowflake.execute( {sqlText: "SELECT 2 * $SESSION_VAR_ZYXW"} );
    rs.next();
    var MyString = rs.getColumnValue(1);

    rs = snowflake.execute( {sqlText: "UNSET SESSION_VAR_ZYXW"} );

    return MyString;
  $$
  ;

CALL sv_proc1();

-- This fails because SESSION_VAR_ZYXW is no longer defined.
SELECT $SESSION_VAR_ZYXW;

Procédures stockées de droits du propriétaire¶

Les procédures stockées avec droits du propriétaire respectent les règles suivantes dans une session :

• Exécuter avec les privilèges du propriétaire, pas les privilèges de l’appelant.

  Astuce

  Si vous avez besoin qu’une procédure stockée avec droits du propriétaire effectue des actions sur une table, une vue ou une fonction à laquelle l’appelant a les privilèges d’accès, vous pouvez demander à l’appelant de transmettre une référence à cette table, cette vue ou cette fonction.

  Pour plus de détails, reportez-vous à Transmission de références pour les tables, les vues, les fonctions et les requêtes aux procédures stockées.

• Hériter l’entrepôt virtuel actuel de l’appelant.

• Utilisez la base de données et le schéma dans lesquels la procédure stockée est créée, et non la base de données et le schéma actuellement utilisés par l’appelant.

• Impossible d’accéder à la plupart des informations spécifiques à l’appelant. Par exemple :

  • Impossible d’afficher, de définir ou de désélectionner les variables de session de l’appelant.

  • Impossible d’exécuter SHOW PARAMETERS pour lister les paramètres.

  • Peut utiliser uniquement un sous-ensemble de paramètres de session définis par l’appelant. Par exemple, les commandes SQL qui produisent des valeurs de date peuvent utiliser le paramètre DATE_OUTPUT_FORMAT qui est défini pour la session de l’appelant).

    Pour la liste de ces paramètres, voir Comprendre les effets des paramètres de session d’un appelant sur la procédure de droits du propriétaire.

  • Ne peut définir ou désélectionner aucun des paramètres de session de l’appelant.

  • Impossible d’interroger des fonctions de table INFORMATION_SCHEMA (par exemple, AUTOMATIC_CLUSTERING_HISTORY) qui renvoient des résultats en fonction de l’utilisateur actuel.

• N’autorisez pas les non-propriétaires à afficher des informations sur la procédure à partir de la vue PROCEDURES .

Les restrictions sur les variables de session et les paramètres de session sont décrites plus en détail ci-dessous.

SET PROVINCE = 'Manitoba';
CALL MyProcedure($PROVINCE);

Restrictions supplémentaires sur les procédures stockées avec droits du propriétaire¶

Les procédures stockées des droits du propriétaire comportent plusieurs restrictions supplémentaires, outre les restrictions liées aux variables de session et aux paramètres de session. Ces restrictions concernent les éléments suivants :

• Les fonctions intégrées qui peuvent être appelées à l’intérieur d’une procédure stockée.

• Possibilité d’exécuter des instructions ALTER USER.

• Surveillance des procédures stockées au moment de l’exécution.

• Commandes SHOW et DESCRIBE.

• Les types d’instructions SQL qui peuvent être appelées à l’intérieur d’une procédure stockée.

Les sections suivantes expliquent ces restrictions plus en détail.

Procédures stockées imbriquées avec des droits différents¶

Si une procédure stockée de droits du propriétaire est appelée par la procédure stockée de droits d’un appelant, ou inversement, les règles suivantes s’appliquent :

• Une procédure stockée se comporte comme une procédure stockée de droits de l’appelant si et uniquement si la procédure et toute la hiérarchie d’appels qui la précède sont des procédures stockées de droits de l’appelant.

• Une procédure stockée de droits du propriétaire se comporte toujours comme une procédure stockée de droits du propriétaire, peu importe d’où elle a été appelée.

• Toute procédure stockée appelée directement ou indirectement à partir d’une procédure stockée des droits d’un propriétaire se comporte comme une procédure stockée des droits du propriétaire.

Choisir entre les droits du propriétaire et les droits de l’appelant¶

Créez une procédure stockée en tant que procédure stockée avec droits du propriétaire si tous les éléments suivants sont vrais :

• Vous souhaitez déléguer une tâche à un autre utilisateur qui s’exécutera avec les privilèges du propriétaire, et non les propres privilèges de l’appelant.

  Par exemple, si vous souhaitez qu’un utilisateur sans privilège DELETE sur une table puisse appeler une procédure stockée supprimant les anciennes données, mais pas les données actuelles, vous souhaiterez probablement utiliser une procédure stockée avec droits du propriétaire. Cette procédure contiendra une instruction DELETE qui inclut un filtre (une clause WHERE ) pour contrôler les données qui peuvent être supprimées via le filtre.

  Astuce

  Si vous avez besoin qu’une procédure stockée avec droits du propriétaire effectue des actions sur une table, une vue ou une fonction à laquelle l’appelant a les privilèges d’accès, vous pouvez demander à l’appelant de transmettre une référence à cette table, cette vue ou cette fonction.

  Pour plus de détails, reportez-vous à Transmission de références pour les tables, les vues, les fonctions et les requêtes aux procédures stockées.

• Les restrictions des procédures stockées du propriétaire n’empêcheront pas la procédure stockée de fonctionner correctement.

Créez une procédure stockée en tant que procédure stockée avec droits de l’appelant si les conditions suivantes sont vraies :

• La procédure stockée ne fonctionne que sur les objets que l’appelant possède ou sur lesquels il dispose des privilèges requis.

• Les restrictions des procédures stockées du propriétaire peuvent empêcher le fonctionnement de la procédure stockée. Par exemple, utilisez une procédure de droits d’appelant si l’appelant de la procédure stockée doit utiliser l’environnement de cet appelant (par exemple, variables de session ou paramètres de compte).

Si une procédure particulière peut fonctionner correctement avec les droits de l’appelant ou les droits du propriétaire, la règle suivante peut vous aider à choisir les droits à utiliser :

• Si une procédure est une procédure de droits d’un propriétaire, l’appelant ne dispose pas du privilège d’afficher le code dans la procédure stockée (à moins que l’appelant en soit également le propriétaire). Si vous souhaitez empêcher les appelants d’afficher le code source de la procédure, créez la procédure en tant que procédure de droits du propriétaire. Inversement, si vous souhaitez que les appelants puissent lire le code source, créez la procédure en tant que procédure des droits de l’appelant.