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

Une procédure stockée s’exécute avec les droits de l’appelant ou les droits du propriétaire . Elle ne peut pas fonctionner avec les deux en même temps. Ce chapitre décrit les différences entre la procédure stockée des droits d’un appelant et la procédure stockée des droits du propriétaire.

Dans ce chapitre :

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.

État de la session

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.

Variables de session

Supposons que la procédure stockée nommée MyProcedure exécute des instructions SQL qui lisent et définissent des variables de niveau de session. Dans cet exemple, les détails des commandes read et set ne sont pas importants, les instructions sont donc représentées sous forme de pseudo-code :

  • READ SESSION_VAR1

  • SET SESSION_VAR2

La procédure stockée ressemble au pseudo-code suivant :

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

Supposez que vous exécutez la séquence d’instructions suivante dans la même session :

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

Cela équivaut à exécuter la séquence suivante :

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

En d’autres termes :

  • La procédure stockée peut afficher la variable qui a été définie par des instructions avant l’appel de la procédure.

  • Les instructions après la procédure stockée peuvent afficher la variable qui a été définie à l’intérieur de la procédure.

Pour obtenir un exemple complet qui ne repose pas sur un pseudo-code, voir Utilisation de variables de session avec les procédures stockées des droits de l’appelant et des droits du propriétaire (dans ce chapitre).

Dans de nombreuses procédures stockées, vous souhaiterez hériter des informations de contexte telles que la base de données actuelle et les variables de niveau session en cours.

Toutefois, dans certains cas, vous souhaiterez peut-être que votre procédure stockée soit plus isolée. Par exemple, si votre procédure stockée définit une variable de niveau session, vous souhaiterez peut-être que la variable de niveau session n’influence pas les instructions futures en dehors de votre procédure stockée.

Pour mieux isoler votre procédure stockée du reste de votre session :

  • Évitez d’utiliser directement des variables de niveau session. À la place, transmettez-les en tant que paramètres explicites. Cela oblige l’appelant à réfléchir aux variables de niveau session exactes que la procédure stockée utilisera.

  • Nettoyez toutes les variables de niveau session que vous avez définies dans la procédure stockée (et utilisez des noms qui ne sont susceptibles d’être utilisés nulle part ailleurs). Ainsi, vous ne risquez pas de nettoyer une variable de session qui a existé avant l’appel de la procédure stockée.

La procédure stockée suivante utilise la valeur d’une variable de session en la recevant en tant que paramètre, pas en utilisant directement la variable de session :

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

La procédure stockée suivante crée une variable de session temporaire avec un nom inhabituel et nettoie cette variable avant la fin de la procédure stockée. Lorsqu’une instruction après l’appel de procédure tente d’utiliser la variable de session qui a été nettoyée, cette instruction échoue :

CREATE PROCEDURE sv_proc1()
    RETURNS VARCHAR
    LANGUAGE JAVASCRIPT
    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;

Note

Si vous maîtrisez la programmation dans le langage C (ou des langages similaires comme Java), notez que les variables de session que vous définissez dans une procédure stockée ne fonctionnent pas comme les variables locales en C qui disparaissent à la fin de l’exécution d’une fonction C. Isoler votre procédure stockée de son environnement nécessite plus d’efforts en SQL qu’en C.

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.

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

    • Peut lire uniquement des paramètres de session spécifiques (répertoriés ici), et ne peut pas définir ou désélectionner les paramètres de session de l’appelant.

    • Impossible d’interroger des fonctions de table INFORMATION_SCHEMA telles que 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.

Variables de session

Une procédure stockée n’a pas accès à Variables SQL créé en dehors de la procédure stockée. Cette restriction empêche une procédure stockée écrite ou détenue par un utilisateur de lire des variables SQL créées par un autre utilisateur (appelant de procédure stockée).

Si votre procédure stockée a besoin de valeurs stockées dans des variables SQL de la session actuelle, celles-ci doivent être transmises en tant qu’arguments explicites à la procédure stockée. Par exemple :

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

Paramètres de session

Dans certains cas, il est utile de permettre à la procédure stockée de lire les paramètres de session de l’appelant. La procédure stockée peut personnaliser le comportement de cet appelant ou de cette session. Par exemple, la procédure stockée peut utiliser la préférence de l’appelant DATE_OUTPUT_FORMAT.

Cependant, dans d’autres cas, la lecture des paramètres de session de l’appelant peut ne pas être souhaitable :

  • Si l’auteur (propriétaire) d’une procédure stockée a défini un paramètre de session spécifique mais que les appelants de la procédure stockée n’ont pas défini ce paramètre, celle-ci peut échouer ou se comporter différemment lorsqu’elle est appelée par des utilisateurs autres que l’auteur.

  • Si un utilisateur écrivait une procédure stockée et qu’un autre appelait cette procédure stockée, et si la procédure stockée était autorisée à lire tous les paramètres de session, il serait alors possible pour l’auteur (le propriétaire) de la procédure stockée de lire les paramètres de session définis par l’appelant sans que l’appelant le sache.

Pour réduire ces problèmes potentiels, Snowflake permet à une procédure stockée d’utiliser un sous-ensemble spécifique des paramètres de session de l’appelant (voir la liste ci-dessous).

Si une instruction dans une procédure stockée fait référence à un paramètre non pris en charge, la procédure stockée utilise la valeur du paramètre de niveau compte du propriétaire, et non le paramètre de niveau de session de l’appelant.

Si le propriétaire de la procédure stockée ne définit jamais le paramètre de compte explicitement, la procédure stockée utilise la valeur par défaut pour le paramètre de compte.

