|
Избыточное чтение при запросе sysprivs и database_permissions
|
|||
---|---|---|---|
#18+
Коллеги, доброго всем дня! Возникло такое затруднение: база у нас большая, пользователей много (тысячи), и раздача прав стала занимать слишком много ресурсов и времени. Включая даже блокировки, при которых от раздачи прав приходится временно отказываться (в случаях, когда раздача сразу на много пользователей, а загрузка велика). Начал пытаться оптимизировать процесс, но всё упёрлось в системные вещи, которые мне неподвластны. В конечном итоге всё упирается вот в такой запрос разрешений на объекты: Код: sql 1. 2. 3. 4. 5. 6. 7. 8. 9.
Вот с таким планом исполнения (см. файл), в котором видно, что запрос к sys.database_permissions это на самом деле чтение sys.sysprivs. И что при выборке просто по grantee_principal_id начитывается более 13 млн записей при результате в 1657 записей. И ведь так каждый раз! Имеет ли проблема какое-то решение? Понятно, что эти системные вещи мне для редактирования недоступны. Но может существует какое-то обновление сервера, в котором накатывается нужный (например) индекс на sysprivs? P.S. Для поиска темы: "residual i/o sysprivs". ... |
|||
:
Нравится:
Не нравится:
|
|||
09.11.2020, 17:04 |
|
Избыточное чтение при запросе sysprivs и database_permissions
|
|||
---|---|---|---|
#18+
Кесарь Имеет ли проблема какое-то решение? Называется "роли БД" ... |
|||
:
Нравится:
Не нравится:
|
|||
09.11.2020, 17:19 |
|
Избыточное чтение при запросе sysprivs и database_permissions
|
|||
---|---|---|---|
#18+
Кесарь, На sys.privs только кластерный индекс, создать что либо на системной таблице не возможно. а нафига вы вычитываете все права отдельного пользователя? при этом чтение database_permissions достаточно нетривиальная вещь. представление не отображает некоторые наследуемые от ролей права. обычно самый верный способ запросить статус нужного права через has_perms_by_name ... |
|||
:
Нравится:
Не нравится:
|
|||
09.11.2020, 17:28 |
|
Избыточное чтение при запросе sysprivs и database_permissions
|
|||
---|---|---|---|
#18+
invm Кесарь Имеет ли проблема какое-то решение? Называется "роли БД" А поподробнее? ... |
|||
:
Нравится:
Не нравится:
|
|||
09.11.2020, 17:28 |
|
Избыточное чтение при запросе sysprivs и database_permissions
|
|||
---|---|---|---|
#18+
Кесарь, это намек к тому что права нужно выдавать не к конкретным пользователем, а ролям. и пользователей уже включать в необходимые роли. ... |
|||
:
Нравится:
Не нравится:
|
|||
09.11.2020, 17:30 |
|
Избыточное чтение при запросе sysprivs и database_permissions
|
|||
---|---|---|---|
#18+
felix_ff Кесарь, На sys.privs только кластерный индекс, создать что либо на системной таблице не возможно. Да я догадался :) Потому и спросил, что может там какое-то обновление есть с индексом (прикладные вещи так не делаются, индексы есть всегда). felix_ff а нафига вы вычитываете все права отдельного пользователя? Чтобы сопоставить, на какие компоненты надо выдать права, а на какие отобрать (при изменениях допусков по должности, например какие-то функции у отдела передали другому отделу и ещё куча подобных случаев). На основе полученного списка уже формируется список команд сервера по раздаче/отбору прав. felix_ff при этом чтение database_permissions достаточно нетривиальная вещь. представление не отображает некоторые наследуемые от ролей права. обычно самый верный способ запросить статус нужного права через has_perms_by_name Спсб! Щас буду пробовать. ... |
|||
:
Нравится:
Не нравится:
|
|||
09.11.2020, 17:36 |
|
Избыточное чтение при запросе sysprivs и database_permissions
|
|||
---|---|---|---|
#18+
felix_ff Кесарь, это намек к тому что права нужно выдавать не к конкретным пользователем, а ролям. и пользователей уже включать в необходимые роли. С точки зрения сервера наверное так и надо. А вот в практической работе провернуть такое крайне сложно. Администрирование ролей будет очень сложным и затратным. Хотя если сопоставить группам... Но для моего случая критическим является тот простой факт, что на таком уровне я ничего не определяю. Переделать начитку прав это одно, а переделать всю архитектуру системы безопасности - другое. ... |
|||
:
Нравится:
Не нравится:
|
|||
09.11.2020, 17:42 |
|
Избыточное чтение при запросе sysprivs и database_permissions
|
|||
---|---|---|---|
#18+
Кесарь, вы сначала определитесь с тем какие именно права вам нужны. у вас ситуации при которых sys.privs может содержать много строк может быть только две: а) у вас в базе дофигища отдельно взятых пользователей которым раздаются персональные права на объекты б) у вас в базе сравнительно мало пользователей, но зато дофигища объектов на которые выданы права (таблицы, колонки, процедуры, etc) вот в варианте а) имеет смысл рассматривать модель выдачи прав через роли в варианте б) превалирующую роль всегда играют записи с типом class = 1 (то есть OBJECT_OR_COLUMN) в этом случае, если вы хотите к примеру контролировать своим скриптом права только на процедуры а таблицы и колонки вас не сильно интересуют то явно указывайте это в предикате запроса к примеру такой запрос даст в оценке меньше строк потому что учтет условие позволяющее отобрать строки более точно Код: sql 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11.
и да для варианта б) опять же можно гранулировать выдачу прав: если вы к примеру в обычном кейсе выдаете чето типа Код: sql 1. 2. 3. 4.
воозможно имеет смысл дать EXECUTE на всю схему а не на отдельные объекты, а тем кому надо запрещать давать явный DENY смотрите всегда каких прав вы выдаете больше, тогда возможно надо идти не от схемы "запретить всем, выдавать кому надо", а наоборот "разрешить всем, запрещать кому не надо". но это опять же требует тщательного анализа. ... |
|||
:
Нравится:
Не нравится:
|
|||
09.11.2020, 17:44 |
|
Избыточное чтение при запросе sysprivs и database_permissions
|
|||
---|---|---|---|
#18+
felix_ff Кесарь, вы сначала определитесь с тем какие именно права вам нужны. у вас ситуации при которых sys.privs может содержать много строк может быть только две: а) у вас в базе дофигища отдельно взятых пользователей которым раздаются персональные права на объекты Да, именно эта ситуация. Любому пользователю могут быть даны права на любой произвольный объект, и часто так и делается. Объект тут с точки зрения прикладной системы. На уровне сервера абс. большинство прав - это execute на процедуру. вот в варианте а) имеет смысл рассматривать модель выдачи прав через роли в этом случае, если вы хотите к примеру контролировать своим скриптом права только на процедуры а таблицы и колонки вас не сильно интересуют то явно указывайте это в предикате запроса к примеру такой запрос даст в оценке меньше строк потому что добавит в seek_predicate условие позволяющее отобрать строки более точно Код: sql 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11.
Я так тоже пробовал и щас попробовал ещё раз: внутри работы запроса ничего не меняется. И это логично, если как сказано выше, там один только кластерный индекс. ... |
|||
:
Нравится:
Не нравится:
|
|||
09.11.2020, 17:58 |
|
Избыточное чтение при запросе sysprivs и database_permissions
|
|||
---|---|---|---|
#18+
Кесарь, вид плана не поменяется он там только таким и может быть как clustered index seek + filter но вот оценка кол-ва строк для residual predicate должна измениться. seek_predicate может меняться от class < 100 прописанного в определении во view до class = <cons> указанного в вашем запросе. кстати иногда полезно обновлять статистику на системных таблицах, вообще она обычно актуальная, но я сталкивался с ситуациями когда запросы на мелких базах кидались ворнингами о отсутствующей статистике. так то с системными таблицами физически ничего дельного сделать нельзя, но вот создать статистику на них можно (в режиме DAC конечно) ... |
|||
:
Нравится:
Не нравится:
|
|||
09.11.2020, 18:10 |
|
Избыточное чтение при запросе sysprivs и database_permissions
|
|||
---|---|---|---|
#18+
Кесарь, администрировать роли и не надо на сервере. Ролями рулят администраторы домена. У себя на сервере добавить группы домена, которые соответствуют ролям безопасности приложения. Группам выдайте права. ... |
|||
:
Нравится:
Не нравится:
|
|||
09.11.2020, 23:44 |
|
Избыточное чтение при запросе sysprivs и database_permissions
|
|||
---|---|---|---|
#18+
Владислав Колосов, не обязательно что там используется windows-аутентификация ... |
|||
:
Нравится:
Не нравится:
|
|||
10.11.2020, 02:12 |
|
Избыточное чтение при запросе sysprivs и database_permissions
|
|||
---|---|---|---|
#18+
felix_ff, если сеть одноранговая и тысяча пользователей - даже не знаю как это все разруливается... Очень кустарное было бы решение. ... |
|||
:
Нравится:
Не нравится:
|
|||
10.11.2020, 11:31 |
|
Избыточное чтение при запросе sysprivs и database_permissions
|
|||
---|---|---|---|
#18+
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.
И вроде работает, но в некоторых случаях, как уже сказано выше, выдаёт ошибку вида: Код: sql 1.
... |
|||
:
Нравится:
Не нравится:
|
|||
10.11.2020, 19:39 |
|
Избыточное чтение при запросе sysprivs и database_permissions
|
|||
---|---|---|---|
#18+
Кесарь felix_ff обычно самый верный способ запросить статус нужного права через has_perms_by_name Исследовал это дело. Работает. Быстрее, но нестабильно быстрее (на разных средах по разному, от 2.5 до 18 раз, почему так, не понял). Однако есть одно но. Это дело требует от выполняющего запрос прав на имперсонализацию. И то не всегда работает (для разных обрабатываемых юзеров)! Короче чтобы работало надёжно, нужно чтобы выполняющий был членом группы sysadmin! А это прямо скажем не то, что нужно в большой компании с серьёзным разделением обязанностей. такое лечится созданием сервисной хранимой процедуры и подписью ее с помощью сертификата И вроде работает, но в некоторых случаях, как уже сказано выше, выдаёт ошибку вида: Код: sql 1.
советую прочитать https://spaghettidba.com/2019/08/16/troubleshooting-could-not-obtain-information-about-windows-nt-group-user/ Вот это и удивляет, что только кластерный. Они там по прежнему в 1993-м году живут? индекс спроектированный для данной системной таблицы по сути идеален. наличие дополнительных некластерных индексов как мы знаем замедляет операции DML, думаю в МС исходили из этих двух фактов. (хотя конечно это гадание на кофейной гуще ) ... |
|||
:
Нравится:
Не нравится:
|
|||
10.11.2020, 23:01 |
|
|
start [/forum/topic.php?fid=46&msg=40016712&tid=1685428]: |
0ms |
get settings: |
10ms |
get forum list: |
12ms |
check forum access: |
4ms |
check topic access: |
4ms |
track hit: |
64ms |
get topic data: |
10ms |
get forum data: |
3ms |
get page messages: |
46ms |
get tp. blocked users: |
1ms |
others: | 15ms |
total: | 169ms |
0 / 0 |