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

Откуда: LA
Сообщений: 571
Добрый день.

Встал вопрос обеспечить видимость каждому пользователю только то что касается его. Для этого все емеется вопрос у меня лишь в том как технически реализовать.

К примеру есть view Задания (строится как просто SELECT * from SysTЗадания) с полями КодСотрудника и Контролирующий. (+ др)
Оба поля типа int - идентификаторы пользователя.
Механизм определения id пользователя таков : на сервере задействована NT авторизация, все пользователи вх в один домен, их логины - это фамилии, функция GetMyUserCod по логину в таблице Сотрудники отыскивает совпадение по фамилии и возвращает код.

Нужно чтобы контролирующий видел те строки где он контролирующий, а исполнитель те где он назначен "исполняющим".

Вариант реализации vew Задания вида

SELECT * FROM SysTЗадания Where КодСотрудника=dbo.GetMyUserCod() or Контролирующий=dbo.GetMyUserCod()
на 30к строках очень медлен, и я понимаю - каждый раз дергается dbo.GetMyUserCod()... и даже если в этой функции зашить соответсвие
Логин - Код и просто Case'ом выбирать, будет быстрее, но все же неприемлимо.

Вариант когда
SELECT * FROM SysTЗадания Where КодСотрудника=12 or Контролирующий=12

hработает быстро....
как добится чтобы запрос выполнялся так же и с функцием ? Если бы были глобальные переменные я бы раз инициировал ее и в дальнейшем юзал, сейчас же у меня каждый раз дергается dbo.GetMyUserCod() .....
Покритикуйте, желательно конструктивно.



ЗЫ veiw должна быть обновляемая
4 май 05, 13:39    [1515873]     Ответить | Цитировать Сообщить модератору
 Re: Вертикальная View. Права на доступ строке.  [new]
BusyMan
Member

Откуда: Москва
Сообщений: 4927

SELECT SysTЗадания.* 
FROM SysTЗадания 
        JOIN Сотрудники WHERE Сотрудники.КодСотрудника=SysTЗадания.КодСотрудника
Where Сотрудники=dbo.GetMyUserCod() 


ИЛИ быстрее будет, если всунуть тело функции во вьюшку:


SELECT SysTЗадания.* 
FROM SysTЗадания 
        JOIN Сотрудники WHERE Сотрудники.КодСотрудника=SysTЗадания.КодСотрудника
Where Сотрудники.UserName=@@SYSTEM_USER

4 май 05, 13:54    [1515929]     Ответить | Цитировать Сообщить модератору
 Re: Вертикальная View. Права на доступ строке.  [new]
aleks2
Guest
SELECT * FROM SysTЗадания Where КодСотрудника=(select dbo.GetMyUserCod()) or Контролирующий=(select dbo.GetMyUserCod())
4 май 05, 14:06    [1515978]     Ответить | Цитировать Сообщить модератору
 Re: Вертикальная View. Права на доступ строке.  [new]
Crimean
Member

Откуда:
Сообщений: 13148
Атличная тема. Через день то здесь то в дизайне промелькивает. Сводится к построению контролируемых выборок над неконтролируемыми данными. Хорошо решается только в применении к конкретным условиям, то есть для каждой задачи - свое решение. Хотя куча стандартных методов есть.
4 май 05, 15:02    [1516180]     Ответить | Цитировать Сообщить модератору
 Re: Вертикальная View. Права на доступ строке.  [new]
squid
Member

Откуда: LA
Сообщений: 571
BusyMan
SysTem_User некатит, собственно Почему я ти описывал свою функцию GetMyUserCod()

aleks2
О, большое списибо. Видимо то что надо !!!
в приведенной вами конструкции запроса функция выполнится лишь дважды, как я понял. Счас проверю.
4 май 05, 15:54    [1516382]     Ответить | Цитировать Сообщить модератору
 Re: Вертикальная View. Права на доступ строке.  [new]
squid
Member

Откуда: LA
Сообщений: 571
1  (просто по исполнителю)
select * from SysTЗадания WHERE  Исполнитель= (SELECT dbo.GetMyUserCod()) 

2 (и исполнитель и контр)
select * from SysTЗадания WHERE  Исполнитель= (SELECT dbo.GetMyUserCod()) or КтоПринял= (SELECT dbo.GetMyUserCod())

3  (с константами)
select * from SysTЗадания WHERE 
   Исполнитель=136 or -- (SELECT dbo.GetMyUserCod()) or 
   КтоПринял=136 -- (SELECT dbo.GetMyUserCod())


Очень любопытно
Запрос №1 мгновенно
Запрос №2 около 2 сек
Запрос №3 мгновенно
4 май 05, 16:02    [1516405]     Ответить | Цитировать Сообщить модератору
 Re: Вертикальная View. Права на доступ строке.  [new]
