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

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

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

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

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

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

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

Конечно, вопрос не будет применяться, если вы используете 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:

  • Мы не используем «привязку к базе данных с перекрестными базами данных»,
  • Безопасность уровня строки – это красная селедка и не имеет значения: мы не используем ее везде

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

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

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

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

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

  3. Создайте новую db-роль под названием «Пользователи» и дайте ей EXECUTE доступ к схеме [exec]. Теперь добавьте всех пользователей в эту роль. Убедитесь, что ваши пользователи имеют только права на подключение и не имеют никакого доступа к какой-либо другой схеме, включая [dbo].

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

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


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

  1. Я всегда реализую безопасность на уровне строк в виде серии представлений, которые зеркально обертывают каждую таблицу и сравнивают идентификатор пользователя (обычно с Suser_Sname () или одним из других) в списке безопасности, заключенном в код безопасности в самой строке. Это Security-Views.

  2. Создайте новую схему, называемую [rows], дайте ее владельцу доступ к схеме [data] и ничего больше. Поместите все Security-Views в эту схему.

  3. Отмените доступ владельца [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: исправленная версия кода)

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

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

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

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

Ты можешь:

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

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

Давайте будем гением компьютера.