La liste des paramètres pris en charge comprend actuellement (la liste peut changer au fil du temps) les éléments suivants :

  • AUTOCOMMIT

  • BINARY_INPUT_FORMAT

  • BINARY_OUTPUT_FORMAT

  • DATE_INPUT_FORMAT

  • DATE_OUTPUT_FORMAT

  • ENABLE_UNLOAD_PHYSICAL_TYPE_OPTIMIZATION

  • ERROR_ON_NONDETERMINISTIC_MERGE

  • ERROR_ON_NONDETERMINISTIC_UPDATE

  • JDBC_TREAT_DECIMAL_AS_INT

  • JSON_INDENT

  • LOCK_TIMEOUT

  • MAX_CONCURRENCY_LEVEL

  • ODBC_USE_CUSTOM_SQL_DATA_TYPES

  • PERIODIC_DATA_REKEYING

  • QUERY_TAG

  • QUERY_WAREHOUSE_NAME

  • ROWS_PER_RESULTSET

  • STATEMENT_QUEUED_TIMEOUT_IN_SECONDS

  • STATEMENT_TIMEOUT_IN_SECONDS

  • STRICT_JSON_OUTPUT

  • TIMESTAMP_DAY_IS_ALWAYS_24H

  • TIMESTAMP_INPUT_FORMAT

  • TIMESTAMP_LTZ_OUTPUT_FORMAT

  • TIMESTAMP_NTZ_OUTPUT_FORMAT

  • TIMESTAMP_OUTPUT_FORMAT

  • TIMESTAMP_TYPE_MAPPING

  • TIMESTAMP_TZ_OUTPUT_FORMAT

  • TIMEZONE

  • TIME_INPUT_FORMAT

  • TIME_OUTPUT_FORMAT

  • TRANSACTION_ABORT_ON_ERROR

  • TRANSACTION_DEFAULT_ISOLATION_LEVEL

  • TWO_DIGIT_CENTURY_START

  • UNSUPPORTED_DDL_ACTION

  • USE_CACHED_RESULT

  • WEEK_OF_YEAR_POLICY

  • WEEK_START

Pour plus d’informations sur les paramètres de session, voir Paramètres, ALTER SESSION et SHOW PARAMETERS.

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 :

  • Possibilité d’appeler la fonction GET_DDL pour obtenir le DDL de la 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.

Note

La plupart des restrictions relatives à la procédure stockée de droits du propriétaire s’appliquent à tous les appelants, y compris le propriétaire.

GET_DDL et procédures stockées

Si une procédure stockée est créée en tant que procédure stockée avec droits du propriétaire, les appelants (autres que le propriétaire) ne peuvent pas afficher le corps de la procédure stockée en appelant GET_DDL( <nom_procédure>, ... ) .

Cela permet à l’auteur de la procédure stockée de protéger les informations confidentielles dans le code source de la procédure stockée.

ALTER USER

Les restrictions suivantes s’appliquent aux instructions ALTER USER dans une procédure stockée dotée des droits du propriétaire :

  • Les procédures stockées avec droits du propriétaire ne peuvent pas exécuter d’instructions ALTER USER qui utilisent implicitement l’utilisateur actuel pour la session. (Cependant, les procédures stockées avec droits du propriétaire peuvent exécuter des instructions ALTER USER identifiant explicitement l’utilisateur, à condition que l’utilisateur ne soit pas l’utilisateur actuel.)

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

Ni le propriétaire ni l’appelant d’une procédure stockée avec droits du propriétaire ne dispose nécessairement de privilèges pour surveiller l’exécution de la procédure stockée.

Un utilisateur disposant du privilège WAREHOUSE MONITOR peut surveiller l’exécution des instructions individuelles SQL liées à l’entrepôt au sein de cette procédure stockée. La plupart des requêtes et des instructions DML sont des instructions liées à l’entrepôt. Les instructions DDL telles que CREATE, ALTER, etc. n’utilisent pas l’entrepôt et ne peuvent pas être surveillées dans le cadre de la surveillance des procédures stockées.

Commandes SHOW et DESCRIBE

La procédure stockée des droits d’un propriétaire ne dispose pas de privilèges suffisants pour lire des informations sur des utilisateurs autres que l’appelant. Par exemple, l’exécution de SHOW USERS LIKE <utilisateur_actuel> affiche des informations sur l’utilisateur actuel, mais SHOW USERS, plus général, ne fonctionne que si l’utilisateur actuel est le seul utilisateur.

Les commandes SHOW suivantes sont autorisées :

  • SHOW DATABASES.

  • SHOW SCHEMAS.

  • SHOW WAREHOUSES.

Restrictions sur les instructions SQL

Bien que les procédures stockées avec droits de l’appelant puissent exécuter n’importe quelle instruction SQL si l’appelant dispose de privilèges suffisants pour en exécuter en dehors d’une procédure stockée, les procédures stockées avec droits du propriétaire ne peuvent appeler qu’un sous-ensemble d’instructions SQL.

Les instructions SQL suivantes peuvent être appelées depuis l’intérieur de la procédure stockée avec droits du propriétaire :

  • SELECT.

  • DML.

  • DDL. (Voir ci-dessus pour les restrictions relatives à l’instruction ALTER USER.)

  • GRANT/REVOKE.

  • Affectation de variable.

  • DESCRIBE et SHOW. (Voir les limitations documentées ci-dessus.)

Les autres instructions SQL ne peuvent pas être appelées depuis l’intérieur de la procédure stockée avec droits du propriétaire.

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.

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