Права в MS SQL и создание пользователей. CREATE USER, GRANT, REVOKE

На первый взгляд, в MS SQL нестандартная система учетных записей. В ней существуют логины и пользователи.

Логины - это понятие всего сервера SQL. У них могут быть права на какие-то административные задачи (например бэкап). Им можно дать права на какие-то действия с базой или таблицой. Для того что бы у логина были права на базу на основе него создается пользователь. Они могут быть как SQL логином, так и созданные на основе существующих пользователя Windows или AD. Например тот пользователь, который устанавливал MS SQL так же добавился в список логинов. Рекомендуется использовать логины на основе Windows или AD, а не SQL логины.

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

Оба типа учетных записи можно создать как в графике, так и с помощью запроса T-SQL

T-SQL CREATE USER, CREATE LOGIN

Перейдем в базу данных, в которой мы работаем:

USE pubs

Для создания логина SQL выполним:

CREATE LOGIN Anton WITH PASSWORD='Password1910'

Anton - имя логина. Password1910 - пароль. У этого логина уже будут права на вход.

Посмотреть что этот логин создался можно так:

Под номером 3 - это кнопка обновления. Может понадобится т.к. в графике изменения происходят не моментально.

Для создания пользователя привязанного к логину нужно выполнить:

CREATE USER AntonUser FOR LOGIN Anton

Отмечу, что пользователь создасться в той базе, откуда мы выполняем запрос.

Что бы просто создать пользователя нужно:

CREATE USER JustUser WITHOUT LOGIN

Где JustUser - просто пользователь

Созданных пользователей можно увидеть так:

Пользователи создаются без каких либо прав на базу.

T-SQL GRANT

GRANT в пер. "Разрешение" дает права на какое-то действие.

Что бы дать пользователю право получать данные из какой-то таблицы нужно выполнить:

GRANT SELECT ON dbo.authors TO AntonUser

Если мы хотим зайти под созданным пользователем нам нужно выполнить ряд действий. Дело в том, что по умолчанию аутентификация по логинам SQL отключена (разрешена только Windows). Для этого зайдем в свойства сервера:

Затем в "Безопасность" и включи аутентификация SQL

Теперь мы сможем зайти под логином SQL. Для этого сделаем следующее - откроем новое окно запросов Ctr + N или нажмем кнопку:

Важно сделать так, что бы у нас было 2 окошка для запросов до того как мы сменем пользователя. Благодаря этому у нас каждое окно будет работать под разными пользователями

Затем окно подключения пользователя:

Меняем тип подключени на SQL и вводим логин/пароль. Я создавал логин Anton, с паролем Password1910.

После подключения можем увидеть, что одно окно работает под логином Anton, а другое под учетной записью с помощью которой мы проводили операции выше. Так же обратите внимания, что новые пользователь подключен к другой базе (master).

Перейдем к базе, на которую мы давали права. В моем случае это база pubs

USE pubs

Я давал права на SELECT для таблицы dbo.authors. Проверем работу:

SELECT *
FROM dbo.authors

Запрос дожен пройти успешно.

Для другой таблицы, на которую мы не давали прав:

SELECT *
FROM dbo.titles

Получим ошибку

The SELECT permission was denied on the object 'titles', database 'pubs', schema 'dbo'.

Что бы отозвать разрешение на SELECT нужно заменить GRANT на REVOKE (не забудьте переключится на окно у которого есть разрешение или переключится на предыдущего пользователя):

REVOKE SELECT ON dbo.authors TO AntonUser

Разрешений, которые мы можем выдать пользователю достаточно много. Это основные:

Мы можем так же явно запретить пользователю выполнять определенные запросы. Для этого есть DENY:

DENY SELECT ON dbo.authors TO AntonUser

Что ба дать разрешение на создание таблиц:

USE Pubs;
GRANT CREATE TABLE TO Anton;

Теперь создадим логин через графический интерфейс. Нажмем следующие кнопки:

В новом окне мы должны заполнить логин (1) и пароль (3). Под цифрой 2 мы можем увидеть выбор способа аутентификации. В случае Windows мы должны будем выбрать локального пользователя или пользователя AD. Перейдем на закладку Server Roles (4).

Перед нами находятся роли уровня сервера. Все эти роли это просто набор прав. В Microsoft SQL мы так же можем создать и свои группы. Public - роль по умолчанию для всех, кому разрешено вход. Из всех созданных ролей только public можно изменять.

Приведу описание самых популярных ролей:

Перейдем на следующую вкладку (2)

На этой закладке можно сразу создать пользователя и привязать его к базе данных. На этой закладке отображаются 4 базы, которые вы скорее всего раньше не видели. Это системные базы данных, которые хранят в себе транзакции, структуру подключенных баз, скрипты агента и т.д. В области 2 можно дать права на выделенную базу данных.

Коротко о нескольких ролях баз данных:

Роль public, в отличии от всех остальных, можно изменять. Т.е. можно дать роли public разрешение на SELECT и тогда все пользователи с логином смогут это делать. По умолчанию эта ролько может делать запросы типа: