Пользователь уровня базы данных

#sql #база_данных #sql_server

У меня на экземпляре SQL Server 2012 зарегистрированно два пользователя: ape и ais2015.
Оба пользователя уровня сервера. ape у меня как администраторская учетка, а ais2015
для пользователя (работает только в режиме для чтения).

Мне необходимо ограничить для ais2015 доступ к таблице coko.or_user.


Какая разница между типом пользователя Пользователь SQL без имени входа и Пользователь
SQL c именем пользователя ? Как вообще используется первый вариант, ведь с помощью
него даже нельзя авторизоваться с помощью SQL Server Management Studio.
Я попытался ограничить доступ к таблице or_user для пользователя ais2015 командами



REVOKE SELECT ON coko.or_user TO ais2015
DENY DELETE ON coko.or_user TO ais2015



но все равно ais2015 имеет доступ к or_user. Видимо потому что эта учетка уровня
сервера. Неважно что будет с ais2015, но как мне создать пользователя, который имел
бы доступ ко всем таблицам базы coko кроме таблицы or_user?
    

Ответы

Ответ 1

Что касается второго пункта, то возможно дело в REVOKE SELECT. REVOKE снимает разрешение,
выданное ранее с помощью GRANT (либо запрещение, установленное с помощью DENY). Если
разрешение/запрещение не делалось, то команда исполняется, но эффекта не имеет. Если
для доступа к объекту разрешение не было выдано ранее с помощью GRANT, то для запрещения
вместо REVOKE следует использовать DENY. Также своим REVOKE SELECT вы могли снять запрещение,
если оно было установлено, сделав доступной выборку из таблицы.

Если делать с нуля, то для достижения того, что вы хотите, у вас должно быть примерно
следующее.

Создаём логин для доступа к серверу и пользователя БД, связанного с этим логином:

USE [master]
GO
CREATE LOGIN [TestLogin] WITH PASSWORD=N'123'
GO
USE [DbName]
GO
CREATE USER [DbUser] FOR LOGIN [TestLogin]
GO


Замечу, что в данном случае имя пользователя БД не совпадает с именем логина к серверу,
но они могут и совпадать (кстати, если создавать логин через диалог в SSMS и делать
ему mapping в пользователя БД, то по-умолчанию так и происходит, в связи с чем может
возникнуть путаница).

Теперь разрешим пользователю чтение данных, но запретим его для выбранного объекта:

USE [DbName]
GO
ALTER ROLE [db_datareader] ADD MEMBER [DbUser]
GO
DENY SELECT ON [coko].[or_user] TO [DbUser]
GO


Также, если требуется, разрешим изменения данных, но запретим их для выбранного объекта:

ALTER ROLE [db_datawriter] ADD MEMBER [DbUser]
GO
DENY UPDATE, INSERT, DELETE ON [coko].[or_user] TO [DbUser]
GO




Что касается первого пункта (пользователи БД без логина):

USE [Test]
GO
CREATE USER [LoginlessUser] WITHOUT LOGIN
GO


то такие пользователи часто используются для имперсонализации, т.е. выполнения действий
кем-то другим от имени такого пользователя.

К примеру, можно создать процедуру, которая будет исполняться от имени определённого
пользователя и делать что-то, что доступно лишь этому пользователю:

CREATE PROCEDURE DoSomething
WITH EXECUTE AS 'LoginlessUser'
AS
BEGIN
    SET NOCOUNT ON;

    -- делаем что-то, что доступно LoginlessUser
END


Другим пользователям можно дать права на исполнение этой процедуры.

Также можно переключать контекст исполнения, для того, чтобы выполнять команды от
имени кого-то:

-- сменим контекст
EXECUTE AS USER = 'LoginlessUser';
GO
-- делаем что-то от имени LoginlessUser
GO
-- вернёмся в свой контекст
REVERT;
GO