powered by simpleCommunicator - 2.0.52     © 2025 Programmizd 02
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Избыточное чтение при запросе sysprivs и database_permissions
15 сообщений из 15, страница 1 из 1
Избыточное чтение при запросе sysprivs и database_permissions
    #40016585
Кесарь
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
Коллеги, доброго всем дня!

Возникло такое затруднение: база у нас большая, пользователей много (тысячи), и раздача прав стала занимать слишком много ресурсов и времени. Включая даже блокировки, при которых от раздачи прав приходится временно отказываться (в случаях, когда раздача сразу на много пользователей, а загрузка велика). Начал пытаться оптимизировать процесс, но всё упёрлось в системные вещи, которые мне неподвластны.

В конечном итоге всё упирается вот в такой запрос разрешений на объекты:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
declare @UserId int = (
  select user_id(convert(sysname, Obj.[SysName]))
  from dbo.[Object] Obj
  where Obj.ID = 11212425230450 );

select
  DBPerms.major_id
from sys.database_permissions DbPerms
where DBPerms.grantee_principal_id = @UserId




Вот с таким планом исполнения (см. файл), в котором видно, что запрос к sys.database_permissions это на самом деле чтение sys.sysprivs. И что при выборке просто по grantee_principal_id начитывается более 13 млн записей при результате в 1657 записей. И ведь так каждый раз!

Имеет ли проблема какое-то решение? Понятно, что эти системные вещи мне для редактирования недоступны. Но может существует какое-то обновление сервера, в котором накатывается нужный (например) индекс на sysprivs?


P.S. Для поиска темы: "residual i/o sysprivs".
...
Рейтинг: 0 / 0
Избыточное чтение при запросе sysprivs и database_permissions
    #40016588
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
Кесарь
Имеет ли проблема какое-то решение?
Ага.
Называется "роли БД"
...
Рейтинг: 0 / 0
Избыточное чтение при запросе sysprivs и database_permissions
    #40016591
felix_ff
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
Кесарь,

На sys.privs только кластерный индекс, создать что либо на системной таблице не возможно.

а нафига вы вычитываете все права отдельного пользователя?
при этом чтение database_permissions достаточно нетривиальная вещь. представление не отображает некоторые наследуемые от ролей права.

обычно самый верный способ запросить статус нужного права через has_perms_by_name
...
Рейтинг: 0 / 0
Избыточное чтение при запросе sysprivs и database_permissions
    #40016592
Кесарь
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
invm
Кесарь
Имеет ли проблема какое-то решение?
Ага.
Называется "роли БД"


А поподробнее?
...
Рейтинг: 0 / 0
Избыточное чтение при запросе sysprivs и database_permissions
    #40016593
felix_ff
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
Кесарь,

это намек к тому что права нужно выдавать не к конкретным пользователем, а ролям. и пользователей уже включать в необходимые роли.
...
Рейтинг: 0 / 0
Избыточное чтение при запросе sysprivs и database_permissions
    #40016596
Кесарь
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
felix_ff
Кесарь,

На sys.privs только кластерный индекс, создать что либо на системной таблице не возможно.


Да я догадался :) Потому и спросил, что может там какое-то обновление есть с индексом (прикладные вещи так не делаются, индексы есть всегда).


felix_ff
а нафига вы вычитываете все права отдельного пользователя?


Чтобы сопоставить, на какие компоненты надо выдать права, а на какие отобрать (при изменениях допусков по должности, например какие-то функции у отдела передали другому отделу и ещё куча подобных случаев).

На основе полученного списка уже формируется список команд сервера по раздаче/отбору прав.


felix_ff
при этом чтение database_permissions достаточно нетривиальная вещь. представление не отображает некоторые наследуемые от ролей права.

обычно самый верный способ запросить статус нужного права через has_perms_by_name


Спсб! Щас буду пробовать.
...
Рейтинг: 0 / 0
Избыточное чтение при запросе sysprivs и database_permissions
    #40016598
Кесарь
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
felix_ff
Кесарь,

это намек к тому что права нужно выдавать не к конкретным пользователем, а ролям. и пользователей уже включать в необходимые роли.


С точки зрения сервера наверное так и надо. А вот в практической работе провернуть такое крайне сложно. Администрирование ролей будет очень сложным и затратным. Хотя если сопоставить группам...

Но для моего случая критическим является тот простой факт, что на таком уровне я ничего не определяю. Переделать начитку прав это одно, а переделать всю архитектуру системы безопасности - другое.
...
Рейтинг: 0 / 0
Избыточное чтение при запросе sysprivs и database_permissions
    #40016599
