Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 MSSQL 2014 и row level security  [new]
Критик
Member

Откуда: Москва / Калуга
Сообщений: 35853
Блог
Коллеги, требуется реализовать RLS на MS SQL 2014.

Пытаюсь делать через такую функцию
CREATE FUNCTION [dbo].[fn_Security](@ID_COMPANY AS int)  
    RETURNS TABLE  
WITH SCHEMABINDING  
AS  
RETURN
SELECT 1 as fn_Security_result
WHERE (IS_MEMBER('группа1') = 1 AND @ID_COMPANY =  5) OR
      (IS_MEMBER('группа2') = 1 AND @ID_COMPANY = -1)

( все это вынесено в функцию, чтобы инкапсулировать логику предоставления доступа, а потом легко и просто перейти на 2016)

В запросе (1), который нужно ускорить
select t1.*
  from таблица as t1 -- есть некластерный индекс по ID_COMPANY
  cross apply [dbo].[fn_Security](t1.ID_COMPANY) as t2

данные фильтруются уже после выборки:

|--Filter(WHERE:(is_member(N'группа1')=(1) AND .[dbo].[таблица].[ID_COMPANY] as [t1].[ID_COMPANY]=(5) OR is_member(N'группа2')=(1) AND [dbo].[таблица].[ID_COMPANY] as [t1].[ID_COMPANY]=(-1)))
|--Table Scan(OBJECT:([dbo].[таблица] AS [t1]))


а если сделать вот так (2)
select *
  from таблица  as t1 -- есть некластерный индекс по ID_COMPANY
  where (IS_MEMBER('группа1') = 1 AND ID_COMPANY =  5) OR
        (IS_MEMBER('группа2') = 1 AND ID_COMPANY = -1)

|--Table Scan(OBJECT:([таблица] AS [t1]), WHERE:(is_member(N'группа1')=(1) AND [dbo].[таблица].[ID_COMPANY] as [t1].[ID_COMPANY]=(5) OR is_member(N'группа2')=(1) AND [dbo].[таблица].[ID_COMPANY] as [t1].[ID_COMPANY]=(-1)))

то в table scan-блоке сразу имеется предикат с фильтром, и этот запрос работает в 20 раз быстрее, чем предыдущий.

Ситуация с проблемным запросом с cross apply волшебным образом исправляется, если убрать одно OR-условие из функции. Но убирать его нельзя, т.к. оно нужно, чтобы для разных групп дать разный доступ.

Как ускорить запрос с cross apply?
18 янв 17, 18:25    [20119784]     Ответить | Цитировать Сообщить модератору
 Re: MSSQL 2014 и row level security  [new]
vanezy
Member

Откуда: Ekaterinburg->Moscow->Frankfurt
Сообщений: 122
Критик,

а если в ф-ции вместо OR заюзать UNION?
18 янв 17, 18:39    [20119835]     Ответить | Цитировать Сообщить модератору
 Re: MSSQL 2014 и row level security  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
Критик,

а зачем APPLY? EXISTS и как пишут заменить на UNION ALL
18 янв 17, 18:43    [20119844]     Ответить | Цитировать Сообщить модератору
 Re: MSSQL 2014 и row level security  [new]
invm
Member

Откуда: Москва
Сообщений: 9913
Критик
потом легко и просто перейти на 2016
Все равно при переходе на штатный RLS 2016-го запросы придется переписывать.
Почему бы не написать "правильную" функцию и применение RLS инкапсулировать в представление?
А потом, при переходе на 2016-й, переписать функцию и представление.
18 янв 17, 21:30    [20120438]     Ответить | Цитировать Сообщить модератору
 Re: MSSQL 2014 и row level security  [new]
Критик
Member

Откуда: Москва / Калуга
Сообщений: 35853
Блог
>>vanezy

Лучше не становится

StmtText
|--Nested Loops(Inner Join, OUTER REFERENCES:([t1].[ID_COMPANY]))
|--Table Scan(OBJECT:([dbo].[таблица] AS [t1]))
|--Concatenation
|--Filter(WHERE:(STARTUP EXPR(is_member(N'группа2')=(1))))
| |--Filter(WHERE:(STARTUP EXPR([dbo].[таблица].[ID_COMPANY] as [t1].[ID_COMPANY]=(-1))))
| |--Constant Scan
|--Filter(WHERE:(STARTUP EXPR(is_member(N'группа1')=(1))))
|--Filter(WHERE:(STARTUP EXPR([dbo].[таблица].[ID_COMPANY] as [t1].[ID_COMPANY]=(5))))
|--Constant Scan

>>TaPaK
план правктически аналогичен тому, что выше, и все также медленно

>>invm
функция создана по образу и подобию отсюда
https://msdn.microsoft.com/ru-ru/library/dn765131.aspx
как по-другому?
19 янв 17, 10:40    [20121627]     Ответить | Цитировать Сообщить модератору
 Re: MSSQL 2014 и row level security  [new]
invm
Member

Откуда: Москва
Сообщений: 9913
Критик
как по-другому?
Например так:
+
use tempdb;
go

create function dbo.fnRLS
(
)
returns table
as
return (
 select
  x.object_id
 from
  (
   select 2 where is_member('role1') = 1
   union all
   select 3 where is_member('role2') = 1
  ) x(object_id)
);
go

select object_id, name into dbo.t from sys.all_objects;
alter table dbo.t add primary key (object_id);
go

create view dbo.vt
as
select
 t.object_id, t.name
from
 dbo.fnRLS() r join
 dbo.t on t.object_id = r.object_id;
go

set statistics xml on;

select
 *
from
 dbo.vt;

set statistics xml off;
go

drop view dbo.vt;
drop table dbo.t;
drop function dbo.fnRLS;
go
19 янв 17, 11:26    [20121907]     Ответить | Цитировать Сообщить модератору
 Re: MSSQL 2014 и row level security  [new]
Критик
Member

Откуда: Москва / Калуга
Сообщений: 35853
Блог
invm,

работает быстро,

но, насколько я понимаю, при переходе на 2016 придется переделывать функцию вычисления предиката + все объекты, по которым были разделены права, так?
19 янв 17, 11:53    [20122054]     Ответить | Цитировать Сообщить модератору
 Re: MSSQL 2014 и row level security  [new]
invm
Member

Откуда: Москва
Сообщений: 9913
Критик
но, насколько я понимаю, при переходе на 2016 придется переделывать функцию вычисления предиката + все объекты, по которым были разделены права, так?
Да. Поэтому я и предложил применение RLS инкапсулировать в представление.
19 янв 17, 12:02    [20122095]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить