SQL Academy · Урок

Разрешения на уровне столбцов

Скрывайте конфиденциальные столбцы

«Разрешения на уровне столбцов» — бесплатный урок SQL Academy на CoddyKit. Это урок 3 из 4. Ты можешь прочитать весь урок бесплатно ниже — а потом практиковать его прямо в браузере с встроенным редактором кода и ИИ-репетитором 24/7. Это часть пути обучения SQL Academy, и твой прогресс синхронизируется между веб-версией и приложением CoddyKit. Курс SQL Academy содержит 4 уроков всего.

Зачем нужны разрешения на уровне столбцов

Не каждый пользователь должен видеть каждый столбец таблицы. Столбец зарплата, хеш пароля или номер кредитной карты может находиться в одной таблице с полностью общедоступными данными, такими как имя пользователя или адрес электронной почты.

Разрешения на уровне столбцов позволяют предоставлять доступ к отдельным столбцам, а не ко всей таблице, скрывая конфиденциальные данные от пользователей, которым нет оснований их видеть.

GRANT для всей таблицы

По умолчанию GRANT SELECT ON table дает роли возможность читать все столбцы. Это подходит для общедоступных данных, но создает проблемы, когда в таблице смешаны конфиденциальные и открытые столбцы.

Приведенный ниже запрос предоставляет роли analyst полный доступ для чтения таблицы employees, включая зарплату и SSN.

GRANT SELECT ON employees TO analyst;

Синтаксис GRANT на уровне столбцов

PostgreSQL (и стандартный SQL) позволяет перечислять имена конкретных столбцов внутри оператора GRANT. Синтаксис выглядит так:

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

Приведенный ниже пример предоставляет роли analyst разрешение читать только id, name и department, но НЕ salary и не ssn.

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

Проверка разрешений для столбцов

Проверить разрешения на уровне столбцов в PostgreSQL можно, запросив представление information_schema.column_privileges. Оно показывает, какой получатель имеет какое разрешение на какой столбец.

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

Что происходит без нужных столбцов

Если роль пытается прочитать столбец, к которому ей не предоставлен доступ, база данных возвращает ошибку в доступе отказано. Успешно выполнится только обращение к разрешенным столбцам.

Если предположить, что роли analyst предоставлен доступ только к id, name и department, первый запрос ниже завершится ошибкой, а второй выполнится успешно.

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

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

Разрешение UPDATE на уровне столбцов

Ограничения на уровне столбцов распространяются и на UPDATE. Вы можете разрешить роли изменять только определенные столбцы — например, позволить сотруднику службы поддержки изменять status пользователя, но не давать ему возможности менять его email или password_hash.

GRANT UPDATE (status) ON users TO helpdesk;

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

Скрытие столбцов с помощью представлений

Еще один распространенный подход — создать представление, показывающее только безопасные столбцы, а затем предоставить доступ к представлению вместо базовой таблицы. Этот подход работает во всех базах данных, а не только в тех, которые поддерживают GRANT на уровне столбцов.

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

GRANT SELECT ON public_employees TO analyst;

Отзыв доступа на уровне столбцов

Как и GRANT, REVOKE можно использовать со списком столбцов, чтобы удалить доступ к определенным столбцам. Если у роли был широкий доступ на уровне таблицы, возможно, сначала потребуется полностью отозвать его, а затем предоставить ограниченный доступ на уровне столбцов.

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

Совместное использование разрешений на уровне столбцов и RLS

Разрешения на уровне столбцов и безопасность на уровне строк (RLS) дополняют друг друга. RLS определяет, какие строки может видеть пользователь, а разрешения на уровне столбцов — какие столбцы в этих строках ему видны.

Вместе они образуют мощную двумерную систему управления доступом: ограничивают набор строк И скрывают конфиденциальные поля внутри каждой строки.

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

Использование функций SECURITY DEFINER

Если Вам нужна более детальная логика, чем простое перечисление столбцов, связующим звеном может стать функция SECURITY DEFINER. Функция выполняется с привилегиями своего владельца, имеющего полный доступ к столбцам, и возвращает только те данные, которые решает предоставить, независимо от того, кто ее вызывает.

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;

Практическая архитектура: многоуровневая защита столбцов

Надежный шаблон для рабочей среды объединяет три уровня:

Владение таблицей — базовой таблицей владеет только служебная учетная запись приложения.
Представления или GRANT для столбцов — ролям, предназначенным для чтения, предоставляется доступ только к неконфиденциальным столбцам.
Аудитные столбцы — с помощью триггеров фиксируйте в журнале, какой пользователь и когда обращался к конфиденциальным данным.

Это гарантирует, что даже если на одном уровне роль случайно получит слишком широкие привилегии, остальные уровни продолжат защищать данные.

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

Быстрая проверка

Какой оператор SQL правильно предоставляет роли hr_viewer возможность читать только столбцы name и department таблицы employees?

Итоги: разрешения на уровне столбцов

Разрешения на уровне столбцов позволяют ограничивать доступ к отдельным полям, а не ко всей таблице, скрывая конфиденциальные данные, такие как зарплаты, номера SSN и хеши паролей, от ролей без необходимых привилегий.

Основные выводы:

Используйте GRANT SELECT (col1, col2) ON table TO role, чтобы ограничить список столбцов, доступных для чтения.
Используйте REVOKE со списком столбцов, чтобы удалить доступ к определенным столбцам.
Представления — переносимая альтернатива, работающая во всех базах данных.
Объединяйте разрешения на уровне столбцов с RLS для двумерного управления доступом.
Функции SECURITY DEFINER обеспечивают программную фильтрацию столбцов с дополнительной логикой.

При последовательном применении защита на уровне столбцов становится одним из самых простых и эффективных способов обеспечить принцип минимальных привилегий на уровне данных.

Часто задаваемые вопросы

Урок «Разрешения на уровне столбцов» бесплатный?

Да — полный текст урока «Разрешения на уровне столбцов» бесплатно доступен здесь в веб-версии. Чтобы практиковать его интерактивно (встроенный редактор кода и ИИ-репетитор 24/7) и разблокировать остальной курс SQL Academy, подпишись на CoddyKit PRO. Курс SQL Academy содержит 4 уроков всего.

Чему я научусь в уроке «Разрешения на уровне столбцов»?

Скрывайте конфиденциальные столбцы Ты практикуешь SQL Academy с помощью реального кода, который запускаешь прямо в браузере, и ИИ-репетитор 24/7 отвечает на твои вопросы во время урока.

Нужен ли мне опыт, чтобы начать SQL Academy?

Предыдущий опыт не требуется. SQL Academy на CoddyKit структурирован для всех уровней — от новичков до продвинутых, поэтому ты можешь начать отсюда или с самого начала и учиться в своем темпе. Это урок 3 из 4.

Сколько времени занимает урок «Разрешения на уровне столбцов»?

Большинство уроков CoddyKit занимают около 5–10 минут. Каждый из них компактный и интерактивный, поэтому ты постоянно делаешь прогресс и продолжаешь с того же места в веб-версии и приложении.

Можно ли писать и запускать код в этом уроке SQL Academy?

Да. Каждый урок SQL Academy включает встроенный редактор кода, поэтому ты пишешь и запускаешь реальный код прямо в браузере и получаешь моментальную обратную связь от AI — локальная установка не требуется.

Все уроки этого курса

← Назад к SQL Academy