felix_ff
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
Кесарь,

вы сначала определитесь с тем какие именно права вам нужны.

у вас ситуации при которых sys.privs может содержать много строк может быть только две:
а) у вас в базе дофигища отдельно взятых пользователей которым раздаются персональные права на объекты
б) у вас в базе сравнительно мало пользователей, но зато дофигища объектов на которые выданы права (таблицы, колонки, процедуры, etc)

вот в варианте а) имеет смысл рассматривать модель выдачи прав через роли

в варианте б) превалирующую роль всегда играют записи с типом class = 1 (то есть OBJECT_OR_COLUMN)
в этом случае, если вы хотите к примеру контролировать своим скриптом права только на процедуры а таблицы и колонки вас не сильно интересуют то явно указывайте это в предикате запроса

к примеру такой запрос даст в оценке меньше строк потому что учтет условие позволяющее отобрать строки более точно
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
declare @UserId int = (
  select user_id(convert(sysname, Obj.[SysName]))
  from dbo.[Object] Obj
  where Obj.ID = 11212425230450 );

select
  DBPerms.major_id
from sys.database_permissions DbPerms
where DBPerms.grantee_principal_id = @UserId
 and class = 1
 and minor_id = 0




и да для варианта б) опять же можно гранулировать выдачу прав:
если вы к примеру в обычном кейсе выдаете чето типа
Код: sql
1.
2.
3.
4.
GRANT EXECUTE ON [dbo].[proc1] TO [user1]
GRANT EXECUTE ON [dbo].[proc2] TO [user1]
...
GRANT EXECUTE ON [dbo].[procN] TO [user1]



воозможно имеет смысл дать EXECUTE на всю схему а не на отдельные объекты, а тем кому надо запрещать давать явный DENY смотрите всегда каких прав вы выдаете больше, тогда возможно надо идти не от схемы "запретить всем, выдавать кому надо", а наоборот "разрешить всем, запрещать кому не надо". но это опять же требует тщательного анализа.
...
Рейтинг: 0 / 0
Избыточное чтение при запросе sysprivs и database_permissions
    #40016603
Кесарь
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
felix_ff
Кесарь,

вы сначала определитесь с тем какие именно права вам нужны.

у вас ситуации при которых sys.privs может содержать много строк может быть только две:
а) у вас в базе дофигища отдельно взятых пользователей которым раздаются персональные права на объекты


Да, именно эта ситуация. Любому пользователю могут быть даны права на любой произвольный объект, и часто так и делается. Объект тут с точки зрения прикладной системы.

На уровне сервера абс. большинство прав - это execute на процедуру.


вот в варианте а) имеет смысл рассматривать модель выдачи прав через роли

в этом случае, если вы хотите к примеру контролировать своим скриптом права только на процедуры а таблицы и колонки вас не сильно интересуют то явно указывайте это в предикате запроса

к примеру такой запрос даст в оценке меньше строк потому что добавит в seek_predicate условие позволяющее отобрать строки более точно
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
declare @UserId int = (
  select user_id(convert(sysname, Obj.[SysName]))
  from dbo.[Object] Obj
  where Obj.ID = 11212425230450 );

select
  DBPerms.major_id
from sys.database_permissions DbPerms
where DBPerms.grantee_principal_id = @UserId
 and class = 1
 and minor_id = 0




Я так тоже пробовал и щас попробовал ещё раз: внутри работы запроса ничего не меняется. И это логично, если как сказано выше, там один только кластерный индекс.
...
Рейтинг: 0 / 0
Избыточное чтение при запросе sysprivs и database_permissions
    #40016606
felix_ff
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
Кесарь,

вид плана не поменяется он там только таким и может быть как clustered index seek + filter

но вот оценка кол-ва строк для residual predicate должна измениться. seek_predicate может меняться от class < 100 прописанного в определении во view до class = <cons> указанного в вашем запросе.

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

так то с системными таблицами физически ничего дельного сделать нельзя, но вот создать статистику на них можно (в режиме DAC конечно)
...
Рейтинг: 0 / 0
Избыточное чтение при запросе sysprivs и database_permissions
    #40016685
Владислав Колосов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
Кесарь,

администрировать роли и не надо на сервере.
Ролями рулят администраторы домена. У себя на сервере добавить группы домена, которые соответствуют ролям безопасности приложения. Группам выдайте права.
...
Рейтинг: 0 / 0
Избыточное чтение при запросе sysprivs и database_permissions
    #40016712
