Как предоставить пользователю доступ к представлению, но не к таблице, которую использует представление?

Question

У меня есть следующее представление, определенное в myCustomDatabase:

CREATE VIEW myCustomDatabase.dbo.myView AS
   SELECT job_id  FROM msdb.dbo.sysjobhistory

Владелец myCustomDatabase - sa.

Мой текущий пользователь (называемый currentUser) имеет только db_reader роль в базе данных myCustomDatabase.

Гость включен в базе данных MSDB.

Когда я выполняю представление, я получаю следующую ошибку:

The SELECT permission was denied on the object 'sysjobhistory', database 'msdb', schema 'dbo'.

Я понимаю, что мой текущий пользователь не имеет роли, определенной в системной базе данных.

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

Для возобновления работы следующее представление должно работать при вызове с currentUser:

CREATE VIEW myCustomDatabase.dbo.myView AS
    SELECT job_id  FROM msdb.dbo.sysjobhistory

, но не следующий запрос:

SELECT *  FROM msdb.dbo.sysjobhistory

---

Изменить: просмотр таблиц в MSDB

---

Edit2: моя версия SQLServer 2008

Answer 1

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

1. в обеих базах данных включена опция DB_CHAINING (по умолчанию в msdb)
2. базы данных имеют одного и того же владельца (sa по умолчанию является владельцем msdb)
3. у пользователя есть контекст безопасности в другой базе данных (гостевой режим включен по умолчанию в msdb)

Следовательно, следующий скрипт должен сделать эту работу.

ALTER DATABASE myCustomDatabase SET DB_CHAINING ON;
ALTER AUTHORIZATION ON DATABASE::myCustomDatabase TO sa;

Обратите внимание, что вы должны включить DB_CHAINING в базах данных, принадлежащих sa, только если вы доверяете привилегированным пользователям с разрешениями на создание объектов, принадлежащих dbo. Это не имеет значения, если только члены роли sysadmin могут создавать объекты в любом случае. Кроме того, если старый владелец не является участником роли sysadmin и вам нужен этот логин для сохранения разрешений dbo, добавьте старого владельца как обычного пользователя базы данных и добавьте в роль db_owner.