Проверка подлинности подключения учётных записей
Администрирование учётных записей
Сопоставление учётных записей ролям и пользователям
Роли
Установка разрешений доступа для объектов, ролей и пользователей
Правила использования учётных записей
Вспомогательные средства обеспечения безопасности
Вопросы для повторения

Проверка подлинности подключения учётных записей

SQLS7 может возложить проверку подлинности на NT или выполнить
её самостоятельно. В первом случае, при подключении клиента к SQLS7, после проверки NT, открывается доверительное подключение.
На SQLS7 передаются данные об учётной записи пользователя или группы NT сервера, в которую он входит. Если эти данные совпадают с информацией о клиенте в таблице syslogins, то авторизация заканчивается успешно. SQLS7 не проверяет подлинность паролей пользователей, эта задача отводится NT. При работе в домене, доступ клиентов к SQLS7 возможен после регистрации в домене сервера баз данных или в домене, связанном с ним доверительными отношениями.
Во втором случае, все проверки (включая пароль) выполняет сам SQLS7, обращаясь всё к той же таблице syslogins.
Каждая учётная запись пользователя или группы имеет свой уникальный код Security Identification Number, поэтому, если Вы удалите, а потом снова создадите запись с тем же именем, для NT и SQLS7 это будут разные учетные записи.
Используя режим проверки подлинности Windows NT, Вы получаете весь набор средств защиты учётных записей, поддерживаемый этой операционной системой: защищённая проверка подлинности, шифрование паролей, аудит, ограничение срока жизни пароля, ограничение на длину пароля, блокировка учётной записи при повторении неверно введённого пароля. В этом режиме Вы можете создать в SQLS7 только одну учётную запись подключения для всей группы NT. Разумеется, всё это значительно упрощает процедуру подключения пользователей к SQLS7, хотя бы потому, что пароль надо вводить только один раз. Но все эти прелести доступны только клиентам на п латформе Windows. Поэтому, если Вам нужно подключать клиентов из интернета или работающих на отличных от адаптированных для SQLS7 платформах, можно выбрать смешанный режим. Кроме того, вы можете получить дополнительный уровень безопасности, не зависящий от администраторов домена, если, например, Вы являетесь только DBA, а все остальные ресурсы сети управляются другими людьми.
Если прочитав эту шпаргалку Вы решили СРОЧНО изменить на своём SQLS7 режим проверки подлинности, вот Вам порядок необходимых для этого действий:
1. Для NT клиентов проверьте наличие поддерживающих доверительное подключение сетевых библиотек (TCP/IP Sockets, Named Pipes, Multiprotocol);
2. В SQL SEM установите выбранный режим проверки подлинности;
3. Перезапустите сервис MSSQLServer;
4. Заведите в вашем домене группу клиентов, которые будут работать с SQLS7;
5. С помощью SQL SEM предоставьте пользователям или группам пользователей доступ к SQLS7;
6. Для не Windows клиентов создайте с помощью SQL SEM собственные логины, указав им язык по умолчанию.

Администрирование учётных записей

Мы уже знаем, что существует два пути создания учётных записей: с помощью средств NT и непосредственно в SQLS7. Таким образом, для SQLS7 могут существовать одновременно записи пользователей и групп NT, собственные (созданные администратором) записи SQLS7 и установленные по умолчанию (такие, как SA, BUILTIN\Administrator и guest). Все эти записи хранит системная таблица syslogins. Для новой записи права назначаются к установленной по умолчанию базе данных. Полные права имеют записи SA и BUILTIN\Administrator, причём под второй подключаются все администраторы NT. Проще всего добавить новую запись или установить права учётной записи NT с помощью SQL SEM. Однако, если вы сисадмин или администратор безопасности SQLS7, Вы можете использовать непосредственно системные хранимые процедуры, для администрирования учётных записей:
Sp_grantlogin {"имя домена \ учётная запись NT" до 128 символов} - подключение записи/группы NT к SQLS7;
Sp_revokelogin {...} - удаляет подключение записи/группы NT к SQLS7;
Sp_denylogin {...} - запрещает подключение записи/группы NT к SQLS7.
Используя систему аутентификации Windows NT, Вы в полной мере сможете насладиться теми возможностями и сервисам, которые предоставляет связка этой ОС с SQLS7. Например, использование одной записи для группы NT позволяет свободно менять состав этой группы средствами администрирования домена даже не обращаясь к средствам SQLS7. После подключения группы или записи к SQLS7, соответствующие записи в таблице syslogins можно удалить только средствами SQLS7.
Порядок удаления записей такой: вначале удаляем пользователя или группу NT, а потом убираем запись в SQLS7. Получить имя учётной записи NT и домена, к которому она приписана можно с помощью функции SUSER_SNAME.
Для добавления в SQLS7 учётной записи, не входящей в домен или входящей, но требующей уникальных прав не сопоставимых ни с одной из групп NT, можно использовать системную хранимую процедуру:

