Пользовательский тип данных SQL Server 2005, база данных tempdb и разрешения

У меня есть БД, работающая на SQL Server 2005 с пользовательским типом данных. Определяемый пользователем тип данных также реализован в базе данных модели, поэтому при создании временной таблицы я могу использовать тот же определяемый пользователем тип данных.

Например, в AppDB я определил этот тип данных:

CREATE TYPE [dbo].[ product_code] FROM [varchar](8) NULL

И то же самое в модели:

CREATE TYPE [dbo].[ product_code] FROM [varchar](8) NULL

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

Тот же вызов хранимой процедуры завершится успешно, если я заменю определяемый пользователем тип данных системным типом данных varchar.

По соображениям безопасности я не хочу добавлять роль сервера sysadmin в систему безопасности приложения - какие у меня есть варианты, если я хочу продолжить использование определяемых пользователем типов данных? Изменились ли эти параметры в SQL Server 2008?

В частности, какие разрешения помимо тех, которые предоставлены роли public, требуются? Ответы, требующие редактирования всех хранимых процедур, будут считаться неотзывчивыми.


user-defined-types sql-server-2005 database-permissions tempdb
person Geoffrey McGrath    schedule 04.03.2012    source источник
comment
Какие разрешения есть у пользователя в AppDB? например могут ли они создать локальную таблицу с этим типом данных?   -  person Aaron Bertrand    schedule 04.03.2012
comment
Кроме того, вы можете найти этот пост в блоге полезным. Документация по вашим типам в конечном итоге доставит гораздо меньше хлопот, чем попытки навязать это с помощью типов псевдонимов. sqlblog. ru / blogs / aaron_bertrand / archive / 2009/10/14 /   -  person Aaron Bertrand    schedule 05.03.2012
comment
Спасибо за ссылку на статью - я отправил ее нашим архитекторам системы для рассмотрения. Я только что добавил пользователя в фиксированную роль db_ddladmin, и проблема решена - правильно ли это? Я полагаю, что это лучше, чем добавлять пользователя к роли системного администратора, но признаю, что мало что знаю об этом.   -  person Geoffrey McGrath    schedule 05.03.2012
comment
Хех, возможно, db_ddladmin излишний - я вижу, что создание хранимых процедур с EXECUTE AS OWNER могло бы иметь больше смысла, согласно этой статье: stackoverflow.com/questions/429051 /   -  person Geoffrey McGrath    schedule 05.03.2012
comment
У меня есть процедура, которая позволяет изменять типы данных, определенные пользователем. Однако это должно быть стандартной функцией SQL Server.   -  person Filip De Vos    schedule 05.03.2012

Ответы (2)


arrow_upward
1
arrow_downward

Проблема с определяемыми пользователем типами данных (также известными как псевдонимы) и tempdb заключается в том, что прежде всего определяемые пользователем типы данных должны быть определены в модели база данных, которая как здесь была сделана правильно. Однако необходимо предоставить разрешение на манипулирование этими объектами - разумный способ сделать это - предоставить db_ddladmin в модели. При перезапуске службы SQL Server созданная tempdb «унаследует» членство в роли db_ddladmin. Если перезапуск SQL Server нежелателен, предоставьте членство в роли db_ddladmin непосредственно tempdb пользователю приложения.

Чтобы программно добавить роль db_ddladmin в модель для пользователя с именем «MyAppUser»:

USE [model]
GO
EXEC sp_addrolemember N'db_ddladmin', N'MyAppUser'
GO

Чтобы быть предельно ясным, фиксированную роль пользователя db_ddladmin не нужно добавлять в базу данных приложения, а только в модель.

person Geoffrey McGrath    schedule 07.03.2012

arrow_upward
-1
arrow_downward

Создавать uddt в tempdb не должно. Если вы хотите сопоставить типы данных ваших временных таблиц, используйте:

select fieldx, fieldy into #temptable from sourcetable

или используйте:

declare @fieldx product_code

select @fieldx into #temptable

Таким образом, размеры полей совпадают и проблем с разрешениями не возникает.

Если вы хотите создать постоянные таблицы в tempdb ... Мой совет - создать конкретную базу данных для этих таблиц.

person Filip De Vos    schedule 04.03.2012