Crimean
Member

Откуда:
Сообщений: 13148
Вариант 3 предпочтительнее, но требует 3-звенок.

Полученные результаты говорят о том, что объем данных для тестирования вряд ли был больше нескольких сотен тысяч строк. Обращаю внимание.
4 май 05, 17:49    [1516879]     Ответить | Цитировать Сообщить модератору
 Re: Вертикальная View. Права на доступ строке.  [new]
squid
Member

Откуда: LA
Сообщений: 571
Строк около 30 тысяч, но интересно следующее запрос с дополнительным условием is_member выполнился мгновенно.....

select * from SysTЗадания WHERE Исполнитель= (SELECT dbo.GetMyUserCod()) or КтоПринял= (SELECT dbo.GetMyUserCod() or is_member('Groupxxx')=1)


ЗЫ is_member('Groupxxx')=1 - некоторые пользователи имеют полный доступ к таблице
4 май 05, 18:25    [1517019]     Ответить | Цитировать Сообщить модератору
 Re: Вертикальная View. Права на доступ строке.  [new]
Crimean
Member

Откуда:
Сообщений: 13148
Увеличь число записей хотя бы до 300 000, если это актуально (планируется).
Почуствуешь разницу. В итоге начнешь делать разные вьювы для разных категорий пользователей. Или сбежишь на 3й вариант.
4 май 05, 19:44    [1517185]     Ответить | Цитировать Сообщить модератору
 Re: Вертикальная View. Права на доступ строке.  [new]
aleks2
Guest
squid
Строк около 30 тысяч, но интересно следующее запрос с дополнительным условием
is_member выполнился мгновенно
.....

select * from SysTЗадания WHERE Исполнитель= (SELECT dbo.GetMyUserCod()) or КтоПринял= (SELECT dbo.GetMyUserCod() or is_member('Groupxxx')=1)


ЗЫ is_member('Groupxxx')=1 - некоторые пользователи имеют полный доступ к таблице


Ты сервер за идиота держишь? Если is_member('Groupxxx')=1 выполняется - остальное не проверяется вовсе.
5 май 05, 05:51    [1517617]     Ответить | Цитировать Сообщить модератору
 Re: Вертикальная View. Права на доступ строке.  [new]
squid
Member

Откуда: LA
Сообщений: 571
Добрый день!
спасибо за отзывы, итак

1. За идиота я сервер не держу ;) скажу только что выполняя запрос от своего имени я в группу Groupxxx не вхожу соответсвенно часть условия c is_member отпадает и .... остаются два предыд условия с которыми запрос выполняется около 2 сек,... но он выполн мгновенно !!!

2. Тест проводил на табличке с 28 тыс записей, есть и табличка и со ста тыс, но там только одно поля идентифицирует пользователя имеющего права доступа - соответсвенно и работать должно без проблем.

Забыл сказать Сервер крутится на 2xPII 366 mhz. Апгрейд будет точно, поэтому тест нпа 300 делать не беду ;) к примеру на P3-800EB все крутится уже втрое быстрее, на P3-866 512k cashe уже вчетверо, а поставив P4 в 10 раз вложимся ;)
5 май 05, 09:25    [1517803]     Ответить | Цитировать Сообщить модератору
 Re: Вертикальная View. Права на доступ строке.  [new]
Crimean
Member

Откуда:
Сообщений: 13148
> Ты сервер за идиота держишь?

Именно.

> Если is_member('Groupxxx')=1 выполняется - остальное не проверяется вовсе.

Ага, а не хочешь, чтобы is_member('Groupxxx') при этом выполнилось ДЛЯ КАЖДОЙ строки ТАБЛИЦЫ (не результата), а? А веТь так оно и будет.
5 май 05, 10:26    [1518049]     Ответить | Цитировать Сообщить модератору
 Re: Вертикальная View. Права на доступ строке.  [new]
squid
Member

Откуда: LA
Сообщений: 571
Согласно плану выполнения запроса

select * 
from SysTЗадания 
WHERE Исполнитель=(SELECT dbo.GetMyUserCod()) 
   or КтоПринял=(SELECT dbo.GetMyUserCod())
   or is_member('Groupxxx')=1


Каждая функция выполняется 1 (один) раз. Это и понятно - функции не зависят от содержимого данных, а наоборот.
5 май 05, 10:42    [1518141]     Ответить | Цитировать Сообщить модератору
 Re: Вертикальная View. Права на доступ строке.  [new]
Crimean
Member

Откуда:
Сообщений: 13148
:) это - пока. И тебе шибко повезет, если планы такими и останутся. Это же view. А ты пока гоняешь оч простые запросы на него. У меня аналогичная проблема. Была. В итоге каждая вьюха имеет по десятку клонов для разных категорий пользователей с разными условиями внутри. Классика, мать ее.
5 май 05, 13:41    [1519085]     Ответить | Цитировать Сообщить модератору
 Re: Вертикальная View. Права на доступ строке.  [new]
