Управление пользователями и правами доступа в PostgreSQL, MySQL и MS SQL: команды CREATE USER, GRANT и REVOKE на практике

Разграничение доступа в реляционной СУБД держится на трёх командах: CREATE USER, GRANT и REVOKE. Сложность в том, что каждая платформа вкладывает в слово «пользователь» свой смысл. В PostgreSQL это роль с атрибутом LOGIN. В MySQL учётная запись существует только в паре «имя + хост». В MS SQL Server сначала появляется логин на уровне сервера, и лишь затем пользователь внутри базы данных.

Задача «дать аналитику доступ только на чтение» решается по-разному. В PostgreSQL понадобится выдать SELECT на все таблицы схемы и отдельно продумать права на последовательности. В MySQL достаточно GRANT SELECT ON app_db.* с явным указанием хоста. В MS SQL Server быстрее всего добавить пользователя в фиксированную роль db_datareader. Ниже собраны готовые сценарии для пользователя приложения и read-only роли, сравнительная таблица моделей привилегий и список ошибок, которые чаще всего приводят к раздутым правам.

Команды соответствуют синтаксису PostgreSQL 15, MySQL 8.0 и MS SQL Server 2019. В других версиях возможны отличия, поэтому применяйте их сначала на тестовом экземпляре и сверяйтесь с документацией своей версии. Общую картину защиты серверов баз данных разбирает материал о безопасности и администрировании СУБД.

Зачем нужен единый подход к управлению доступом в трёх СУБД

Права пользователя приложения определяют масштаб ущерба при компрометации. Если учётная запись умеет только SELECT, INSERT, UPDATE и DELETE в своей схеме, SQL-инъекция не сможет удалить таблицы или прочитать чужие базы. Если у неё GRANT ALL PRIVILEGES и роль с SUPERUSER, атакующий получает весь сервер. Это различие и делает управление доступом основной задачей администрирования, а не формальностью при установке.

Модели привилегий устроены по-разному, и одинаковые названия команд не означают одинаковое поведение.

• PostgreSQL объединяет пользователей и роли в одном понятии: пользователь - это роль с атрибутом LOGIN. Права наследуются через членство в ролях, а уровни объектов идут от кластера к базе, схеме, таблице и столбцу.
• MySQL разделяет пользователя и роль. Пользователь описывается парой «имя + хост», роли появились только в версии 8.0. Отдельных схем в MySQL нет, их роль играет база данных.
• MS SQL Server разделяет логин уровня сервера и пользователя уровня базы. Права выдаются объектам внутри базы, а DENY перекрывает любой GRANT.

Практический пример: в PostgreSQL 15 и новее схема public больше не даёт право CREATE всем ролям по умолчанию, как это было в версиях 14 и ниже. Администратор, переносящий скрипты с PostgreSQL 13 на 15, обнаружит, что приложение потеряло возможность создавать временные объекты в public. В MySQL обратная ловушка: пользователь 'app_user'@'localhost' и 'app_user'@'127.0.0.1' - это два разных аккаунта с независимыми паролями и правами, даже если имена совпадают. В MS SQL Server попытка выполнить CREATE USER без предварительного CREATE LOGIN завершается ошибкой, потому что пользователь базы должен на что-то ссылаться.

Знание этих различий экономит часы отладки и снимает главный риск: выдать прав больше, чем нужно, просто потому что «так короче писать».

Создание пользователя в PostgreSQL: команды и особенности

CREATE USER в PostgreSQL - это синоним CREATE ROLE с атрибутом LOGIN. Роль без LOGIN не может подключиться к серверу, но может владеть объектами, наследовать права и быть членом других ролей.

CREATE ROLE db_owner;

CREATE USER app_user WITH PASSWORD 'Str0ng_App_P@ss' VALID UNTIL '2027-01-01';

ALTER ROLE app_user CONNECTION LIMIT 20;

CREATE DATABASE app_db OWNER db_owner;

Параметр VALID UNTIL задаёт дату, после которой пароль перестаёт работать. CONNECTION LIMIT ограничивает число одновременных подключений и защищает сервер от исчерпания соединений одним сервисом. Полезные атрибуты роли: LOGIN, PASSWORD, CREATEDB, CREATEROLE, INHERIT, CONNECTION LIMIT. Атрибут SUPERUSER на практике не нужен ни приложению, ни аналитику.

