Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Как передать запросам код пользователя из соответствующей таблицы c SUSER_SNAME()?  [new]
LightN
Member

Откуда: Москва
Сообщений: 192
Как правильно передать запросам код пользователя из соответствующей таблицы и в зависимости от where c SUSER_SNAME()?
Это нужно для того, чтобы с конкретной РС были видны данные только для неё.
Сейчас я делаю это через функцию.

ALTER function [dbo].[IAM]() returns int as
  return (select id from dbo.WE (nolock) where DomainName = SUSER_SNAME())


Получается слишком много запросов используют [dbo].[IAM]() и соответственно их число = миллионы.

Пример запроса:
SELECT dbo.Invoices.* FROM dbo.Invoices WHERE ChargedID = dbo.IAM()

Как вообще правильно и оптимально это делать?
11 июл 14, 16:21    [16292975]     Ответить | Цитировать Сообщить модератору
 Re: Как передать запросам код пользователя из соответствующей таблицы c SUSER_SNAME()?  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
сделать таблицу с правами и в запросе джойнить с ней
11 июл 14, 16:25    [16293004]     Ответить | Цитировать Сообщить модератору
 Re: Как передать запросам код пользователя из соответствующей таблицы c SUSER_SNAME()?  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8828
Непонятен смысл этих действий - храните имя/логин в таблицах без промежуточного преобразования в ID.
Иначе в каждый селект придётся добавлять связку со справочником ID.
11 июл 14, 16:29    [16293041]     Ответить | Цитировать Сообщить модератору
 Re: Как передать запросам код пользователя из соответствующей таблицы c SUSER_SNAME()?  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8828
Или получайте ID при старте и храните его на клиенте, если уж "очень надо".
11 июл 14, 16:31    [16293055]     Ответить | Цитировать Сообщить модератору
 Re: Как передать запросам код пользователя из соответствующей таблицы c SUSER_SNAME()?  [new]
LightN
Member

Откуда: Москва
Сообщений: 192
Maxx,
Это Да.
Но IAM() уже живёт нескольких сотнях мест (вью, процедуры, триггеры, функции). Есть ли мысли о том, как часто не дёргая We пропихнуть в IAM() этот код?
11 июл 14, 16:34    [16293082]     Ответить | Цитировать Сообщить модератору
 Re: Как передать запросам код пользователя из соответствующей таблицы c SUSER_SNAME()?  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8828
автор
Это нужно для того, чтобы с конкретной РС были видны данные только для неё.

Такое решается не прямыми запросами, а параметризованными процедурами. Иначе рискуете забить кэш планов ад-хоками и тратить время на ненужную компиляцию -> снижение производительности.
11 июл 14, 16:35    [16293089]     Ответить | Цитировать Сообщить модератору
 Re: Как передать запросам код пользователя из соответствующей таблицы c SUSER_SNAME()?  [new]
LightN
Member

Откуда: Москва
Сообщений: 192
Владислав Колосов
автор
Это нужно для того, чтобы с конкретной РС были видны данные только для неё.

Такое решается не прямыми запросами, а параметризованными процедурами. Иначе рискуете забить кэш планов ад-хоками и тратить время на ненужную компиляцию -> снижение производительности.


Именно это и происходит. Дайте примерчик "параметризованной процедуры" в моем случае.
11 июл 14, 16:44    [16293172]     Ответить | Цитировать Сообщить модератору
 Re: Как передать запросам код пользователя из соответствующей таблицы c SUSER_SNAME()?  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8828
Параметризованная - значит с параметрами:

CREATE PROC dbo.GetInvoices
@id int
AS 
SELECT dbo.Invoices.* FROM dbo.Invoices WHERE ChargedID = @id
GO
11 июл 14, 16:54    [16293243]     Ответить | Цитировать Сообщить модератору
 Re: Как передать запросам код пользователя из соответствующей таблицы c SUSER_SNAME()?  [new]