felix_ff
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
Владислав Колосов,

не обязательно что там используется windows-аутентификация
...
Рейтинг: 0 / 0
Избыточное чтение при запросе sysprivs и database_permissions
    #40016760
Владислав Колосов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
felix_ff,

если сеть одноранговая и тысяча пользователей - даже не знаю как это все разруливается... Очень кустарное было бы решение.
...
Рейтинг: 0 / 0
Избыточное чтение при запросе sysprivs и database_permissions
    #40016938
Кесарь
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
felix_ff
Кесарь,

На sys.privs только кластерный индекс, создать что либо на системной таблице не возможно.


Вот это и удивляет, что только кластерный. Они там по прежнему в 1993-м году живут?



felix_ff
а нафига вы вычитываете все права отдельного пользователя?
при этом чтение database_permissions достаточно нетривиальная вещь. представление не отображает некоторые наследуемые от ролей права.


Возможно вы правы по поводу неполного списка прав. Но зато это представление! И его использование намного более универсальное, нежели у функции. Если бы оно внутри было нормально реализовано, то было бы почти идеальным.


felix_ff
обычно самый верный способ запросить статус нужного права через has_perms_by_name


Исследовал это дело. Работает. Быстрее, но нестабильно быстрее (на разных средах по разному, от 2.5 до 18 раз, почему так, не понял).

Однако есть одно но. Это дело требует от выполняющего запрос прав на имперсонализацию. И то не всегда работает (для разных обрабатываемых юзеров)! Короче чтобы работало надёжно, нужно чтобы выполняющий был членом группы sysadmin! А это прямо скажем не то, что нужно в большой компании с серьёзным разделением обязанностей.


Вот такой кусочек кода получился:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
declare @UserId int, @login sysname;

declare @db_perms table ([object_id] int not null primary key,
  ServerName sysname not null, ObjectType char(2) not null,
  index db_perms_idx nonclustered (ServerName, ObjectType));

select
  @UserId = user_id(convert(sysname, Obj.[SysName])),
  @login = convert(sysname, Obj.[SysName])
from dbo.[Object] Obj
where Obj.ID =    11212425230450 --11068120629040 ;

execute as user = @login;

insert into @db_perms ([object_id], ServerName, ObjectType)	
select
  DbObject.[object_id],
  DbObject.[name],
  DbObject.[type]
--  HAS_PERMS_BY_NAME(DbObject.name, 'OBJECT', 'ANY')
from sys.objects DbObject
where DbObject.[type] in ('U', 'P', 'V', 'FN', 'TF', 'IF')
  and HAS_PERMS_BY_NAME(DbObject.[name], 'OBJECT', 'ANY') = 1   -- ?

revert;



И вроде работает, но в некоторых случаях, как уже сказано выше, выдаёт ошибку вида:

Код: sql
1.
Could not obtain information about Windows NT group/user 'DOMAIN\login', error code 0x6ba.
...
Рейтинг: 0 / 0
Избыточное чтение при запросе sysprivs и database_permissions
    #40016982
felix_ff
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
Кесарь


felix_ff
обычно самый верный способ запросить статус нужного права через has_perms_by_name


Исследовал это дело. Работает. Быстрее, но нестабильно быстрее (на разных средах по разному, от 2.5 до 18 раз, почему так, не понял).

Однако есть одно но. Это дело требует от выполняющего запрос прав на имперсонализацию. И то не всегда работает (для разных обрабатываемых юзеров)! Короче чтобы работало надёжно, нужно чтобы выполняющий был членом группы sysadmin! А это прямо скажем не то, что нужно в большой компании с серьёзным разделением обязанностей.




такое лечится созданием сервисной хранимой процедуры и подписью ее с помощью сертификата

И вроде работает, но в некоторых случаях, как уже сказано выше, выдаёт ошибку вида:

Код: sql
1.
Could not obtain information about Windows NT group/user 'DOMAIN\login', error code 0x6ba.




советую прочитать https://spaghettidba.com/2019/08/16/troubleshooting-could-not-obtain-information-about-windows-nt-group-user/

Вот это и удивляет, что только кластерный. Они там по прежнему в 1993-м году живут?

индекс спроектированный для данной системной таблицы по сути идеален. наличие дополнительных некластерных индексов как мы знаем замедляет операции DML, думаю в МС исходили из этих двух фактов. (хотя конечно это гадание на кофейной гуще )
...
Рейтинг: 0 / 0
15 сообщений из 15, страница 1 из 1
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Избыточное чтение при запросе sysprivs и database_permissions
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]