Список ролей смотрится через метакоманду psql и системный каталог:

\du

SELECT rolname, rolcanlogin, rolsuper FROM pg_roles ORDER BY rolname;

Само по себе создание роли не даёт доступа к базе. Нужно отдельно разрешить подключение:

GRANT CONNECT ON DATABASE app_db TO app_user;

Аутентификация при подключении настраивается отдельно от привилегий, в файле pg_hba.conf: правила сопоставляют адрес клиента, базу и роль с методом проверки пароля. Выдать GRANT CONNECT и забыть про pg_hba.conf - частая причина того, что пользователь получает отказ, хотя права в базе выглядят корректно.

Как выдать права пользователю приложения в PostgreSQL

Последовательность для приложения: подключение к базе, доступ к схеме, права на таблицы и на последовательности.

GRANT CONNECT ON DATABASE app_db TO app_user;

GRANT USAGE ON SCHEMA public TO app_user;

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;

GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_user;

ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_user;

ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT USAGE, SELECT ON SEQUENCES TO app_user;

Два момента, на которых спотыкаются чаще всего. Первый: GRANT на последовательности обязателен для колонок типа serial, иначе INSERT падает с ошибкой permission denied for sequence. Второй: ALTER DEFAULT PRIVILEGES действует только на объекты, которые создаёт роль, выполнившая эту команду. Если таблицы создаёт db_owner, а ALTER DEFAULT PRIVILEGES выполнил администратор, на новые таблицы права не распространятся. Команду нужно запускать под владельцем схемы или явно указать его через FOR ROLE.

Проверить итоговый набор прав можно так:

\dp public.*

SELECT grantee, privilege_type, table_name
FROM information_schema.role_table_grants
WHERE grantee = 'app_user';

Read-only роль для аналитика в PostgreSQL

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

CREATE ROLE analyst WITH LOGIN PASSWORD 'An@lyst_2026';

GRANT CONNECT ON DATABASE app_db TO analyst;

GRANT USAGE ON SCHEMA public TO analyst;

GRANT SELECT ON ALL TABLES IN SCHEMA public TO analyst;

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO analyst;

Чтобы ограничить аналитика конкретными таблицами, права выдаются поимённо. Колоночные привилегии позволяют скрыть чувствительные поля вроде номера карты или телефона:

GRANT SELECT ON TABLE public.orders, public.customers TO analyst;

GRANT SELECT (id, email, created_at) ON public.customers TO analyst;

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

REVOKE INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public FROM analyst;

REVOKE CREATE ON SCHEMA public FROM analyst;

Для построчного ограничения служит row-level security. Политика привязывается к таблице и фильтрует строки в зависимости от текущего пользователя:

ALTER TABLE public.orders ENABLE ROW LEVEL SECURITY;

CREATE POLICY analyst_region ON public.orders
  FOR SELECT USING (region = current_setting('app.region'));

ALTER TABLE public.orders FORCE ROW LEVEL SECURITY;

Без FORCE ROW LEVEL SECURITY владелец таблицы обходит политики по умолчанию. Это стоит учитывать, если таблицей владеет та же роль, под которой ходит аналитика.

Создание пользователя в MySQL: команды и особенности

В MySQL пользователь определяется парой «имя + хост». Хост в имени аккаунта задаёт, с каких адресов разрешено подключение, и одновременно служит частью идентификатора.

CREATE USER 'app_user'@'10.0.0.%' IDENTIFIED BY 'Str0ng_App_P@ss';

CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'Str0ng_Local_P@ss';

Начиная с MySQL 8.0 плагин аутентификации по умолчанию - caching_sha2_password. Старые клиенты и драйверы, которые его не поддерживают, потребуют либо обновления, либо явного указания IDENTIFIED WITH mysql_native_password. Список аккаунтов и их плагинов смотрится так:

SELECT user, host, plugin FROM mysql.user ORDER BY user, host;

Команда FLUSH PRIVILEGES нужна только тогда, когда таблицы mysql.* правятся напрямую через INSERT, UPDATE или DELETE. После CREATE USER и GRANT она не требуется.

Отдельной сущности «схема» в MySQL нет, её роль играет база данных, поэтому права выдаются на уровне базы или таблицы. Разбор аутентификации и шифрования соединений для этой СУБД собран в статье о безопасности MySQL.

Как выдать права пользователю приложения в MySQL

