SQL Server: как разрешить схемы?

Вдохновленный различными вопросами, связанными с схемой, которые я видел...

Цепочка прав собственности позволяет мне использовать EXECUTE в хранимой процедуре без явных разрешений на таблицы, которые я использую, если обе хранимые процедуры и таблицы находятся в та же схема.

Если мы используем отдельные схемы, мне нужно будет явно указать GRANT XXX в таблицах разных схем. Этот пример демонстрирует пример цепной привязки. Это означает, что хранимый исполняющий процесс proc пользователь может напрямую читать/записывать ваши таблицы.

Это будет похоже на прямой доступ к вашим переменным экземпляра в классе, минуя getter/seters, прерывая инкапсуляцию.

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

Итак, как мы можем поддерживать разделение схемы и предотвращать прямой доступ к таблице?

Конечно, вопрос не будет применяться, если вы используете ORM или не используете хранимые процедуры. Но я не спрашиваю, должен ли я использовать ORM или хранимую процедуру на случай, если кто-то почувствует необходимость просветить меня...

Изменить, пример

CREATE USER OwnsMultiSchema WITHOUT LOGIN
GO
CREATE SCHEMA MultiSchema1 AUTHORIZATION OwnsMultiSchema
GO
CREATE SCHEMA MultiSchema2 AUTHORIZATION OwnsMultiSchema
GO

CREATE USER OwnsOtherSchema WITHOUT LOGIN
GO
CREATE SCHEMA OtherSchema AUTHORIZATION OwnsOtherSchema
GO

CREATE TABLE MultiSchema1.T1 (foo int)
GO
CREATE TABLE MultiSchema2.T2 (foo int)
GO
CREATE TABLE OtherSchema.TA (foo int)
GO

CREATE PROC MultiSchema1.P1
AS
SELECT * FROM MultiSchema1.T1
SELECT * FROM MultiSchema2.T2
SELECT * FROM OtherSchema.TA
Go
EXEC AS USER = 'OwnsMultiSchema'
GO
--gives error on OtherSchema
EXEC MultiSchema1.P1
GO
REVERT
GO

CREATE PROC OtherSchema.PA
AS
SELECT * FROM MultiSchema1.T1
SELECT * FROM MultiSchema2.T2
SELECT * FROM OtherSchema.TA
Go
GRANT EXEC ON OtherSchema.PA TO OwnsMultiSchema
GO
EXEC AS USER = 'OwnsMultiSchema'
GO
--works
EXEC OtherSchema.PA
GO
REVERT
GO

Изменить 2:

Мы не используем "привязку к базе данных с перекрестными базами данных"
Безопасность уровня строки - это красная селедка и не имеет значения: мы не используем ее везде

Ответ 1

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

"Цепочка прав собственности" просто ссылается на тот факт, что при выполнении хранимой процедуры (или просмотра) на SQL Server текущая исполняемая партия временно приобретает права/разрешения владельца sProc (или владельца схемы sProc) при выполнении этого SQL-код. Таким образом, в случае sProc Пользователь не может использовать эти привилегии для выполнения чего-либо, что код sProc не реализует для них. Обратите внимание, что он никогда не получает Identity владельца, только его права временно (однако, EXECUTE AS... делает это).

Таким образом, типичный подход к обеспечению этого для безопасности заключается в следующем:

Поместите все таблицы данных (и все не связанные с безопасностью представления) в свою собственную схему, позвольте ей [данные] (хотя обычно используется [dbo], потому что он уже существует и слишком привилегирован для Пользовательская схема). Убедитесь, что никакие существующие пользователи, схемы или владельцы не имеют доступа к этой [данные] схеме.
Создайте схему с именем [exec] для всех sProcs (и/или, возможно, любых представлений безопасности). Убедитесь, что владелец этой схемы имеет доступ к схеме [data] (это легко сделать, если вы сделали dbo владельцем этой схемы).
Создайте новую db-роль под названием "Пользователи" и дайте ей EXECUTE доступ к схеме [exec]. Теперь добавьте всех пользователей в эту роль. Убедитесь, что ваши пользователи имеют только права на подключение и не имеют никакого доступа к какой-либо другой схеме, включая [dbo].