LightN
Member

Откуда: Москва
Сообщений: 192
Владислав Колосов
Параметризованная - значит с параметрами:

CREATE PROC dbo.GetInvoices
@id int
AS 
SELECT dbo.Invoices.* FROM dbo.Invoices WHERE ChargedID = @id
GO

Проекту >15 лет и если посмотреть на депенденс по dbo.[IAM](), то там огромное дерево ... М.б. есть мысли о том, как пропихнуть именно в IAM() этот код малой кровью? М.б. как-то через массив переменных ... Задача такая: SUSER_SNAME() подпихнуть нужную цифру из таблицы, чтобы сервер доставал эту цифру с той же скоростью что и SUSER_SNAME().
11 июл 14, 17:09    [16293319]     Ответить | Цитировать Сообщить модератору
 Re: Как передать запросам код пользователя из соответствующей таблицы c SUSER_SNAME()?  [new]
Glory
Member

Откуда:
Сообщений: 104751
LightN
Задача такая: SUSER_SNAME() подпихнуть нужную цифру из таблицы, чтобы сервер доставал эту цифру с той же скоростью что и SUSER_SNAME().

http://msdn.microsoft.com/en-us/library/ms180125.aspx
11 июл 14, 17:11    [16293332]     Ответить | Цитировать Сообщить модератору
 Re: Как передать запросам код пользователя из соответствующей таблицы c SUSER_SNAME()?  [new]
Crimean
Member

Откуда:
Сообщений: 13147
а это 2 или 3 - уровневое приложение с имперсонациями?
и - проблемы "везде" или "только на вычитках"? :) подозреваю, что "только на вычитках" же из-за UDF
11 июл 14, 17:20    [16293366]     Ответить | Цитировать Сообщить модератору
 Re: Как передать запросам код пользователя из соответствующей таблицы c SUSER_SNAME()?  [new]
LightN
Member

Откуда: Москва
Сообщений: 192
Опять есть необходимость вернуться к старому поставленному вопросу.
CONTEXT_INFO не подходит, т.к. действует только на текущую сессию, а dbo.IAM() должна выдавать всегда один и тот же код (int) соответственно равный определенному = SUSER_SNAME() для 100-ни Views.
(Напоминаю, что у каждого SUSER_SNAME() свой код, который лежит в dbo.We)

Смотрел на этом форуме, и данная задача стоит у многих. Также у многих остается без ответа ... Предлагалось принудительно кэшировать часто используемую таблицу (оказалось не выход), делать Join (что правильно не трудазатратно) и т.д. и т.п. Э
Но всё таки как правильно выйти из ситуации?

Что если так, завести пользователю доп. свойство:
EXEC sys.sp_addextendedproperty @name=N'BASE_CH_ID', @value=N'3' , @level0type=N'USER',@level0name=N'ABC\LightN'

--
а потом через fn_listextendedproperty передавать его в dbo.IAM().
Соответственно можно реплицировать значение соответствия этого свойства триггером из dbo.We

Это выход или нет? Как вообще правильно?
2 июл 15, 11:53    [17842347]     Ответить | Цитировать Сообщить модератору
 Re: Как передать запросам код пользователя из соответствующей таблицы c SUSER_SNAME()?  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8828
LightN, однако... у Вас был год, чтобы переписать запросы, а Вы до сих пор этого не сделали. За год не то, чтобы сотни можно переписать, а сотни тысяч.
2 июл 15, 11:56    [17842387]     Ответить | Цитировать Сообщить модератору
 Re: Как передать запросам код пользователя из соответствующей таблицы c SUSER_SNAME()?  [new]
Glory
Member

Откуда:
Сообщений: 104751
LightN
CONTEXT_INFO не подходит, т.к. действует только на текущую сессию, а dbo.IAM() должна выдавать всегда один и тот же код (int) соответственно равный определенному = SUSER_SNAME() для 100-ни Views.