GRANT SELECT, INSERT, UPDATE, DELETE ON app_db.* TO 'app_user'@'10.0.0.%';

GRANT SELECT ON app_db.countries TO 'app_user'@'10.0.0.%';

SHOW GRANTS FOR 'app_user'@'10.0.0.%';

Право USAGE, означающее «подключение без дополнительных привилегий», MySQL выдаёт автоматически вместе с CREATE USER. Отзывать его не нужно и не стоит: без него аккаунт не сможет войти.

С ограничением доступа к отдельным таблицам есть тонкость. Если аккаунту уже выдан доступ на всю базу, отозвать права на одну таблицу можно только при включённой системной переменной partial_revokes, иначе сервер вернёт ошибку:

SET GLOBAL partial_revokes = ON;

REVOKE SELECT ON app_db.salaries FROM 'app_user'@'10.0.0.%';

Read-only роль для аналитика в MySQL

CREATE USER 'analyst'@'%' IDENTIFIED BY 'An@lyst_2026';

GRANT SELECT ON app_db.* TO 'analyst'@'%';

В MySQL 8.0 права удобнее группировать в роли. Роль активируется автоматически, если назначить её ролью по умолчанию:

CREATE ROLE 'read_only';

GRANT SELECT ON app_db.* TO 'read_only';

GRANT 'read_only' TO 'analyst'@'%';

SET DEFAULT ROLE 'read_only' TO 'analyst'@'%';

SHOW GRANTS FOR 'analyst'@'%';

Без SET DEFAULT ROLE назначенная роль остаётся неактивной до команды SET ROLE, и аналитик увидит пустой результат вместо данных. Отзыв прав на изменение выполняется так:

REVOKE INSERT, UPDATE, DELETE ON app_db.* FROM 'analyst'@'%';

REVOKE 'read_only' FROM 'analyst'@'%';

Проверить, какие именно привилегии активны в текущей сессии, можно через SHOW GRANTS и таблицу information_schema.USER_PRIVILEGES и SCHEMA_PRIVILEGES.

Создание логина и пользователя в MS SQL Server

Модель доступа в MS SQL Server двухуровневая. Логин - это учётная запись на уровне экземпляра, пользователь - учётная запись внутри конкретной базы. Один логин может быть сопоставлен пользователям в нескольких базах.

CREATE LOGIN app_login WITH PASSWORD = 'Str0ng_App_P@ss',
  CHECK_POLICY = ON, DEFAULT_DATABASE = app_db;

Параметр CHECK_POLICY = ON включает проверку пароля по политике Windows, включая срок действия и минимальную длину. Далее логин нужно связать с пользователем внутри базы:

USE app_db;

CREATE USER app_user FOR LOGIN app_login WITH DEFAULT_SCHEMA = dbo;

Существуют и пользователи с автономной аутентификацией, без логина на уровне сервера. Такие аккаунты работают в базах с включённым параметром contained database:

CREATE USER app_user WITH PASSWORD = 'Str0ng_App_P@ss';

На уровне сервера действуют роли sysadmin, securityadmin, dbcreator. Внутри базы доступны фиксированные роли db_datareader, db_datawriter, db_ddladmin, db_owner. Выдавать роль db_owner пользователю приложения не следует: она делает его владельцем всех объектов базы.

Как выдать права пользователю приложения в MS SQL Server

GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::dbo TO app_user;

GRANT EXECUTE ON SCHEMA::dbo TO app_user;

ALTER ROLE db_datareader ADD MEMBER app_user;

ALTER ROLE db_datawriter ADD MEMBER app_user;

Схемные привилегии удобны тем, что действуют и на будущие объекты, если они принадлежат владельцу схемы. Проверить владельца схемы стоит заранее: объекты, созданные в dbo другой учётной записью, могут не подпасть под выданные права. Команда GRANT EXECUTE нужна, когда приложение вызывает хранимые процедуры: без неё вызов завершится ошибкой «EXECUTE permission was denied».

SELECT s.name AS schema_name, p.name AS owner_name
FROM sys.schemas AS s
JOIN sys.database_principals AS p ON s.principal_id = p.principal_id;

Read-only роль для аналитика в MS SQL Server

CREATE LOGIN analyst_login WITH PASSWORD = 'An@lyst_2026';

USE app_db;

CREATE USER analyst_user FOR LOGIN analyst_login;

