Невозможно создать хранимые процедуры с помощью EXECUTE AS с использованием Принципа, основанного на сертификате

Я пытаюсь воссоздать сертификат с истекшим сроком действия, который использовался на наших серверах для создания принципов, а затем эти принципы использовались с олицетворением для выполнения хранимых процедур.

Это то, что я делаю (конечно, намного сложнее в производстве, но этот точный тест также терпит неудачу).

Use ReportingDb GO CREATE CERTIFICATE MyCertTest ENCRYPTION BY PASSWORD = 'acrazygoodpassword' WITH SUBJECT = 'Stored procedure signing for Reports' ,EXPIRY_DATE = '11/18/2019'; GO BACKUP CERTIFICATE MyCertTest TO FILE = 'D:\MyCertTest.CER'; GO CREATE USER TestReportUser FROM CERTIFICATE MyCertTest; GO EXEC sp_addrolemember 'db_datareader','TestReportUser'; GRANT AUTHENTICATE TO TestReportUser; GO GRANT EXECUTE TO TestReportUser; GO USE Master; GO CREATE CERTIFICATE MyCertTest FROM FILE = 'D:\MyCertTest.CER'; GO CREATE USER TestReportUser FROM CERTIFICATE MyCertTest; GO EXEC sp_addrolemember 'db_datareader','TestReportUser'; GRANT AUTHENTICATE TO TestReportUser; GRANT EXECUTE TO TestReportUser; GO use ReportingDb GO CREATE PROCEDURE dbo.Reports_DC_Project_sp WITH EXECUTE AS 'TestReportUser' AS SELECT 1 GO 

Не уверен, действительно ли мне нужен мастер-дБ. Все это будет успешным до создания хранимой процедуры, а затем:

Msg 15517, уровень 16, состояние 1, процедура Reports_DC_Project_sp, строка 47
Не может выполняться в качестве принципала базы данных, поскольку основной «TestReportUser» не существует, этот тип принципала не может быть выдан за личность или у вас нет разрешения.

Я также попытался создать хранимую процедуру с помощью EXECUTE AS 'dbo'. Это отлично работает … затем добавляет подпись к хранимой процедуре и, наконец, изменяет хранимую процедуру для выполнения в качестве моего пользователя сертификата. Такая же ошибка на последнем шаге.

Есть ли настройка / шаг, который мне не хватает?

использовать [databasename]

ИДТИ

EXEC sp_changedbowner 'sa'

ИДТИ

Я пытаюсь воссоздать сертификат с истекшим сроком действия, который использовался на наших серверах для создания принципов …

Хм, нет. Даты истечения сертификата не проверяются / не проверяются при создании принципов из них или даже при их использовании с ADD SIGNATURE для подписи модуля.

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

Определенно нет. Этого не происходило, потому что это технически невозможно; Сертификаты и Assymetric Key-основанные принципы не могут использоваться для олицетворения.

Не уверен, действительно ли мне нужен мастер-дБ.

Нет. Он ничего не делает в этом использовании. Если вы хотите связать сервер (например, экземпляр) с правами доступа (даже членство в роли sysadmin ) с сертификатом и каждым подписанным им модулем, то вы должны восстановить сертификат в master как вы показали, но тогда вы создали бы Войдите в этот сертификат и дайте ему соответствующие разрешения. Создание пользователя в master ничего не делает, если в master базе данных нет чего-то определенного, с которым Пользователь должен взаимодействовать. Но что касается вашего теста, это совершенно не имеет значения.

Кроме того, предоставление AUTHENTICATE основанной на сертификате абсолютно бесполезно и бесполезно, поскольку сертификаты на основе сертификатов и асимметричных ключей не могут аутентифицироваться. Вообще. Когда-либо.

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

Даже если бы можно было ВЫПОЛНИТЬ как сертификат или несимметричный ключевой принцип, было бы бессмысленно добавлять подпись перед изменением хранимой процедуры с предложением EXECUTE AS , так как оператор ALTER автоматически аннулировал подпись. Подписи автоматически удаляются при изменении определения модуля или условия EXECUTE AS. Вам нужно будет повторно подписать модуль после инструкции ALTER , поэтому не рекомендуется добавлять подпись заранее.

Есть ли настройка / шаг, который мне не хватает?

Вы не пропустите шаг, так как нет возможности выполнить эту работу. Однако вы понимаете понятия здесь. Олицетворение (через EXECUTE AS ) и подписание модуля (через ADD SIGNATURE ) являются взаимоисключающими. Фактически, подписание модуля в значительной степени заменяет / устаревает олицетворение.

Принципы, основанные на сертификатах и ​​асимметричных ключах (логины и пользователи) не могут быть выданы или аутентифицированы. Следовательно, вы получаете ошибку для (выделено мной):

Не может выполняться в качестве принципала базы данных, поскольку основной «TestReportUser» не существует, этот тип принципала не может быть выдан за личность или у вас нет разрешения.

Это часть того, что делает подписывание модуля более безопасным, чем олицетворение.

Поэтому избавитесь от следующего:

  1. GRANT AUTHENTICATE
  2. все операторы, выполняемые в master
  3. предложение EXECUTE AS

и все будет хорошо.

  • SQL Server - несколько счетчиков в одной проблеме с результатами запроса
  • Получите Min In Time и Max out TIme. Запись посещаемости Fom на сервере Sql
  • Заменить значение строки пустой строкой, если дубликат
  • Самый эффективный способ SELECT строк WHERE ID EXISTS IN во второй таблице
  • Ошибка входа для пользователя «Domain \ UserID». Причина: не удалось найти логин, соответствующий указанному имени.
  • Подсчитать количество пользователей в базе данных определенного возраста на дату рождения
  • Могу ли я создавать индексы в нескольких столбцах в SQL Server 2012?
  • Платежи лицензий на SQL Server 2008 и 2012 для одного разработчика?
  • как вернуть результат выбора при попытке безопасной общей хранимой процедуры, используя sp_executesql
  • Удалите несколько строк, чтобы общее количество групп ниже порога
  • Запрос с столбцом резервных слов, который использовался для работы в SQL Server 2008, больше не работает с SQL Server 2012
  • Interesting Posts

    Почему UPDATE .WRITE () работает только в том случае, если я использую .WRITE (), но не . .WRITE ()?

    SQL select, pad с хронологическими отсутствующими месяцами

    Это неэффективно написанный вложенный курсор. Можете ли вы заменить его более эффективным запросом и объяснить, что случилось с моим?

    Предложение «LIKE» с оператором «AND» в SQL Server 2012

    Как получить суммирование дней в конкретном месяце года в диапазоне

    Идентификация Asp.net: почему кластерный индекс находится в столбце guid (nvarchar) и как это изменить?

    Выберите Timeout With 2 Parameters

    Проблема SQL-SQL с SQL Server 2005: нужна помощь в исключении записей

    Система расписания студентов SQL Database

    Попытка передать недавний идентификатор с областью видимости scope_identity и его ошибку me: Указанный приказ недействителен

    Короткое замыкание SQL / T-SQL

    Рекурсивный запрос COUNT (SQL Server)

    Несколько операторов SQL в одном обратном переходе с помощью Dapper.NET

    Функции и команды SQL Server, которые большинство разработчиков не знают о

    Вычислить размер таблицы кластера Mysql Ndb

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