Привилегии в SQL: GRANT, REVOKE и принцип наименьших привилегий

Привилегия в SQL — это разрешение на конкретное действие с объектом базы данных (например, чтение таблицы) или на действие уровня системы (например, создание базы). Привилегиями управляет подъязык DCL (Data Control Language) — команды GRANT (выдать) и REVOKE (отозвать). Ниже разберем, чем объектные права отличаются от системных, как работают роли и WITH GRANT OPTION, и почему безопасную схему доступа строят по принципу наименьших привилегий, а не выдачей ALL всем подряд.

Синтаксис ниже показан для стандарта SQL, PostgreSQL 18 и Microsoft SQL Server. Где диалекты расходятся — это отмечено явно.

Словарь: субъект, объект, привилегия, роль

Четыре понятия легко путаются, поэтому разведем их сразу.

• Пользователь (субъект) — учетная запись, которая подключается к СУБД и выполняет запросы.
• Объект — то, на что выдается право: таблица, представление, столбец, схема, процедура, последовательность.
• Привилегия — разрешение на одно действие с объектом или системой (SELECT, INSERT, CREATE).
• Роль — именованный набор привилегий, который выдают сразу нескольким пользователям, чтобы не раздавать права поштучно.

В разных СУБД границы между этими терминами смещены. В PostgreSQL пользователь — это просто роль с правом входа (LOGIN), отдельной сущности «user» нет. В MS SQL Server субъект уровня сервера (LOGIN) и субъект уровня базы (USER) — разные объекты, и это важно при выдаче прав.

Два вида привилегий: объектные и системные

Объектные права разрешают действие над конкретным объектом. Системные права разрешают действие уровня всей базы или сервера и не привязаны к одному объекту.

Владелец объекта (тот, кто его создал) по умолчанию имеет полный набор объектных прав на него и может выдавать эти права другим. Права суперпользователя (SUPERUSER в PostgreSQL, роль sysadmin в MS SQL) обходят проверки доступа полностью — поэтому их дают минимальному числу учеток.

GRANT: выдать право

Минимальная форма команды одинакова во всех диалектах:

GRANT привилегия ON объект TO субъект;

Полный рабочий пример в PostgreSQL — создаем роль-читателя и разрешаем ей только чтение одной таблицы:

-- PostgreSQL 18
CREATE ROLE app_reader LOGIN PASSWORD 'slozhnyj_parol';
GRANT SELECT ON contacts TO app_reader;

После этого app_reader может выполнять SELECT * FROM contacts, но UPDATE contacts ... вернет ошибку permission denied for table contacts. Права на изменение мы не выдавали — и это правильное поведение, а не сбой.

В MS SQL Server та же задача требует двух субъектов: логина уровня сервера и пользователя уровня базы. Объект адресуется через префикс класса OBJECT::.

-- Microsoft SQL Server
CREATE LOGIN app_reader WITH PASSWORD = 'Slozhnyj_Parol1';
USE Sales;
CREATE USER app_reader FOR LOGIN app_reader;
GRANT SELECT ON OBJECT::dbo.contacts TO app_reader;

Права на отдельные столбцы

Право можно сузить до конкретных столбцов — это часть принципа наименьших привилегий, когда учетке не нужна вся строка. Ключевая оговорка: столбцовое право ограничивает доступ только если у роли нет табличного SELECT на этой таблице. Табличная выдача перекрывает столбцовую, поэтому столбцовый грант дают чистой роли, а не поверх уже выданного GRANT SELECT ON contacts (нашему app_reader табличный SELECT уже выдан выше, для него столбцовое сужение не сработает — заводим отдельную роль).

-- PostgreSQL: у app_viewer только два столбца, без табличного SELECT
-- (синтаксис столбцового GRANT есть и в стандарте SQL)
CREATE ROLE app_viewer LOGIN PASSWORD 'slozhnyj_parol';
GRANT SELECT (id, name) ON contacts TO app_viewer;

Теперь SELECT id, name FROM contacts для app_viewer работает, а SELECT salary FROM contacts вернет отказ. Столбцовые права поддерживают SELECT, INSERT, UPDATE и REFERENCES; для DELETE они не имеют смысла — удаление затрагивает строку целиком, поэтому синтаксиса DELETE (столбец) нет.

REVOKE: отозвать право

REVOKE — противоположность GRANT, снимает ранее выданные права. Возьмем роль app_writer, которой раньше выдали полный набор CRUD, и понизим ее до чтения и вставки:

-- Снимаем изменение и удаление; SELECT и INSERT остаются
REVOKE UPDATE, DELETE ON contacts FROM app_writer;

Отзыв не трогает права, которые вы не назвали: после этой команды SELECT и INSERT у app_writer сохранятся. Отдельно стоит DENY — это конструкция MS SQL Server (и Sybase), в стандарте SQL и PostgreSQL ее нет. DENY не просто снимает право, а явно запрещает действие, и этот запрет перекрывает любые GRANT, в том числе полученные через роль.

-- MS SQL Server: явный запрет на чтение столбца salary сильнее табличного GRANT
DENY SELECT ON OBJECT::dbo.contacts (salary) TO app_reader;

Правило приоритета в MS SQL: DENY побеждает GRANT. Выше app_reader получил табличный SELECT на contacts, но DENY на столбец salary перекрывает его — этот столбец читаться не будет, даже если табличное право осталось. То же с ролями: если SELECT пришел через роль, DENY SELECT все равно сильнее, и доступа не будет. На столбец в MS SQL можно навесить DENY только для SELECT, UPDATE и REFERENCES.

Роли: права набором, а не поштучно

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