А у вас для каждого view отдельная сессия открывается что ли ?
2 июл 15, 11:56    [17842388]     Ответить | Цитировать Сообщить модератору
 Re: Как передать запросам код пользователя из соответствующей таблицы c SUSER_SNAME()?  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8828
Если хотите хоть какой-то производительности, измените скалярную функцию на табличную in-line. Но запросы все равно надо будет переписать.
2 июл 15, 11:58    [17842406]     Ответить | Цитировать Сообщить модератору
 Re: Как передать запросам код пользователя из соответствующей таблицы c SUSER_SNAME()?  [new]
komrad
Member

Откуда:
Сообщений: 5759
LightN
Maxx,
Это Да.
Но IAM() уже живёт нескольких сотнях мест (вью, процедуры, триггеры, функции). Есть ли мысли о том, как часто не дёргая We пропихнуть в IAM() этот код?


А что если значения в таблице dbo.WE привести в соответствие с suser_id() ?
Тогда, при условии, что все пользователи имеют персональные (а не групповые) логины в сиквел, можно будет упростить функцию до
...return (select suser_id(original_login()))
2 июл 15, 12:02    [17842450]     Ответить | Цитировать Сообщить модератору
 Re: Как передать запросам код пользователя из соответствующей таблицы c SUSER_SNAME()?  [new]
LightN
Member

Откуда: Москва
Сообщений: 192
Владислав Колосов
LightN, однако... у Вас был год, чтобы переписать запросы, а Вы до сих пор этого не сделали. За год не то, чтобы сотни можно переписать, а сотни тысяч.

--
:) купили новый сервер ... Работает всё достаточно быстро, но статистики не радуют :(
Клиент у нас на Access (торговая система) в ней есть ODBC-линки к Views (для работы с рекордсетами форм) и куча клиентских процедур напрямую на SQL, где каждый раз открываются и закрываются новая сессии к серверу. Так было создано >10 лет тому назад (100Mb кода!)
--
Думаете от этого не уйти - "Но запросы все равно надо будет переписать"? Жаль-жаль.
2 июл 15, 12:17    [17842560]     Ответить | Цитировать Сообщить модератору
 Re: Как передать запросам код пользователя из соответствующей таблицы c SUSER_SNAME()?  [new]
Glory
Member

Откуда:
Сообщений: 104751
LightN
Жаль-жаль.

Вы серьезно думаете написать пользовательскую функцию, работающую быстрее встроенных в движок функций ?
RLS
2 июл 15, 12:49    [17842768]     Ответить | Цитировать Сообщить модератору
 Re: Как передать запросам код пользователя из соответствующей таблицы c SUSER_SNAME()?  [new]
LightN
Member

Откуда: Москва
Сообщений: 192
komrad
LightN
Maxx,
Это Да.
Но IAM() уже живёт нескольких сотнях мест (вью, процедуры, триггеры, функции). Есть ли мысли о том, как часто не дёргая We пропихнуть в IAM() этот код?


А что если значения в таблице dbo.WE привести в соответствие с suser_id() ?
Тогда, при условии, что все пользователи имеют персональные (а не групповые) логины в сиквел, можно будет упростить функцию до
...return (select suser_id(original_login()))

Точно! Именно такая же мысль была!!! Только пока не придумал как подменять (=сделать соответствие suser_id и we.id при ins, del и upd на we). Перекодировать We нереально. А вот можно ли менять как-то suser_id(), пусть даже пересоздавая?
2 июл 15, 13:02    [17842867]     Ответить | Цитировать Сообщить модератору
 Re: Как передать запросам код пользователя из соответствующей таблицы c SUSER_SNAME()?  [new]
komrad
Member

Откуда:
Сообщений: 5759
LightN
komrad
пропущено...


А что если значения в таблице dbo.WE привести в соответствие с suser_id() ?
Тогда, при условии, что все пользователи имеют персональные (а не групповые) логины в сиквел, можно будет упростить функцию до
...return (select suser_id(original_login()))

Точно! Именно такая же мысль была!!! Только пока не придумал как подменять (=сделать соответствие suser_id и we.id при ins, del и upd на we). Перекодировать We нереально.


ну можно проверить расхождения по id
select w.id [DB id], w.DomainName, sp.name [Login], principal_id [SUSER_SID]
from sys.server_principals sp
 join [DB].dbo.WE w on w.DomainName=sp.name 
 -- and w.id<>sp.principal_id


потом
1) посмотреть, на что они завязаны у вас в базе и процедурах
2) проверить как добавляются/модифицируются записи в dbo.WE
3) далее по обстоятельствам