Sp_addlogin {"имя учётной записи SQLS7" до 128 символов, не пустое, не SA и без "\"}.

Изменение пароля пользователей SQLS7 можно осуществить с помощью: Sp_password. Обычные пользователи могут сменить только свои пароли, а DBA может всё (вместо старого пароля вводится NULL).

Сопоставление учётных записей ролям и пользователям

Используя доверительное подключение групп NT к SQLS7 Вы, тем самым, уже получаете возможность регулирования прав доступа к объектам SQLS7. Но, поскольку NT не единственный источник пользователей, сам SQLS7 имеет обширный набор возможностей для разграничения прав доступа зарегистрированных в таблице sysusers пользователей к своим объектам. Одной из главных задач DBA по обеспечению безопасности данных является грамотное сопоставление учётных записей подключения пользователям и ролям. Данные о каждом пользователе/группе NT, каждом пользователе SQLS7 и о каждой роли в базе данных записываются в sysusers, а права доступа для них хранятся в каждой базе данных в таблице sysprotects.
Кроме SQL SEM, добавить любую из зарегистрированных учётных записей SQLS7 к базе данных можно с помощью системной процедуры:

Sp_grantdbaccess {"имя учётной записи"} [, "имя в базе данных"]

Таким образом, Вы не только можете использовать назначенную пользователю учётную запись SQLS7, но и задать любое подходящее для него имя в базе данных. Поскольку указанная процедура затрагивает жизненно - важные области базы данных, исполнить её может либо владелец базы, либо имеющий права администратора пользователь.
Естественно, кроме добавления возможно и удаление (Sp_revokedbaccess) и изменение (Sp_change_user_login).
Что бы наглядно рассмотреть сопоставление учётных записей пользователей учётным записям подключения, давайте обратимся к двум учётным записям, всегда присутствующим в вашем SQLS7, это DBO и GUEST.
DBO всегда сопоставляется SA и членам роли sysadmin (System Administrators). Кроме того, что dbo не может быть удалена, она ещё и владелец для всех баз данных, которые были, есть и будут созданы в будущем.
В отличие от dbo, учётная запись guest может быть удалена из всех баз данных, кроме master и tempdb. Иногда бывает удобно, что бы к SQLS7 могли подключиться "гости" или, что бы пользователь имел доступ к SQLS7, но для некоторых баз данных он всё равно оставался "гостем".

Роли

Для сложных, многопрофильных приложений баз данных большую помощь в разграничении полномочий между пользователями и/или администраторами SQLS7 может предоставить механизм ролей. Когда необходимо выстроить многоуровневую иерархическую схему взаимодействия с набором баз данных исходя из должностных обязанностей пользователей и разграничения прав доступа администраторов приложений и различных баз данных, Вы в полной мере можете рассчитывать на возможности SQLS7 с помощью ролей решать поставленную задачу.
Фиксированные роли сервера и баз данных, а также пользовательские роли баз данных помогут Вам оперировать не правами доступа каждого пользователя SQLS7, а понятием роли в смыли набора должностных функций или прав доступа применительно к группе пользователей, которые, в свою очередь, могут входить (или не входить вообще) в разные группы NT, числится в разных доменах, работать на платформе отличной от Windows, подключаться к серверу через коммуникационные каналы, относится к разным структурным подразделениям вашей организации и т.д. и т.п.
В виду того, что допускается вложение ролей друг в друга (кроме рекурсивного), Вы можете строить иерархию ролей по доменной схеме. В нижних ярусах будут роли с элементарными наборами прав, а в вершине пирамиды окажутся состоящие из наборов элементарных ролей роли старших менеджеров и администраторов баз данных и приложений. Принцип достаточно прост: если учётная запись входит во многие роли, то в результате права доступа являются объединением предусмотренных этими ролями прав (исключением является только запрет доступа, который имеет более высокий приоритет над остальными правами).Такой подход позволит значительно облегчить изменение прав доступа пользователей в связи с кадровыми перестановками, а также облегчит Вам работу, если потребуется вносить изменения в состав прав доступа группы технологически связанных пользователей, если назрела необходимость преобразования их должностных функций/технологий. Не стоит только сильно злоупотреблять вложением ролей друг в друга, да бы не пострадала производительность сервера.
Фиксированные роли сервера в первую очередь направлены на разграничение функций администрирования SQLS7. Местом их дислокации является таблица syslogins базы master. Как обычно, кроме SQL SEM, включить пользователя в фиксированную роль можно с помощью Sp_addsrvrolemember {учётная запись, фикс. серв. роль}, при условии, что вы член этой фиксированной роли (Sp_dropsrvrolemember - для удаления).
Поскольку роли фиксированные, Вам не удастся их удалить, изменить или добавить. Также Вы не сможете выполнять указанную выше системную хранимую процедуру в рамках запущенной пользователем транзакции. Названия фиксированных серверных ролей говорят сами за себя: sysadmin, dbcreator, diskadmin, processadmin (только относящихся к SQLS7), serveradmin, setupadmin (настройка репликаций), securityadmin (подключения к SQLS7 и аудит).
Такими же бессмертными являются фиксированные роли баз данных. Если для серверных ролей объектом доступа был сам SQLS7, то для фиксированных ролей баз данных объектом являются базы данных и всё, что их составляет. Кроме того, эти роли хранятся непосредственно в базах, причём каждая база данных содержит только относящийся к ней набор ролей. Для больших не любителей SQL SEM, существует системная хранимая процедура Sp_addrolemember {роль, учётная запись}, которой правда могут воспользоваться только те пользователи, которые уже являются участниками соответствующей роли. Естественно обратный процесс можно запустить с помощью Sp_droprolemember. Роль баз данных бывают как разрешительные, так и запретительные.
Например, роли db_datareader и db_datawriter разрешают соответственно чтение / добавление + изменение + удаление данных из всех таблиц базы.
В то же время роли db_denydatareader и db_denydatawriter несут полностью противоположный смысл. Пять ролей несут администраторские привилегии: db_accessadmin (управление пользователями, группами и ролями), db_ddladmin (управление объектами БД), db_securityadmin (доступ к операторам и объектам), db_backupoperator (резервное копирование), db_owner (владелец всей базы данных). Как и большинство систем оперирующих понятием "пользователь", SQLS7 содержит специфическую фиксированную роль баз данных - public. Любой пользователь SQLS7 автоматический становится участником этой роли и будет там состоять, пока его полностью не удалят. Абсолютно все базы SQLS7 имеют у себя эту роль. Назначение этой роли понятно, существует целый ряд операций, которые должны быть доступны абсолютно всем пользователям SQLS7 (печать, оповещения и т.п.). Поскольку все пользователи, группы и роли непременным образом входят в эту роль, добавление этой роли кому бы - то ни было не имеет смысла и посему невозможно. Прелесть роли public в том, что даже не имеющий никаких прав пользователь, подключившись к SQLS7 уже может выполнять операторы или другие задачи, которые не требуют специального разрешения (например, оператор PRINT). Он может видеть содержание системных таблиц и даже выполнять некоторые системные хранимые процедуры, а заодно, для тех баз где этой роли определён Вами некий вариант доступа, он может работать с пользовательскими базами, воспользовавшись учётной записью guest.
К сожалению набор фиксированных ролей не в состоянии охватить все возможные задачи группировки прав пользователей к ресурсам сервера баз данных. Наверняка Вам придётся воспользоваться пользовательскими ролями баз данных. Тем более, что у Вас просто не будет другого выхода, если группы SQLS7 и NT не совпадают по составу пользователей или вы не можете администрировать учётные записи NT. Для ярых не любителей SQL SEM я бы всё же рекомендовал использовать при работе с пользовательскими ролями именно его, потому что эти роли являются смертными и системных процедур для них придумано больше. Кроме того, Вам придётся помнить, какие фиксированные роли могут создавать те или иные пользовательские роли. Например, только db_securityadmin и db_owner могут воспользоваться системной хранимой процедурой Sp_addrole {роль, владелец}, которая в таблице sysusers текущей базы создаст запись для Вашей новой пользовательской роли. Но когда Вам потребуется добавить пользователя в эту новую роль, выяснится, что кроме уже указанных двух фиксированных ролей баз данных, к ролям способным это сделать добавится sysadmin, а процедурой будет всё та же Sp_addrolemember. Те же лица могут выполнять и обратные действия с помощью; Sp_droprole и Sp_droprolemember.

Установка разрешений доступа для объектов, ролей и пользователей

Учётным записям подключения пользователей, которые успешно подключились к SQLS7 и которым сопоставлены учётные записи пользователей сервера БД или роли, для успешной работы необходимо получить разрешение на доступ и работу с объектами баз данных SQLS7.
Для каждой базы разрешения свои и могут быть трёх типов: предопределённые (для фиксированных ролей и владельцев, не подлежат изменению), объектные (доступ к объектам БД и функциям их создания, изменения, просмотра и удаления), операторные (доступ к операторам создающим: базы, таблицы, представления, процедуры, правила, определения, и резервирующим объекты БД). Само разрешение подразумевает три состояния: предоставлено (GRANT), запрещено (DENY) и отозвано (REVOKE). По умолчанию, до явного определения разрешения пользователю, оно находится в отозванном состоянии и запись о нём хранится в таблице sysprotects. Отозванное разрешение, в силу своей аддитивности, может быть переопределено назначенной ролью, а запрещение переопределить невозможно.
Для некоторых фиксированных ролей заранее предопределены разрешения, и устанавливать их нет необходимости. Например, для роли sysadmin автоматически наследуются абсолютно все разрешения, и поделать с этим ничего нельзя. Кроме того, некоторые предопределённые разрешения присущие этой роли невозможно применить к обычным учётным записям.
Схожий механизм действия предопределённых разрешений используют и владельцы объектов, только безграничные разрешения предоставляются им на область их владений.
Разрешения для обычных пользователей могут регулироваться на уровне объектов и операторов. Чётко представляя себе границы прав каждого пользователя, Вы можете установить им разрешения/запрещения на работу с таблицами, столбцами или хранимыми процедурами, а также на возможность использования операторов для создания баз или их элементов. Причём разрешения на операторы применяются не к объекту, на который он воздействует, а к самому оператору. Т.е. права на сам объект (например, БД) у пользователя могут быть сколь угодно большие, но некоторые операторы, воздействующие на этот объект, Вы можете ему запретить использовать. Помните только, что устанавливать разрешения на операторы могут только sysadmin, db_owner и db_securityadmin.
Разрешение на выполнение хранимых процедур сводится, в конечном итоге, к разрешению на выполнение оператора EXECUTE. Правда, часто этого бывает недостаточно, если пользователь не обладает необходимыми объектными разрешениями. Объектные разрешения позволяют Вам тонко регулировать прав доступа к данным и процедурам их обработки. Вы можете предоставлять разрешения на целые таблицы и представления, а также к отдельным столбцам. Важно только помнить некоторые правила, которые проистекают из внутренней природы обработчика запросов T-SQL. Например, устанавливая разрешения на таблицы и представления, Вы регулируете права доступа пользователей к применению операторов UPDATE, DELETE, INSERT и SELECT, но должны помнить, что возможность применения пользователем БД предложения WHERE в операторах потребует предоставления ему таких разрешений, что бы он мог выполнять операторы UPDATE и SELECT.
Другой пример относится к разрешениям на столбцы. Вы можете регулировать применение пользователем операторов UPDATE, SELECT и REFERENCES к конкретному столбцу, но когда пользователь попытается добавить строку в таблицу, подчиняющуюся ограничению FOREIGN KEY, выяснится, что пользователь должен обладать разрешениями для этого столбца для выполнения операторов SELECT или REFERENCES.
Для предоставления разрешений пользователям удобно воспользоваться SQL SEM или оператором GRANT. Для запрещения пользователю неких прав применяется оператор DENY. С помощью оператора REVOKE можно удалить заданные до этого разрешения или снять запрет. Это бывает удобно, когда вы переводите пользователей в новую групп и собираетесь, в силу аддитивности, делегировать разрешения и запрещения пользователям, устанавливая их для группы.

Правила использования учётных записей

Постарайтесь минимизировать использование учётных записей подключения sysadmin и BUILTIN\Adminstrators. Тщательно проанализируйте необходимость и возможность их использования.
Системны администраторы, члены фиксированной роли sysadmin должны егистрироваться под своими логинами, а оставленную в порядке обратной совместимости учётную запись подключения администратора SA старайтесь не использовать никогда.
Если нет необходимости, что бы администраторы NT имели полный доступ к SQLS7, уберите пользователя BUILTIN\Adminstrators (под которым скрывается группа Administrators системы Windows NT) из роли sysadmin.
Ещё одно, широко известное имя учётной записи пользователя, это guest. Если Вам очень необходимо пользоваться учётной записью гостя, примите все меры, что бы через него не возможно было нанести вреда вашим данным и всему, что их обслуживает. Удалите его из тех баз данных, где он не нужен.
Предоставляя права для базы данных через роль public, помните, что членами этой роли являются все пользователи этой базы. Не даром, по умолчанию, она не имеет никаких разрешений.
Если для разграничения прав и разрешений Вам хватит групп Windows NT, старайтесь ограничиваться только ими, при добавлении членов в пользовательские роли. Разрешения старайтесь давать только через пользовательские роли. Роли создавайте по принципу близости набора должностных функций пользователей. Например, сотрудникам одного отдела или сотрудникам, выполняющим одинаковые функции в разных подразделениях (на вскидку: ответственные за имущество).
Старайтесь, чтобы разрешения на создание объектов БД оставалось только за фиксированными ролями баз данных sysadmin, db_owner и db_ddladmin. Лучше, если владельцем всех объектов будет dbo. Это избавит Вас и ваших пользователей от ссылки на владельца объекта (dbo можно опускать). Кроме того, если объект создаёт sysadmin, то владелец получается dbo. Иначе Вам для смены владельца придётся использовать процедуру sp_changeobjectowner. И, кроме того, вы должны для этого быть одним из db_owner, db_ddladmin или членом серверной роли securityadmin.
Вообще, сама процедура смены владельца объекта БД может оказаться кропотливым занятием, если для этого Вы будете вынуждены внести исправления во все пакетные файлы, скрипты, запросы и другие сценарии, где упомянуто это имя владельца. Dbo - же останется собой всегда.

Вспомогательные средства обеспечения безопасности

Использование представлений и хранимых процедур, при условии назначения разрешений к ним, а не к используемым ими объектам, позволяет организовать дополнительный уровень безопасности на уровне приложения. Такой подход, при использовании представлений, скрывает от пользователя изменения, вносимые в данные используемые представлениями. Определив представление, в котором включены только те столбцы, которые должны быть доступны пользователю, мы можем ограничить ему доступ к информации в столбцах таблицы, которая должна быть ему недоступна.
Кроме того, такое применение представлений и хранимых процедур может оказаться эффективней по производительности и с точки зрения удобства администрирования, чем обеспечение безопасности на уровне столбцов.
С помощью предоставления пользователям разрешений на выполнение хранимых процедур, можно добиться того, что пользователям не будет разрешено работать непосредственно с таблицами, но те операции, которые формализованы в самой процедуре.
Использование механизма ролей может помочь разграничить доступ к данным из разных клиентских приложений. Если обеспечить доступ пользователей к данным из конкретного приложения, которое работает через определённую специально для него роль приложения с уникальными правами на доступ к данным, то пользователи смогут выполнять только разрешённые этой роли действия, а другие приложения, не имеющие разрешения на использование этой роли, работать с этими данными не смогут. Однако у такого типа ролей есть определённые особенности. Роли приложений могут быть задействованы пользователем только при запуске приложения и не имеют членов. Т.е. разрешения действительны для пользователя только в момент работы конкретного приложения, которому сопоставлена роль, определяющая набор этих разрешений.
Использование роли приложений потребует ввода пароля. После запуска приложения, с момента вступления в силу разрешений определяемых ролью приложения, пользователь теряет все другие разрешения в текущей базе, за исключением относящихся к роли public. Фактически происходит наследование разрешения роли приложения в текущей базе. Кроме всего прочего, пользователю становится недоступно выполнение оператора USE в текущей базе.
Правда, через учётную запись guest можно получить доступ к информации других баз через ввод полных имён объектов, если таковой предоставлены необходимые разрешения.
Участники ролей db_owner, db_securityadmin и sysadmin с помощью стандартных возможностей SQL SEM или с помощью специальной системной процедуры (sp_addapprole 'роль', 'пароль, который будут вводить пользователи для использования роли') могут создавать роли приложений, запись о которой добавляется в sysusers текущей базы. Удаляет роль sp_dropapprole (если под этой ролью нет активных пользователей), а изменить пароль можно с помощью sp_apprplepassword. Управление разрешений ролей приложений осуществляется из SQL SEM или с помощью операторов GRANT, REVOKE и DENY. Например, DENY SELECT ON 'имя таблицы' TO 'имя роли приложения'.
Для активизации роли приложения подключённого к серверу баз данных клиента через сопоставленное этой роли приложение, необходимо, что бы в этом приложении был выполнен оператор T-SQL sp_setapprole. Выполнить этот оператор из хранимой процедуры или в определённой пользователем транзакции не получится. Синтаксис оператора следующий:

sp_setapprole [@rolename=] 'роль',
   [@password=] {Encrypt N 'пароль'} | 'пароль'
   [,[@encrypt=] 'стиль_шифрования']

Приложение должно уметь подставлять пароль, даже если он зашифрован, например, с использованием функции ODBC ENCRYPT. При шифрации пароля он должен быть преобразован в строку типа Unicode, посредством размещения непосредственно перед ним символа "N".
Роль приложения ограничивает права пользователя только в пределах текущей базы данных. В других базах пользователь сохраняет разрешения, которые ему там установлены.

Вопросы для повторения

ВОПРОС
Какой режим проверки подлинности следует реализовать в среде, в которой пользователи подключаются из операционных систем UNIX и Windows NT? Почему?
ОТВЕТ
Следует использовать смешанный режим, поскольку он поддерживает как проверку подлинности с участием операционной системы Windows NT, так и проверку подлинности сервером SQL Server.
ВОПРОС
Когда назначать разрешения учетной записи пользователя следует непосредственно?
ОТВЕТ
Назначать разрешения непосредственно учетной записи пользователя следует в случаях, когда она отображается в группу Windows NT, для которой требуются стандартные разрешения.
ВОПРОС
Когда следует использовать учетную запись подключения sa?
ОТВЕТ
Учетная запись подключения sa применяется только при установке сервера SQL Server и для совместимости с предыдущими версиями.
ВОПРОС
Если пользователю предоставлено разрешение на обновление таблицы, однако эти разрешения запрещены для роли, членом которой является пользователь, сохранит ли он для себя право обновления таблицы?
ОТВЕТ
Нет. Отказ в разрешении, безусловно, отменяет предоставление прав.