Добавление пользователей базы данных
Пользователь может войти в систему баз данных, используя учетную запись пользователя Windows или регистрационное имя входа в SQL Server. Для последующего доступа и работы с определенной базой данных пользователь также должен иметь учетную запись пользователя базы данных. Для работы с каждой отдельной базой данных требуется иметь учетную запись пользователя именно для этой базы данных. Учетную запись пользователя базы данных можно сопоставить с существующей учетной записью пользователя Windows, группой Windows (в которой пользователь имеет членство), регистрационным именем или ролью.
Управлять пользователями баз данных можно с помощью среды Management Studio или инструкций языка Transact-SQL. Оба эти способа рассматриваются в следующих подразделах.
Управление пользователями базы данных с помощью среды Management Studio
Чтобы добавить пользователя базы данных с помощью среды Management Studio, разверните узел сервера в окне Object Explorer и в нем папку «Databases», в этой папке разверните узел требуемой базы данных, а в ней папку «Security». Щелкните правой кнопкой мыши папку «Users» и в контекстном меню выберите пункт New User. Откроется диалоговое окно Database User — New, в котором следует ввести имя пользователя User name и выбрать соответствующее регистрационное имя Login name:
Здесь можно также выбрать схему по умолчанию для данного пользователя.
Управление безопасностью базы данных посредством инструкций языка Transact-SQL
Для добавления пользователя в текущую базу данных используется инструкция CREATE USER. Синтаксис этой инструкции выглядит таким образом:
Параметр user_name определяет имя, по которому пользователь идентифицируется в базе данных, а в параметре login указывается регистрационное имя, для которого создается данный пользователь. В параметрах cert_name и key_name указываются соответствующий сертификат и асимметричный ключ соответственно. Наконец, в параметре WITH DEFAULT_SCHEMA указывается первая схема, с которой сервер базы данных будет начинать поиск для разрешения имен объектов для данного пользователя базы данных.
Применение инструкции CREATE USER показано в примере ниже:
USE SampleDb; CREATE USER Vasya FOR LOGIN Vasya; CREATE USER Alex FOR LOGIN [ProfessorWeb\Alexandr] WITH DEFAULT_SCHEMA = poco;Для успешного выполнения на вашем компьютере второй инструкции примера требуется сначала создать учетную запись Windows для пользователя Alexandr и вместо домена (сервера) ProfessorWeb указать имя вашего сервера.
В этом примере первая инструкция CREATE USER создает пользователя базы данных Vasya для пользователя Vasya учетной записи Windows. Схемой по умолчанию для пользователя Vasya будет dbo, поскольку для параметра DEFAULT_SCHEMA значение не указано. Вторая инструкция CREATE USER создает нового пользователя базы данных Alex. Схемой по умолчанию для этого пользователя будет схема poco. (Параметру DEFAULT_SCHEMA можно присвоить в качестве значения схему, которая в данное время не существует в базе данных.)
Каждая база данных имеет своих конкретных пользователей. Поэтому инструкцию CREATE USER необходимо выполнить для каждой базы данных, для которой должна существовать учетная запись пользователя. Кроме этого, для определенной базы данных регистрационное имя входа в SQL Server может иметь только одного пользователя базы данных.
С помощью инструкции ALTER USER можно изменить имя пользователя базы данных, изменить схему пользователя по умолчанию или переопределить пользователя с другим регистрационным именем. Подобно инструкции CREATE USER, пользователю можно присвоить схему по умолчанию прежде, чем она создана.
Для удаления пользователя из текущей базы данных применяется инструкция DROP USER. Пользователя, который является владельцем защищаемых объектов (объектов базы данных), удалить нельзя.
Схемы базы данных по умолчанию
Каждая база данных в системе имеет следующие схемы по умолчанию:
Компонент Database Engine позволяет пользователям, которые не имеют учетной записи пользователя, работать с базой данных, используя схему guest. (Каждая созданная база данных имеет эту схему.) Для схемы guest можно применять разрешения таким же образом, как и для любой другой схемы. Кроме этого, схему guest можно удалить из любой базы данных, кроме системных баз данных master и tempdb.
Каждый объект базы данных принадлежит одной, и только одной схеме, которая является схемой по умолчанию для данного объекта. Схема по умолчанию может быть определена явно или неявно. Если при создании объекта его схема по умолчанию не определена явно, этот объект принадлежит к схеме dbo. Кроме этого, при использовании принадлежащей ему базы данных регистрационное имя всегда имеет специальное имя пользователя dbo.
Вся информация о схемах содержится в схеме information_schema. Схема sys, как можно догадаться, содержит системные объекты, такие как представления каталога.
Создание пользователя базы данных
В этой статье описывается создание наиболее распространенных типов пользователей базы данных. Существует 13 типов пользователей. Полный список представлен в статье CREATE USER (Transact-SQL). Все разновидности SQL Server поддерживают пользователей базы данных, но не обязательно все типы пользователей.
Вы можете создать пользователя базы данных с помощью SQL Server Management Studio или с помощью Transact-SQL.
Основные сведения о типах пользователей
Management Studio предоставляет шесть вариантов при создании пользователя базы данных. На следующем рисунке показаны шесть параметров в зеленом поле и указываются значения, которые они представляют.
Выбор типа пользователя
Имя для входа или пользователь, который не сопоставлен с именем для входа
Если вы не знакомы с SQL Server, может быть трудно определить, какой тип пользователя вы хотите создать. Сначала спросите себя, имеется ли имя для входа у пользователя или группы, которым нужен доступ к базе данных? Имена входа в master базе данных являются общими для пользователей, управляющих SQL Server, и для пользователей, которым требуется доступ ко многим или всем базам данных в экземпляре SQL Server. В этой ситуации вы создадите пользователя SQL с именем входа. Пользователь базы данных — это удостоверение входа при подключении к базе данных. Пользователь базы данных может использовать то же имя, что и имя входа, но это не обязательно. В этой статье предполагается, что имя входа уже существует в SQL Server. Дополнительные сведения о создании имени для входа см. в статье Создание логина.
Если у пользователя или группы, которым требуется доступ к базе данных, нет имени входа и если у них есть доступ только к одной или нескольким базам данных, создайте пользователя Windows или пользователя SQL с паролем. Также называется пользователем автономной базы данных. Пользователь автономной базы данных не связан с именем входа в master базу данных. Это отличный выбор, когда вы хотите легко перемещать базу данных между экземплярами SQL Server. Чтобы использовать этот параметр в SQL Server, администратор должен сначала включить содержащиеся базы данных для SQL Server и включить базу данных для хранения. Дополнительные сведения см. в разделе Пользователи автономной базы данных — создание переносимой базы данных.
При подключении в качестве пользователя автономной базы данных необходимо указать имя базы данных в составе строки подключения. Чтобы указать базу данных в СРЕДЕ SQL Server Management Studio (SSMS), в диалоговом окне «Подключение к » выберите пункт «Параметры» и перейдите на вкладку «Свойства подключения».
Выберите пользователя SQL с паролем или пользователем SQL с именем входа на основе имени входа проверки подлинности SQL Server, если пользователь, подключающийся, не может пройти проверку подлинности с помощью проверки подлинности Windows. Это часто происходит, когда пользователи за пределами вашей организации (например, клиенты) подключаются к SQL Server.
Для сотрудников внутри организации лучше выбрать проверку подлинности Windows. Это избавит их от необходимости запоминать еще один пароль. Кроме того, проверка подлинности Windows представляет дополнительные возможности безопасности, такие как Kerberos.
Общие сведения
Пользователь является субъектом безопасности уровня базы данных. Для соединения с базой данных имя входа должно быть сопоставлено с пользователем базы данных. Имя входа может быть сопоставлено с различными базами данных в качестве разных пользователей, но в каждой базе данных ему может быть сопоставлен только один пользователь. В частично автономной базе данных пользователь может быть создан, у него нет имени входа. Дополнительные сведения о пользователях автономной базы данных см. в статье CREATE USER (Transact-SQL). Если гостевой пользователь в базе данных включен, имя входа, которое не сопоставлено с пользователем базы данных, может ввести базу данных в качестве гостевого пользователя.
Как правило, пользователь guest отключен. Не используйте пользователя guest без крайней необходимости.
Как субъектам безопасности, пользователям могут предоставляться разрешения. Областью действия пользователя является база данных. Чтобы подключиться к определенной базе данных в экземпляре SQL Server, необходимо сопоставить имя входа с пользователем базы данных. Разрешения внутри базы данных предоставляются и запрещаются для пользователя базы данных, а не имени входа.
Разрешения
Необходимо разрешение ALTER ANY USER на базу данных.
Создание пользователя с использованием SSMS
- В обозревателе объектов раскройте папку Базы данных .
- Разверните базу данных, в которой создается новый пользователь базы данных.
- Щелкните правой кнопкой мыши папку Безопасность, выберите пункт Создать, а затем Пользователь. .
- В диалоговом окне Пользователь базы данных — создание на странице Общие выберите один из следующих типов пользователей в списке Тип пользователя:
- пользователь SQL с именем для входа
- Пользователь SQL с паролем (если включена содержащаяся база данных)
- Пользователь SQL без имени для входа
- Пользователь, сопоставленный с сертификатом
- Пользователь, сопоставленный с асимметричным ключом
- пользователя Windows
- Когда вы выбираете вариант, остальные параметры в диалоговом окне могут поменяться. Некоторые варианты применимы только к определенным типам пользователей базы данных. Некоторые параметры можно не заполнять. В результате будут использоваться значения по умолчанию. Имя пользователя
Введите имя нового пользователя. Если вы выбрали пользователя Windows из списка типов пользователей, вы также можете выбрать многоточие (. ), чтобы открыть диалоговое окно выбора пользователя или группы. Имя входа
Введите имя входа для пользователя. Кроме того, выберите многоточие (. ), чтобы открыть диалоговое окно выбора входа . Имя входа доступно, если выбрать элемент SQL-пользователь с именем входа или Пользователь Windows в списке Тип пользователя . Пароль и Подтверждение пароля
Введите пароль для пользователей, которым требуется доступ к базе данных. Язык по умолчанию
Укажите язык по умолчанию для пользователя. Схема по умолчанию
Укажите схему, которая будет владеть объектами, созданными пользователем. Кроме того, выберите многоточие (. ), чтобы открыть диалоговое окно «Выбор схемы «. Схема по умолчанию доступна, если выбрать элемент SQL-пользователь с именем входа, SQL-пользователь без имени входаили Пользователь Windows в списке Тип пользователя . Имя сертификата
Укажите сертификат для пользователя базы данных. Кроме того, выберите многоточие (. ), чтобы открыть диалоговое окно «Выбор сертификата «. Имя сертификата доступно, если выбрать элемент Пользователь, сопоставленный с сертификатом в списке Тип пользователя . Имя асимметричного ключа
Введите ключ для пользователя базы данных. Кроме того, выберите многоточие (. ), чтобы открыть диалоговое окно «Выбрать асимметричный ключ«. Имя асимметричного ключа доступно, если выбрать элемент Пользователь, сопоставленный с асимметричным ключом в списке Тип пользователя . - Нажмите ОК.
Дополнительные параметры
Новое диалоговое окно «Пользователь базы данных» также предлагает варианты на четырех других страницах: собственные схемы, членство, защищаемые объекты и расширенные свойства.
- На странице Собственные схемы перечислены все возможные схемы, которые принадлежат новому пользователю базы данных. Чтобы добавить схему для пользователя базы данных или удалить ее, на вкладке Схемы, принадлежащие этому пользователюустановите или снимите флажки для соответствующей схемы.
- На странице Членство приведен список всех ролей, к которым может принадлежать новый пользователь базы данных. Чтобы добавить роль для пользователя базы данных или удалить ее, на вкладке Членство ролей базы данныхустановите или снимите флажки для соответствующей роли.
- На странице Защищаемые объекты перечислены все возможные защищаемые объекты и разрешения на эти объекты, которые могут быть предоставлены для имени входа.
- Страница Расширенные свойства позволяет добавлять пользовательские свойства пользователям базы данных. На этой странице доступны следующие параметры. База данных
Отображает имя выбранной базы данных. Это поле доступно только для чтения. Параметры сортировки
Отображает параметры сортировки, используемые для выбранной базы данных. Это поле доступно только для чтения. Свойства
Просмотрите или укажите расширенные свойства объекта. Каждое расширенное свойство состоит из пары имя/значение метаданных, связанных с объектом. Кнопка с многоточием (. )
Выберите многоточие (. ) после значения, чтобы открыть диалоговое окно «Значение для расширенного свойства«. Введите или просмотрите значение расширенного свойства в этом более просторном окне. Дополнительные сведения см. в разделе Диалоговое окно «Значение для расширенного свойства». Удаление
Удаляет выбранное расширенное свойство.
Создание пользователя с использованием T-SQL
- В обозревателе объектов подключитесь к экземпляру ядра СУБД.
- На панели «Стандартный » выберите «Создать запрос«.
- Скопируйте приведенный ниже пример в окно запроса и нажмите кнопку Выполнить.
-- Creates the login AbolrousHazem with password '340$Uuxwp7Mcxo7Khy'. CREATE LOGIN AbolrousHazem WITH PASSWORD = '340$Uuxwp7Mcxo7Khy'; GO -- Creates a database user for the login created above. CREATE USER AbolrousHazem FOR LOGIN AbolrousHazem; GO Дополнительные сведения см. в статье CREATE USER (Transact-SQL), которая содержит множество дополнительных примеров Transact-SQL.
См. также
- Субъекты (ядро СУБД)
- Создание имени входа
- CREATE LOGIN (Transact-SQL)
Создание нового пользователя на сервере БД
Для создания логина для пользователя с правами администратора БД нужно выполнить следующие шаги.
Запустите программу Microsoft SQL Server Management Studio, подключитесь к серверу и откройте папку «Security\Logins».
Нажмите правую кнопку мыши и выберите пункт «New Login» .
Выберите вкладку «General». В поле «Login Name» введите имя пользователя, под которым требуется подключаться к базе данных. Выберите вариант авторизации «SQL Server authentication». В поле Password задайте пароль для этого логина. Уберите галочку «User must change password at next login». В поле Database нужно выбрать базу данных СИСТЕМЫ. В поле Language выберите язык – русский или оставьте .
Нажмите кнопку «ОК» — пользователь с указанным логином и паролем будет создан.
После создания логинов следующая задача администратора – спуститься на уровень базы данных и создать объекты пользователей базы данных.
Откройте папку — «Databases\\Security\Users». Нажмите правую кнопку мыши и выберите пункт «New User» .
При создании пользователя Вам нужно будет указать:
— Имя пользователя (User name), к которому применяются те же правила, что и для других объектов SQL Server.
— Логин (Login name), который будет назначен пользователю этой базы данных.
— Какие роли базы данных (Role members) будут ему назначены.
— Для администратора выставить флажок – db_owner. Этой роли автоматически предоставляются полные права на базу данных.
— Для остальных ролей (пользователь, оператор, гость) выставить флажки db_datareader и db_datawriter. Эти встроенные роли дают право просматривать и изменять соответственно любую информацию в базе данных .
Создание объекта пользователей базы данных:
Создание и удаление пользователей в Microsoft SQL Server
Всем привет! Сейчас мы с Вами рассмотрим примеры создания и удаления пользователей в СУБД Microsoft SQL Server как с использованием инструкций Transact-SQL, так и с использованием среды Management Studio.
Процесс создания пользователей в MS SQL Server включает два этапа:
- Создание имени входа на SQL Server. Данное имя необходимо, для того чтобы предоставить пользователю возможность подключиться к экземпляру SQL Server;
- Создание пользователя базы данных. В данном случае мы уже предоставляем пользователю разрешения на объекты базы данных.
Примечание! В качестве SQL сервера у меня для примера будет выступать версия Microsoft SQL Server 2012 Express. На данном SQL сервере создана тестовая база данных Test.
Создание имени входа на MS SQL Server
Прежде чем приступать к созданию имени входа на SQL сервер необходимо определиться с методом аутентификации. Существует два варианта:
- Проверка подлинности Windows – это когда имя входа может идентифицировать пользователя как учетную запись Windows или как члена группы Windows (в том числе и доменные учетные записи, и группы);
- Проверка подлинности SQL Server. В данном случае имя входа существует только в SQL Server.
Примечание! Рекомендовано по возможности использовать проверку подлинности Windows. Проверку подлинности SQL Server можно использовать тогда, когда необходимо создать служебное или тестовое имя входа.
Давайте рассмотрим пару примеров создания имени входа на SQL сервер. Сначала мы это сделаем с помощью среды SQL Server Management Studio, а затем с использованием языка Transact-SQL.
Создание имени входа с использованием среды SQL Server Management Studio
Запускаем Management Studio, затем в обозревателе объектов находим пункт «Безопасность», раскрываем его плюсиком, кликаем правой кнопкой мыши по пункту «Имена входа» и выбираем пункт «Создать имя входа».
Далее для примера давайте создадим тестовое имя входа с проверкой подлинности SQL Server. Мы указываем имя входа, придумываем пароль и подтверждаем его. Также мы можем отметить несколько опций, например, использование политики паролей, база данных по умолчанию, язык по умолчанию и другие.
Затем нажимаем на кнопку «ОК», после чего будет создано имя входа TestLogin. По умолчанию данное имя входа будет включено, и оно будет иметь права роли сервера «public».
Создание имени входа с использованием языка Transact-SQL
Для того чтобы создать имя входа на языке Transact-SQL необходимо в Management Studio открыть редактор запросов и выполнить следующую инструкцию (она делает ровно то же самое, что и наши действия выше в графическом интерфейсе Management Studio).
CREATE LOGIN [TestLogin] WITH PASSWORD=N'Pa$$w0rd', DEFAULT_DATABASE=[Test], DEFAULT_LANGUAGE=[русский], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON GO
Другими словами для создания имени входа в SQL сервер используется инструкция CREATE LOGIN.
Создание имени входа на SQL Server с проверкой подлинности Windows
Для того чтобы создать имя входа с проверкой подлинности Windows выполните следующую SQL инструкцию:
CREATE LOGIN [ComputerName\NameUser] FROM WINDOWS WITH DEFAULT_DATABASE=[Test], DEFAULT_LANGUAGE=[русский]; GO
- ComputerName\NameUser – это Имя компьютера\Имя пользователя;
- FROM WINDOWS – указывает, что будет использоваться проверка подлинности Windows;
- WITH DEFAULT_DATABASE=[Test] – база данных по умолчанию;
- DEFAULT_LANGUAGE=[русский] – язык по умолчанию.
Отключение и включение имен входа в MS SQL Server
В случае необходимости Вы можете временно отключить имя входа, для того чтобы пользователю заблокировать доступ к серверу.
--Отключение ALTER LOGIN TestLogin DISABLE; --Включение ALTER LOGIN TestLogin ENABLE;
Создание пользователя базы данных в MS SQL Server
После того как имя входа создано, можно переходить к созданию пользователя базы данных, т.е. сопоставлению пользователя с именем входа.
Давайте создадим пользователя TestLogin также двумя способами, т.е. с помощью Management Studio и языка T-SQL.
Создание пользователя базы данных с помощью Management Studio
Открываем Management Studio, в обозревателе объектов находим нужную базу данных и открываем ее плюсиком. Затем также плюсиком открываем пункт «Безопасность» и кликаем по папке «Пользователи» правой кнопкой мыши и выбираем пункт «Создать пользователя».
Далее вводим имя пользователя и имя входа, которое соответствует данному пользователю (в моем случае имена совпадают), а также указываем схему по умолчанию (если не указывать, то присвоится схема dbo).
Также давайте сразу отметим роль базы данных, которую будет иметь данный пользователь. На странице «Членство» я поставил галочку напротив роли db_datareader, т.е. пользователь будет иметь права на чтение данных из пользовательских таблиц. Жмем «ОК».
Создание пользователя базы данных с помощью языка Transact-SQL
Следующая инструкция T-SQL создает пользователя базы данных (схема по умолчанию dbo) и назначает ему роль db_datareader, т.е. делает то же самое, что и мы чуть ранее в графическом интерфейсе Management Studio.
USE Test GO CREATE USER [TestLogin] FOR LOGIN [TestLogin] WITH DEFAULT_SCHEMA=[dbo] GO ALTER ROLE [db_datareader] ADD MEMBER [TestLogin]; GO
Таким образом, инструкция CREATE USER используется для создания пользователя базы данных.
Удаление пользователя базы данных и имени входа в MS SQL Server
Для того чтобы удалить пользователя базы данных можно написать простую SQL инструкцию, например
DROP USER Testlogin;
Или использовать графический инструмент Management Studio, т.е. в обозревателе объектов, в нужной базе данных выбираем «Безопасность -> Пользователи» и щелкаем правой кнопкой мыши по пользователю, которого необходимо удалить, и выбираем «Удалить».
Примечание! Пользователи, которые владеют защищаемыми объектами, не могут быть удалены из базы данных.
Для удаления имени входа можно также использовать и графический инструмент Management Studio (т.е. «Безопасность -> Имена входа» правой кнопкой мыши по имени, а затем нажать на пункт «Удалить») и инструкцию Transact-SQL т.е.
DROP LOGIN TestLogin;
Примечание! Удалить текущее имя входа нельзя, как и имя входа, владеющее любым защищаемым объектом уровня сервера или заданием агента SQL Server. Также имя входа нельзя удалить, если в данный момент пользователь подключен к системе. Удалить имя входа без удаления сопоставленного пользователя базы данных можно, но это приведет к появлению пользователей, утративших связь с учетными записями.
Заметка! Если Вы хотите освоить язык SQL и T-SQL, рекомендую посмотреть мои видеокурсы по T-SQL.
На этом у меня все надеюсь, материал был Вам полезен, пока!