Цепочки владения в SQL Server

По материалам статьи Amol Kulkarni: Ownership Chains in Yukon

Перевод Виталия Степаненко

Когда объекты базы данных последовательно обращаются друг к другу, такая последовательность называется "цепочкой". Хотя эти цепочки не существуют независимо, когда SQL Server рассматривает ссылки в цепочке, то он оценивает права пользователя на доступ к объектам иначе, чем если бы пользователь обращался к ним по отдельности. Эти различия имеют большое влияние на управление безопасностью.

В этой статье мы рассмотрим, как работают цепочки владения в Yukon. Для демонстрации, мы используем троих пользователей (Mary, John и Scott) и рассмотрим три сценария.

Четыре сценария

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

sp_addlogin 'Mary','mary123','AdventureWorks' GO USE AdventureWorks GO sp_grantdbaccess 'Mary' GO sp_addrolemember 'db_ddladmin','Mary'

Повторите вышеуказанный код для двух других пользователей: John и Scott. (замените в коде Mary на John, а потом на Scott).

Сценарий I

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

Она дает права на выполнение хранимой процедуры другому пользователю, John. Когда John выполняет хранимую процедуру, SQL Server проверяет, что он имеет права на выполнение хранимой процедуры. Т.к. John имеет права на работу с хранимой процедурой, и т.к. хранимая процедура и таблица, на которую ссылается эта процедура, имеют общего владельца, то дополнительная проверка доступа не производится и команда выполняется. Другими словами, когда Mary дала John права на работу с хранимой процедурой, она косвенно дала ему права на работу с таблицей, на которую ссылается хранимая процедура (и которая также принадлежит Mary).

1. Подсоединитесь к серверу как Mary и создайте таблицу и хранимую процедуру.

CREATE TABLE students -- Создание таблицы students (rollno INT ,name VARCHAR(30) ,address VARCHAR(100)) GO INSERT INTO students -- Вставка записей в таблицу students SELECT '101','ABC','LA' UNION SELECT '102','XYZ','LA' UNION SELECT '103','PQR','LA' GO CREATE PROCEDURE stud_sp -- Создание хранимой процедуры stud_sp AS SELECT * FROM students

2. Дайте John права на выполнение хранимой процедуры 'stud_sp'.

GRANT EXECUTE ON stud_sp TO john

3. Теперь подсоединитесь к серверу как John и выполните хранимую процедуру 'stud_sp'.

EXECUTE Mary.stud_sp

Сценарий II

John создает хранимую процедуру, которая ссылается на таблицу, которая ему не принадлежит, но он имеет права на выполнение SELECT из этой таблицы (владелец таблицы - Mary, и она дала John права на таблицу).

John дает Scott права на выполнение хранимой процедуры. Когда Scott выполняет хранимую процедуру, SQL Server проверяет, что он имеет права на выполнение хранимой процедуры. Scott имеет такие права, но т.к. John не является владельцем таблицы, на которую ссылается процедура, то SQL Server проверяет, имеет ли Scott права на эту таблицу. Если нет, то выполнение хранимой процедуры прерывается.

1. Подсоединитесь к серверу как Mary и дайте John права доступа к таблице students.

GRANT SELECT ON students TO john

2. Подсоединитесь к серверу как John и создайте хранимую процедуру 'stud_sp_john'.

CREATE PROCEDURE stud_sp_john AS SELECT * FROM Mary.students

3. Дайте Scott права на выполнение хранимой процедуры 'stud_sp_john'.

GRANT EXECUTE ON stud_sp_john TO Scott

4. Теперь подсоединитесь к серверу как Scott и выполните хранимую процедуру.

EXECUTE John.stud_sp_john

На заметку: это не сработает из-за разорванной цепочки владения

В Yukon хранимая процедура в сценариях 1 и 2 может также выглядеть следующим образом:

CREATE PROCEDURE stud_sp_john WITH EXECUTE AS CALLER AS SELECT * FROM Mary.students

EXECUTE AS CALLER является значением по умолчанию (для обратной совместимости).

Сценарий III

Mary создает хранимую процедуру, которая ссылается на таблицу, владельцем которой Mary не является (владельцем таблицы является John, который дал Mary права на выборку данных из таблицы), но имеет права на выборку данных. Она указывает EXECUTE AS USER = Mary в команде CREATE PROCEDURE.

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

1. Подсоединитесь к серверу как Mary и создайте хранимую процедуру 'stud_sp_mary' с опцией "WITH EXECUTE AS USER = Mary".

CREATE PROCEDURE stud_sp_mary WITH EXECUTE AS USER = Mary AS SELECT * FROM students

2. Теперь дайте Scott права на выполнение 'stud_sp_mary'.

GRANT EXECUTE ON stud_sp_mary TO Scott

3. Подсоединитесь к серверу как Scott и выполните хранимую процедуру 'stud_sp_mary'

EXECUTE Mary.stud_sp_mary

Сценарий IV

EXECUTE AS SELF является сокращенным вариантом опции для текущего пользователя (того, который создает или изменяет процедуру), чтобы определить контекст, в котором этот пользователь хочет выполнять команды процедуры. EXECUTE AS SELF является эквивалентом AS USER = user_name (описано в предыдущем сценарии), где указанный в опции пользователь есть одновременно лицо, создающее или изменяющее процедуру. В каталоге на самом деле хранится ID пользователя, а не значение SELF.

1. Подсоединитесь к серверу как Mary и создайте хранимую процедуру 'stud_sp_mary' с опцией "WITH EXECUTE AS SELF".

CREATE PROCEDURE stud_sp_mary WITH EXECUTE AS SELF AS SELECT * FROM John.items

2. Теперь дайте Scott права на выполнение 'stud_sp_mary'.

GRANT EXECUTE ON stud_sp_mary TO Scott

3. Подсоединитесь к серверу как Scott и выполните хранимую процедуру 'stud_sp_mary'

EXECUTE Mary.stud_sp_mary

Заключение

Используйте EXECUTE AS CALLER в следующих случаях:

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

Используйте EXECUTE AS USER = user_name в следующих случаях:

• Вы хотите, чтобы команды в процедуре выполнялись в контексте определенного пользователя.
• Вы не можете полагаться на цепочку владения (например, если процедура обращается к объектам с разными владельцами), чтобы скрыть нижележащую схему, и хотите избежать выдачи прав доступа к нижележащим объектам.
• Вы хотите создать индивидуальный набор прав доступа. Например, чтобы дать права доступа к операциям DDL, для которых специфические права доступа обычно не могут быть выданы.

Используйте EXECUTE AS SELF в следующих случаях:

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

Amol Kulkarni - сотрудник Tata Consultancy Services (TCS), Hyderabad, India.