ALTER ROLE db_datareader ADD MEMBER analyst_user;

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

CREATE ROLE read_only_role;

GRANT SELECT ON SCHEMA::dbo TO read_only_role;

ALTER ROLE read_only_role ADD MEMBER analyst_user;

GRANT SELECT ON dbo.customers (Id, Email) TO analyst_user;

REVOKE SELECT ON dbo.orders FROM analyst_user;

Для отзыва прав при увольнении или смене роли служит не только REVOKE, но и DENY. Семантика такая: DENY перекрывает любые GRANT, выданные пользователю прямо или через роли. Это самый надёжный способ закрыть доступ к таблице, если учётная запись входит в несколько ролей одновременно.

Построчная фильтрация выполняется через функцию-предикат и политику безопасности:

CREATE FUNCTION dbo.fn_region_filter(@region sysname)
RETURNS TABLE WITH SCHEMABINDING
AS RETURN SELECT 1 AS allowed WHERE @region = USER_NAME();

CREATE SECURITY POLICY dbo.RegionPolicy
ADD FILTER PREDICATE dbo.fn_region_filter(region) ON dbo.orders
WITH (STATE = ON);

Функция должна быть создана со SCHEMABINDING, иначе SQL Server не примет политику. Права на функцию и на политику сохраняются за db_owner.

Сравнение моделей привилегий: PostgreSQL, MySQL и MS SQL

Сводная таблица показывает, почему одинаковые по названию команды дают разный результат.

Отсюда практический вывод: скрипты выдачи прав не переносятся между СУБД копированием. Переносится только логика: минимальный набор привилегий, группировка в роли, отзыв при смене обязанностей. Как проверять уже выданные права и находить лишние, подробно описано в руководстве по аудиту безопасности и контролю привилегий.

Типичные ошибки при выдаче прав и как их избежать

1. GRANT ALL PRIVILEGES «на всякий случай». Приложение обычно обходится четырьмя операциями над данными. Полный набор прав открывает доступ к DDL, работе с другими базами и часто к системным каталогам.
2. Роль PUBLIC в PostgreSQL. Права, выданные PUBLIC, получают все роли кластера, включая будущие. В PostgreSQL 15 и новее стоит явно выполнить REVOKE CREATE ON SCHEMA public FROM PUBLIC, если приложение не создаёт объекты в public.
3. WITH GRANT OPTION без необходимости. Пользователь с этой опцией передаёт свои привилегии дальше и может создать аккаунт, о котором администратор не узнает. Выдавайте опцию только администраторам баз данных.
4. Забытый отзыв при смене роли сотрудника. Права аналитика сохраняются после перехода в другой отдел. Регулярная сверка списка учётных записей с кадровыми данными закрывает эту дыру.
5. Отсутствие проверки после миграции. При переносе базы права на последовательности, схемы и функции часто теряются. После переезда стоит сравнить вывод SHOW GRANTS, \dp или sys.database_permissions до и после.
6. Игнорирование принципа наименьших привилегий. Каждой задаче соответствует своя роль: приложению - операции с данными в своей схеме, аналитику - SELECT, сервису резервного копирования - только чтение.
7. Хост в имени пользователя MySQL. Шаблон '%' разрешает подключение с любого адреса, включая внешние. Для внутренних сервисов указывайте подсеть, например '10.0.0.%'.
8. Путаница логина и пользователя в MS SQL Server. Попытка выдать пользователю права сервера или назначить логину привилегии базы приведёт к ошибке.

Дополнительно проверьте, что права не выданы через временные или забытые роли. Комплексный чек-лист по поиску лишних привилегий и анализу логов для PostgreSQL, MySQL и MongoDB собран в статье об аудите баз данных в 2026 году.

Шпаргалка: основные команды для управления доступом

Компактная сводка для повседневной работы. Команды приведены для PostgreSQL 15, MySQL 8.0 и MS SQL Server 2019; в других версиях проверяйте синтаксис по документации.

После настройки проверьте результат от лица самого пользователя: подключитесь его учётной записью и попробуйте выполнить операцию, которая должна быть запрещена. Успешный SELECT и ошибка на UPDATE подтверждают, что права выданы ровно в нужном объёме. Дальше остаётся зафиксировать схему доступа в документации и добавить периодический аудит привилегий в регламент обслуживания базы.