Теперь ваши пользователи могут получить доступ к данным только путем выполнения sProcs в [exec]. Они не могут получить доступ к каким-либо другим данным или выполнить любые другие объекты.

Я не уверен, что это отвечает на ваш вопрос (потому что я был не уверен, что вопрос был точно), поэтому не стесняйтесь перенаправлять меня.

Что касается безопасности на уровне строк, вот как я всегда это делаю с помощью схемы безопасности выше:

Я всегда реализую безопасность на уровне строк как серию представлений, которые зеркально обертывают каждую таблицу и сравнивают идентификатор пользователя (обычно с Suser_Sname() или одним из других) в список безопасности с ключом из кода безопасности в самой строке. Это Security-Views.
Создайте новую схему, называемую [rows], дайте ей доступ владельцу к схеме [data] и ничего больше. Поместите все виды безопасности в эту схему.
Отмените доступ владельца [exec] к схеме [data] и вместо этого предоставите ему доступ к данным в схему [rows].

Готово. Теперь безопасность на уровне строк была реализована путем прозрачного перемещения ее между sProcs и таблицами.

Наконец, вот хранимый товар, который я использую, чтобы помочь мне запомнить, как много этого непонятного материала безопасности работает и взаимодействует с ним (oops, исправленная версия кода):

CREATE proc [TestCnxOnly].[spShowProc_Security_NoEX]  as
--no "With Execute as Owner" for this version
--create User [UserNoLogin] without login
--Grant connect on database :: TestSecurity to Guest
--alter database TestSecurity set trustworthy on

--Show current user context:
select current_user as current_
, session_user as session
, user_name() as _name
, suser_name() as [suser (sproc)]
, suser_sname() as sname
, system_user as system_


--Execute As Login = 'UserNoLogin'
select current_user as current_
, session_user as session
, user_name() as _name
, suser_name() as [suser (after exec as)]
, suser_sname() as sname
, system_user as system_

EXEC('select current_user as current_
, session_user as session
, user_name() as _name
, suser_name() as [suser (in Exec(sql))]
, suser_sname() as sname
, system_user as system_')

EXEC sp_ExecuteSQL N'select current_user as current_
, session_user as session
, user_name() as _name
, suser_name() as [suser (in sp_Executesql)]
, suser_sname() as sname
, system_user as system_'

--Revert
select current_user as current_
, session_user as session
, user_name() as _name
, suser_name() as [suser (aftr revert)]
, suser_sname() as sname
, system_user as system_

[EDIT: исправленная версия кода)

Ответ 2

My 2c: Собственность цепочки - это наследие. Это датируется днями, когда не было альтернатив, и по сравнению с сегодняшними альтернативами это небезопасно и грубо.

Я говорю, что альтернатива - это не разрешения схемы, альтернативой является подписание кода. При подписи кода вы можете предоставить необходимые разрешения для подписи процедуры и предоставить широкий доступ к выполнению процедуры, пока доступ к данным жестко контролируется. Подписание кода предлагает более гранулированный и более точный контроль, и его нельзя злоупотреблять, как может быть привязка к целям. Он работает внутри схемы, он работает по схеме, он работает через базу данных и не требует, чтобы открытая огромная дыра в области защиты от перекрестного доступа к базе данных. И это не требует захвата владения объектом для целей доступа: владельцем процедуры может быть любой пользователь.

Что касается второго вопроса о безопасности на уровне строк: безопасность на уровне строк на самом деле не существует в SQL Server версии 2014 и ранее, как функция, предлагаемая движком. У вас есть различные способы обхода проблемы, и эти методы работы работают лучше с подписями кода, чем с цепочкой прав собственности. Поскольку sys.login_token содержит подписи и подписи контекста, вы можете сделать более сложные проверки, чем вы могли бы в контексте цепочки владения.

Начиная с версии 2016 SQL Server полностью поддерживает безопасность уровня строки.

Ответ 3

Вы можете:

Grant Execute On Schema::[schema_name] To [user_name]

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