-- PostgreSQL: роль только на чтение всей схемы
CREATE ROLE readonly;
GRANT USAGE ON SCHEMA public TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
GRANT readonly TO analyst1, analyst2;

Важная граница: GRANT SELECT ON ALL TABLES выдает права только на таблицы, существующие на момент вызова. Для таблиц, которые создадут позже, права придется выдать заново или заранее настроить ALTER DEFAULT PRIVILEGES.

В MS SQL Server есть встроенные (фиксированные) роли базы данных — их не нужно создавать:

-- MS SQL Server: db_datareader = SELECT на все пользовательские таблицы БД
ALTER ROLE db_datareader ADD MEMBER report_user;

Роль db_datareader дает чтение всех таблиц базы, db_datawriter — INSERT, UPDATE и DELETE. Это удобно, но помните: это доступ ко всей базе сразу, что противоречит принципу наименьших привилегий, если учетке хватило бы пары таблиц.

WITH GRANT OPTION и иерархия делегирования

По умолчанию тот, кому выдали право, не может передать его дальше. Опция WITH GRANT OPTION меняет это — получатель становится способен сам выдавать это право другим.

-- Право читать contacts и передавать это право дальше
GRANT SELECT ON contacts TO team_lead WITH GRANT OPTION;

Так строится цепочка делегирования: владелец -> team_lead -> подчиненные. Проблема в том, что отозвать корневое право, когда оно уже передано дальше, в PostgreSQL напрямую не выйдет. Показать риск избыточных прав проще всего на ошибке.

Неверно — пытаемся отозвать право, которое team_lead уже раздал:

-- PostgreSQL
REVOKE SELECT ON contacts FROM team_lead;

Фактический результат:

ERROR:  dependent privileges exist
HINT:  Use CASCADE to revoke them too.

Исправление — явно каскадно снять и производные права:

REVOKE SELECT ON contacts FROM team_lead CASCADE;

Отсюда вывод: WITH GRANT OPTION раздавайте осознанно. Каждый обладатель этой опции расширяет круг доступа неявно, и полная картина «кто кому что выдал» перестает помещаться в голове.

Принцип наименьших привилегий и безопасность

Принцип наименьших привилегий (least privilege): каждая учетка получает ровно те права, что нужны ее задаче, и ничего сверх. Соблазн выдать GRANT ALL PRIVILEGES и забыть про отказы — главный источник дыр: скомпрометированная учетка приложения с полными правами открывает атакующему всю базу, включая DROP таблиц.

Практические правила для боевой схемы:

• Отдельная учетка для приложения, не администратор. Веб-приложению обычно нужны SELECT, INSERT, UPDATE, DELETE на своих таблицах — и не нужны CREATE, DROP или права управления ролями.
• Разделяйте чтение и запись. Учетке отчетов и аналитики достаточно роли readonly. Пользователь DELETE-прав на исторические таблицы не получает вовсе.
• Разделяйте окружения. Учетки прод-базы не должны иметь доступа к тестовой и наоборот; пароли и роли не переиспользуются между окружениями.
• Ролями, а не поштучно. Назначайте права через роли — так их проще пересматривать и отзывать при увольнении или смене задачи.
• Регулярный аудит. Периодически проверяйте, кто что имеет: в PostgreSQL — представление information_schema.role_table_grants, в MS SQL — sys.database_permissions и sys.database_principals.

Подберем права под задачу, а не «на всякий случай»:

Важна и граница защиты. Права в БД ограничивают действия уже подключенной учетки, но не заменяют защиту подключения: сетевой доступ, шифрование канала (TLS), надежные пароли и хранение секретов вне кода — отдельные слои. Урезанные привилегии уменьшают ущерб от утечки, но не предотвращают саму утечку учетных данных.

Выводы

• Привилегиями в SQL управляет DCL: GRANT выдает право, REVOKE отзывает; в MS SQL есть еще DENY, который перекрывает любые GRANT.
• Права делятся на объектные (действие над таблицей, столбцом, процедурой) и системные (создание объектов, администрирование); столбцовые права позволяют сузить доступ до отдельных полей.
• Роли выдают набор прав сразу многим субъектам; в PostgreSQL пользователь — это роль с LOGIN, в MS SQL логин и пользователь БД разделены.
• WITH GRANT OPTION разрешает передавать право дальше и строит иерархию делегирования, но усложняет отзыв (в PostgreSQL нужен CASCADE).
• Безопасная схема строится по принципу наименьших привилегий: отдельные учетки для приложения, разделение чтения и записи, окружений, роли вместо поштучной раздачи и регулярный аудит.

Где применяется / связь с практикой

FAQ

Чем DENY отличается от REVOKE?
REVOKE снимает ранее выданное право, и если пользователь получит его другим путем (через роль), доступ вернется. DENY (только MS SQL Server) явно запрещает действие и перекрывает любые GRANT, в том числе через роль. В PostgreSQL и стандарте SQL команды DENY нет — там ограничивают доступ отзывом и невыдачей права.

Что означает GRANT … TO PUBLIC?
PUBLIC — это псевдороль, обозначающая всех пользователей базы, включая будущих. GRANT SELECT ON contacts TO PUBLIC открывает чтение таблицы всем. По принципу наименьших привилегий выдачу прав PUBLIC на данные обычно избегают. В PostgreSQL 15 и новее по умолчанию у PUBLIC больше нет права CREATE в схеме public.

Нужны ли особые права на внешний ключ?
Да. Чтобы создать внешний ключ, ссылающийся на чужую таблицу, нужна привилегия REFERENCES на таблицу (или на конкретные столбцы) родителя. Владения собственной таблицей для этого недостаточно, если родительская таблица принадлежит другому пользователю.