SQL Academy · Leçon

Autorisations au niveau des colonnes

Masquez les colonnes sensibles.

Autorisations au niveau des colonnes est une leçon SQL Academy gratuite sur CoddyKit. Ceci est la leçon 3 sur 4. Tu peux lire la leçon complète ci-dessous gratuitement — puis la pratiquer en direct dans le navigateur avec un éditeur de code intégré et un tuteur IA 24/7. Elle fait partie du parcours d'apprentissage SQL Academy, et ta progression se synchronise sur le web et l'application CoddyKit. Le cours SQL Academy comprend 4 leçons au total.

Pourquoi les permissions au niveau des colonnes sont importantes

Tous les utilisateurs ne devraient pas voir toutes les colonnes d'une table. Une colonne salaire, un password_hash ou un credit_card_number peut se trouver dans la même table que des données parfaitement publiques, comme un nom d'utilisateur ou une adresse e-mail.

Les permissions au niveau des colonnes permettent d'accorder l'accès à des colonnes précises plutôt qu'à la table entière, afin de masquer les données sensibles aux utilisateurs qui n'ont aucune raison professionnelle de les consulter.

GRANT sur une table entière

Par défaut, GRANT SELECT ON table permet à un rôle de lire toutes les colonnes. Cela convient aux données publiques, mais pose problème lorsque la table mélange des colonnes sensibles et non sensibles.

La requête ci-dessous accorde au rôle analyst un accès complet en lecture à la table employees, y compris au salaire et au SSN.

GRANT SELECT ON employees TO analyst;

Syntaxe de GRANT au niveau des colonnes

PostgreSQL (et le langage SQL standard) permet de lister des noms de colonnes précis dans une instruction GRANT. La syntaxe est la suivante :

GRANT privilege (col1, col2) ON table TO role;

L'exemple ci-dessous accorde au rôle analyst l'autorisation de lire uniquement id, name et department — mais PAS salary ni ssn.

GRANT SELECT (id, name, department) ON employees TO analyst;

Vérifier les permissions des colonnes

Vous pouvez examiner les privilèges au niveau des colonnes dans PostgreSQL en interrogeant la vue information_schema.column_privileges. Elle indique quel bénéficiaire dispose de quelle permission sur quelle colonne.

SELECT grantee, table_name, column_name, privilege_type
FROM information_schema.column_privileges
WHERE table_name = 'employees'
ORDER BY grantee, column_name;

Que se passe-t-il sans les bonnes colonnes

Si un rôle tente de lire une colonne à laquelle il n'a pas reçu accès, la base de données renvoie une erreur d'autorisation refusée. Seules les références aux colonnes autorisées aboutiront.

En supposant que analyst ait reçu uniquement id, name et department, la première requête ci-dessous échoue ; la seconde réussit.

-- This will fail for analyst (no permission on salary):
-- SELECT id, name, salary FROM employees;

-- This succeeds:
SELECT id, name, department FROM employees;

Permission UPDATE au niveau des colonnes

Les restrictions au niveau des colonnes s'appliquent également à UPDATE. Vous pouvez autoriser un rôle à mettre à jour uniquement certaines colonnes — par exemple, permettre à un rôle du service d'assistance de modifier le statut d'un utilisateur sans pouvoir changer son adresse e-mail ou son password_hash.

GRANT UPDATE (status) ON users TO helpdesk;

-- helpdesk can now run:
UPDATE users SET status = 'suspended' WHERE id = 42;

Masquer des colonnes avec des vues

Une autre approche courante consiste à créer une vue qui n'expose que les colonnes sûres, puis à accorder l'accès à la vue plutôt qu'à la table de base. Cette approche fonctionne avec toutes les bases de données, et pas uniquement avec celles qui prennent en charge GRANT au niveau des colonnes.

CREATE VIEW public_employees AS
SELECT id, name, department, hire_date
FROM employees;

GRANT SELECT ON public_employees TO analyst;

Révoquer l'accès au niveau des colonnes

Tout comme avec GRANT, vous pouvez utiliser REVOKE avec une liste de colonnes pour supprimer l'accès à des colonnes précises. Si un rôle disposait d'un accès étendu au niveau de la table, vous devrez peut-être le révoquer entièrement avant d'accorder un accès limité au niveau des colonnes.

-- Remove all SELECT on the table first
REVOKE SELECT ON employees FROM analyst;

-- Then grant only safe columns
GRANT SELECT (id, name, department) ON employees TO analyst;

Permissions des colonnes et sécurité au niveau des lignes ensemble

Les permissions au niveau des colonnes et la sécurité au niveau des lignes (RLS) sont complémentaires. RLS contrôle quelles lignes un utilisateur peut voir ; les permissions au niveau des colonnes contrôlent quelles colonnes de ces lignes sont visibles.

Ensemble, elles forment un contrôle d'accès puissant à deux dimensions : limiter l'ensemble des lignes ET masquer les champs sensibles de chaque ligne.

-- RLS policy: employees can see only their own row
CREATE POLICY own_row ON employees
  FOR SELECT
  USING (user_id = current_user_id());

