Utiliser des vues sécurisées paramétrées

Ce document explique comment utiliser des vues sécurisées paramétrables dans Cloud SQL pour MySQL, ce qui vous permet de limiter l'accès aux données en fonction de paramètres nommés spécifiques à l'application, tels que les identifiants utilisateur de l'application. Les vues sécurisées paramétrables améliorent la sécurité et le contrôle des accès en étendant les fonctionnalités des vues MySQL. Ces vues atténuent également les risques d'exécution de requêtes non fiables à partir d'applications en appliquant automatiquement un certain nombre de restrictions à toute requête exécutée.

Pour en savoir plus, consultez la section Vues sécurisées paramétrables dans Cloud SQL.

Avant de commencer

Ce document suppose que vous avez déjà créé une instance Cloud SQL pour MySQL.

  1. Assurez-vous que votre instance Cloud SQL exécute MySQL 8.0.43 ou une version ultérieure. Si votre instance MySQL 8.0 exécute une version mineure antérieure, vous pouvez effectuer une mise à niveau de la version mineure.

  2. Activez le flag de base de données cloudsql_parameterized_secure_view pour votre instance.

    Cette modification d'option ne nécessite pas de redémarrer de base de données. Pour en savoir plus, consultez la page Configurer des options de base de données.

Créer une vue sécurisée paramétrable

Pour créer une vue sécurisée paramétrable, exécutez la commande LDD CREATE VIEW. Exemple :

CREATE VIEW v_orders
AS SELECT * FROM orders
WHERE customer_id = @local_customer_id;

Utilisez la syntaxe suivante :

-- Create a view with a parameter using the WHERE clause
CREATE VIEW VIEW_NAME AS
SELECT * FROM TABLE_NAME
WHERE CONDITION;
 
-- Create a view with a parameter using the HAVING clause
CREATE VIEW VIEW_NAME AS
SELECT * FROM TABLE_NAME
WHERE CONDITION_1      -- row level security
GROUP BY COLUMN_NAME
HAVING CONDITION_2;    -- grouping
 
-- Create a view with a parameter using the ON clause
CREATE VIEW VIEW_NAME AS
SELECT * FROM TABLE_NAME_1
JOIN TABLE_NAME_2
ON CONDITION_1       --  join criteria
WHERE CONDITION_2;   --  row level security

Remplacez les éléments suivants :

  • VIEW_NAME : nom de la vue sécurisée paramétrable
  • TABLE_NAME ou TABLE_NAME_N : nom de la table ou des tables à utiliser dans la vue paramétrable
  • COLUMN_NAME ou COLUMN_NAME_N : nom de la ou des colonnes de table à utiliser dans la vue paramétrable

  • CONDITION ou CONDITION_N : instruction ou instructions de condition à évaluer dans le cadre d'une clause WHERE, ON ou HAVING. Vous pouvez spécifier la variable de session utilisée comme paramètre lorsque vous effectuez ultérieurement une requête sur la vue. Une condition ressemble à ce qui suit :

    WHERE customer_id = @local_customer_id

    Les variables de session, @variable_name, ne sont autorisées que dans les clauses WHERE, ON ou HAVING d'une instruction CREATE VIEW.

Interroger une vue sécurisée paramétrable

Pour interroger une vue sécurisée paramétrable dans Cloud SQL pour MySQL, vous avez deux possibilités :

  • Attribuez des valeurs aux variables à l'aide de l'indicateur SET_VIEW_VARS lorsque vous interrogez la vue sécurisée paramétrable. Si l'une des variables référencées par la vue n'est pas définie ou si des variables non liées apparaissent dans l'indicateur SET_VIEW_VARS, la requête sur la vue sécurisée paramétrable échoue.

  • Appelez la procédure mysql.execute_parameterized_query pour interroger la vue sécurisée paramétrable. Si vous utilisez la procédure mysql.execute_parameterized_query, vous fournissez deux arguments à la procédure. Le premier argument est une requête sur la vue sécurisée paramétrable sans aucun indicateur concernant la variable de session. Le deuxième argument fournit toutes les variables de session nécessaires et les valeurs que vous souhaitez fournir.

Utiliser des valeurs d'indicateur

Pour interroger la vue sécurisée paramétrable à l'aide de l'indicateur SET_VIEW_VARS, procédez comme suit :