squid
Member

Откуда: LA
Сообщений: 571
На этой же табличке с 220 тыс строк P3 800EB Запрос выполняется около 2 сек. Таблица занимает 200мб
Я считаю это приемлимым на таком конфиге..... Пока что ;)
5 май 05, 17:23    [1520252]     Ответить | Цитировать Сообщить модератору
 Re: Вертикальная View. Права на доступ строке.  [new]
dlednev
Member

Откуда: Омск
Сообщений: 143
кстати, не хочешь сделать функию, типа:
CREATE function dbo.fnMy(Исполнитель varchar(..),Контролер varchar(..))
returns table
AS
return
(
select ..... (как и было предложено)
)

а потом
SELECT * FROM dbo.fnMy('XX','XX')
а юэер при этом НУ НИКАК не увидит всех записей (одной выборкой)

.. а запросы оптимизируй, вижу - процесс идет, прогресс налицо ))
5 май 05, 17:56    [1520462]     Ответить | Цитировать Сообщить модератору
 Re: Вертикальная View. Права на доступ строке.  [new]
squid
Member

Откуда: LA
Сообщений: 571
TO dlednev
Вариант приемлим и имеет место быть, единственное - источник записей должен быть обновляемый (клиент ACCESS). Такую view приделать обновляемость - слоять insdead триггера. Морока ;(
6 май 05, 09:49    [1521364]     Ответить | Цитировать Сообщить модератору
 Re: Вертикальная View. Права на доступ строке.  [new]
DVN
Member

Откуда: Minsk, Belarus
Сообщений: 184
Не претендую на оригинальность, но хочу поделиться как я решал данную проблему. Аутентификация примерно как и у тебя. Как я понял ты при каждом запросе дергаешь функцию dbo.GetMyUserCod()... чтобы получить ID из таблицы Сотрудники. Я сделал так : после того, как юзер залогинился, допустим дергаю функцию dbo.GetMyUserCod()... (заметь только один раз) и получаю его ID. Эта ID как глобальная переменная "сидит" в приложении, работающее с этой БД. Ну а дальше в хранимку передаю ID юзера (если необходимо получить ограничение по записям) и использую запросы примерно как этот

SELECT * FROM SysTЗадания Where КодСотрудника=12 or Контролирующий=12

только так

SELECT * FROM SysTЗадания Where КодСотрудника=@ID or Контролирующий=@ID

И еще - отказался от вьюшек по причине

Crimean
это - пока. И тебе шибко повезет, если планы такими и останутся. Это же view. А ты пока гоняешь оч простые запросы на него. У меня аналогичная проблема. Была. В итоге каждая вьюха имеет по десятку клонов для разных категорий пользователей с разными условиями внутри. Классика, мать ее.


а использую дополнительную промежуточную таблицу между юзерами и, например, клиентами с которыми они работают. Через эту табличку происходит "разрешение" на работу определенного юзера с набором клиентов.
Поди ты определи (критерии доступа), что этому юзеру надо работать с этими клиентами, а тому с теми. А так поставил в соответствие IDUser и IDClient и все пучком.
Да, можно сказать, что клиентов мало и в любом случае запросы будут работать быстро. Но за таблицей клиентов следует таблица операций с клиентами, где сотни тысяч записей... Работает довольно быстро.

Еще раз повторюсь, что не претендую на оригинальность, кто-то разобъет мои высказывания в пух и прах. Но только в общении познается истина.
Удачи.
6 май 05, 11:42    [1521960]     Ответить | Цитировать Сообщить модератору
 Re: Вертикальная View. Права на доступ строке.  [new]
Crimean
Member

Откуда:
Сообщений: 13148
2 DVN

Вариант с промежуточной таблицей неплох. Пока нет миллиона клиентов и тысячи пользователей. Если каждому пользователю по 10 000 клиентов, то... Все это получается очень и очень громоздко.

В общем, вариантов, как говорилось, немало. Надо применять соответствующий к каждой конкретной ситуации. Критерии - требования к функциональности и практика применения.
6 май 05, 13:18    [1522389]     Ответить | Цитировать Сообщить модератору
 Re: Вертикальная View. Права на доступ строке.  [new]
DVN
Member

Откуда: Minsk, Belarus
Сообщений: 184
Абсолютно согласен с

Crimean
В общем, вариантов, как говорилось, немало. Надо применять соответствующий к каждой конкретной ситуации.

Может кто-нибудь еще поделится опытом на "Права на доступ к строке". В любом случае, учиться никогда не поздно, а увидеть альтернативное решение задачи всегда интересно, особенно если оно оптимальнее твоего.
6 май 05, 13:46    [1522521]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить