Определение действующих разрешений для ядра СУБД
В этой статье описывается, как определить, кто имеет разрешения на различные объекты в ядре СУБД SQL Server. SQL Server реализует две системы разрешений для ядра СУБД. Более старая система предопределенных ролей имеет предварительно настроенные разрешения. Начиная с версии SQL Server 2005, доступна более гибкая и точная система. (Сведения в этой статье также относятся к SQL Server начиная с версии 2005. Отдельные типы разрешений недоступны в некоторых версиях SQL Server.)
Сводка
Более старая система разрешений предопределенной роли
Для предопределенных ролей сервера и предопределенных ролей базы данных предварительно настроены разрешения, которые не могут быть изменены. Чтобы определить, кто является участником предопределенной роли сервера, выполните указанный ниже запрос.
Не применимо к Базе данных SQL или Azure Synapse Analytics, где разрешение уровня сервера недоступно. Столбец is_fixed_role таблицы sys.server_principals был добавлен в SQL Server 2012. Он не требуется для более старых версий SQL Server.
Чтобы определить, кто является участником предопределенной роли базы данных, выполните следующий запрос в каждой базе данных.
Чтобы понять разрешения, предоставляемые каждой роли, см. описания роли на иллюстрациях в электронной документации (о ролях уровня сервера и ролях уровня базы данных).
Новая система детализированных разрешений
Эта система гибкая, но может быть достаточно сложной, если необходимо настроить права более точно. Чтобы упростить работу, система позволяет создавать роли, назначать разрешения ролям, а затем добавлять группы пользователей в роли. В идеале команда разработки базы данных может разделить действия в виде схемы, а затем предоставить разрешения роли всей схеме, а не отдельным таблицам или процедурам. Реальные сценарии являются сложными, а в зависимости от потребностей бизнеса могут предъявляться неожиданные требования к безопасности.
На следующей схеме показаны разрешения и их связи друг с другом. Некоторые из разрешений более высокого уровня (например, CONTROL SERVER ) указаны несколько раз. Рисунок в этой статье слишком мал для чтения. Щелкните изображение, чтобы скачать плакат разрешений для ядра СУБД в формате PDF.
Классы безопасности
Субъекты
Разрешения предоставляются субъектам. Субъектами могут быть серверные роли, имена входа, роли базы данных или пользователи. Имена входа могут представлять группы Windows, которые содержат множество пользователей Windows. Так как SQL Server не управляет группами Windows, он не всегда знает, кто является участником группы Windows. Когда пользователь Windows подключается к SQL Server, пакет входа содержит токены членства в группе Windows для пользователя.
При подключении пользователя Windows с помощью имени входа на основе группы Windows для некоторых действий серверу SQL Server потребуется создать имя входа или пользователя для олицетворения отдельного пользователя Windows. Например, группа Windows («Инженеры») содержит пользователей (Мария, Тимофей, Григорий), и у этой группы есть учетная запись пользователя базы данных. Если Мария имеет разрешение и создает таблицу, можно создать пользователя (Mariya) в качестве владельца таблицы. Если для Тимофея отклонено разрешение, которое имеют остальные участники группы «Инженеры», необходимо создать соответствующего пользователя, чтобы отследить отклонение разрешения.
Помните, что пользователь Windows может быть участником нескольких групп Windows (например, «Инженеры» и «Менеджеры»). Разрешения, предоставленные или отклоненные для имени входа «Инженеры» или «Менеджеры», индивидуально для каждого пользователя или для ролей, участником которых является пользователь, будут объединены и оценены для определения действующих разрешений. Функция HAS_PERMS_BY_NAME выявляет наличие определенного разрешения у пользователя или имени входа. Тем не менее невозможно определить источник предоставления или отклонения разрешения. Изучите список разрешений и попробуйте их на практике.
Полезные запросы
Разрешения сервера
Следующий запрос возвращает список разрешений, которые были предоставлены или запрещены на уровне сервера. Этот запрос должен быть выполнен в базе данных master.
Разрешения уровня сервера нельзя предоставлять и запрашивать в Базе данных SQL или Azure Synapse Analytics.
Разрешения базы данных
Следующий запрос возвращает список разрешений, которые были предоставлены или отклонены на уровне базы данных. Этот запрос должен быть выполнен в каждой базе данных.
Каждый класс разрешений в таблице разрешений может быть присоединен к другим системным представлениям, содержащим связанные сведения о классе защищаемого объекта. Например, следующий запрос предоставляет имя объекта базы данных, на которого распространяется разрешение.
Сведения о синтаксисе см. в статье HAS_PERMS_BY_NAME.
Как в MS SQL Server создать пользователя
На первый взгляд, в MS SQL нестандартная система учетных записей. В ней существуют логины и пользователи.
Оба типа учетных записи можно создать как в графике, так и с помощью запроса T-SQL
T-SQL CREATE USER, CREATE LOGIN
Перейдем в базу данных, в которой мы работаем:
Для создания логина SQL выполним:
Посмотреть что этот логин создался можно так:
Для создания пользователя привязанного к логину нужно выполнить:
Отмечу, что пользователь создасться в той базе, откуда мы выполняем запрос.
Что бы просто создать пользователя нужно:
Созданных пользователей можно увидеть так:
Пользователи создаются без каких либо прав на базу.
T-SQL GRANT
GRANT в пер. «Разрешение» дает права на какое-то действие.
Что бы дать пользователю право получать данные из какой-то таблицы нужно выполнить:
Если мы хотим зайти под созданным пользователем нам нужно выполнить ряд действий. Дело в том, что по умолчанию аутентификация по логинам SQL отключена (разрешена только Windows). Для этого зайдем в свойства сервера:
Затем в «Безопасность» и включи аутентификация SQL
Важно сделать так, что бы у нас было 2 окошка для запросов до того как мы сменем пользователя. Благодаря этому у нас каждое окно будет работать под разными пользователями
Затем окно подключения пользователя:
Меняем тип подключени на SQL и вводим логин/пароль. Я создавал логин Anton, с паролем Password1910.
После подключения можем увидеть, что одно окно работает под логином Anton, а другое под учетной записью с помощью которой мы проводили операции выше. Так же обратите внимания, что новые пользователь подключен к другой базе (master).
Перейдем к базе, на которую мы давали права. В моем случае это база pubs
Я давал права на SELECT для таблицы dbo.authors. Проверем работу:
Запрос дожен пройти успешно.
Для другой таблицы, на которую мы не давали прав:
The SELECT permission was denied on the object ‘titles’, database ‘pubs’, schema ‘dbo’.
Что бы отозвать разрешение на SELECT нужно заменить GRANT на REVOKE (не забудьте переключится на окно у которого есть разрешение или переключится на предыдущего пользователя):
Разрешений, которые мы можем выдать пользователю достаточно много. Это основные:
Мы можем так же явно запретить пользователю выполнять определенные запросы. Для этого есть DENY:
Что ба дать разрешение на создание таблиц:
Теперь создадим логин через графический интерфейс. Нажмем следующие кнопки:
В новом окне мы должны заполнить логин (1) и пароль (3). Под цифрой 2 мы можем увидеть выбор способа аутентификации. В случае Windows мы должны будем выбрать локального пользователя или пользователя AD. Перейдем на закладку Server Roles (4).
Приведу описание самых популярных ролей:
Перейдем на следующую вкладку (2)
На этой закладке можно сразу создать пользователя и привязать его к базе данных. На этой закладке отображаются 4 базы, которые вы скорее всего раньше не видели. Это системные базы данных, которые хранят в себе транзакции, структуру подключенных баз, скрипты агента и т.д. В области 2 можно дать права на выделенную базу данных.
Коротко о нескольких ролях баз данных:
Роль public, в отличии от всех остальных, можно изменять. Т.е. можно дать роли public разрешение на SELECT и тогда все пользователи с логином смогут это делать. По умолчанию эта ролько может делать запросы типа:
Роли уровня базы данных
Предопределенные роли базы данных задаются на уровне базы данных и предусмотрены в каждой базе данных. Члены ролей базы данных db_owner могут управлять членством в предопределенных ролях базы данных. Кроме того, в базе данных msdb имеются специальные роли базы данных.
Не добавляйте пользовательские роли базы данных в качестве членов предопределенных ролей. Это может привести к непреднамеренному повышению прав доступа.
Разрешения пользовательских ролей базы данных можно настроить с помощью инструкций GRANT, DENY и REVOKE. Дополнительные сведения см. в разделе Разрешения (компонент Database Engine).
предопределенные роли базы данных
В следующей таблице представлены предопределенные роли базы данных и их возможности. Эти роли существуют во всех базах данных. За исключением открытой роли базы данных разрешения, назначенные предопределенным ролям базы данных изменять нельзя.
Нельзя изменить разрешения, назначенные предопределенным ролям базы данных. На следующем рисунке показаны разрешения, назначенные предопределенным ролям базы данных:
Специальные роли для База данных SQL и Azure Synapse
Эти роли базы данных существуют только в виртуальной базе данных master. Их разрешения ограничены действиями, выполняемыми в базе данных master. В эти роли можно добавить только пользователей из базы данных master. Для этих ролей нельзя добавить имена входа, однако можно создать пользователей на основе имен входа, а затем добавить этих пользователей в роли. Кроме того, в эти роли можно добавить пользователей автономной базы данных из базы данных master. При этом пользователи автономной базы данных, добавленные в роль dbmanager в базе данных master, не могут использоваться для создания новых баз данных.
| Имя роли | Описание |
|---|---|
| dbmanager | Может создавать и удалять базы данных. Член роли dbmanager, который создает базу данных, становится ее владельцем, что позволяет ему подключиться к этой базе данных в качестве пользователя dbo. Пользователь dbo имеет все разрешения в этой базе данных. Члены роли dbmanager необязательно имеют разрешения на доступ к базам данных, которые им не принадлежат. |
| db_exporter | Применяется только к выделенным пулам SQL Azure Synapse Analytics (ранее SQL DW). Члены предопределенной роли базы данных db_exporter могут выполнять все действия для экспорта данных. Разрешения, предоставляемые для этой роли: CREATE TABLE, ALTER ANY SCHEMA, ALTER ANY EXTERNAL DATA SOURCE, ALTER ANY EXTERNAL FILE FORMAT. |
| loginmanager | Может создать и удалять имена входа в виртуальной базе данных master. |
Субъект на уровне сервера и администратор Azure Active Directory (если настроено) имеют все разрешения в База данных SQL и Azure Synapse без необходимости участия в каких-либо ролях. См. дополнительные сведения об аутентификации и авторизация базы данных SQL, включая предоставление доступа.
Некоторые роли баз данных не применимы к Azure SQL или Synapse SQL:
Роли базы данных msdb
База данных msdb содержит специальные роли, показанные в следующей таблице.
| Имя роли базы данных msdb | Описание |
|---|---|
| db_ssisadmin db_ssisoperator db_ssisltduser | Члены этих ролей базы данных могут администрировать и использовать службы Integration Services. Экземпляры SQL Server, обновленные с предыдущей версии, могут содержать более старую версию роли, имя которой присвоено с помощью служб DTS, а не служб Integration Services. Дополнительные сведения см. в разделе Роли служб Integration Services (службы SSIS). |
| dc_admin dc_operator dc_proxy | Члены этих ролей базы данных могут администрировать и использовать сборщик данных. Дополнительные сведения см. в разделе Data Collection. |
| PolicyAdministratorRole | Члены роли базы данных db_ PolicyAdministratorRole могут выполнять все действия по настройке и обслуживанию политик и условий средства «Управление на основе политики». Дополнительные сведения см. в разделах Администрирование серверов с помощью управления на основе политик. |
| ServerGroupAdministratorRole ServerGroupReaderRole | Члены этих ролей базы данных могут администрировать и использовать зарегистрированные группы серверов. |
| dbm_monitor | Создается в базе данных msdb при регистрации в мониторе зеркального отображения базы данных первой базы данных. Роль dbm_monitor не имеет членов до тех пор, пока системный администратор не назначит ее пользователям. |
Работа с ролями уровня базы данных
В следующей таблице описаны команды, представления и функции, предназначенные для работы с ролями уровня баз данных.
Роль базы данных public
Примеры
В примерах этого раздела показано, как работать с ролями уровня базы данных.
A. Добавление пользователя к роли уровня базы данных
Б. Перечисление всех субъектов базы данных, которые являются членами роли уровня базы данных
Следующая инструкция возвращает все члены любой роли базы данных.
Управление пользователями, ролями и именами входа
В SMO имена входа представлены объектом Login. Если имя входа существует в SQL Server, его можно добавить в роль сервера. Роль сервера представлена объектом ServerRole. Роль базы данных представлена объектом DatabaseRole, а роль приложения представлена объектом ApplicationRole.
Права доступа уровня сервера перечислены в виде свойств объекта ServerPermission. Права доступа уровня сервера можно предоставлять, запрещать или отзывать для отдельных учетных записей входа.
У каждого объекта Database есть объект UserCollection, в котором указываются все пользователи базы данных. С каждым пользователем связано имя входа. Одно имя входа может быть связано с пользователями в нескольких базах данных. Метод Login объекта EnumDatabaseMappings используется для перечисления всех пользователей во всех базах данных, связанных с именем входа. И наоборот, свойство User объекта Login содержит имя входа, связанное с пользователем.
В базах данных SQL Server также есть роли, которые определяют набор прав доступа уровня базы данных, позволяющих пользователям выполнять определенные задачи. В отличие от ролей сервера, роли базы данных не являются фиксированными. Их можно создавать, изменять и удалять. Права доступа и пользователей можно назначать роли базы данных для пакетного администрирования.
Пример
Перечисление имен входа и связанных пользователей на языке Visual C#
Перечисление имен входа и связанных пользователей в PowerShell
Управление ролями и пользователями
Этот образец демонстрирует методы управления ролями и пользователями. Для выполнения этого примера потребуется ссылка на следующие сборки:
Запрос SQL Server для поиска всех разрешений/доступа для всех пользователей в базе данных
Я хотел бы написать запрос на sql 2008, который будет сообщать обо всех пользователях, имеющих доступ к конкретной базе данных, или объектам в базе данных, таким как таблицы, представления и хранимые процедуры, напрямую или из-за ролей, и т.д. Этот отчет будет использоваться для целей аудита безопасности. Не уверен, что у кого-то есть запрос, который полностью удовлетворит мои потребности, но, надеюсь, что-то, что даст мне хорошее начало. Любой sql 2008, 2005 или 2000 будет делать, я могу, возможно, преобразовать по мере необходимости.
Это мой первый треск на запрос, основанный на предложениях Andomar. Этот запрос предназначен для предоставления списка разрешений, которые пользователь применял либо непосредственно к учетной записи пользователя, либо через роли, которые пользователь имеет.
Вот полная версия запроса Jeremy Aug 2011 с изменениями, предложенными Brad (октябрь 2011 г.) и iw.kuchin(май 2012 г.):
Надеюсь, это спасет кого-то еще час или две из их жизней. 
В SQL Server 2005 вы можете использовать для этого системные представления. Например, в этом запросе перечислены все пользователи в базе данных с их правами:
Имейте в виду, что пользователь может иметь права и через роль. Например, роль db_data_reader предоставляет права select для большинства объектов.
Не могу комментировать принятый ответ, поэтому я добавлю несколько комментариев здесь:
Я выложу первую часть script со всеми предлагаемыми исправлениями, другие части также должны быть изменены:
Awesome script Джереми и авторы! Спасибо!
У меня есть тонна пользователей, поэтому запуск этого для всех пользователей был кошмаром. Я не мог добавлять комментарии, поэтому я публикую все script с изменениями. Я добавил предложение variable + where, чтобы я мог искать все, что соответствовало бы до 5 символов в имени пользователя (или всех пользователей, когда они были пустыми). Ничего особенного, но я подумал, что это будет полезно в некоторых случаях.
Другие ответы, которые я видел, пропускают некоторые разрешения, которые возможны в базе данных. Первый запрос в приведенном ниже коде получит разрешение на уровне базы данных для всего, которое не является системным объектом. Он также генерирует соответствующие заявления GRANT. Второй запрос получает все роли.
Это нужно запускать для каждой базы данных, но слишком долго для использования с sp_MSforeachdb. Если вы хотите сделать это, вам придется добавить его в основную базу данных в качестве хранимой процедуры системы.
Чтобы охватить все возможности, вам также необходимо иметь script, который проверяет разрешения на уровне сервера.
Хранимая процедура GetPermissions выше хороша, однако использует Sp_msforeachdb, что означает, что она сломается, если ваш экземпляр SQL имеет любые имена баз данных, которые включают пробелы или тире и другие символы, отличные от лучших практик. Я создал версию, которая позволяет избежать использования Sp_msforeachdb, а также включает в себя два столбца, которые указывают 1 – если Login – это логин sysadmin (IsSysAdminLogin) и 2 – если логин является пользователем-сиротой (IsEmptyRow).
Вот моя версия, адаптированная от других. Сейчас я потратил 30 минут, пытаясь вспомнить, как я это придумал, и ответ @Jeremy, кажется, является основным источником вдохновения. Я не хотел обновлять ответ Джереми, на всякий случай, если я представил ошибки, поэтому я публикую свою версию здесь.
Я предлагаю объединить полный сценарий с вдохновением, взятым из Kenneth Fisher T-SQL вторник: какие разрешения имеет конкретный пользователь? : Это позволит вам отвечать на вопросы соответствия/аудита снизу вверх, а не сверху вниз.
Вот сценарий, без включения такой логики обратного просмотра.
Большое спасибо за потрясающие сценарии аудита.
Я настоятельно рекомендую для аудита использовать удивительные хранимые процедуры Кеннета Фишера (b | t):
К сожалению, я не смог прокомментировать сообщение Шона Роуза из-за недостаточной репутации, однако мне пришлось изменить часть сценария с “публичной” ролью, так как он не отображал права доступа SCHEMA из-за (INNER) JOIN против sys. объекты. После того, как это было изменено на LEFT JOIN, мне также пришлось изменить логику предложения WHERE, чтобы исключить системные объекты. Мой исправленный запрос на публичные пермиты ниже.
Я попробовал почти все из этого, но я быстро заметил, что некоторые из них отсутствуют, особенно пользователи sysadmin. Наличие такой дыры не будет хорошо выглядеть в нашей предстоящей ревизии, так что это то, что я придумал