-- Set the parameter and query
SELECT /*+ SET_VIEW_VARS(SESSION_VARIABLE = VALUE) */ *
FROM VIEW_NAME;

Remplacez les éléments suivants :

  • SESSION_VARIABLE: paramètre nommé de la vue sécurisée paramétrable pour lequel vous pouvez fournir différentes valeurs. Vous définissez le nom de la variable de session lorsque vous créez la vue à l'aide de la @variable_name notation dans la clause de condition de la vue.
  • VALUE : valeur de ce paramètre nommé
  • VIEW_NAME : nom de la vue sécurisée paramétrable

Exemple :

SELECT /*+ SET_VIEW_VARS(local_customer_id = 12345) */ *
FROM v_orders;

Utiliser la procédure de requête paramétrable

Pour interroger la vue sécurisée paramétrable à l'aide de la procédure mysql.execute_parameterized_query, utilisez la syntaxe suivante :

CALL mysql.execute_parameterized_query(
  'SELECT * FROM DATABASE_NAME.VIEW_NAME',
  'SESSION_VARIABLE=VALUE');

Remplacez les éléments suivants :

  • SESSION_VARIABLE: paramètre nommé de la vue sécurisée paramétrable pour lequel vous pouvez fournir différentes valeurs. Vous définissez le nom de la variable de session lorsque vous créez la vue à l'aide de la @variable_name notation dans la clause de condition de la vue.
  • VALUE : valeur de ce paramètre nommé
  • DATABASE_NAME : nom de la base de données de la vue
  • VIEW_NAME : nom de la vue sécurisée paramétrable

Exemple :

   CALL mysql.execute_parameterized_query(
    'SELECT * FROM db.v_orders',
    'local_customer_id = 5, earliest_year = 2021');

Restrictions au niveau des requêtes

La liste suivante présente l'ensemble des opérations restreintes pour les requêtes que vous exécutez à l'aide des options décrites dans Interroger une vue sécurisée paramétrable :

  • La requête ne peut être qu'une instruction SELECT.
  • La requête ne peut pas inclure d'appels à des fonctions ou procédures stockées définies par l'utilisateur.
  • La requête ne peut pas attribuer de variables.
  • Si vous utilisez la méthode CALL mysql.execute_parameterized_query, la requête ne peut pas être constituée de plusieurs instructions concaténées en une seule. Par exemple, l'instruction de requête SELECT * FROM a; DROP TABLE a; n'est pas valide.

Afficher les vues sécurisées paramétrables dans les journaux

Lorsque les utilisateurs de la base de données Cloud SQL pour MySQL tentent de créer une vue sécurisée paramétrable, un message de niveau information est consigné dans le journal d'erreurs MySQL, comme suit :

 User variables permitted in %s.%s through Parameterized
 Secure View feature

Résoudre les problèmes liés aux vues sécurisées paramétrables

Problème Dépannage
Le flag cloud_parameterized_secure_view n'est pas défini pour l'instance Si le flag cloudsql_parameterized_secure_view est défini sur OFF, toute tentative d'interrogation d'une vue sécurisée paramétrable existante génère l'erreur suivante :

Cannot operate on a Parameterized Secure View without `cloudsql_parameterized_secure_view` being set. Please turn the flag on first before querying Parameterized Secure Views

Si vous tentez de créer une vue sécurisée paramétrable sans que le flag soit défini, l'erreur suivante s'affiche :

View's SELECT contains a variable or parameter

Vous pouvez vérifier si les vues sécurisées paramétrables sont activées pour une instance Cloud SQL en vérifiant la variable globale :

SHOW VARIABLES LIKE 'cloudsql_parameterized_secure_view';
La requête échoue avec l'erreur View's SELECT contains a variable or parameter. Les variables ne sont pas autorisées en dehors des clauses WHERE, HAVING, ou ON. L'utilisation d'une variable en dehors de ces clauses génère une erreur.
La requête échoue avec l'erreur User variable `%s` used in Parameterized Secure View %s must be set in the query before using SET_VIEW_VARS hint Définissez la variable de session avant d'exécuter la requête.
La requête échoue avec une erreur de version MySQL Si vous essayez d'interroger une vue sécurisée paramétrable existante à partir d'une version de MySQL antérieure à 8.0.43, l'erreur suivante peut s'afficher :

Variable %s found in view %s. Please upgrade to 8.0.43 or above to use Parameterized Secure Views

Étape suivante