LightN
А вот можно ли менять как-то suser_id(), пусть даже пересоздавая?

нет, это очень врядли
как вариант: написать триггер для dbo.WE, который будет анализировать добавление и модификацию записей и проставлять корректный id равный principal_id из sys.server_principals для пользователя
2 июл 15, 13:50    [17843323]     Ответить | Цитировать Сообщить модератору
 Re: Как передать запросам код пользователя из соответствующей таблицы c SUSER_SNAME()?  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31984
LightN
Проекту >15 лет и если посмотреть на депенденс по dbo.[IAM](), то там огромное дерево .
...
Думаете от этого не уйти - "Но запросы все равно надо будет переписать"? Жаль-жаль.
Ну если чайники проектировали?

Либо покупать новые всё более мощные серверы, либо переписать, какие ещё варианты?

Нельзя в сиквеле повторно использовать/структурировать код, выделяя его подмножества в функции, по крайней мере, в лоб, не думая.
Опыт из других языков программирования тут неприменим, нужен свой.
2 июл 15, 14:35    [17843745]     Ответить | Цитировать Сообщить модератору
 Re: Как передать запросам код пользователя из соответствующей таблицы c SUSER_SNAME()?  [new]
o-o
Guest
LightN
можно ли менять как-то suser_id(), пусть даже пересоздавая?

если вопрос про то, поменяется ли suser_id() при пересоздании логина, то да, поменяется.
он возрастает при drop login/create login, т.е. старый уже просто не задействуется
2 июл 15, 14:41    [17843795]     Ответить | Цитировать Сообщить модератору
 Re: Как передать запросам код пользователя из соответствующей таблицы c SUSER_SNAME()?  [new]
LightN
Member

Откуда: Москва
Сообщений: 192
Вопрос не совсем по этой теме, а так вообще ...

Нужно сделать ALTER dbo.IAM() или какой нибудь другой функции, а она сидит в некоторых таблицах как Default Value or Binding, т.е. как дефолтовое значение какого-нибудь поля.

Msg 3729, Level 16, State 3, Procedure IAM, Line 8
Невозможно ALTER "dbo.IAM", так как на него ссылается объект "DF_Rezerv_Mng_EnteredPerson".


Как правильно обойти это "Невозможно" на рабочей БД?
21 авг 15, 11:55    [18050602]     Ответить | Цитировать Сообщить модератору
 Re: Как передать запросам код пользователя из соответствующей таблицы c SUSER_SNAME()?  [new]
invm
Member

Откуда: Москва
Сообщений: 9838
LightN
Как правильно обойти это "Невозможно"
Вынести тело dbo.IAM в отдельную функцию, оставив dbo.IAM только как обертку.
21 авг 15, 12:40    [18050891]     Ответить | Цитировать Сообщить модератору
 Re: Как передать запросам код пользователя из соответствующей таблицы c SUSER_SNAME()?  [new]
invm
Member

Откуда: Москва
Сообщений: 9838
Но для этого таки придется удалить все ссылки, а затем их воссоздать. Можно написать для этого скрипт.
Зато потом сможете безболезненно менять функцию.
21 авг 15, 12:47    [18050941]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить