Динамическая безопасность на уровне строк в базе данных SQL Server с использованием расширенных свойств

Question

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

Я не ищу предложений по созданию отдельных баз данных или представлений для каждого клиента, так как оба предложения - это высокий уровень обслуживания и низкая эффективность.

Мое решение должно работать с:

• База данных 100 ГБ
• 400 столов
• Обновления каждые 30 минут из основной базы данных транзакций
• Ежеквартальные изменения схемы (Приложение находится в постоянном развитии).

Может кто-нибудь дать мне однозначный ответ, почему следующий метод небезопасен или не будет работать?:

Я настроил пользователя базы данных для каждого клиента с его customerID в качестве расширенного свойства.

Я создал представление каждой таблицы, которая динамически выбирает * из таблицы, где столбец customerID совпадает с расширенным свойством CustomerID вошедшего в систему пользователя. Код выглядит следующим образом и работает хорошо:

CREATE VIEW [CustomerAccessDatabase].[vw_Sales] 
AS SELECT * FROM [CustomerAccessDatabase].[Sales] 
WHERE [Sales].[CustomerID]= 
         (SELECT CONVERT(INT,p.value) AS [Value] 
         FROM sys.extended_properties
         JOIN sys.sysusers ON extended_properties.major_id=sysusers.[uid] 
         AND extended_properties.name = 'CustomerID' 
         AND sysusers.[SID]=(SELECT suser_sid())
         );
GO

Чтобы обеспечить доступ к представлениям, я создал общую роль базы данных "Customer_Access_Role". Этой роли предоставлен доступ ко всем табличным представлениям, но доступ к самим таблицам базы данных запрещен.

Чтобы пользователи не могли изменить свой собственный идентификатор клиента, я запретил доступ к расширенным свойствам, например:

USE [master];
GO
DENY EXEC ON sys.sp_addextendedproperty to [public];
GO
DENY EXEC ON sys.sp_dropextendedproperty to [public];
GO
DENY EXEC ON sys.sp_updateextendedproperty to [public];
GO

Конечный результат - мне нужна только одна база данных и один набор разрешений.

Чтобы добавить нового клиента, мне нужно всего лишь создать нового пользователя с его customerID в качестве расширенного атрибута и добавить его в Customer_Access_Role. Это оно!

Answer 1

Я собираюсь повторить то, что все уже заявляют и подвести итог.

1. Вы делаете свою работу тяжелее, чем она должна быть.
2. Создайте представление, это просто их данные, а затем предоставьте им безопасный доступ к этому представлению.
3. В качестве альтернативы, извлеките все их данные из базы данных "Core" в свою собственную и предоставьте им необходимый доступ к этим данным.