-- Column grant: hide salary even for own row
GRANT SELECT (id, name, department) ON employees TO employee_role;

Utiliser des fonctions SECURITY DEFINER

Lorsque vous avez besoin d'une logique précise allant au-delà de simples listes de colonnes, une fonction SECURITY DEFINER peut servir d'intermédiaire. La fonction s'exécute avec les privilèges de son propriétaire (qui dispose d'un accès complet aux colonnes) et ne renvoie que ce qu'elle choisit d'exposer, quel que soit son appelant.

CREATE OR REPLACE FUNCTION get_employee_summary(emp_id INT)
RETURNS TABLE(id INT, name TEXT, department TEXT)
SECURITY DEFINER
LANGUAGE sql AS
$$
  SELECT id, name, department
  FROM employees
  WHERE id = emp_id;
$$;

GRANT EXECUTE ON FUNCTION get_employee_summary(INT) TO analyst;

Conception pratique : sécurité des colonnes en couches

Un modèle de production robuste combine trois couches :

Propriété de la table — seul le compte de service de l'application est propriétaire de la table de base.
Vues ou commandes GRANT — les rôles de lecture n'ont accès qu'aux colonnes non sensibles.
Colonnes d'audit — consigner l'utilisateur et l'horodatage associés aux modifications de données sensibles au moyen de déclencheurs.

Ainsi, même si un rôle reçoit accidentellement trop de privilèges à un niveau, les autres niveaux continuent de protéger les données.

-- Layer 1: revoke public access
REVOKE ALL ON employees FROM PUBLIC;

-- Layer 2: expose safe columns via view
CREATE VIEW employee_public AS
SELECT id, name, department, hire_date FROM employees;

GRANT SELECT ON employee_public TO reporting_role;

-- Layer 3: audit trigger logs sensitive field reads (pseudocode)
-- CREATE TRIGGER audit_salary AFTER SELECT ON employees ...

Vérification rapide

Quelle instruction SQL accorde correctement au rôle hr_viewer la possibilité de lire uniquement les colonnes name et department de la table employees ?

Récapitulatif : permissions au niveau des colonnes

Les permissions au niveau des colonnes permettent de limiter l'accès à des champs individuels plutôt qu'à des tables entières, en masquant les données sensibles comme les salaires, les numéros SSN et les hachages de mots de passe aux rôles ne disposant pas des privilèges nécessaires.

Points essentiels :

Utilisez GRANT SELECT (col1, col2) ON table TO role pour limiter les colonnes accessibles en lecture.
Utilisez REVOKE avec une liste de colonnes pour supprimer l'accès à certaines colonnes.
Les vues constituent une solution portable qui fonctionne avec toutes les bases de données.
Combinez les permissions au niveau des colonnes avec RLS pour créer un contrôle d'accès à deux dimensions.
Les fonctions SECURITY DEFINER fournissent un filtrage programmatique au niveau des colonnes, avec une logique supplémentaire.

Appliquée de manière cohérente, la sécurité au niveau des colonnes est l'un des moyens les plus simples et efficaces de faire respecter le principe du moindre privilège au niveau des données.

Questions Fréquemment Posées

La leçon « Autorisations au niveau des colonnes » est-elle gratuite ?

Oui — le texte complet de « Autorisations au niveau des colonnes » est gratuit à lire ici sur le web. Pour la pratiquer de manière interactive (un éditeur de code intégré et un tuteur IA 24/7) et déverrouiller le reste du cours SQL Academy, passe à CoddyKit PRO. Le cours SQL Academy comprend 4 leçons au total.

Qu'est-ce que j'apprendrai dans « Autorisations au niveau des colonnes » ?

Masquez les colonnes sensibles. Tu pratiques SQL Academy avec du code pratique que tu exécutes directement dans le navigateur, et un tuteur IA 24/7 répond à tes questions au fur et à mesure que tu avances dans la leçon.

Dois-je avoir de l'expérience pour commencer SQL Academy ?

Aucune expérience préalable n'est requise. SQL Academy sur CoddyKit est structuré pour les débutants jusqu'aux apprenants avancés, donc tu peux commencer ici ou depuis le début et avancer à ton rythme. Ceci est la leçon 3 sur 4.

Combien de temps prend la leçon « Autorisations au niveau des colonnes » ?

La plupart des leçons CoddyKit prennent environ 5–10 minutes. Chacune est courte et interactive, tu progresses régulièrement et tu repiques exactement où tu t'es arrêté sur le web et l'app.

Peux-tu écrire et exécuter du code dans cette leçon SQL Academy ?

Oui. Chaque leçon SQL Academy inclut un éditeur de code intégré, tu écris et exécutes du vrai code directement dans ton navigateur et tu reçois des retours IA instantanés — aucune configuration locale requise.

Toutes les leçons de ce cours

Rôles et privilèges
Politiques de sécurité au niveau des lignes
Autorisations au niveau des colonnes
Auditer les accès

← Retour à SQL Academy