Суть вопроса:
Имеем настроенную БД на MS SQL, в которой соответствующим образом розданы права через группы AD — для каждой группы свой login в MS SQL.
Но хотим внести в механизм раздачи прав одну модификацию, а именно:
Некоторым пользователям через группы выданы права и на запись и на DDL, но хотим только для одной таблицы, назовём её CONFIG_TABLE, запретить права на запись для всех, кроме некоторых пользователей.
Создавать для этого отдельные группы в AD и делить пользователей на читающих/пишуших все таблицы и читающих/пишущих все, кроме одной таблицы — нецелесообразно.
В будущем такая таблца может оказаться не одна, и тогда количество групп в AD стало бы слишком большим и управление безопасностью непрозрачным
Исходные данные
Назовём нашу БД DBTEST
В AD есть пользователи U00, U01, ..., U99
U00, U01 — через группы AD включены в серверную роль sysadmin
U10...U19 — через группы AD обладают правами (включены в роли БД) ddl_admin, db_writer, db_reader
U20..U29 — через группы AD обладают правами db_writer, db_reader
U30..U39 — через группы AD обладают правами db_reader
Задача:
Разрешить редактирование таблицы CONFIG_TABLE только для некоторых немногочисленных пользователей из числа U10...U29, пусть у нас будет U15 и U25
Дополнительно:
Таблицы в БД могут часто добавляться и пересоздаваться.
Пользователи в БД создаются часто. По умолчанию, новый пользователь не должен иметь права редактировать CONFIG_TABLE, даже если ему были даны права ddl_admin, db_writer, db_reader.
Разработанная политика безопасности должна быть надёжна.
Первое же решение, которое приходит в голову сразу, использовать
DENY INSERT, UPDATE, DELETE ON OBJECT::dbo.CONFIG_TABLE TO PUBLIC
GO
К сожалению, оно нам не подходит.
Пользователи U15 и U25 не смогут редактировать таблицу.
Преодолеть этот DENY смогут только пользователи с серверной ролью sysadmin — U00, U01
Вариант использовать вместо PUBLIC группу или роль для запрета, тоже не подходит, поскольку пользователи добавляются часто, люди будут просто забывать добавить запрет. А для финансовых организаций вопрос безопасности на первом месте.
Второе решение, которое тоже сразу приходит в голову — определённым группам явно (через GRANT) дать права на все таблицы (на каждую).
Тоже не подходит, потому что таблицы создаются часто.
Поэтому будем искать обходные варианты.
Пока я нашёл 4.
- Триггер, логгирующий изменения в таблицу и отправляющий уведомления о модификациях конфигурации по почте;
- Запрет на редактирование с помощью триггера (UPD: Список пользователей для редактирования может быть получен из группы AD, но надо тестировать);
- Использование встроенных возможностей аудита MS SQL;
- Аудит изменений CONFIG_TABLE с помощью CHANGE DATA CAPTURE.
Первый вариант уведомляет хозяина данных сразу. Третий и четвёртый требуют ежедневного мониторинга.
Можно скомбинировать варианты 1 и 2 (это мне кажется предпочтительным).
Можно скомбинировать варианты 1 и 2 (это мне кажется предпочтительным).
Все скрипты, приведнные в этотй статье, можно запускать последовательно на одном тестовом сервере. Они используют одну базу DBTEST, удаляют её, если она есть, и пересоздают.
Несмотря на кажущуюся простоту триггера здесь есть нюансы.
Вариант 1 — Триггер, логгирующий изменения в таблицу и отправляющий уведомления по почте
Несмотря на кажущуюся простоту триггера здесь есть нюансы.
По умолчанию триггер запускается под правами того пользователя. который редактирует таблицу конфигурации, и под этими же права пытается сделать записать в журнал изменений. А это ему запрещено.
Поэтому триггер должен запускаться под правами достаточными, для записи в таблицу лога конфигурации.
Та же ситуация с отправкой почты.
Пользователь, модифицирубщий таблицу конфигурации обычно не должен иметь прав на отправку почты — вряд ли эту привиллегию следут давать PUBLIC'у, а триггер должен суметь отправить почту. Поэтому триггер должен выполняться в другом контексте.
А из этого следует важное следствие — в триггере кроме текущего пользователя мы должны выяснить, кто его инициировал, поэтому фунция SUSER_SNAME в таком триггере неприменима.
Ниже приведён текст триггера без отправки почты, только с записью в таблицу журнала.
Текст скрипта триггера с отправкой почты можно скачать ниже по ссылке.
Эти два скрипта можно запускать на тестовом окружении под правами SYSADMIN под логином, SA например. Серверная роль sysadmin требуется для создания тестовой базы DBTEST.
Все скрипты можно запускать многократно
Вот результат, который записывается в таблицу лога
Текст скрипта триггера с отправкой почты можно скачать ниже по ссылке.
Эти два скрипта можно запускать на тестовом окружении под правами SYSADMIN под логином, SA например. Серверная роль sysadmin требуется для создания тестовой базы DBTEST.
Все скрипты можно запускать многократно
-- RUN
THIS SCRIPT UNDER sa OR ANY MEMBER OF sysadmin SERVER ROLE
USE
[master]
GO
IF DB_ID(N'DBTEST') IS NOT NULL
BEGIN
PRINT 'Dropping database [DBTEST]';
ALTER DATABASE [DBTEST] SET
SINGLE_USER WITH
ROLLBACK IMMEDIATE
DROP DATABASE [DBTEST];
END ELSE PRINT 'Database [DBTEST] not exist yet';
GO
PRINT 'Creating database [DBTEST]'
GO
CREATE DATABASE [DBTEST]
GO
USE
[DBTEST]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
PRINT 'Creating tables';
CREATE TABLE [dbo].[CONFIG_TABLE](
[ID] [int] identity (1, 1) NOT NULL,
[PARM_NAME] [nvarchar] (64) NOT NULL,
[PARM_VALUE] [nvarchar] (1024) NOT NULL,
CONSTRAINT
[PK_CONFIG_TABLE] PRIMARY KEY CLUSTERED ([ID]),
CONSTRAINT
[UK1_CONFIG_TABLE] UNIQUE ([PARM_NAME])
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[CONFIG_TABLE_LOG] (
[ID] [int]
identity (1, 1) NOT NULL,
[OPERATION] [char](3) NOT NULL,
[CONFIG_TABLE_ID] [int] NOT NULL,
[PARM_NAME_OLD] [nvarchar](64),
[PARM_NAME_NEW] [nvarchar](64),
[PARM_VALUE_OLD] [nvarchar](1024),
[PARM_VALUE_NEW] [nvarchar](1024),
[ORIGINAL_LOGIN] [sysname],
[ORIGINAL_SID] [varbinary](85),
[SYSUTCDATETIME] [datetime2],
[HOST_NAME] [nvarchar](128),
[APP_NAME] [nvarchar](128),
[SPID] [smallint],
CONSTRAINT
[PK_CONFIG_TABLE_AUDIT] PRIMARY KEY CLUSTERED ([ID])
) ON [PRIMARY]
GO
--
Only SA or any member of of sysadmin server role may overcome following DENY
and clean log
DENY INSERT, UPDATE, DELETE ON OBJECT::dbo.CONFIG_TABLE_LOG TO PUBLIC
GO
-->
START OF CREATING TRIGGER
PRINT 'Creating trigger'
GO
CREATE TRIGGER [dbo].[TR_CONFIG_TABLE_AUDIT]
ON [dbo].[CONFIG_TABLE]
WITH EXECUTE AS SELF
AFTER INSERT, UPDATE, DELETE AS
BEGIN
SET NOCOUNT ON;
INSERT INTO [dbo].[CONFIG_TABLE_LOG]
([OPERATION], [CONFIG_TABLE_ID],
[PARM_NAME_OLD], [PARM_NAME_NEW]
, [PARM_VALUE_OLD],
[PARM_VALUE_NEW],
[ORIGINAL_LOGIN], [ORIGINAL_SID]
, [SYSUTCDATETIME],
[HOST_NAME], [APP_NAME],
[SPID])
SELECT CASE WHEN DELETED.ID IS NULL AND INSERTED.ID IS NOT NULL THEN N'INS'
WHEN
DELETED.ID IS NOT NULL AND INSERTED.ID IS NULL THEN N'DEL'
ELSE N'UPD' END
, ISNULL (DELETED.ID, INSERTED.ID), DELETED.PARM_NAME, INSERTED.PARM_NAME,
DELETED.PARM_VALUE, INSERTED.PARM_VALUE
, ORIGINAL_LOGIN(), SUSER_SID(ORIGINAL_LOGIN()),
SYSUTCDATETIME(),
HOST_NAME(),
APP_NAME(), @@SPID
FROM
INSERTED FULL OUTER
JOIN DELETED ON (INSERTED.ID = DELETED.ID);
END
GO
--<
END OF TRIGGER
--
START FILL TEST DATA
SET NOCOUNT ON;
INSERT INTO CONFIG_TABLE
([PARM_NAME],
[PARM_VALUE])
VALUES
(N'QUEUES_ACTIVE', N'Y'), (N'REPLICATION_SERVER', N'10.20.30.45')
, (N'SMTP_SERVER', N'10.20.30.46'), (N'RECEIVE_PAYMENTS', N'Y')
, (N'LOG_LEVEL', N'FULL'), (N'HOST_NAME', N'MSSQL.CORP.SUPERBANK.RU');
UPDATE
[CONFIG_TABLE] SET [PARM_VALUE] = N'N' WHERE [PARM_NAME] = N'QUEUES_ACTIVE';
DELETE FROM [CONFIG_TABLE] WHERE
[PARM_NAME] = N'SMTP_SERVER';
UPDATE
CONFIG_TABLE SET PARM_VALUE = PARM_VALUE + N'*bulk_update' WHERE
ID <= 4;
UPDATE
CONFIG_TABLE SET PARM_VALUE = N'MSSQL.CORP.SUPERBANK.RU2', PARM_NAME = N'HOST_NAME2' WHERE
PARM_NAME = N'HOST_NAME';
--
OUTPUT
select * from
CONFIG_TABLE_LOG
Скачать скрипт с отправкой почты можно по этой ссылке
Пререквизиты:
- Необходимо иметь включённую и настроенную функцию Database Mail. Лучше использовать локальный SMTP сервер из соображений безопасности;
- Я использую профиль отправки почты "Main", значит профиль должен быть либо таким, либо замените его в скрипте;
- Замените адрес получателя в скрипте.
Вот пример полученного мной письма
2017-03-11
11:35:18 GMT Update "RECEIVE_PAYMENTS" = "Y"
-> "Y*bulk_update". From: Original login ="sa", Host
name = "XXXXX"
2017-03-11
11:35:18 GMT Update "REPLICATION_SERVER" =
"10.20.30.45" -> "10.20.30.45*bulk_update". From:
Original login ="sa", Host name = "XXXXX"
2017-03-11
11:35:18 GMT Update "QUEUES_ACTIVE" = "N"
-> "N*bulk_update". From: Original login ="sa", Host
name = "XXXXX"
For
details - query table "DDD4-SQL1.DBTEST.dbo.CONFIG_TABLE_LOG"
Вариант 2 — Запрет на редактирование с помощью триггера
Ниже приведен скрипт создания триггера, который запрещает редактирование таблицы конфигурации всем пользователям, кроме двух перечисленных
-- RUN
THIS SCRIPT UNDER sa OR ANY MEMBER OF sysadmin SERVER ROLE
USE
[master]
GO
IF DB_ID(N'DBTEST') IS NOT NULL
BEGIN
PRINT 'Dropping database [DBTEST]';
ALTER DATABASE [DBTEST] SET
SINGLE_USER WITH
ROLLBACK IMMEDIATE
DROP DATABASE [DBTEST];
END ELSE PRINT 'Database [DBTEST] not exist yet';
GO
PRINT 'Creating database [DBTEST]'
GO
CREATE DATABASE [DBTEST]
GO
USE
[DBTEST]
GO
PRINT 'Creating tables';
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[CONFIG_TABLE](
[ID] [int]
identity (1, 1) NOT NULL,
[PARM_NAME] [nvarchar] (64) NOT
NULL,
[PARM_VALUE] [nvarchar] (1024) NOT NULL,
CONSTRAINT
[PK_CONFIG_TABLE] PRIMARY KEY CLUSTERED ([ID]),
CONSTRAINT
[UK1_CONFIG_TABLE] UNIQUE ([PARM_NAME])
) ON [PRIMARY]
GO
-->
START OF CREATING TRIGGER
PRINT 'Creating trigger'
GO
CREATE TRIGGER [dbo].[TR_CONFIG_TABLE_CHECK_ACCESS_RIGHTS]
ON [dbo].[CONFIG_TABLE]
AFTER INSERT, UPDATE, DELETE AS
BEGIN
SET NOCOUNT ON;
-- List of permitted users can be easy implemented as table.
-- For this table need to DENY access from PUBLIC
-- UPDATE: LIST OF PERMITTED USERS CAN BE OBTAINED FROM AD GROUP -- AS "AB" WANTED
IF (ORIGINAL_LOGIN() NOT IN ('sa', 'BANKDOMAIN\User2'))
THROW
50000,'Only permitted
users can modify [dbo].[CONFIG_TABLE].',1;
END
GO
--<
END OF TRIGGER
--
START FILL TEST DATA
SET NOCOUNT ON;
INSERT INTO CONFIG_TABLE ([PARM_NAME],
[PARM_VALUE])
VALUES (N'QUEUES_ACTIVE', N'Y'), (N'REPLICATION_SERVER',
N'10.20.30.45')
, (N'SMTP_SERVER', N'10.20.30.46'), (N'RECEIVE_PAYMENTS', N'Y')
, (N'LOG_LEVEL', N'FULL'), (N'HOST_NAME', N'MSSQL.CORP.SUPERBANK.RU');
UPDATE
[CONFIG_TABLE] SET [PARM_VALUE] = N'N' WHERE [PARM_NAME] = N'QUEUES_ACTIVE';
DELETE FROM [CONFIG_TABLE] WHERE
[PARM_NAME] = N'SMTP_SERVER';
UPDATE
CONFIG_TABLE SET PARM_VALUE = PARM_VALUE + N'*bulk_update' WHERE
ID <= 4;
UPDATE
CONFIG_TABLE SET PARM_VALUE = N'MSSQL.CORP.SUPERBANK.RU2', PARM_NAME = N'HOST_NAME2' WHERE
PARM_NAME = N'HOST_NAME';
Отредактируйте строку со списком разрешённых пользоватлей и попробуйте редактировать таблицу под разными пользователями.
Вариант 3 — Использование встроенных возможностей аудита MS SQL
MS SQL содержит встроенную возможность аудита различных событий.
В примере ниже включается и настраивается аудит на изменения таблицы CONFIG_TABLE.
Журнал аудита нужно периодически просматривать.
После того как закончите эксперименты с этим скриптом, даже после удаления БД DBTEST остаётся один объект серверного уровня.
Его нужно удалить вручную.
Откройте SSMS \ Security \ Audits. Правой кнопкой удалите аудит BankConfig.
!!!! Перед выполнением скрипта создайте папку C:\SQLAUDIT на сервере
-- RUN
THIS SCRIPT UNDER sa OR ANY MEMBER OF sysadmin SERVER ROLE
-- CREATE FOLDER "C:\SQLAudit" ON SERVER
USE
[master]
GO
IF EXISTS (SELECT * FROM sys.server_audits WHERE [name] = N'BankConfig')
BEGIN
IF EXISTS (SELECT * FROM sys.server_audits
WHERE [name] = N'BankConfig' and
[is_state_enabled] = 1)
BEGIN
PRINT
'Disabling existing audit...'
ALTER
SERVER AUDIT [BankConfig] WITH (STATE = OFF);
END
PRINT 'Dropping existing audit...'
DROP SERVER AUDIT [BankConfig];
END;
GO
IF DB_ID(N'DBTEST') IS NOT NULL
BEGIN
PRINT 'Dropping database [DBTEST]';
ALTER DATABASE [DBTEST] SET
SINGLE_USER WITH
ROLLBACK IMMEDIATE
DROP DATABASE [DBTEST];
END ELSE PRINT 'Database [DBTEST] not exist yet';
GO
PRINT 'Creating database [DBTEST]'
GO
CREATE DATABASE [DBTEST]
GO
USE
[DBTEST]
GO
PRINT 'Creating tables';
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[CONFIG_TABLE](
[ID] [int]
identity (1, 1) NOT NULL,
[PARM_NAME] [nvarchar] (64) NOT
NULL,
[PARM_VALUE] [nvarchar] (1024) NOT NULL,
CONSTRAINT
[PK_CONFIG_TABLE] PRIMARY KEY CLUSTERED ([ID]),
CONSTRAINT
[UK1_CONFIG_TABLE] UNIQUE ([PARM_NAME])
) ON [PRIMARY]
GO
--
START FILL TEST DATA
SET NOCOUNT ON;
INSERT INTO CONFIG_TABLE
([PARM_NAME],
[PARM_VALUE])
VALUES
(N'QUEUES_ACTIVE', N'Y'), (N'REPLICATION_SERVER', N'10.20.30.45')
, (N'SMTP_SERVER', N'10.20.30.46'), (N'RECEIVE_PAYMENTS', N'Y')
, (N'LOG_LEVEL', N'FULL'), (N'HOST_NAME', N'MSSQL.CORP.SUPERBANK.RU');
USE [master]
GO
CREATE SERVER AUDIT [BankConfig]
TO FILE (FILEPATH
= N'C:\SQLAudit\', MAXSIZE = 100 MB
, MAX_ROLLOVER_FILES =
20, RESERVE_DISK_SPACE =
OFF)
WITH
(/*QUEUE_DELAY = 1000,*/ ON_FAILURE = CONTINUE)
WHERE object_name =
'CONFIG_TABLE'
GO
ALTER SERVER AUDIT [BankConfig] WITH (STATE = ON)
GO
USE DBTEST
GO
CREATE DATABASE AUDIT SPECIFICATION [t_CONFIG_TABLE]
FOR SERVER AUDIT [BankConfig]
ADD (INSERT, UPDATE, DELETE ON OBJECT::[dbo].[CONFIG_TABLE] BY
[public])
WITH (STATE = ON)
GO
--
TEST AUDIT
USE DBTEST
GO
SET NOCOUNT OFF
UPDATE
[CONFIG_TABLE] SET [PARM_VALUE] = N'N' WHERE [PARM_NAME] = N'QUEUES_ACTIVE';
DELETE FROM [CONFIG_TABLE] WHERE
[PARM_NAME] = N'SMTP_SERVER';
UPDATE
[CONFIG_TABLE] SET [PARM_VALUE] = [PARM_VALUE] + N'*bulk_update' WHERE
[ID] <= 4;
UPDATE
[CONFIG_TABLE] SET [PARM_VALUE] = N'MSSQL.CORP.SUPERBANK.RU2'
, [PARM_NAME] = N'HOST_NAME2'
WHERE
[PARM_NAME] = N'HOST_NAME';
INSERT INTO [CONFIG_TABLE] ([PARM_NAME], [PARM_VALUE]) VALUES (N'REPLICATION_ENABLED',
N'Y')
WAITFOR DELAY '00:00:10'
SELECT [event_time], [action_id], [server_principal_name]
,
[server_instance_name], [statement]
FROM fn_get_audit_file (N'C:\SQLAudit\BankConfig_'
+ (SELECT cast ([audit_guid] as nvarchar(200))
FROM sys.server_audits WHERE
[name] = N'BankConfig')
+ '*.sqlaudit',default,default);
Недостаток: Не сохраняются предыдущие значения полей. Не фиксируется имя хоста, с котрого производились изменения
Вариант 4 — Аудит изменений CONFIG_TABLE с помощью CHANGE DATA CAPTURE
Change Data Capture feature (далее, CDC) позволяет захватывать INSERT, UPDATE, DELETE операции и записывать их в отдельную таблицу.
CDC не предназначена для аудита, я включаю эту функцию сюда только для полноты картины.
С помощью CDC можно восстановить всю истрию изменений таблицы и её состояние на каждый предшествующий момент времени.
CDC не записывает login или username. В принципе, это можно обойти, если сделать триггер на UPDATE, который в специальную колонку запишет login обновляющего пользователя.
Большей частью CDC предназначен для ETL — для инкрементного обновления таблиц фактов.
